绑定字符串时，将WHERE IN(...)与PDO一起使用不起作用 [英] Using WHERE IN (...) with PDO doesn't work when the string is bound

问题描述

我有一个如下查询:

  UPDATE table SET column = UNIX_TIMESTAMP()
  WHERE id IN (:idString)

idString是用逗号分隔的ID的字符串，并以数组形式传递给execute().令我惊讶的是，执行此查询时，仅更新idString中具有第一个ID的行.

Where idString is a string of comma separated ids and is passed to execute() in an array. To my surprise, when this query is executed, only the row with the first id in idString is updated.

将我的头撞在墙上一会儿之后，我终于决定尝试这样:

After banging my head against the wall for a while, I finally decided to try it like this:

  UPDATE table SET column = UNIX_TIMESTAMP()
  WHERE id IN (' . $idString . ')

第二个查询按预期工作.

The second query works as expected.

当我使用PDO绑定ID字符串时，为什么查询不起作用?

Why won't the query work when I bind the string of ids using PDO?

推荐答案

在SQL中，字符串

'1,2,3,5,12'

是单个值，并将其强制转换为数字上下文，它将只具有前导数字的值，因此只有值1.

Is a single value, and casting it in a numeric context, it will just have the value of the leading digits, so just the value 1.

这与多个值的集合有很大不同:

This is much different from the set of multiple values:

'1', '2', '3', '5', '12'

每当您使用绑定参数时，即使您传递一串用逗号分隔的值，只要参数值变成一个单一值，您传递的值都将变为

Any time you use bound parameters, whatever you pass as the parameter value becomes just one single value, even if you pass a string of comma-separated values.

如果要将一组多个值传递给SQL查询中的参数，则必须具有多个参数占位符:

If you want to pass a set of multiple values to parameters in your SQL query, you must have multiple parameter placeholders:

UPDATE table SET column = UNIX_TIMESTAMP()
WHERE id IN (:id1, :id2, :id3, :id4, :id5)

然后爆炸您的值字符串，并将其作为数组传递:

Then explode your string of values and pass them as an array:

$idlist = array('id1' => 1, 'id2' => 2, 'id3' => 3, 'id4' => 5, 'id5' => 12);
$pdoStmt->execute($idlist);

对于这种情况，我建议使用位置参数而不是命名参数，因为您可以传递一个简单的数组而不是一个关联数组:

For cases like this, I would recommend using positional parameters instead of named parameters, because you can pass a simple array instead of an associative array:

$pdoStmt = $pdo->prepare("UPDATE table SET column = UNIX_TIMESTAMP()
    WHERE id IN (?, ?, ?, ?, ?)");
$idlist = explode(",", "1,2,3,5,12");
$pdoStmt->execute($idlist);

---

@mario添加注释，您可以使用

---

@mario adds a comment that you can use FIND_IN_SET(). That query would look allow you to pass one string formatted as a comma-separated string of values:

$pdoStmt = $pdo->prepare("UPDATE table SET column = UNIX_TIMESTAMP()
    WHERE FIND_IN_SET(id, :idString)");
$pdoStmt->execute(["idString" => "1,2,3,5,12"]);

但是，我通常不建议使用该函数，因为它会破坏使用索引来缩小搜索范围的任何可能性.它实际上必须检查表中的每一行，并且在UPDATE期间意味着必须锁定表中的每一行.

However, I usually don't recommend that function because it spoils any chance of using an index to narrow down the search. It will literally have to examine every row in the table, and during an UPDATE that means it has to lock every row in the table.

这篇关于绑定字符串时，将WHERE IN(...)与PDO一起使用不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！