MySQL 行轉列實戰：利用序號表拆分逗號分隔字符串

場景背景

在一對多關係未建立中間表的情況下，常將「多」的一方用分隔符（如逗號）存儲在「一」的一方某字段中。雖然節省了關聯表，但導致無法直接用 SQL 進行關聯查詢，通常只能在應用層拆分後多次查詢。本文展示如何純 SQL 實現行轉列，將單行逗號分隔字符串拆分為多行記錄。

目標效果

假設 user 表結構如下：

+----+------+------------------+
| id | name | mobile           |
+----+------+------------------+
| 1  | 張三 | 13800001111,13900002222 |
+----+------+------------------+

期望查詢結果：

+------+--------------+
| name | mobile       |
+------+--------------+
| 張三 | 13800001111  |
| 張三 | 13900002222  |
+------+--------------+

核心思路：借助序號表

創建一張僅包含連續自增 ID 的序號表 sequence（或 squence），ID 範圍覆蓋單字段拆分後的最大可能數量。

CREATE TABLE sequence (id INT PRIMARY KEY AUTO_INCREMENT);
-- 插入足夠多的行，例如 10000 行
INSERT INTO sequence VALUES (), (), ... ;

實現 SQL 詳解

SELECT 
    b.name,
    REPLACE(
        SUBSTRING_INDEX(b.mobile, ',', a.id),
        CONCAT(SUBSTRING_INDEX(b.mobile, ',', a.id - 1), ','),
        ''
    ) AS mobile
FROM sequence a
CROSS JOIN (
    SELECT 
        name,
        CONCAT(mobile, ',') AS mobile,  -- 統一結尾添加分隔符，便於 SUBSTRING_INDEX 邏輯
        LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 AS size  -- 計算分隔符數量+1 = 字段數
    FROM `user`
) b
ON a.id <= b.size;

關鍵點解析

1. 子查詢預處理：給 mobile 尾部拼接逗號，確保每個字段後都有分隔符；同時計算字段個數 size。
2. 笛卡爾積過濾：CROSS JOIN 產生笛卡爾積，通過 ON a.id <= b.size 只保留有效拆分行數。
3. SUBSTRING_INDEX 取第 N 段：SUBSTRING_INDEX(str, delim, N) 返回第 N 個分隔符之前的子串（含前 N-1 個字段）。
4. REPLACE 去除前 N-1 段：用第 N-1 段內容加分隔符替換為空串，留下第 N 個字段。

適用場景與限制

• 適用於字段值固定分隔符、拆分後行數可控（如手機號、標籤 ID 列表）的場景。
• 若拆分後行數極大（超過序號表最大 ID），需擴充序號表。
• MySQL 8.0+ 可使用 WITH RECURSIVE 遞歸 CTE 或 JSON_TABLE 替代序號表，寫法更簡潔。