MySQL IN 语句的 PDO 绑定值 [英] PDO binding values for MySQL IN statement

问题描述

我对 PDO 有一个问题，在被它困扰了很长一段时间后，我真的很想得到答案.

I have an issue with PDO that I'd really like to get an answer for after being plagued by it for quite some time.

以这个例子为例:

我正在将一组 ID 绑定到 PDO 语句以在 MySQL IN 语句中使用.

I am binding an array of ID's to a PDO statement for use in a MySQL IN statement.

数组会说:$values = array(1,2,3,4,5,6,7,8);

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

因此，$products 将是一个 STRING，其值为:'1,2,3,4,5,6,7,8'

So, $products would then be a STRING with a value of: '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 绑定到语句.

Of course, $products would be bound to the statement as :products.

然而，当语句被编译并绑定值时，它实际上看起来像这样:

However, when the statement is compiled and values bound, it would actually look like this:

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 语句中的所有内容作为单个字符串执行，因为我已将其准备为以逗号分隔的值来绑定到该语句.

The problem is it is executing everything inside of the IN statement as a single string, given that I've prepared it as comma-separated values to bind to the statement.

我真正需要的是:

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

我实际上可以做到这一点的唯一方法是将值放在字符串本身中而不绑定它们，但是我肯定知道必须有一种更简单的方法来做到这一点.

The only way I can actually do this is by placing the values within the string itself without binding them, however I know for certain there has to be an easier way to do this.

推荐答案

这与在此问题中提出的问题相同:我可以将数组绑定到 IN() 条件吗?

This is the same thing as was asked in this question: Can I bind an array to an IN() condition?

答案是，对于 in 子句中的可变大小列表，您需要自己构建查询.

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

但是，您可以使用 find_in_set，但对于大型数据集，这会对性能产生相当大的影响，因为表中的每个值都必须进行转换到字符类型.

However, you can use the quoted, comma-separated list using find_in_set, though for large data sets, this would have considerable performance impact, since every value in the table has to be cast to a char type.

例如:

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(...) 子句的查询时.

Or, as a third option, you could create a user defined function that splits the comma-separated list for you (cf. http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/). This is probably the best option of the three, especially if you have a lot of queries that rely on in(...) clauses.

这篇关于MySQL IN 语句的 PDO 绑定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！