MySQL IN语句的PDO绑定值

Question

我一直被PDO困扰着，我想解决这个问题。

拿这个例子来说:

我正在将一个ID数组绑定到PDO语句中，用于MySQL IN语句。

假设数组为: $values = array(1,2,3,4,5,6,7,8);

安全的数据库变量为$products = implode(',', $values);

因此，$products现在是一个值为'1,2,3,4,5,6,7,8'的字符串。

语句如下:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (:products)

当然，$products将绑定到语句中的:products。

但是，在编译语句并绑定值时，它实际上会像这样：

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN ('1,2,3,4,5,6,7,8')

问题在于它将IN语句中的所有内容作为单个字符串执行，尽管我已经将其准备为逗号分隔的值以绑定到语句。

实际上我需要的是:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (1,2,3,4,5,6,7,8)

我唯一能够做到的是直接将值放在字符串中而不进行绑定，但我确信一定有更简单的方法可以实现这一点。

- hd82

Answer 1

这与以下问题相同：我可以将数组绑定到IN()条件吗？

那里的答案是，对于in子句中的变量大小列表，您需要自己构造查询。

但是，您可以使用带引号、逗号分隔的列表，使用find_in_set，不过对于大型数据集，这将具有相当大的性能影响，因为表中的每个值都必须转换为char类型。

例如：

select users.id
from users
join products
on products.user_id = users.id
where find_in_set(cast(products.id as char), :products)

或者，作为第三个选项，您可以创建一个用户定义的函数来为您拆分逗号分隔的列表（参见http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/）。如果您有很多查询依赖于in(...)子句，这可能是三个选项中最好的选择。

- James McNellis

---

PDO 是一个非常不错的库，但缺少一些最基本的功能。有点遗憾... - hd82

如果你有一小组数据，你可能可以使用find_in_set（我已经更新了答案并提供了一个例子），或者你可以尝试使用用户定义的函数来解析逗号分隔的列表。（抱歉不断添加想法，哈哈；在我发布了链接到另一个问题之后，我想，“一定有办法做到这一点...”） - James McNellis

感谢 FIND_IN_SET 的提示，真的救了我的一天。但我发现它可以在不进行 CAST 的情况下工作，所以 find_in_set(products.id, :products) 就足够了。 - Andy

一个非常有用的答案。你能否在你所链接的问题中添加这个答案的一个版本？因为它得到了更多的投票，更有可能出现在搜索结果的顶部。 - Blazemonger

我只想补充一点：当你将参数:products绑定到字符串时，它不能有任何空格，例如1,2,3,4是正确的，但1, 2, 3, 4是不正确的。 - R Picheta

Answer 2

处理这种情况的一个好方法是使用str_pad来为SQL查询中的每个值放置一个?。然后，您可以将值数组（在您的情况下是$values）作为参数传递给execute函数。

$sql = '
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products.id IN ('.str_pad('',count($values)*2-1,'?,').')
';

$sth = $dbh->prepare($sql);
$sth->execute($values);
$user_ids = $sth->fetchAll();

这样使用预编译语句比直接将值插入SQL语句中能获得更多的好处。

PS - 如果具有给定ID的产品共享用户ID，则结果将返回重复的用户ID。如果您只想要唯一的用户ID，我建议将查询的第一行更改为SELECT DISTINCT users.id。

- ghbarratt

---

@Nalum：因为str_pad的第二个参数是“填充长度”，它是以字符为单位的。既然你想要每个值都有一个问号并且它们之间有逗号，那么结果就是count($values)*2-1。如果你愿意，也可以使用类似于str_repeat('?,',count($values)-1).'?'的东西。 - ghbarratt

我更喜欢使用 implode(',', array_fill(1,count($values),'?'))，因为这样可以减少出现偏移错误的可能性，而且你不必特殊处理最后一个元素。 - Bill Karwin

你也可以使用str_repeat()来实现，这可能是列出的方法中最有效的：rtrim(str_reapeat('?,', count($values)), ',')。 - ivanhoe

将变量直接放在查询中什么时候是一个好主意？我们真的为了SQL注入对策而奋斗，只为了支持一个提倡不应该做的解决方案，得到17票吗？ - Dominique

@Dominique 你误读了代码。它并不会直接将变量放在查询中，这正是该解决方案的重点。它为每个变量放置一个“？”，以便PDO可以准备实际的语句。是的，我们喜欢PDO的保障措施，而这个解决方案提供了一种使用它们的方法。 - ghbarratt

Answer 3

在这种情况下，你可能想到的最佳预处理语句是类似于以下内容：

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (?,?,?,?,?,?,?,?)

然后，您需要循环遍历您的值，并将它们绑定到准备好的语句上，确保问号的数量与您绑定的值的数量相同。

- Asaph

---

当你知道要绑定的变量数量时，逻辑解决方案很简单，但当有不确定数量的参数时，情况会变得有些混乱。 - hd82

@hd82：那么，你需要巧妙地结合sprintf和count。 - Boldewyn

Answer 4

您需要在IN中提供与您的$values数组中的值数量相同的?s

这可以通过创建一个由?s组成的数组轻松完成

 $in = join(',', array_fill(0, count($values), '?'));

并在您的IN子句中使用此 $in 数组。

这将根据您不断更改的 $values 数组动态为您提供定制的?s数组。

- ashsam786

Answer 5

您可以很容易地这样做。 如果您有一个用于IN（）语句的值数组 例如：

$test = array(1,2,3);

您可以简单地执行以下操作

$test = array(1,2,3);
$values = count($test);
$criteria = sprintf("?%s", str_repeat(",?", ($values ? $values-1 : 0)));
//Returns ?,?,?
$sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria);
//Returns DELETE FROM table where column NOT IN(?,?,?)
$pdo->sth = prepare($sql);
$pdo->sth->execute($test);

- fyrye

---

这已经在Asaph的答案中提到过了，如果你正在使用命名参数，它是不起作用的，因为你不能混合使用它们。 - cincodenada

Answer 6

如果表达式基于用户输入而没有使用bindValue()绑定值，那么实验性的SQL可能不是一个很好的选择。 但是，您可以通过使用MySQL的REGEXP检查输入的语法来使其更安全。

例如：

SELECT *
FROM table
WHERE id IN (3,156)
AND '3,156' REGEXP '^([[:digit:]]+,?)+$'

- Vunse

Answer 7

这里是一个绑定未知数量记录列到插入值的示例。

public function insert($table, array $data)
{
    $sql = "INSERT INTO $table (" . join(',', array_keys($data)) . ') VALUES ('
        . str_repeat('?,', count($data) - 1). '?)';

    if($sth = $this->db->prepare($sql))
    {
        $sth->execute(array_values($data));
        return $this->db->lastInsertId();
    }
}

$id = $db->insert('user', array('name' => 'Bob', 'email' => '[e-mail]'));

- Xeoncross

Answer 8

请尝试像这样：

WHERE products IN(REPLACE(:products, '\'', ''))

敬礼

- Kopendrex

---

这个问题在5年前已经被提出和回答过了，但是此回答并没有解决问题。 - OGHaza