php 一个准备好的语句，`WHERE .. IN(..)` 查询和排序——使用 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3703180/
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
A prepared statement, `WHERE .. IN(..)` query and sorting — with MySQL
提问by kolypto
Imagine we have a query:
假设我们有一个查询：
SELECT * FROM somewhere WHERE `id` IN(1,5,18,25) ORDER BY `name`;
and an array of IDs to fetch: $ids = array(1,5,18,25)
以及要获取的 ID 数组： $ids = array(1,5,18,25)
With prepared statements it's advicedto prepare one statement and call it multiple times:
对于准备好的语句，建议准备一个语句并多次调用它：
$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id`=?;');
foreach ($ids as $id){
$stmt->bind_params('i', $id);
$stmt->exec();
}
But now I'll have to sort the results manually. Do I have any nice alternatives?
但现在我必须手动对结果进行排序。我有什么好的选择吗？
回答by sled
you could do it this way:
你可以这样做：
$ids = array(1,5,18,25);
// creates a string containing ?,?,?
$clause = implode(',', array_fill(0, count($ids), '?'));
$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');
call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();
// loop through results
Using this you're calling bind_param for each id and you have sorting done by mysql.
使用它，您可以为每个 id 调用 bind_param，并且您已经通过 mysql 完成了排序。
回答by Fabian N.
Had the same problem and in addition to the answer of @sled 7 years ago, here is a possibility without making the call_user_func_array(array($stmt, 'bind_param'), $ids);step, but only call bind_params once:
遇到了同样的问题，除了 7 年前@sled 的答案外，还有一种可能无需执行此call_user_func_array(array($stmt, 'bind_param'), $ids);步骤，但只调用一次 bind_params：
$ids = array(1,5,18,25);
// creates a string containing ?,?,?
$bindClause = implode(',', array_fill(0, count($ids), '?'));
//create a string for the bind param just containing the right amount of iii
$bindString = str_repeat('i', count($ids));
$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $bindClause . ') ORDER BY `name`;');
$stmt->bind_params($bindString, ...$ids);
$stmt->execute();
回答by Martin J.
I believe this is the simplest possible answer :
我相信这是最简单的答案：
$ids = [1,2,3,4,5];
$pdos = $pdo->prepare("SELECT * FROM somwhere WHERE id IN (:"
. implode(',:', array_keys($ids)) . ") ORDER BY id");
foreach ($ids as $k => $id) {
$pdos->bindValue(":". $k, $id);
}
$pdos->execute();
$results = $pdos->fetchAll();
So long your array of Ids does not contain keys or keys with illegal characters, it wil work.
只要您的 Id 数组不包含键或带有非法字符的键，它就会起作用。
回答by kolypto
I'll add an ultimately slow & ugly solution which nevertheless uses prepared statements for ANY number of array items :) 3 statements are universal for any case and can be reused everywhere.
我将添加一个最终缓慢且丑陋的解决方案，该解决方案仍然对任意数量的数组项使用准备好的语句:) 3 个语句在任何情况下都是通用的，并且可以在任何地方重复使用。
CREATE TEMPORARY TABLEids(idINT );INSERT INTOidsVALUES(?);this will insert your IDsSELECTidFROMidsLEFT JOIN .... ;use data from other tables to sort theidslistSELECTidFROMids;select everything back
CREATE TEMPORARY TABLE(身号码INT );INSERT INTOidsVALUES(?);这将插入您的 IDSELECTidFROMidsLEFT JOIN .... ;使用来自其他表的数据对ids列表进行排序SELECTidFROMids;选择一切回来
Otherwise you'll have to use IN (?,?,?,....or sort the rows manually. The best idea is to use simple MySQL-queries, or, try to get the list of IDs already sorted in the way you like.
否则，您将不得不IN (?,?,?,....手动使用或对行进行排序。最好的想法是使用简单的 MySQL 查询，或者尝试获取已按您喜欢的方式排序的 ID 列表。
回答by Ryan Schumacher
An alternative would be to use PHP usort function on the result object, but this is "manual."
另一种方法是在结果对象上使用 PHP usort 函数，但这是“手动的”。
See this: Sort Object in PHP
请参阅： 在 PHP 中对对象进行排序
回答by Tomasz Kowalczyk
No, it's not recommended, if you are to fetch certain records from the database using ORDER BYclause.
不，如果您要使用ORDER BY子句从数据库中获取某些记录，则不建议这样做。
回答by zeros-and-ones
Have you considered rewriting you original query using a JOIN and WHERE clause to get the IDS you need to avoid the need for a WHERE IN clause? I came here with the same question and after reviewing the possible solutions I realized an INNER JOIN was my solution.
您是否考虑过使用 JOIN 和 WHERE 子句重写原始查询以获得所需的 IDS 以避免需要 WHERE IN 子句？我带着同样的问题来到这里，在查看了可能的解决方案后，我意识到 INNER JOIN 是我的解决方案。
