php MySQL IN 语句的 PDO 绑定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1586587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO binding values for MySQL IN statement
提问by hd82
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.
我有一个 PDO 问题，在被它困扰了很长一段时间后，我真的很想得到答案。
Take this example:
拿这个例子：
I am binding an array of ID's to a PDO statement for use in a MySQL IN statement.
我正在将一组 ID 绑定到 PDO 语句以在 MySQL IN 语句中使用。
The array would be say: $values = array(1,2,3,4,5,6,7,8);
该数组会说：$values = array(1,2,3,4,5,6,7,8);
The database-safe variable would be $products = implode(',' $values);
数据库安全变量将是$products = implode(',' $values);
So, $productswould then be a STRINGwith a value of: '1,2,3,4,5,6,7,8'
因此，$products将是一个值为：'1,2,3,4,5,6,7,8'的字符串
The statement would look like:
该语句将如下所示：
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (:products)
Of course, $productswould be bound to the statement as :products.
当然，$products将绑定到语句: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')
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.
问题是它将 IN 语句中的所有内容作为单个字符串执行，因为我已将其准备为逗号分隔值以绑定到该语句。
What I actually need is:
我真正需要的是：
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.
我实际上可以做到这一点的唯一方法是将值放在字符串本身中而不绑定它们，但是我肯定知道必须有一种更简单的方法来做到这一点。
采纳答案by James McNellis
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 inclause, you'll need to construct the query yourself.
答案是，对于in子句中的可变大小列表，您需要自己构建查询。
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.
但是，您可以使用带引号的逗号分隔列表find_in_set，尽管对于大型数据集，这会对性能产生相当大的影响，因为表中的每个值都必须转换为 char 类型。
For example:
例如：
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.
或者，作为第三种选择，您可以创建一个用户定义的函数，为您拆分逗号分隔的列表（参见http://www.slickdev.com/2008/09/15/mysql-query-real-values- from-delimiter-separated-string-ids/）。这可能是三者中最好的选择，尤其是当您有大量依赖in(...)子句的查询时。
回答by ghbarratt
A good way to handle this situation is to use str_padto place a ?for every value in the SQL query. Then you can pass the array of values (in your case $values) as the argument to execute:
处理这种情况的一个好方法是使用str_pad为?SQL 查询中的每个值放置一个。然后您可以将值数组（在您的情况下$values）作为要执行的参数传递：
$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();
This way you get more benefit from using prepared statements rather than inserting the values directly into the SQL.
这样，您可以从使用准备好的语句中获得更多好处，而不是将值直接插入到 SQL 中。
PS - The results will return duplicate user ids if the products with the given ids share user ids. If you only want unique user ids I suggest changing the first line of the query to SELECT DISTINCT users.id
PS - 如果具有给定 ID 的产品共享用户 ID，结果将返回重复的用户 ID。如果您只想要唯一的用户 ID，我建议将查询的第一行更改为SELECT DISTINCT users.id
回答by Asaph
The best prepared statement you could probably come up with in a situation like this is something resembling the following:
在这种情况下，您可能想出的最好的陈述类似于以下内容：
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (?,?,?,?,?,?,?,?)You would then loop through your values and bind them to the prepared statement making sure that there are the same number of question marks as values you are binding.
然后，您将遍历您的值并将它们绑定到准备好的语句，确保有与您绑定的值相同数量的问号。
回答by ashsam786
you need to provide same number of ?sin INas the number of values in your $valuesarray
您需要在IN 中提供与$values数组中的值数量相同数量的?s
this can be done easily by creating an array of ?s as
这可以通过创建一个 ?s 数组轻松完成
$in = join(',', array_fill(0, count($values), '?'));
and use this $in array in your IN clause
并在您的 IN 子句中使用此 $in 数组
THis will dynamically provide you with tailor made array of ?sas per your changiing $values array
这将根据您不断变化的 $values 数组动态地为您提供定制的?s数组
回答by fyrye
You can do so very easily. If you have an array of values for your IN() statement EG:
你可以很容易地做到这一点。如果您的 IN() 语句 EG 有一组值：
$test = array(1,2,3);
You can simply do
你可以简单地做
$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);
回答by Vunse
If the expression is based on user input without binding the values with bindValue(), experimental SQL might not be a great choice. But, you can make it safe by checking the syntax of the input with MySQL's REGEXP.
如果表达式基于用户输入而不使用 bindValue() 绑定值，那么实验性 SQL 可能不是一个很好的选择。但是，您可以通过使用 MySQL 的 REGEXP 检查输入的语法来确保安全。
For example:
例如：
SELECT *
FROM table
WHERE id IN (3,156)
AND '3,156' REGEXP '^([[:digit:]]+,?)+$'
回答by Xeoncross
Here is an example of binding an unknown number of record columns to values for an insert.
这是将未知数量的记录列绑定到插入值的示例。
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' => '[email protected]'));
回答by Kopendrex
Please try like this:
请尝试这样：
WHERE products IN(REPLACE(:products, '\'', ''))
Regards
问候
