如何传递PDO参数数组但仍指定其类型? [英] How can I pass an array of PDO parameters yet still specify their types?

问题描述

$sql = "SELECT * FROM table WHERE id LIKE CONCAT('%', :id, '%')
LIMIT :limit1, :limit2";

我仍要像这样使用数组输入:

I want to still use the array input like this:

$stmt->execute($array);

否则，我不能重复使用相同的方法来执行查询.

Otherwise I cannot reuse the same method for executing my queries.

同时，:limit1和:limit2不能正常工作，除非这样输入:

At the same time, the :limit1 and :limit2 doesn't work unless it is put in like this:

$stmt->bindParam(':limit1', $limit1, PDO::PARAM_INT);

我试图同时做这两个，但是不能与bindParams一起执行:

I tried to do both but it doesn't execute with the bindParams:

$stmt->bindParam(':limit2', $limit2, PDO::PARAM_INT);
$stmt->execute($array);

如何解决?

我以为我可以扩展PDOStatement并添加一个新的方法"bindLimit"之类的东西，但是我不知道PDO使用哪种内部方法将参数绑定到变量.

I thought I could extend PDOStatement and add a new method "bindLimit" or something but I can't figure out what internal method PDO uses to bind parameters to a variable.

推荐答案

如果关闭PDO::ATTR_EMULATE_PREPARES的默认设置，则它将起作用.我刚刚发现mysql的默认设置是打开的，这意味着您实际上从未使用过准备好的语句，php在内部为您创建了动态sql，引用了您的值并替换了占位符.是的，主要的wtf.

If you turn off the default setting of PDO::ATTR_EMULATE_PREPARES, then it will work. I just found out that that setting is on by default for mysql, which means you never actually use prepared statements, php internally creates dynamic sql for you, quoting the values for you and replacing the placeholders. Ya, a major wtf.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare($sql);
$stmt->execute(array(5)); //works!

由于性能原因，默认情况下会模拟准备.

The prepares are emulated by default because of performance reasons.

另请参见 PDO MySQL:使用PDO ::是否是ATTR_EMULATE_PREPARES?

这篇关于如何传递PDO参数数组但仍指定其类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！