PDO :: PARAM_INT在bindParam中很重要吗? [英] PDO::PARAM_INT is important in bindParam?

问题描述

在MySQL查询中添加PDO::PARAM_INT或PDO::PARAM_STR有什么意义?

Add PDO::PARAM_INT or PDO::PARAM_STR have any meaning in Mysql query?

$sql  = 'SELECT TagId FROM tagthread WHERE ThreadId = :ThreadId';

$stmt = $this->db->prepare($sql);
$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);

$stmt->execute();

推荐答案

是的，请使用它.

我做了一些测试(使用PDO :: ATTR_EMULATE_PREPARES false)，发现这些值周围的引号会有所不同.

I did a few tests (with PDO::ATTR_EMULATE_PREPARES false) and I found out that the quotes around the values will be different.

当您使用PARAM_INT绑定整数值时，查询中将没有引号(带有PARAM_INT的字符串值带有引号).如果您用PDO::PARAM_STR绑定一个整数值，将使用引号，并且mysql必须转换为整数.

When you bind an integer value with PARAM_INT there will be no quotes in the query (A string value with PARAM_INT has quotes). If you bind an integer value with PDO::PARAM_STR there will be quotes and mysql has to cast to integer.

示例:

$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

我进一步测试并阅读了该主题.结论:Implicit casting is dangerous and can lead to unexpected results.进一步了解这里.始终使用PDO::PARAM_STR的另一个缺点是性能.了解有关性能的更多信息在Mysql查询中引用整数的缺点?

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_STR is the performance. Read more on performance Disadvantages of quoting integers in a Mysql query?

因此，如果您的列的类型为[TINY|SMALL|MEDIUM|BIG]INT，则请使用PARAM_INT.如果它是LIMIT子句，并且如果PHP中的变量类型不是整数，则强制转换为整数.

So if your column is of type [TINY|SMALL|MEDIUM|BIG]INT than use PARAM_INT. And in case it is a LIMIT clause than cast to integer if the variable type in PHP is not integer.

这篇关于PDO :: PARAM_INT在bindParam中很重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！