前陣子接手一個歷史數(shù)據(jù)清洗的活讓我徹底領(lǐng)教了“把字符串分割并展開成表格”這句話的分量。需求聽起來特別簡單某張表里有個字段存了多個ID用逗號拼在一起比如“1,3,7”現(xiàn)在要把它拆成三行每行一個 ID好去做關(guān)聯(lián)統(tǒng)計?？烧鎰邮植虐l(fā)現(xiàn)同樣是 SQLMySQL、PostgreSQL、SQL Server 寫出來的方案完全不是一回事坑也是一個接一個。這篇文章我就把幾種主流實現(xiàn)方式、背后的原理和我實際踩過的坑整理出來誰以后再遇到這個需求能少走不少彎路。先說明一下適用讀者日常做報表、數(shù)據(jù)清洗、ETL 開發(fā)的 SQL 使用者以及需要把“非規(guī)范化的多值字段”變成可統(tǒng)計行集的人。文章不會只給代碼會盡量把思路講透這樣你在 SQL Server 上學(xué)到的東西換到 MySQL 也知道該怎么改。1. 為什么非要把字符串拆成行——三類繞不開的業(yè)務(wù)場景有人可能會問字段里存了“1,3,7”我直接WHERE FIND_IN_SET(1, ids)不就行了為什么非得拆開這個想法我很理解早期我自己也這么干過直到遇到三個繞不開的場景才意識到拆行不是炫技而是數(shù)據(jù)處理的基本功。1.1 多值字段的統(tǒng)計分析反向匹配做不了 GROUP BY最典型的就是標簽類字段。比如活動表里有region_list字段存的是“華東,華南,華北”我要按區(qū)域統(tǒng)計活動數(shù)量。用FIND_IN_SET只能回答“哪個活動包含華東”回答不了“華東這個區(qū)域獨立出現(xiàn)在多少活動里”因為你沒法把一個字段里的多個值分別拎出來計數(shù)。這時候必須先把字符串拆成多行得到一張“活動-區(qū)域”的明細表再GROUP BY聚合。1.2 拆分后的 ID 要和主數(shù)據(jù)表做 JOIN第二個場景是列表型 ID 字段。某業(yè)務(wù)表里存了一串權(quán)限點 ID“101,202,303”需要和權(quán)限字典表關(guān)聯(lián)查出每個權(quán)限點的名稱。直接用FIND_IN_SET寫 JOIN 條件性能差且語義別扭更麻煩的是如果你想把拆出來的單個 ID 作為驅(qū)動列去過濾、去重、排序幾乎無從下手。只有把字符串轉(zhuǎn)換成行集才能用普通的 JOIN 邏輯索引也能正常發(fā)揮作用。1.3 上游系統(tǒng)的扁平化導(dǎo)出ETL 必須做的事第三個場景來自數(shù)據(jù)管道。外部系統(tǒng)導(dǎo)出的 CSV、日志、接口返回經(jīng)常把多個值塞在同一個字段里常見的有“訂單號列表、SKU 列表、地理區(qū)域編碼列表”。這類數(shù)據(jù)進數(shù)倉之前第一道清洗邏輯往往就是“拆分隔符、展開成行”。有一次我處理一個文件一行數(shù)據(jù)里有幾十個 SKU如果不拆開后續(xù)所有分析都做不了。這個場景下字符串拆分不是可選項而是硬性前置步驟。說白了拆分字符串成行集本質(zhì)上是把“欠規(guī)范的一對多數(shù)據(jù)”還原成關(guān)系型數(shù)據(jù)庫擅長處理的形態(tài)。反范式存儲在遺留系統(tǒng)里太常見了改表結(jié)構(gòu)成本極高學(xué)會用 SQL 快速拆行就是最務(wù)實的補救手段。2. 各數(shù)據(jù)庫的現(xiàn)成武器——內(nèi)置函數(shù)與語法差異對比不同數(shù)據(jù)庫處理字符串分割的思路差異很大有的給了一站式函數(shù)有的要自己拼。我把幾個主流庫的方案列一下方便大家按需取用。2.1 PostgreSQLstring_to_array unnest 是目前最舒服的PostgreSQL 的語法可以說是這幾個庫里最直觀的SELECT id, unnest(string_to_array(ids, ,)) AS item_id FROM demo_table;string_to_array把字符串變成數(shù)組unnest把數(shù)組展開成多行兩句話搞定。它還有一個變體regexp_split_to_table(ids, ,)支持正則分隔符不過要注意regexp_split_to_table會過濾掉空字符串而string_to_arrayunnest會保留空元素。這個差異在數(shù)據(jù)不干凈的時候非常關(guān)鍵后面坑點章節(jié)會細說。2.2 SQL ServerSTRING_SPLIT 與 2022 的順序增強SQL Server 2016 之后提供了官方函數(shù)STRING_SPLIT用起來也簡單SELECT t.id, s.value AS item_id FROM demo_table t CROSS APPLY STRING_SPLIT(t.ids, ,) s;坑在于老版本的STRING_SPLIT不保證輸出順序官方文檔也明確說順序不保證。如果你的業(yè)務(wù)對“第幾個元素”敏感2016-2019 版本就只能自己寫 CTE 遞歸或者用 XML 拆分。到了 SQL Server 2022函數(shù)新增了第三個參數(shù)enable_ordinal可以輸出序號SELECT t.id, s.value AS item_id, s.ordinal FROM demo_table t CROSS APPLY STRING_SPLIT(t.ids, ,, 1) s;這樣順序就穩(wěn)了。升級到 2022 之后我基本上不再為順序問題寫額外代碼。2.3 MySQL老式 SUBSTRING_INDEX 方案與 8.0 的 JSON_TABLEMySQL 沒有現(xiàn)成的split函數(shù)最常見的做法是用SUBSTRING_INDEX配合數(shù)字輔助表這個方案我在第 4 章詳細展開。MySQL 8.0 之后多了一個思路把字符串轉(zhuǎn)成 JSON 數(shù)組再用JSON_TABLE展開SELECT d.id, j.item_id FROM demo_table d, JSON_TABLE( CONCAT([, REPLACE(d.ids, ,, ,), ]), $[*] COLUMNS (item_id VARCHAR(255) PATH $) ) j;這個寫法看著巧妙但有個致命前提原數(shù)據(jù)里不能出現(xiàn)雙引號和 JSON 特殊字符否則數(shù)組會被搞壞。所以我一般只在數(shù)據(jù)非常干凈的場景用它生產(chǎn)環(huán)境還是老實的序列表方案更穩(wěn)。2.4 OracleREGEXP_SUBSTR CONNECT BY 的經(jīng)典組合Oracle 沒有內(nèi)置 split 表函數(shù)新版有JSON_TABLE可用傳統(tǒng)寫法是正則加層級查詢SELECT id, REGEXP_SUBSTR(ids, [^,], 1, LEVEL) AS item_id FROM demo_table CONNECT BY REGEXP_SUBSTR(ids, [^,], 1, LEVEL) IS NOT NULL;這里L(fēng)EVEL會從 1 開始不斷遞增正則表達式逐個取出第 1 段、第 2 段……直到取不到為止。需要注意CONNECT BY在有多行源數(shù)據(jù)時容易產(chǎn)生重復(fù)行或笛卡爾膨脹通常要配合條件控制實踐時我會加PRIOR條件或者先給源表加唯一鍵避免行數(shù)爆炸。2.5 各數(shù)據(jù)庫能力橫向?qū)Ρ葦?shù)據(jù)庫推薦方式是否支持多字符分隔符是否保序PostgreSQLstring_to_array unnest支持保序SQL Server 2022STRING_SPLIT 帶 ordinal支持任意字符串保序SQL Server 2016-2019STRING_SPLIT 不帶 ordinal僅單字符不保證MySQL 8.0JSON_TABLE 或序列表視寫法而定序列表保序OracleREGEXP_SUBSTR CONNECT BY支持正則按 LEVEL 保序沒有哪個方案是萬能的選型時要先看你手上的庫版本再看業(yè)務(wù)是否需要順序最后看數(shù)據(jù)本身干不干凈。3. 沒有內(nèi)置函數(shù)時的通用武器——遞歸拆分原理與模板如果你的數(shù)據(jù)庫版本比較老或者分隔符比較復(fù)雜內(nèi)置函數(shù)不夠用那就得自己寫遞歸。別覺得遞歸很難本質(zhì)就一句話從字符串左側(cè)切出一段剩下的繼續(xù)切直到切完。3.1 遞歸拆分的核心思想每次從左邊切一份我們把字符串“a,b,c”拆成三行人工操作是先取第一個逗號之前的“a”剩下“b,c”再取“b,c”里第一個逗號之前的“b”剩下“c”最后取“c”沒有剩余了結(jié)束。SQL 遞歸 CTE 就是把這個人工過程機械化錨點成員負責(zé)“第一次切”遞歸成員負責(zé)“切一刀再切一刀”直到剩余字符串為空。3.2 一個以 MySQL 8.0 為例的遞歸模板WITH RECURSIVE split_cte (id, item_id, remainder) AS ( -- 錨點成員取第一段并計算剩余部分 SELECT id, SUBSTRING_INDEX(ids, ,, 1) AS item_id, SUBSTRING(ids, LENGTH(SUBSTRING_INDEX(ids, ,, 1)) 2) AS remainder FROM demo_table WHERE ids IS NOT NULL AND ids UNION ALL -- 遞歸成員從剩余部分里繼續(xù)取第一段 SELECT id, SUBSTRING_INDEX(remainder, ,, 1) AS item_id, SUBSTRING(remainder, LENGTH(SUBSTRING_INDEX(remainder, ,, 1)) 2) AS remainder FROM split_cte WHERE remainder ) SELECT id, item_id FROM split_cte ORDER BY id;這段 SQL 里最關(guān)鍵的是剩余字符串的位移計算LENGTH(SUBSTRING_INDEX(ids, ,, 1)) 2。第一個SUBSTRING_INDEX取出的是“a”長度是 1逗號本身占 1 位所以要從第 3 位開始截取才能拿到“b,c”。如果段為“ab”長度是 2則從第 4 位開始。這個加 2 的細節(jié)我第一次寫的時候漏了結(jié)果剩余串里總帶著逗號遞歸永遠切不完。3.3 邊界條件設(shè)計四種輸入形態(tài)都得測遞歸模板好不好用邊界條件說了算。至少要考慮四種情況純空字符串錨點成員直接WHERE ids 過濾掉不進入遞歸。NULLWHERE ids IS NOT NULL過濾掉避免SUBSTRING_INDEX(NULL, ,, 1)產(chǎn)生空結(jié)果。字段里只有一個元素沒有逗號錨點成員取出的item_id就是整個串remainder是空串遞歸直接終止。字符串以逗號開頭或結(jié)尾比如,a,b或a,b,遞歸會產(chǎn)生空元素。如果業(yè)務(wù)容忍空元素模板可以保留如果不容忍要在最終結(jié)果里WHERE item_id 過濾。我實際跑的時候最常踩的是第三種和第四種混在一起的情況數(shù)據(jù)里既有“a,b,”又有“a,,b”。保險做法是先REPLACE(ids, ,,, ,)清洗連續(xù)逗號再進入遞歸流程。但要注意清洗連續(xù)逗號其實改變了原始信息如果空位代表業(yè)務(wù)含義就不能這么干。3.4 遞歸深度限制是個隱藏雷區(qū)MySQL 的遞歸 CTE 默認最大迭代深度是 1000 層。如果一個字段里有幾千個元素遞歸到一半就直接報錯。解決辦法是設(shè)置會話變量SET cte_max_recursion_depth 10000;cte_max_recursion_depth是 10.2.4 以后的 MySQL 版本才有的參數(shù)8.0 沒問題。不過我要提醒一句能拆出 1000 段以上的數(shù)據(jù)本身就該懷疑是不是數(shù)據(jù)格式出了問題或者是不是該換方案了。遞歸深度調(diào)太大會拖垮數(shù)據(jù)庫別亂調(diào)。4. 不想碰遞歸數(shù)字序列表方案——穩(wěn)定跨庫的拆分行術(shù)遞歸雖然通用但很多人一看到WITH RECURSIVE就頭大而且有些老版本數(shù)據(jù)庫不支持遞歸 CTE。這時候還有更“樸素”的思路用一張數(shù)字表把每個元素編號對應(yīng)到一行。4.1 序列表的整體思路先算段數(shù)再取第 N 段不管分隔符是什么一個字符串拆出來的元素個數(shù)是有公式的段數(shù) 分隔符出現(xiàn)次數(shù) 1分隔符出現(xiàn)次數(shù)怎么算用LENGTH和REPLACELENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1這個公式非常實用如果ids是“a,b,c”LENGTH是 5REPLACE后長度是 3差值是 2加 1 得到 3正好是 3 段。如果ids只有一個元素沒有逗號差值是 0加 1 得到 1公式依然成立。有了段數(shù)之后我們再造一張數(shù)字表讓數(shù)字從 1 到段數(shù)之間都和源表 join 一次。每一行對應(yīng)一個序號再用“取第 N 段”的方法取出元素就完成了拆行。4.2 標準模板以 MySQL 為例-- 數(shù)字表可以根據(jù)需要擴充1-1024 基本夠用 CREATE TABLE seq_number (n INT PRIMARY KEY); INSERT INTO seq_number VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); -- 實際使用時按需插入到 1024這里只做示意 SELECT d.id, SUBSTRING_INDEX(SUBSTRING_INDEX(d.ids, ,, s.n), ,, -1) AS item_id FROM demo_table d JOIN seq_number s ON s.n LENGTH(d.ids) - LENGTH(REPLACE(d.ids, ,, )) 1 ORDER BY d.id, s.n;這個語句的核心是SUBSTRING_INDEX(SUBSTRING_INDEX(d.ids, ,, s.n), ,, -1)。內(nèi)層SUBSTRING_INDEX(ids, ,, s.n)取出前 N 段比如 N2 時得到“a,b”外層SUBSTRING_INDEX(..., ,, -1)取倒數(shù)第一段拿到“b”。套兩層就能精確定位第 N 段這是 MySQL 里非常經(jīng)典的小技巧。4.3 為什么這個方案跨庫穩(wěn)定序列表方案的優(yōu)點在于它只依賴SUBSTRING_INDEX、LENGTH、REPLACE、JOIN這幾個幾乎所有數(shù)據(jù)庫都有的通用能力。換句話說這套思路在 MySQL、MariaDB、甚至某些國產(chǎn)數(shù)據(jù)庫上都能跑邏輯也容易理解。很多 OLTP 系統(tǒng)的 MySQL 老版本不支持遞歸 CTE序列表幾乎是唯一選擇。它的缺點也很明顯數(shù)字表要事先準備字段元素數(shù)量如果超過數(shù)字表上限拆分就會漏數(shù)據(jù)。我的習(xí)慣是建一張從 1 到 1024 的數(shù)字表一旦發(fā)現(xiàn)某行拆出來的數(shù)量超過 1024就說明這個字段已經(jīng)異常到不該用 SQL 處理了。4.4 序列表 vs 遞歸實際場景怎么選維度遞歸 CTE序列表代碼可讀性中等錨點遞歸成員理解成本高較高公式直觀依賴條件需要支持遞歸 CTE 的版本只需基礎(chǔ)字符串函數(shù)元素數(shù)量上限受遞歸深度限制受數(shù)字表上限限制性能表現(xiàn)每次迭代都要計算一次分割長串偏慢嵌套函數(shù)重復(fù)計算長串同樣偏慢維護成本低SQL 寫在查詢里即可需要維護數(shù)字表實測下來絕大多數(shù)業(yè)務(wù)場景兩者性能差別不大真正拉開差距的是數(shù)據(jù)量。幾萬行的表怎么拆都還行到了百萬行級別兩種寫法都會在字符串函數(shù)上消耗大量 CPU此時不如先用 ETL 把數(shù)據(jù)拆好落地成臨時表后面再隨便查。5. 實測中最容易翻車的四個細節(jié)——分隔符、空值、順序與性能字符串拆分代碼寫出來只是第一步真正讓項目延期的是那些看似不起眼的邊角問題。我把自己踩過的坑整理成四個大類每個都有具體表現(xiàn)和解決辦法。5.1 分隔符藏在內(nèi)容里數(shù)據(jù)比你想象的臟最常見的坑就是字段內(nèi)容本身包含了分隔符。比如商品描述里存了“紅色,大號,男士,含棉,可機洗”其中某個值“含棉,可機洗”自帶逗號一拆分就變成兩個值數(shù)據(jù)直接錯亂。這種情況在 CSV 格式的導(dǎo)入數(shù)據(jù)里尤其常見。要徹底解決得靠“引號包裹 轉(zhuǎn)義”級別的解析純 SQL 寫起來非常痛苦。我的建議是先判斷分隔符是否可能出現(xiàn)在內(nèi)容中如果可能要么在 ETL 階段用腳本語言處理要么要求上游統(tǒng)一改成不會沖突的分隔符比如把逗號換成|。SQL 里強行拆這種數(shù)據(jù)性價比太低。5.2 NULL、空串、開頭結(jié)尾分隔符三種形態(tài)要分別處理NULL 會讓SUBSTRING_INDEX直接返回 NULL拆出來的結(jié)果也是 NULL如果后續(xù) JOIN 字典表這些 NULL 都會被過濾掉統(tǒng)計結(jié)果憑空少了一截空串則會產(chǎn)生一行空字符串瞬間多出一個“未知”分類開頭或結(jié)尾有分隔符的字符串比如“a,b,”會多拆出一個空元素。我的處理模板是統(tǒng)一做防御SELECT d.id, NULLIF(TRIM(item_raw.item_id), ) AS item_id FROM ( -- 這里放拆分語句 ) item_raw WHERE item_raw.item_id IS NOT NULL AND item_raw.item_id ;先把可能出現(xiàn)的空白 trim 掉再把空串轉(zhuǎn)成 NULL最后統(tǒng)一過濾。這樣無論原始數(shù)據(jù)是哪種臟形態(tài)最終結(jié)果都是干凈的行集。5.3 拆分結(jié)果的順序到底能不能保證很多業(yè)務(wù)場景要求“第 1 個元素就是主分類第 2 個是副分類”順序錯了整個報表就亂了。MySQL 的序列表方案天然帶序號s.n順序是可靠且可排序的PostgreSQL 的unnest(string_to_array(...))也會保持數(shù)組原始順序。但 SQL Server 老版STRING_SPLIT不保證順序Oracle 的CONNECT BY在多行源表上也可能出現(xiàn)順序錯亂。所以凡是順序敏感的業(yè)務(wù)我都建議在拆分結(jié)果里顯式帶序號字段。如果不能帶序號就換遞歸方案或者升級到支持 ordinal 的版本。不要賭數(shù)據(jù)庫“這次剛好是按順序的”這個賭注風(fēng)險太高。5.4 大表上的性能滑坡與應(yīng)對思路字符串函數(shù)本身就無法使用索引拆分操作基本等于全表掃描加逐行計算。就算拆出來的結(jié)果只有幾十萬行源表幾百萬行時也能把數(shù)據(jù)庫 CPU 打滿。而且 seq 方案里嵌套函數(shù)會重復(fù)計算加上 JOIN 數(shù)字表的笛卡爾效應(yīng)性能只會更糟。我的應(yīng)對思路是“拆一次存下來反復(fù)用”。把拆分結(jié)果寫入臨時表或者中間表之后所有的統(tǒng)計、JOIN、報表都基于中間表進行絕不在大表上反復(fù)執(zhí)行拆分。為了快速定位問題數(shù)據(jù)還可以先跑一個診斷 SQL查出每個字段拆分后的最大段數(shù)SELECT MAX(LENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1) AS max_parts FROM demo_table;如果max_parts異常大先處理異常數(shù)據(jù)再跑正式拆分。5.5 驗證拆分正確性的自查 SQL拆分完了不能直接信結(jié)果我一般用兩個維度自查。第一拆后行數(shù)是否等于所有字段的段數(shù)之和SELECT (SELECT COUNT(*) FROM split_result) AS actual_rows, (SELECT SUM(LENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1) FROM demo_table) AS expect_rows;第二抽樣幾條數(shù)據(jù)手工核對拆分后的字段拼接起來是否等于原串。這兩個檢查在數(shù)據(jù)清洗項目里能省下大量返工時間。6. 拆行之后的下一步——關(guān)聯(lián)、聚合與動態(tài)透視的經(jīng)典組合把字符串拆成行不是終點拆完之后的去向才是真正產(chǎn)生業(yè)務(wù)價值的地方。我拿一個最常見的“活動-區(qū)域”案例把拆行后的完整鏈路串一遍。6.1 標簽類字段拆開后做分組統(tǒng)計假設(shè)marketing_activity表有activity_id和region_list字段值是“華東,華南,華北”。第一步拆行第二步按區(qū)域聚合WITH split_region AS ( SELECT d.activity_id, SUBSTRING_INDEX(SUBSTRING_INDEX(d.region_list, ,, s.n), ,, -1) AS region_code FROM marketing_activity d JOIN seq_number s ON s.n LENGTH(d.region_list) - LENGTH(REPLACE(d.region_list, ,, )) 1 ) SELECT region_code, COUNT(DISTINCT activity_id) AS activity_cnt FROM split_region GROUP BY region_code ORDER BY activity_cnt DESC;這一步實現(xiàn)的本質(zhì)就是把一個字段里的多個標簽還原成明細行再走常規(guī)聚合。沒有拆行之前這個統(tǒng)計根本寫不出來。6.2 拆出來的 ID 與主數(shù)據(jù)表關(guān)聯(lián)拆出來的 ID 是編碼一般還關(guān)聯(lián)字典表取名稱WITH split_region AS ( -- 同上拆分邏輯 ) SELECT r.region_code, r.region_name, COUNT(*) AS activity_cnt FROM split_region sr LEFT JOIN region_dict r ON sr.region_code r.region_code GROUP BY r.region_code, r.region_name;注意這里我用LEFT JOIN因為拆分結(jié)果里可能有字典表不存在的臟編碼。此時GROUP BY r.region_code會把NULL區(qū)域歸到同一組但r.region_name可能是 NULL需要業(yè)務(wù)上確認這種雜項統(tǒng)計是否要展示。6.3 動態(tài)透視思路先拆行再聚合實現(xiàn)多值字段的歸一還有一種進階玩法拆行后配合條件聚合把“一列多值”轉(zhuǎn)成“一行多列”相當(dāng)于對反規(guī)范化字段做透視。比如想知道每個活動覆蓋了哪幾個區(qū)域可以用SELECT activity_id, MAX(CASE WHEN region_code 華東 THEN 1 ELSE 0 END) AS has_east, MAX(CASE WHEN region_code 華南 THEN 1 ELSE 0 END) AS has_south, MAX(CASE WHEN region_code 華北 THEN 1 ELSE 0 END) AS has_north FROM split_region GROUP BY activity_id;這種寫法在行為分析、用戶標簽矩陣里很常用。拆分是第一步透視是第二步兩步連起來就能把“一個字段存多個標簽”變成標準的“寬表特征”。6.4 什么時候該考慮把它落地成規(guī)范表結(jié)構(gòu)如果你發(fā)現(xiàn)某個多值字段每周都要拆一次、拆完還要 JOIN、統(tǒng)計、透視那大概率是表結(jié)構(gòu)設(shè)計出了問題。一次性拆分沒問題長期反復(fù)拆就是技術(shù)債。正確做法是把拆分結(jié)果固化成一維子表比如“活動-區(qū)域”關(guān)系表既方便加索引也方便數(shù)據(jù)質(zhì)量管理和權(quán)限控制。我在實際項目中摸索出來的經(jīng)驗是先把舊數(shù)據(jù)的拆分結(jié)果一次性落地成中間表交給下游使用同時推動上游業(yè)務(wù)系統(tǒng)在寫入時同步維護好關(guān)系表雙軌運行一段時間后徹底下線多值字段。這樣既不用推倒重來又能讓后續(xù)查詢都走正路子。做字符串拆分這件事本身不難難的是想清楚“拆完之后拿它干什么”。如果你只是偶爾用一次序列表方案足夠如果要長期跑還是老老實實建中間表、改表結(jié)構(gòu)。別讓 SQL 技巧幫一個壞設(shè)計續(xù)命太久。