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