MySQL 列数据作为逗号分隔列表返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10987154/
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
MySQL column data returned as comma delimited list
提问by Delos Chang
I currently have a MySQL table like:
我目前有一个 MySQL 表，如：
id | friend
1 | 2
1 | 5
1 | 10
3 | 6
15 | 19
21 | 4
I'm trying to grab all the friend id's of one particular user and arrange them into a comma-delimited list. For example, grabbed user1's friends, it would return as
我试图获取一个特定用户的所有朋友 ID，并将它们排列成一个逗号分隔的列表。比如抓取user1的好友，返回为
$friend_list = 2,5,10
Currently, I have:
目前，我有：
$sql = "SELECT friend FROM table__friends WHERE id = ".$user_id;
This only grabs one row though..please help!
虽然这只能抓住一排..请帮忙！
Thanks
谢谢
回答by GDP
You want to use GROUP_CONCAT :
你想使用 GROUP_CONCAT ：
$sql = "SELECT GROUP_CONCAT(friend) FROM table__friends GROUP BY id HAVING id = ".$user_id;
Adjusted for correctness per the better answer.
根据更好的答案调整正确性。
回答by WojtekT
$sql = "SELECT GROUP_CONCAT (DISTINCT friend SEPARATOR ',')
FROM table_friends GROUP BY id
HAVING id =".$user_id;
回答by We Are All Monica
Your query will return more than one row - it sounds like you're only reading the first row. You want something like this instead:
您的查询将返回不止一行 - 听起来您只是在阅读第一行。你想要这样的东西：
$sql = "SELECT friend FROM table__friends WHERE id = "
. mysql_real_escape_string($user_id);
$result = mysql_query($sql);
if (!$result) {
die("Something bad happened");
}
$friend_arr = array();
while ($row = mysql_fetch_array($result)) {
$friend_arr[] = $row[0];
}
$friend_list = implode(',', $friend_arr);
Post the PHP code you're using to run the query and we'll be able to help more.
发布您用于运行查询的 PHP 代码，我们将能够提供更多帮助。
A couple of notes:
一些注意事项：
- I added the
mysql_real_escape_stringfunction. Sanitizing your user inputs in this way is crucialto avoid SQL injectionattacks. - Depending on what you're actually doing - there's a good chance that storing a comma-separated list of strings isn't a good way to do it.
- 我加了这个
mysql_real_escape_string功能。以这种方式清理用户输入对于避免SQL 注入攻击至关重要。 - 取决于您实际在做什么 - 存储逗号分隔的字符串列表很可能不是一个好方法。
回答by Andrius Naru?evi?ius
回答by jcho360
I used a Variable called @a, to store the ids, at the end you have the values in the variable or try with a limit, group order by, like this:
我使用了一个名为@a 的变量来存储 id，最后你有变量中的值或尝试使用限制，分组顺序，如下所示：
mysql> show create table actor\G
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @a:=concat(@a,",",actor_id) as actor_id from actor where actor_id>195 order by (actor_id) desc limit 1;
+----------------------------+
| actor_id |
+----------------------------+
| 0,196,197,198,199,200,205, |
+----------------------------+
1 row in set (0.00 sec)
In your case change the "WHERE condition"
在您的情况下，更改“WHERE 条件”
or you can also after the select:
或者你也可以在选择之后：
mysql> select @a;
+-------------------------------+
| @a |
+-------------------------------+
| 0,196,197,198,199,200,205,206 |
+-------------------------------+
1 row in set (0.00 sec)
