GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
將分組內的非 NULL 值連接成字串,預設以逗號分隔。
-- 表 ta
+----+------+
| id | name |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | b |
| 3 | d |
+----+------+
SELECT id, GROUP_CONCAT(name)
FROM ta
GROUP BY id;
結果:
| id | GROUP_CONCAT(name) |
|---|---|
| 1 | a,b,c |
| 2 | a,b |
| 3 | d |
SELECT id, GROUP_CONCAT(name SEPARATOR ' | ') FROM ta GROUP BY id;
結果:a | b | c
SELECT id, GROUP_CONCAT(name ORDER BY name DESC) FROM ta GROUP BY id;
結果:c,b,a
SELECT id, GROUP_CONCAT(DISTINCT name) FROM ta GROUP BY id;
預設最大長度 1024 位元組(group_concat_max_len),超截斷無警告。
-- 會話級調大
SET SESSION group_concat_max_len = 1000000;
SELECT id, GROUP_CONCAT(long_text SEPARATOR '') FROM big_table GROUP BY id;
SELECT article_id, GROUP_CONCAT(tag_name) AS tags FROM article_tags GROUP BY article_idSELECT GROUP_CONCAT(id) FROM users WHERE status=1 → 應用層拼接 IN (...)IFNULL(expr, '')。max_allowed_packet 會返回 NULL,需同步調大該變數。ORDER BY 在分組內生效,不影響最終結果集順序。