MIS 腳印 logo

MIS 腳印

記錄 IT 學習的軌跡

MySQL GROUP_CONCAT() 多筆查詢結果串接合併為一筆 JSON

MySQL 使用 GROUP BY 搭配 GROUP_CONCAT() 即可將多筆查詢結果串接合併為一筆,在運用 CONCAT() 將資料串接成 JSON,並自訂 GROUP_CONCAT() 預設可串接最大長度 group_concat_max_len。

MySQL

現有資料表

假設現有 departments (部門) 和 employees (員工) 兩張 table 資料。

departments (部門)
id namt
1 資訊部
2 人事部
employees (員工)
id namt sex last_day departments_id (FK)
E201308002 李四 2020-08-18 1
E201908005 張三 NULL 1
E202003001 小美 NULL 2
E202009009 小娟 2019-02-05 2

多筆查詢結果串接合併為一筆 JSON

departments 和 employees 是 一對多 (one-to-many) 的關係,使用 JOIN 的查詢結果則會以 employees 的資料數量取得對應的 rows。

SELECT
    departments.id AS departments_id,
    departments.name AS departments_name,
    employees.id AS employees_id,
    employees.name AS employees_name,
    employees.sex AS employees_sex,
    employees.last_day AS employees_last_day
FROM departments
    LEFT JOIN employees
        ON departments.id = employees.departments_id
JOIN 查詢結果
departments_id departments_name employees_id employees_name employees_sex employees_last_day
1 資訊部 E201308002 李四 2020-08-18
1 資訊部 E201908005 張三 NULL
2 人事部 E202003001 小美 NULL
2 人事部 E202009009 小娟 2019-02-05

子查詢 (subquery) 使用 GROUP BY 搭配 GROUP_CONCAT() 即可將多筆查詢結果串接合併為一筆,在運用 CONCAT() 將資料串接成 JSON

SELECT
    departments.id AS departments_id,
    departments.name AS departments_name,
    -- 子查詢
    (
        SELECT
            -- 將多筆查詢結果依 GROUP BY 分組合併為一筆 JSON
            CONCAT(
                '[',
                GROUP_CONCAT(
                    CONCAT('{"name":"', employees.name, '",'),
                    CONCAT('"sex":"', employees.sex, '",'),
                    -- 如果資料會有 NULL,必須使用 IFNULL() 排除,否則會導致整筆資料為 NULL
                    CONCAT('"last_day":"', IFNULL(employees.last_day, '0'), '"}')
                ),
                ']'
            )
        FROM employees
        WHERE employees.departments_id = departments.id
        -- 將不重覆的欄位分組
        GROUP BY employees.departments_id
    ) AS employees
FROM departments
GROUP BY 搭配 GROUP_CONCAT() 和 CONCAT() 查詢結果
departments_id departments_name employees
1 資訊部 [{"name":"李四","sex":"男","last_day":"2020-08-18"},{"name":"張三","sex":"男","last_day":"0"}]
2 人事部 [{"name":"小美","sex":"女","last_day":"0"},{"name":"小娟","sex":"女","last_day":"2019-02-05"}]

GROUP_CONCAT() 串接長度限制

GROUP_CONCAT() 預設可串接最大長度 group_concat_max_len 為 1024 字元,可如下自訂可串接最大長度:

vim /etc/my.cnf
[mysqld]
# 自訂 GROUP_CONCAT() 可串接最大長度 (預設 1024 字元)
group_concat_max_len=10240

設定完須重啟 MySQL:

systemctl restart mysqld

參考

發表迴響