MySQL GROUP_CONCAT() 函式完全指南：將多行資料合併為單一字串

函式語法

GROUP_CONCAT([DISTINCT] expr [,expr ...]
    [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
    [SEPARATOR str_val])

實戰範例：學生選課表

建立測試資料

CREATE TABLE student_courses (
    student_id INT UNSIGNED NOT NULL,
    courses_id INT UNSIGNED NOT NULL,
    KEY(student_id)
);

INSERT INTO student_courses VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5);

傳統查詢方式（需迴圈處理）

SELECT student_id, courses_id FROM student_courses WHERE student_id = 2;
/* 結果：
student_id | courses_id
2          | 3
2          | 4
2          | 5
*/

PHP 處理需迴圈：

$result = [];
foreach ($pdo->query("SELECT student_id, courses_id FROM student_courses WHERE student_id=2") as $row) {
    $result[] = $row['courses_id'];
}
// $result = [3, 4, 5]

GROUP_CONCAT 簡化查詢

SELECT student_id, GROUP_CONCAT(courses_id) AS courses
FROM student_courses
WHERE student_id = 2
GROUP BY student_id;
/* 結果：
student_id | courses
2          | 3,4,5
*/

PHP 處理變簡單：

$row = $pdo->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id")->fetch();
$result = explode(',', $row['courses']);
// $result = [3, 4, 5]

進階用法

自訂分隔符

SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses
FROM student_courses
WHERE student_id = 2
GROUP BY student_id;
/* 結果：3|||4|||5 */

結果排序

-- 依課程 ID 降序
SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses
FROM student_courses
WHERE student_id = 2
GROUP BY student_id;
/* 結果：5,4,3 */

去重複值

SELECT student_id, GROUP_CONCAT(DISTINCT courses_id) AS courses
FROM student_courses
GROUP BY student_id;

多欄位合併

SELECT student_id, 
       GROUP_CONCAT(CONCAT(course_name, ':', score) ORDER BY score DESC SEPARATOR '; ') AS course_scores
FROM student_courses sc
JOIN courses c ON sc.courses_id = c.id
GROUP BY student_id;

重要限制與調整

預設長度限制： 1024 位元組，超截會被截斷。

-- 暫時調大（當前連線有效）
SET SESSION group_concat_max_len = 10000;

-- 永久調整（my.cnf/my.ini）
[mysqld]
group_concat_max_len = 10000

效能考量

• 適用於「一對多」關聯的主表查詢，減少 N+1 查詢問題
• 大量資料時注意 group_concat_max_len 與記憶體使用
• 搭配索引使用：GROUP BY 欄位應建立索引

適用場景總結