php PDO::PARAM_INT 在 bindParam 中很重要吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19751360/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO::PARAM_INT is important in bindParam?
提问by Jarek Kowol
Add PDO::PARAM_INTor PDO::PARAM_STRhave any meaning in Mysql query?
在Mysql查询中添加PDO::PARAM_INT或者PDO::PARAM_STR有什么意义？
$sql = 'SELECT TagId FROM tagthread WHERE ThreadId = :ThreadId';
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);
$stmt->execute();
采纳答案by bitWorking
Yes, use it.
是的，使用它。
I did a few tests (with PDO::ATTR_EMULATE_PREPARES false) and I found out that the quotes around the values will be different.
我做了一些测试（使用 PDO::ATTR_EMULATE_PREPARES false），我发现值周围的引号会有所不同。
When you bind an integer value with PARAM_INTthere will be no quotes in the query (A string value with PARAM_INT has quotes). If you bind an integer value with PDO::PARAM_STRthere will be quotes and mysql has to cast to integer.
当您绑定一个整数值时，PARAM_INT查询中将没有引号（带有 PARAM_INT 的字符串值有引号）。如果你绑定一个整数值，PDO::PARAM_STR就会有引号，mysql 必须转换为整数。
Examples:
例子：
$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);
$threadid = 123;
// SELECT TagId FROM tagthread WHERE ThreadId = 123
$threadid = '123test';
// SELECT TagId FROM tagthread WHERE ThreadId = '123test'
// mysql will cast 123test to 123
EDIT:
编辑：
I further tested and read on that topic. Conclusion: Implicit casting is dangerous and can lead to unexpected results.Read more on that here. Another disadvantage to always use PDO::PARAM_STRis the performance. Read more on performance Disadvantages of quoting integers in a Mysql query?
我进一步测试并阅读了该主题。结论：Implicit casting is dangerous and can lead to unexpected results.在此处阅读更多相关信息。始终使用的另一个缺点PDO::PARAM_STR是性能。阅读有关在 Mysql 查询中引用整数的性能缺点的更多信息？
So if your column is of type [TINY|SMALL|MEDIUM|BIG]INTthan use PARAM_INT. And in case it is a LIMITclause than cast to integer if the variable type in PHP is not integer.
因此，如果您的列的类型[TINY|SMALL|MEDIUM|BIG]INT比使用PARAM_INT. LIMIT如果 PHP 中的变量类型不是整数，则它是一个子句，而不是强制转换为整数。
回答by tfont
Edit: Depends! See Your Common Sensecomment below.
编辑：取决于！请参阅下面的您的常识评论。
If the value is a integer it should be treated as an integer. Apply this with as many datatypes as possible.
如果该值为整数，则应将其视为整数。将其应用于尽可能多的数据类型。
If you don't set the Attribute of PDO::ATTR_EMULATE_PREPARES to false, you will get a nasty error.
如果你没有将 PDO::ATTR_EMULATE_PREPARES 的 Attribute 设置为 false，你会得到一个严重的错误。
Solid example:
坚实的例子：
$stmt = $dbh->prepare("SELECT * FROM table123 WHERE raw_field = :field LIMIT 1 OFFSET :offset;");
$stmt->bindParam(':field', $field);
$stmt->bindParam(':offset', $offset);
if ($map_stmt->execute())
{
$data = stmt->fetch(PDO::FETCH_ASSOC);
}
else
{
echo 'Error :';
echo '<pre>';
print_r($map_stmt->errorInfo());
print_r($map_stmt->debugDumpParams());
echo '</pre>';
}
Will return back a nasty error containing:
将返回一个令人讨厌的错误，其中包含：
Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0'' at line 1
Query: SELECT * FROM table123 WHERE raw_field = 'home' LIMIT 1 OFFSET '0'
错误代码：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“0”附近使用的正确语法
查询：SELECT * FROM table123 WHERE raw_field = 'home' LIMIT 1 OFFSET '0'
Useless you treat it as an integer, and it will remove the string (e.g.: ' ').
没用你把它当作一个整数，它会删除字符串（例如：''）。
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
In a nutshell:
简而言之：
You choose! Strict data or not..
你选！严格的数据与否..
回答by Your Common Sense
I cannot tell for all the drivers supported by PDO, but for mysql it's ok not to use PDO::PARAM_INTmost of time.
我无法确定 PDO 支持的所有驱动程序，但对于 mysql，PDO::PARAM_INT大部分时间都可以不使用。
Therefore, it makes no sense to bloat your code with numerous bindParamcalls. As a rule, just send your variables directly into execute():
因此，通过大量bindParam调用使代码膨胀是没有意义的。通常，只需将您的变量直接发送到execute()：
$sql = 'SELECT TagId FROM tagthread WHERE ThreadId = ?';
$stmt = $this->db->prepare($sql);
$stmt->execute([$threadid]);
Here your $threadidvariable will be silently bound as a string, but it will make not a single problem for mysql to compare it with integer value stored in database. In reality, everyone does it this way and never has any problem.
在这里，您的$threadid变量将被静默绑定为字符串，但是 mysql 将其与存储在数据库中的整数值进行比较不会成为一个问题。实际上，每个人都是这样做的，从来没有任何问题。
The problem with string type bindnig in LIMITclause can be easily solved by switfhing the emulation mode OFF.
字符串类型 bindnig inLIMIT子句的问题可以通过关闭仿真模式轻松解决。
Note that PDO::PARAM_INTdoesn't cast your value. Means if you're trying to bind a string type value using this mode, it will be bound as a stringnevertheless, even if you explicitly set type to PDO::PARAM_INT. This mode will be actually applied only for integer values.
请注意，PDO::PARAM_INT这不会投射您的价值。意味着如果您尝试使用此模式绑定字符串类型值，它仍将绑定为字符串，即使您明确将 type 设置为PDO::PARAM_INT. 此模式实际上仅适用于整数值。
There are few edge cases where you may want to bind an integer explicitly though:
不过，在极少数情况下，您可能希望显式绑定整数：
- peculiar column types, like
BIGINTorBOOLEANthat require an operand of exact type to be bound (note that in order to bind a BIGINT value with PDO::PARAM_INT you need a mysqlnd-based installation). - some DBAs claim that complex queries with non-trivial query plan can be affected by a wrong operand type. though noone provided a verifiable example yet
- 特殊的列类型，例如
BIGINT或BOOLEAN需要绑定确切类型的操作数（请注意，为了将 BIGINT 值与 PDO::PARAM_INT 绑定，您需要基于 mysqlnd 的安装）。 - 一些 DBA 声称具有非平凡查询计划的复杂查询可能会受到错误的操作数类型的影响。虽然还没有人提供可验证的例子
All other issues are common for the loose typing and neither mysql nor PDO binding has any special effect in them.
所有其他问题对于松散类型都是常见的，mysql 和 PDO 绑定对它们都没有任何特殊影响。
Also, to avoid possible problems you should choose right column types for your data.Say, for big integers you should use BIGINT, while for any price-like data it have to be DECIMAL. And there will be not a single issue with comparison.
此外，为避免可能出现的问题，您应该为数据选择正确的列类型。比如说，对于大整数，您应该使用BIGINT，而对于任何类似价格的数据，它必须是DECIMAL。并且不会有任何比较的问题。
