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.

推荐答案

这与在此问题中提出的问题相同:

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.

但是，您可以使用

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)

或者，作为第三个选项，您可以创建一个用户定义的函数，该函数可以为您拆分以逗号分隔的列表(参见

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屋！