MySQL 如何从mysql的一列中选择多条记录（行）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/544750/
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
How to select multiple records (row) from a column in mysql?
提问by roa3
I want to display four (4) items'name from these id: Can I do like this?
我想从这些 id 中显示四 (4) 个项目的名称：我可以这样做吗？
SELECT item_name from items WHERE item_id IN ('001', '012', '103', '500')
or
或者
SELECT item_name from items WHERE item_id = '001' or item_id = '012' or item_id = '103' or item_id = '500'
IN RESPONSE TO ALL ANSWERS
回应所有答案
Well, most of the answers said it works, but it does not really work. Here is my code:
好吧，大多数答案都说它有效，但实际上并没有用。这是我的代码：
$query = "SELECT `item_name` from items WHERE item_id IN('s001','a012','t103','p500')";
$result = mysql_query($query, $conn) or die (mysql_error());
$fetch = mysql_fetch_assoc($result) or die (mysql_error());
$itemsCollected = $fetch['item_name'];
echo $itemsCollected;
The item_idis alphanumeric.
该item_id是字母数字。
回答by Jay
You can do either one, but the IN query is much more efficient for this purpose for any large queries. I did some simple testing long ago that revealed it's about 10 times faster to use the IN construct for this. If you're asking if the syntax is correct then yes, it looks fine, other than missing semi-colons to complete the statement.
您可以执行任一操作，但对于任何大型查询，IN 查询为此目的的效率要高得多。很久以前我做了一些简单的测试，结果表明使用 IN 构造要快 10 倍。如果你问语法是否正确，那么是的，它看起来不错，除了缺少分号来完成语句。
EDIT: It looks like the actual question you were asking was "why do these queries only return one value". Well, looking at the sample code you posted, the problem is here:
编辑：看起来你问的实际问题是“为什么这些查询只返回一个值”。好吧，查看您发布的示例代码，问题就在这里：
$fetch = mysql_fetch_assoc($result) or die (mysql_error());
$itemsCollected = $fetch['item_name'];
echo $itemsCollected;
You need to loop through and iterate until there are no more results to be fetched, as Pax pointed out. See the PHP manual page for mysql_fetch_assoc:
正如Pax 指出的那样，您需要循环并迭代，直到没有更多的结果可供获取。请参阅mysql_fetch_assoc的 PHP 手册页：
$sql = "SELECT item_name from items WHERE item_id IN('s001','a012')";
$result = mysql_query($sql);
if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}
if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
// then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
echo $row["userid"];
echo $row["fullname"];
echo $row["userstatus"];
}
mysql_free_result($result);
回答by paxdiablo
Yes, both those should work fine. What's the actual problem you're seeing?
是的，这两个都应该可以正常工作。你看到的实际问题是什么？
If, as you say, only one record is being returned, try:
如果，如您所说，只返回一条记录，请尝试：
select item_name from items order by item_id
and check the full output to ensure you have entries for 001, 012, 103 and 500.
并检查完整输出以确保您有 001、012、103 和 500 的条目。
If boththose queries only return one row, I would suspect not.
如果这两个查询都只返回一行，我怀疑不会。
If they all do exist, check the table definitions, it may be that the column is CHAR(4) and contains spaces for the others. You may have genuinely found a bug in MySQL but I doubt it.
如果它们都存在，请检查表定义，可能是该列是 CHAR(4) 并且包含其他空格。您可能真的发现了 MySQL 中的错误，但我对此表示怀疑。
After EDIT:
编辑后：
This is a perl/mysql problem, not an SQL one: mysql_fetch_array()returns only onerow of the dataset at a time and advances a pointer to the next.
这是一个 perl/mysql 问题，而不是 SQL 问题：一次只mysql_fetch_array()返回数据集的一行，并将指针前进到下一行。
You need to do something like:
您需要执行以下操作：
$query = "SELECT item_name from items WHERE item_id IN('s001','a012')";
$result = mysql_query($query, $conn) or die (mysql_error());
if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
while ($row = mysql_fetch_assoc($result)) {
echo $row["item_name"];
}
回答by Tanmay
Your ID field must be set to auto increment, i guess. i had problems with that once and i changed the auto increment to int. in the IN field if you pass the parameters to match against the auto increment variable you get back only the first parameter, the remaining generates an error.
我猜您的 ID 字段必须设置为自动递增。我有一次遇到问题，我将自动增量更改为 int。在 IN 字段中，如果您传递参数以匹配自动增量变量，您只会返回第一个参数，其余参数会产生错误。
回答by user1457304
You could also use the mysql_num_rows function to tell you how many rows your query retrieved and then use that result to increment a for loop. An example.
您还可以使用 mysql_num_rows 函数来告诉您查询检索了多少行，然后使用该结果来增加 for 循环。一个例子。
$num_rows=mysql_num_rows($query_results);
for ($i=0; $i <$num_rows ; $i++) {
$query_array[]=mysql_fetch_assoc($query_results);
}
回答by Khalid
Use mysql_fetch_assocto get the query and assign the values from mysql_fetch_assocquery into a an array. Simple as that
使用mysql_fetch_assoc得到的查询，并指定由值mysql_fetch_assoc查询到一个数组。就那么简单
$i=0;
$fullArray = array();
$query = mysql_query("SELECT name FROM users WHERE id='111' OR id='112' OR id='113' ")or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
foreach ($row as $value) {
$fullArray[$i] = $value;
}
$i++;
}
var_dump($fullArray);
echo $fullArray[0]."<br/>".$fullArray[1]."<br/>".$fullArray[2];`
