如何传递 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屋！