前阵子接手一个历史数据清洗的活让我彻底领教了“把字符串分割并展开成表格”这句话的分量。需求听起来特别简单某张表里有个字段存了多个ID用逗号拼在一起比如“1,3,7”现在要把它拆成三行每行一个 ID好去做关联统计。可真动手才发现同样是 SQLMySQL、PostgreSQL、SQL Server 写出来的方案完全不是一回事坑也是一个接一个。这篇文章我就把几种主流实现方式、背后的原理和我实际踩过的坑整理出来谁以后再遇到这个需求能少走不少弯路。先说明一下适用读者日常做报表、数据清洗、ETL 开发的 SQL 使用者以及需要把“非规范化的多值字段”变成可统计行集的人。文章不会只给代码会尽量把思路讲透这样你在 SQL Server 上学到的东西换到 MySQL 也知道该怎么改。1. 为什么非要把字符串拆成行——三类绕不开的业务场景有人可能会问字段里存了“1,3,7”我直接WHERE FIND_IN_SET(1, ids)不就行了为什么非得拆开这个想法我很理解早期我自己也这么干过直到遇到三个绕不开的场景才意识到拆行不是炫技而是数据处理的基本功。1.1 多值字段的统计分析反向匹配做不了 GROUP BY最典型的就是标签类字段。比如活动表里有region_list字段存的是“华东,华南,华北”我要按区域统计活动数量。用FIND_IN_SET只能回答“哪个活动包含华东”回答不了“华东这个区域独立出现在多少活动里”因为你没法把一个字段里的多个值分别拎出来计数。这时候必须先把字符串拆成多行得到一张“活动-区域”的明细表再GROUP BY聚合。1.2 拆分后的 ID 要和主数据表做 JOIN第二个场景是列表型 ID 字段。某业务表里存了一串权限点 ID“101,202,303”需要和权限字典表关联查出每个权限点的名称。直接用FIND_IN_SET写 JOIN 条件性能差且语义别扭更麻烦的是如果你想把拆出来的单个 ID 作为驱动列去过滤、去重、排序几乎无从下手。只有把字符串转换成行集才能用普通的 JOIN 逻辑索引也能正常发挥作用。1.3 上游系统的扁平化导出ETL 必须做的事第三个场景来自数据管道。外部系统导出的 CSV、日志、接口返回经常把多个值塞在同一个字段里常见的有“订单号列表、SKU 列表、地理区域编码列表”。这类数据进数仓之前第一道清洗逻辑往往就是“拆分隔符、展开成行”。有一次我处理一个文件一行数据里有几十个 SKU如果不拆开后续所有分析都做不了。这个场景下字符串拆分不是可选项而是硬性前置步骤。说白了拆分字符串成行集本质上是把“欠规范的一对多数据”还原成关系型数据库擅长处理的形态。反范式存储在遗留系统里太常见了改表结构成本极高学会用 SQL 快速拆行就是最务实的补救手段。2. 各数据库的现成武器——内置函数与语法差异对比不同数据库处理字符串分割的思路差异很大有的给了一站式函数有的要自己拼。我把几个主流库的方案列一下方便大家按需取用。2.1 PostgreSQLstring_to_array unnest 是目前最舒服的PostgreSQL 的语法可以说是这几个库里最直观的SELECT id, unnest(string_to_array(ids, ,)) AS item_id FROM demo_table;string_to_array把字符串变成数组unnest把数组展开成多行两句话搞定。它还有一个变体regexp_split_to_table(ids, ,)支持正则分隔符不过要注意regexp_split_to_table会过滤掉空字符串而string_to_arrayunnest会保留空元素。这个差异在数据不干净的时候非常关键后面坑点章节会细说。2.2 SQL ServerSTRING_SPLIT 与 2022 的顺序增强SQL Server 2016 之后提供了官方函数STRING_SPLIT用起来也简单SELECT t.id, s.value AS item_id FROM demo_table t CROSS APPLY STRING_SPLIT(t.ids, ,) s;坑在于老版本的STRING_SPLIT不保证输出顺序官方文档也明确说顺序不保证。如果你的业务对“第几个元素”敏感2016-2019 版本就只能自己写 CTE 递归或者用 XML 拆分。到了 SQL Server 2022函数新增了第三个参数enable_ordinal可以输出序号SELECT t.id, s.value AS item_id, s.ordinal FROM demo_table t CROSS APPLY STRING_SPLIT(t.ids, ,, 1) s;这样顺序就稳了。升级到 2022 之后我基本上不再为顺序问题写额外代码。2.3 MySQL老式 SUBSTRING_INDEX 方案与 8.0 的 JSON_TABLEMySQL 没有现成的split函数最常见的做法是用SUBSTRING_INDEX配合数字辅助表这个方案我在第 4 章详细展开。MySQL 8.0 之后多了一个思路把字符串转成 JSON 数组再用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;这个写法看着巧妙但有个致命前提原数据里不能出现双引号和 JSON 特殊字符否则数组会被搞坏。所以我一般只在数据非常干净的场景用它生产环境还是老实的序列表方案更稳。2.4 OracleREGEXP_SUBSTR CONNECT BY 的经典组合Oracle 没有内置 split 表函数新版有JSON_TABLE可用传统写法是正则加层级查询SELECT id, REGEXP_SUBSTR(ids, [^,], 1, LEVEL) AS item_id FROM demo_table CONNECT BY REGEXP_SUBSTR(ids, [^,], 1, LEVEL) IS NOT NULL;这里LEVEL会从 1 开始不断递增正则表达式逐个取出第 1 段、第 2 段……直到取不到为止。需要注意CONNECT BY在有多行源数据时容易产生重复行或笛卡尔膨胀通常要配合条件控制实践时我会加PRIOR条件或者先给源表加唯一键避免行数爆炸。2.5 各数据库能力横向对比数据库推荐方式是否支持多字符分隔符是否保序PostgreSQLstring_to_array unnest支持保序SQL Server 2022STRING_SPLIT 带 ordinal支持任意字符串保序SQL Server 2016-2019STRING_SPLIT 不带 ordinal仅单字符不保证MySQL 8.0JSON_TABLE 或序列表视写法而定序列表保序OracleREGEXP_SUBSTR CONNECT BY支持正则按 LEVEL 保序没有哪个方案是万能的选型时要先看你手上的库版本再看业务是否需要顺序最后看数据本身干不干净。3. 没有内置函数时的通用武器——递归拆分原理与模板如果你的数据库版本比较老或者分隔符比较复杂内置函数不够用那就得自己写递归。别觉得递归很难本质就一句话从字符串左侧切出一段剩下的继续切直到切完。3.1 递归拆分的核心思想每次从左边切一份我们把字符串“a,b,c”拆成三行人工操作是先取第一个逗号之前的“a”剩下“b,c”再取“b,c”里第一个逗号之前的“b”剩下“c”最后取“c”没有剩余了结束。SQL 递归 CTE 就是把这个人工过程机械化锚点成员负责“第一次切”递归成员负责“切一刀再切一刀”直到剩余字符串为空。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 -- 递归成员从剩余部分里继续取第一段 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 里最关键的是剩余字符串的位移计算LENGTH(SUBSTRING_INDEX(ids, ,, 1)) 2。第一个SUBSTRING_INDEX取出的是“a”长度是 1逗号本身占 1 位所以要从第 3 位开始截取才能拿到“b,c”。如果段为“ab”长度是 2则从第 4 位开始。这个加 2 的细节我第一次写的时候漏了结果剩余串里总带着逗号递归永远切不完。3.3 边界条件设计四种输入形态都得测递归模板好不好用边界条件说了算。至少要考虑四种情况纯空字符串锚点成员直接WHERE ids 过滤掉不进入递归。NULLWHERE ids IS NOT NULL过滤掉避免SUBSTRING_INDEX(NULL, ,, 1)产生空结果。字段里只有一个元素没有逗号锚点成员取出的item_id就是整个串remainder是空串递归直接终止。字符串以逗号开头或结尾比如,a,b或a,b,递归会产生空元素。如果业务容忍空元素模板可以保留如果不容忍要在最终结果里WHERE item_id 过滤。我实际跑的时候最常踩的是第三种和第四种混在一起的情况数据里既有“a,b,”又有“a,,b”。保险做法是先REPLACE(ids, ,,, ,)清洗连续逗号再进入递归流程。但要注意清洗连续逗号其实改变了原始信息如果空位代表业务含义就不能这么干。3.4 递归深度限制是个隐藏雷区MySQL 的递归 CTE 默认最大迭代深度是 1000 层。如果一个字段里有几千个元素递归到一半就直接报错。解决办法是设置会话变量SET cte_max_recursion_depth 10000;cte_max_recursion_depth是 10.2.4 以后的 MySQL 版本才有的参数8.0 没问题。不过我要提醒一句能拆出 1000 段以上的数据本身就该怀疑是不是数据格式出了问题或者是不是该换方案了。递归深度调太大会拖垮数据库别乱调。4. 不想碰递归数字序列表方案——稳定跨库的拆分行术递归虽然通用但很多人一看到WITH RECURSIVE就头大而且有些老版本数据库不支持递归 CTE。这时候还有更“朴素”的思路用一张数字表把每个元素编号对应到一行。4.1 序列表的整体思路先算段数再取第 N 段不管分隔符是什么一个字符串拆出来的元素个数是有公式的段数 分隔符出现次数 1分隔符出现次数怎么算用LENGTH和REPLACELENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1这个公式非常实用如果ids是“a,b,c”LENGTH是 5REPLACE后长度是 3差值是 2加 1 得到 3正好是 3 段。如果ids只有一个元素没有逗号差值是 0加 1 得到 1公式依然成立。有了段数之后我们再造一张数字表让数字从 1 到段数之间都和源表 join 一次。每一行对应一个序号再用“取第 N 段”的方法取出元素就完成了拆行。4.2 标准模板以 MySQL 为例-- 数字表可以根据需要扩充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)。内层SUBSTRING_INDEX(ids, ,, s.n)取出前 N 段比如 N2 时得到“a,b”外层SUBSTRING_INDEX(..., ,, -1)取倒数第一段拿到“b”。套两层就能精确定位第 N 段这是 MySQL 里非常经典的小技巧。4.3 为什么这个方案跨库稳定序列表方案的优点在于它只依赖SUBSTRING_INDEX、LENGTH、REPLACE、JOIN这几个几乎所有数据库都有的通用能力。换句话说这套思路在 MySQL、MariaDB、甚至某些国产数据库上都能跑逻辑也容易理解。很多 OLTP 系统的 MySQL 老版本不支持递归 CTE序列表几乎是唯一选择。它的缺点也很明显数字表要事先准备字段元素数量如果超过数字表上限拆分就会漏数据。我的习惯是建一张从 1 到 1024 的数字表一旦发现某行拆出来的数量超过 1024就说明这个字段已经异常到不该用 SQL 处理了。4.4 序列表 vs 递归实际场景怎么选维度递归 CTE序列表代码可读性中等锚点递归成员理解成本高较高公式直观依赖条件需要支持递归 CTE 的版本只需基础字符串函数元素数量上限受递归深度限制受数字表上限限制性能表现每次迭代都要计算一次分割长串偏慢嵌套函数重复计算长串同样偏慢维护成本低SQL 写在查询里即可需要维护数字表实测下来绝大多数业务场景两者性能差别不大真正拉开差距的是数据量。几万行的表怎么拆都还行到了百万行级别两种写法都会在字符串函数上消耗大量 CPU此时不如先用 ETL 把数据拆好落地成临时表后面再随便查。5. 实测中最容易翻车的四个细节——分隔符、空值、顺序与性能字符串拆分代码写出来只是第一步真正让项目延期的是那些看似不起眼的边角问题。我把自己踩过的坑整理成四个大类每个都有具体表现和解决办法。5.1 分隔符藏在内容里数据比你想象的脏最常见的坑就是字段内容本身包含了分隔符。比如商品描述里存了“红色,大号,男士,含棉,可机洗”其中某个值“含棉,可机洗”自带逗号一拆分就变成两个值数据直接错乱。这种情况在 CSV 格式的导入数据里尤其常见。要彻底解决得靠“引号包裹 转义”级别的解析纯 SQL 写起来非常痛苦。我的建议是先判断分隔符是否可能出现在内容中如果可能要么在 ETL 阶段用脚本语言处理要么要求上游统一改成不会冲突的分隔符比如把逗号换成|。SQL 里强行拆这种数据性价比太低。5.2 NULL、空串、开头结尾分隔符三种形态要分别处理NULL 会让SUBSTRING_INDEX直接返回 NULL拆出来的结果也是 NULL如果后续 JOIN 字典表这些 NULL 都会被过滤掉统计结果凭空少了一截空串则会产生一行空字符串瞬间多出一个“未知”分类开头或结尾有分隔符的字符串比如“a,b,”会多拆出一个空元素。我的处理模板是统一做防御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 ;先把可能出现的空白 trim 掉再把空串转成 NULL最后统一过滤。这样无论原始数据是哪种脏形态最终结果都是干净的行集。5.3 拆分结果的顺序到底能不能保证很多业务场景要求“第 1 个元素就是主分类第 2 个是副分类”顺序错了整个报表就乱了。MySQL 的序列表方案天然带序号s.n顺序是可靠且可排序的PostgreSQL 的unnest(string_to_array(...))也会保持数组原始顺序。但 SQL Server 老版STRING_SPLIT不保证顺序Oracle 的CONNECT BY在多行源表上也可能出现顺序错乱。所以凡是顺序敏感的业务我都建议在拆分结果里显式带序号字段。如果不能带序号就换递归方案或者升级到支持 ordinal 的版本。不要赌数据库“这次刚好是按顺序的”这个赌注风险太高。5.4 大表上的性能滑坡与应对思路字符串函数本身就无法使用索引拆分操作基本等于全表扫描加逐行计算。就算拆出来的结果只有几十万行源表几百万行时也能把数据库 CPU 打满。而且 seq 方案里嵌套函数会重复计算加上 JOIN 数字表的笛卡尔效应性能只会更糟。我的应对思路是“拆一次存下来反复用”。把拆分结果写入临时表或者中间表之后所有的统计、JOIN、报表都基于中间表进行绝不在大表上反复执行拆分。为了快速定位问题数据还可以先跑一个诊断 SQL查出每个字段拆分后的最大段数SELECT MAX(LENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1) AS max_parts FROM demo_table;如果max_parts异常大先处理异常数据再跑正式拆分。5.5 验证拆分正确性的自查 SQL拆分完了不能直接信结果我一般用两个维度自查。第一拆后行数是否等于所有字段的段数之和SELECT (SELECT COUNT(*) FROM split_result) AS actual_rows, (SELECT SUM(LENGTH(ids) - LENGTH(REPLACE(ids, ,, )) 1) FROM demo_table) AS expect_rows;第二抽样几条数据手工核对拆分后的字段拼接起来是否等于原串。这两个检查在数据清洗项目里能省下大量返工时间。6. 拆行之后的下一步——关联、聚合与动态透视的经典组合把字符串拆成行不是终点拆完之后的去向才是真正产生业务价值的地方。我拿一个最常见的“活动-区域”案例把拆行后的完整链路串一遍。6.1 标签类字段拆开后做分组统计假设marketing_activity表有activity_id和region_list字段值是“华东,华南,华北”。第一步拆行第二步按区域聚合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;这一步实现的本质就是把一个字段里的多个标签还原成明细行再走常规聚合。没有拆行之前这个统计根本写不出来。6.2 拆出来的 ID 与主数据表关联拆出来的 ID 是编码一般还关联字典表取名称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因为拆分结果里可能有字典表不存在的脏编码。此时GROUP BY r.region_code会把NULL区域归到同一组但r.region_name可能是 NULL需要业务上确认这种杂项统计是否要展示。6.3 动态透视思路先拆行再聚合实现多值字段的归一还有一种进阶玩法拆行后配合条件聚合把“一列多值”转成“一行多列”相当于对反规范化字段做透视。比如想知道每个活动覆盖了哪几个区域可以用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 什么时候该考虑把它落地成规范表结构如果你发现某个多值字段每周都要拆一次、拆完还要 JOIN、统计、透视那大概率是表结构设计出了问题。一次性拆分没问题长期反复拆就是技术债。正确做法是把拆分结果固化成一维子表比如“活动-区域”关系表既方便加索引也方便数据质量管理和权限控制。我在实际项目中摸索出来的经验是先把旧数据的拆分结果一次性落地成中间表交给下游使用同时推动上游业务系统在写入时同步维护好关系表双轨运行一段时间后彻底下线多值字段。这样既不用推倒重来又能让后续查询都走正路子。做字符串拆分这件事本身不难难的是想清楚“拆完之后拿它干什么”。如果你只是偶尔用一次序列表方案足够如果要长期跑还是老老实实建中间表、改表结构。别让 SQL 技巧帮一个坏设计续命太久。