1. 为什么字符串分割是SQL里的日常硬需求——先想清楚业务场景先聊点实在的。很多业务表里都有这种字段某行数据的某个字段存的是逗号分隔的ID列表或者标签集合比如商品表的tag_ids字段值为12,35,88,102用户表的role_list字段值为admin,editor,viewer。这种设计在规范的关系模型里属于反范式但现实中几乎每个系统都有尤其是对接第三方接口、处理历史数据、或者一开始建表图省事的时候这类数据已经沉淀在库底了。需求通常分几层最基础的是把这个字段拆开变成多行多列再进一步是拆开之后和维表做关联查出每个ID对应的名称或者按拆分结果做分组统计比如统计每个标签下有多少商品。这些场景在开发、数据分析、运维取数里都极其常见。我个人的感受是字符串分割本身不难难的是选对当前数据库方言下最合适的写法、处理好分隔符边界和空值、以及别让拆分查询把性能拖垮。这篇文章不绕弯子直接覆盖主流数据库环境MySQL、PostgreSQL、SQL Server、Oracle的拆分方案、参数计算逻辑、性能对比以及我自己实际踩过的坑。适合被这类需求折磨过的开发、DBA、数据分析师也适合刚接手老系统、发现表里一堆逗号分隔字段的新人参考。2. 多数据库方言的拆分方案与选型思路不同数据库的语法体系差异很大一个方案不可能通吃所有环境。选型的本质是三件事先确认你的数据库版本再确认拆分结果需不需要保留顺序最后确认数据量和执行频率。顺序错了后面全是坑。2.1 MySQL递归CTE是最通用的底牌MySQL 8.0 提供了WITH RECURSIVE可以基于递归逻辑做拆分。以拆分商品表product(id, tags)为例tags字段为逗号分隔的标签ID目标是把每行拆成多条记录WITH RECURSIVE split AS ( SELECT id, 1 AS pos, SUBSTRING_INDEX(tags, ,, 1) AS part, SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ,, 1)) 2) AS rest FROM product WHERE tags IS NOT NULL UNION ALL SELECT id, pos 1, SUBSTRING_INDEX(rest, ,, 1), SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ,, 1)) 2) FROM split WHERE rest ) SELECT id, pos, part FROM split ORDER BY id, pos;核心逻辑得拆开讲。非递归部分先取第一个逗号前的部分作为第一段然后把去掉第一段和一个逗号后剩下的内容记为rest。递归部分不断对rest重复同样的操作直到rest为空。这里LENGTH(SUBSTRING_INDEX(tags, ,, 1)) 2看起来很绕其实就是第一段的长度LENGTH再加1个逗号1再加1个起始位置偏移1合计2这样SUBSTRING才精准跳到下一段的开头。如果分隔符是中文逗号、分号或竖线把这个字符替换进去位置计算同样成立。MySQL 5.7 及更早的版本没有递归CTE就得借助数字辅助表方案。先把每条拆分成行需要先知道这条字符串有几个段。段的个数计算公式是CHAR_LENGTH(a,b,c) - CHAR_LENGTH(REPLACE(a,b,c, ,, )) 1也就是原字符总长度比去掉分隔符后长度多出的数量再加1。然后通过数字表生成序号去匹配SELECT a.id, SUBSTRING_INDEX(SUBSTRING_INDEX(a.tags, ,, n.n), ,, -1) AS part FROM product a JOIN numbers n ON CHAR_LENGTH(a.tags) - CHAR_LENGTH(REPLACE(a.tags, ,, )) 1 n.n ORDER BY a.id, n.n;numbers是预先建好的1到1000的数字表。SUBSTRING_INDEX嵌套的用法是精髓内层取出前n.n段外层用-1再取内层结果的最后一段。比如SUBSTRING_INDEX(12,35,88, ,, 2)得到12,35再SUBSTRING_INDEX(12,35, ,, -1)得到35。这套写法在5.7环境里是主流只是数字表最大编号会限制单行最多拆几段如果业务字段最多50个标签数字表建到200就足够。2.2 PostgreSQLregexp_split_to_table天生干这个PostgreSQL 里最省事的方案是regexp_split_to_table函数名直译就是把字符串按正则拆成表SELECT id, regexp_split_to_table(tags, ,) AS part FROM product;一行 SQL 就搞定多行拆分没有递归也不需要辅助表。如果表里只有少量数据直接跑这个就行。但我实测发现一个容易忽略的行为regexp_split_to_table会自动丢弃空字段。比如字符串a,,c拆出来只有a和c中间的空串直接没了。这通常不是我们想要的因为拆分结果一旦错位关联维表的时候会莫名丢数据。解决方案是用string_to_table(tags, ,)这是 PostgreSQL 14 引入的不会丢弃空字符串SELECT p.id, s.part FROM product p CROSS JOIN LATERAL string_to_table(p.tags, ,) AS s(part);如果还想保留顺序号可以在 PostgreSQL 14 之后用WITH ORDINALITYSELECT p.id, s.part, s.ordinality AS pos FROM product p CROSS JOIN LATERAL string_to_table(p.tags, ,) WITH ORDINALITY AS s(part, ordinality);WITH ORDINALITY会为 lateral 子查询的输出自动追加一个1-based的序号列相当于免费送了一个拆分后的位置信息。这个序号在做第几个标签是什么这类需求时极为有用。2.3 SQL ServerSTRING_SPLIT从2016开始就是官方答案SQL Server 2016 之后的官方方案是STRING_SPLIT配合CROSS APPLY使用非常直观SELECT p.id, s.value FROM product p CROSS APPLY STRING_SPLIT(p.tags, ,);这是所有方案里最简洁的一个。但有个经典陷阱STRING_SPLIT不保证输出顺序官方文档也明确说明顺序不受保证。如果你只是把标签列表拆开做过滤条件这无所谓但如果你要按原有顺序展示第一个标签是主标签、第二个是副标签直接依赖行序就是给自己埋雷。官方后续版本加了可选的第三参数enable_ordinal从 SQL Server 2022 开始支持SELECT p.id, s.value, s.ordinal FROM product p CROSS APPLY STRING_SPLIT(p.tags, ,, 1);ordinal就是拆分结果的顺序号。如果生产环境还是2019或更早需要顺序就绕道OPENJSONSELECT p.id, j.[value], j.[key] AS pos FROM product p CROSS APPLY OPENJSON([ REPLACE(p.tags, ,, ,) ]) AS j;OPENJSON把字符串包装成JSON数组格式后解析key就是数组的下标0-based。这个技巧在2016及以上版本可用但要注意源字符串里不能包含英文双引号或反斜杠否则JSON解析会直接报错。看过一个案例某业务字段里确实存了带引号的文本值用OPENJSON方案炸了半天最后换回递归写法教训就是能用官方字符串函数就别折腾JSON拐弯。2.4 Oracle多层CONNECT BY的经典写法Oracle 界的常见姿势是CONNECT BYREGEXP_SUBSTR的组合SELECT id, REGEXP_SUBSTR(tags, [^,], 1, level) AS part FROM product CONNECT BY LEVEL REGEXP_COUNT(tags, ,) 1;REGEXP_SUBSTR的第三个参数是起始位置第四个参数level表示第几段配合CONNECT BY LEVEL循环提取每一段。REGEXP_COUNT(tags, ,) 1计算的是总段数作为循环上限。这个写法有个需要特别注意的问题当product表有多行数据时CONNECT BY会产生行与行之间的级联重复也就是笛卡尔式的行爆炸。因为每行的connect_by_root不是单独声明的Oracle 会把整个表作为一棵树去遍历。它虽然能满足单行拆多行的需求但在多行表上直接跑经常会出现比预期多出好几倍的行数。一个比较稳妥的改写方式是借助唯一主键字段隔离迭代范围SELECT id, REGEXP_SUBSTR(tags, [^,], 1, iter.level) AS part FROM product p CROSS JOIN LATERAL ( SELECT LEVEL AS level FROM dual CONNECT BY LEVEL REGEXP_COUNT(p.tags, ,) 1 ) iter;把迭代限制在每行的 lateral 范围内避免全局树遍历。我见过不少 Oracle 环境的经典写法在生产库上把结果炸成几十万行的案例排查手段除了看执行计划还要对比COUNT(*)和预期行数是否一致。如果发现多了优先怀疑就是CONNECT BY的关联重复。2.5 其他常用环境Hive、Spark SQL、ClickHouse、SQLite大数据场景里也经常要做这种转换。Hive 和 Spark SQL 的套路是把字符串变成数组再炸开SELECT id, tag FROM product LATERAL VIEW EXPLODE(SPLIT(tags, ,)) t AS tag;SPLIT生成数组EXPLODE把数组展开成多行LATERAL VIEW保证展开后的每行还保留原行的其他字段。ClickHouse 里则是SELECT id, arrayJoin(splitByString(,, tags)) AS part FROM product;SQLite 原生没有专门函数一般用递归CTE或者先REPLACE把分隔符换成UNION ALL的套路但那是字符串拼接生成SQL的可读性灾难不如递归CTE清晰。这些方案里核心点都是先问自己要不要顺序、要不要保留空串、数据量多大答案不同选择自然不同。3. 拆分后的实践细节关联、顺序、空值、多字符分隔符很多需求不止拆开看看而是要把拆出来的ID关联到名称表。这时候最常见的错误就是拿拆分结果直接JOIN然后行数暴涨却不知道原因。本质上拆分是把一个单元格变成多行如果关联键上有重复JOIN 结果会按笛卡尔积放大。正确做法是先拆出明细表临时表或子查询明确主键和位置序号再去关联维表。比如MySQL的递归CTE结果(id, pos)可以作为唯一键的候选。顺序保留是我反复强调的点。业务上经常需要知道第一个标签是主分类标签如果没有pos字段拆分结果顺序在关系模型里是不保证的。PostgreSQL 可以用WITH ORDINALITYSQL Server 2022 可以用enable_ordinalMySQL 递归CTE的pos是自己生成的天然可靠。只要设计表的时候把pos保留下来后面按ORDER BY id, pos展示就不会乱。空串处理是下一个高频坑。字符串a,,b拆完之后中间那段的空值怎么处理如果是一个逗号分隔的ID列表中间出现空串往往意味着上游数据异常直接过滤掉可能掩盖问题。我的建议是先用 REPLACE 清理连续分隔符还是保留空串取决于你的语义。如果空串有业务含义比如用户设置了空标签保留如果没有清洗的时候可以把连续逗号替换成单个逗号例如REGEXP_REPLACE(tags, ,, ,)之后再拆。多字符分隔符也很常见比如a||b||c的||分隔。前面几种方案里REGEXP_SUBSTR和string_to_table这类支持正则或多字符模式的函数处理起来没问题但SUBSTRING_INDEX、STRING_SPLIT这类按单字符分隔的工具就不适用了。遇到这种情况绕回递归CTE用LOCATE(分隔符, rest)定位而不是写死位置比如用LOCATE(||, rest)去找每一次的分隔位置。曾经优化过的一个报表任务就是因为上游从逗号换成了竖线分隔符函数行为完全变了排查了大半天。还有一个细节字符串首尾就有分隔符比如,a,b,。按[^,]正则提取的写法Oracle[^,]、PostgreSQL 的regexp_split_to_table在某些版本行为和这个也有差异会忽略首尾空串。如果你关心空串导致的偏移需要先把首尾分隔符去掉再拆或者用按位置切割的方式保证每段都返回。4. 性能问题与写法的底层逻辑性能上第一个原则是优先用数据库内置函数避免循环调用自定义函数。有些人会把拆分逻辑封装成一个自定义函数比如MySQL里的split_str()存储函数然后在SELECT里对每行调用小数据量没问题但一旦几万行起步SQL Server 的标量函数配合逐行拆分性能可以直接掉两个量级。多数情况下用集合式的CTE或内置拆分函数优化器还能生成更合理的执行计划。第二个性能要点是递归CTE的深度和基数。假设一张表有10万行每行标签字段平均有50个标签递归CTE的中间结果就是50万行虽然技术上能跑但对内存和临时表的压力不小。我的习惯做法是需要频繁拆分的业务优先考虑建一张明细映射表比如 product_tag_mapETL的时候一次性拆好查询时直接JOIN。这样把计算成本前置到写入阶段查询侧就完全不需要在SQL里做拆分。毕竟在SQL里做字符串分割再快也快不过一张结构规整的关系表。第三个容易忽略的点是分隔符在数据里原生出现。比如地址字段里用了逗号分隔但个别地址本身就带了逗号。这种情况任何按固定分隔符暴力切的方案都救不了只能先清洗数据或者改分隔符。实测过用两个字符组合做分隔符如~~几乎不会和数据内容冲突比单字符靠谱得多。最后一个性能建议是拆分之前先看执行计划。MySQL里用数字辅助表拆分小表很快但大表 JOIN numbers 会产生一个较大的中间集。把numbers表的连接条件写好或者先按条件过滤原表再拆分效果会好很多。有人在500万行的表上直接做拆分查询跑了十几秒改成先按业务筛选条件过滤到1万行再拆瞬间变毫秒级。这个优化思路比纠结用哪种拆分函数都重要。5. 常见问题与排查技巧实录以下是我在实际项目里遇到频率最高的几类问题整理成速查表方便遇到类似情况时直接对照。异常现象常见原因排查/解决思路拆分后行数暴增CONNECT BY级联重复或 JOIN 关联键有重复用唯一主键隔离递归范围先统计拆分后行数和预期值对比结果顺序错乱SQL ServerSTRING_SPLIT不保证顺序升级到2022用enable_ordinal或改用OPENJSON取key空串被静默丢弃PostgreSQLregexp_split_to_table的行为改用string_to_tableWITH ORDINALITY包含中文/多字符分隔符时切错位置SUBSTRING_INDEX不支持多字符分隔符改用递归CTE配合LOCATE定位分隔符首尾有分隔符导致多出空行正则[^,]忽略首尾空段先TRIM/REPLACE清洗首尾或按位置方式拆分查询极慢循环调用自定义函数拆分前未过滤数据改集合式CTE先过滤原表再拆分考虑预计算明细表结果出现 NULL 行原字段为 NULL无法拆分用WHERE tags IS NOT NULL过滤或用COALESCE兜底再补几条只有实操过才会注意到的避坑经验。第一拆出的结果如果直接用于IN子查询注意别让空串参与匹配。 IN (1,2,3)不会报错但语义上容易产生误判。这也是为什么拆分前最好明确空串策略的原因。第二在MySQL里用SUBSTRING_INDEX嵌套方案时分隔符出现在最后一位的情况比如a,b,会生成一个空字符串作为最后一段。如果你不需要这个空段在递归终止条件里把rest 改成rest AND part 更稳妥。但这样会跳过数据本身的问题属于掩盖问题而非解决问题如果上游生成的数据规则不允许尾分隔符存在更合理的做法是修正上游规则。第三PostgreSQL 的regexp_split_to_table走的是正则引擎分隔符如果包含正则特殊字符比如.或|需要转义。我自己就遇到过一次用.做分隔符把IP字符串切开的需求直接用.去拆结果小数点全部匹配成了通配符行数完全不对。正确写法是regexp_split_to_table(ip, \.)。简单场景下优先用string_to_table它按字面分隔符拆分没有正则转义的负担。第四SQL Server 里STRING_SPLIT输出行的排序不确定性在官方文档里写得清清楚楚但在很多线上代码里还是有人默认它有序。我曾经协助排查过一个报表模块某天的数据顺序偶尔错乱监控了很久才发现是开发早期用了STRING_SPLIT且没有ordinal后来升级到2022加了enable_ordinal 1才稳定。生产库版本升级不是小事如果暂时无法升级就用OPENJSON或者自己写个带序号的递归方案。第五Oracle 的REGEXP_SUBSTR对 NULL 或空串的处理容易让人看走眼。空串在 Oracle 里视为 NULL所以REGEXP_SUBSTR(, [^,], 1, 1)返回的是 NULL。若你的业务里空值和空串有区别拆分前必须用NVL或者LENGTH判断把空串场景单独处理。6. 从拆分出发如何让数据模型少走弯路做久了会发现字符串拆分本身往往不是根本解法。很多时候这种表结构本身就是历史遗留问题的体现——一个字段里塞多个值违反了关系模型的第一范式。与其每次都花精力写拆分SQL不如思考怎么让数据模型更合理。然而现实业务里改表结构、改写入逻辑、做数据迁移的成本高到离谱老系统里的逗号分隔字段通常只能继续凑合着用。这时候把拆分逻辑沉淀成一套标准工具或者固定做法反而是性价比最高的方案。我觉得一个比较务实的做法是在团队内部维护一套字段拆分规范。包括分隔符统一用逗号还是竖线拆出来的结果要不要带顺序号空值怎么处理数据要不要经过清洗后再入库。这些约定可以避免每个开发都凭自己的理解写一套逻辑后续维护就轻松得多。另外如果这类拆分需求出现的频率非常高我会强烈建议在ETL/数仓层面做一次性拆分产出明细表。比如每日凌晨把商品标签拆分结果写进一张标签明细表业务查询直接读明细表SQL简单、索引友好、执行快。这比在报表查询SQL里现场拆分要稳定得多。查询场景、报表场景、线上交易场景三者的性能要求完全不同方案也要跟着场景走不存在一个万能答案。我个人的实操体会是遇到SQL按指定字符分割字符串这类需求先别急着写SQL花几分钟想清楚三个问题数据是历史遗留还是每天产生拆分结果用在什么地方允不允许换一种存储方式这三个问题的答案往往能帮你省下大量的排查时间。字符串分割的SQL写法就那么多真正拉开好坏差距的是写之前有没有想清楚业务和数据的全貌。 
长河君 · 2026/10/13 4:11:58 · 阅读 1.2k SQL字符串分割全攻略：主流数据库拆分方案与避坑指南
1. 为什么字符串分割是SQL里的日常硬需求——先想清楚业务场景先聊点实在的。很多业务表里都有这种字段某行数据的某个字段存的是逗号分隔的ID列表或者标签集合比如商品表的tag_ids字段值为12,35,88,102用户表的role_list字段值为admin,editor,viewer。这种设计在规范的关系模型里属于反范式但现实中几乎每个系统都有尤其是对接第三方接口、处理历史数据、或者一开始建表图省事的时候这类数据已经沉淀在库底了。需求通常分几层最基础的是把这个字段拆开变成多行多列再进一步是拆开之后和维表做关联查出每个ID对应的名称或者按拆分结果做分组统计比如统计每个标签下有多少商品。这些场景在开发、数据分析、运维取数里都极其常见。我个人的感受是字符串分割本身不难难的是选对当前数据库方言下最合适的写法、处理好分隔符边界和空值、以及别让拆分查询把性能拖垮。这篇文章不绕弯子直接覆盖主流数据库环境MySQL、PostgreSQL、SQL Server、Oracle的拆分方案、参数计算逻辑、性能对比以及我自己实际踩过的坑。适合被这类需求折磨过的开发、DBA、数据分析师也适合刚接手老系统、发现表里一堆逗号分隔字段的新人参考。2. 多数据库方言的拆分方案与选型思路不同数据库的语法体系差异很大一个方案不可能通吃所有环境。选型的本质是三件事先确认你的数据库版本再确认拆分结果需不需要保留顺序最后确认数据量和执行频率。顺序错了后面全是坑。2.1 MySQL递归CTE是最通用的底牌MySQL 8.0 提供了WITH RECURSIVE可以基于递归逻辑做拆分。以拆分商品表product(id, tags)为例tags字段为逗号分隔的标签ID目标是把每行拆成多条记录WITH RECURSIVE split AS ( SELECT id, 1 AS pos, SUBSTRING_INDEX(tags, ,, 1) AS part, SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ,, 1)) 2) AS rest FROM product WHERE tags IS NOT NULL UNION ALL SELECT id, pos 1, SUBSTRING_INDEX(rest, ,, 1), SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ,, 1)) 2) FROM split WHERE rest ) SELECT id, pos, part FROM split ORDER BY id, pos;核心逻辑得拆开讲。非递归部分先取第一个逗号前的部分作为第一段然后把去掉第一段和一个逗号后剩下的内容记为rest。递归部分不断对rest重复同样的操作直到rest为空。这里LENGTH(SUBSTRING_INDEX(tags, ,, 1)) 2看起来很绕其实就是第一段的长度LENGTH再加1个逗号1再加1个起始位置偏移1合计2这样SUBSTRING才精准跳到下一段的开头。如果分隔符是中文逗号、分号或竖线把这个字符替换进去位置计算同样成立。MySQL 5.7 及更早的版本没有递归CTE就得借助数字辅助表方案。先把每条拆分成行需要先知道这条字符串有几个段。段的个数计算公式是CHAR_LENGTH(a,b,c) - CHAR_LENGTH(REPLACE(a,b,c, ,, )) 1也就是原字符总长度比去掉分隔符后长度多出的数量再加1。然后通过数字表生成序号去匹配SELECT a.id, SUBSTRING_INDEX(SUBSTRING_INDEX(a.tags, ,, n.n), ,, -1) AS part FROM product a JOIN numbers n ON CHAR_LENGTH(a.tags) - CHAR_LENGTH(REPLACE(a.tags, ,, )) 1 n.n ORDER BY a.id, n.n;numbers是预先建好的1到1000的数字表。SUBSTRING_INDEX嵌套的用法是精髓内层取出前n.n段外层用-1再取内层结果的最后一段。比如SUBSTRING_INDEX(12,35,88, ,, 2)得到12,35再SUBSTRING_INDEX(12,35, ,, -1)得到35。这套写法在5.7环境里是主流只是数字表最大编号会限制单行最多拆几段如果业务字段最多50个标签数字表建到200就足够。2.2 PostgreSQLregexp_split_to_table天生干这个PostgreSQL 里最省事的方案是regexp_split_to_table函数名直译就是把字符串按正则拆成表SELECT id, regexp_split_to_table(tags, ,) AS part FROM product;一行 SQL 就搞定多行拆分没有递归也不需要辅助表。如果表里只有少量数据直接跑这个就行。但我实测发现一个容易忽略的行为regexp_split_to_table会自动丢弃空字段。比如字符串a,,c拆出来只有a和c中间的空串直接没了。这通常不是我们想要的因为拆分结果一旦错位关联维表的时候会莫名丢数据。解决方案是用string_to_table(tags, ,)这是 PostgreSQL 14 引入的不会丢弃空字符串SELECT p.id, s.part FROM product p CROSS JOIN LATERAL string_to_table(p.tags, ,) AS s(part);如果还想保留顺序号可以在 PostgreSQL 14 之后用WITH ORDINALITYSELECT p.id, s.part, s.ordinality AS pos FROM product p CROSS JOIN LATERAL string_to_table(p.tags, ,) WITH ORDINALITY AS s(part, ordinality);WITH ORDINALITY会为 lateral 子查询的输出自动追加一个1-based的序号列相当于免费送了一个拆分后的位置信息。这个序号在做第几个标签是什么这类需求时极为有用。2.3 SQL ServerSTRING_SPLIT从2016开始就是官方答案SQL Server 2016 之后的官方方案是STRING_SPLIT配合CROSS APPLY使用非常直观SELECT p.id, s.value FROM product p CROSS APPLY STRING_SPLIT(p.tags, ,);这是所有方案里最简洁的一个。但有个经典陷阱STRING_SPLIT不保证输出顺序官方文档也明确说明顺序不受保证。如果你只是把标签列表拆开做过滤条件这无所谓但如果你要按原有顺序展示第一个标签是主标签、第二个是副标签直接依赖行序就是给自己埋雷。官方后续版本加了可选的第三参数enable_ordinal从 SQL Server 2022 开始支持SELECT p.id, s.value, s.ordinal FROM product p CROSS APPLY STRING_SPLIT(p.tags, ,, 1);ordinal就是拆分结果的顺序号。如果生产环境还是2019或更早需要顺序就绕道OPENJSONSELECT p.id, j.[value], j.[key] AS pos FROM product p CROSS APPLY OPENJSON([ REPLACE(p.tags, ,, ,) ]) AS j;OPENJSON把字符串包装成JSON数组格式后解析key就是数组的下标0-based。这个技巧在2016及以上版本可用但要注意源字符串里不能包含英文双引号或反斜杠否则JSON解析会直接报错。看过一个案例某业务字段里确实存了带引号的文本值用OPENJSON方案炸了半天最后换回递归写法教训就是能用官方字符串函数就别折腾JSON拐弯。2.4 Oracle多层CONNECT BY的经典写法Oracle 界的常见姿势是CONNECT BYREGEXP_SUBSTR的组合SELECT id, REGEXP_SUBSTR(tags, [^,], 1, level) AS part FROM product CONNECT BY LEVEL REGEXP_COUNT(tags, ,) 1;REGEXP_SUBSTR的第三个参数是起始位置第四个参数level表示第几段配合CONNECT BY LEVEL循环提取每一段。REGEXP_COUNT(tags, ,) 1计算的是总段数作为循环上限。这个写法有个需要特别注意的问题当product表有多行数据时CONNECT BY会产生行与行之间的级联重复也就是笛卡尔式的行爆炸。因为每行的connect_by_root不是单独声明的Oracle 会把整个表作为一棵树去遍历。它虽然能满足单行拆多行的需求但在多行表上直接跑经常会出现比预期多出好几倍的行数。一个比较稳妥的改写方式是借助唯一主键字段隔离迭代范围SELECT id, REGEXP_SUBSTR(tags, [^,], 1, iter.level) AS part FROM product p CROSS JOIN LATERAL ( SELECT LEVEL AS level FROM dual CONNECT BY LEVEL REGEXP_COUNT(p.tags, ,) 1 ) iter;把迭代限制在每行的 lateral 范围内避免全局树遍历。我见过不少 Oracle 环境的经典写法在生产库上把结果炸成几十万行的案例排查手段除了看执行计划还要对比COUNT(*)和预期行数是否一致。如果发现多了优先怀疑就是CONNECT BY的关联重复。2.5 其他常用环境Hive、Spark SQL、ClickHouse、SQLite大数据场景里也经常要做这种转换。Hive 和 Spark SQL 的套路是把字符串变成数组再炸开SELECT id, tag FROM product LATERAL VIEW EXPLODE(SPLIT(tags, ,)) t AS tag;SPLIT生成数组EXPLODE把数组展开成多行LATERAL VIEW保证展开后的每行还保留原行的其他字段。ClickHouse 里则是SELECT id, arrayJoin(splitByString(,, tags)) AS part FROM product;SQLite 原生没有专门函数一般用递归CTE或者先REPLACE把分隔符换成UNION ALL的套路但那是字符串拼接生成SQL的可读性灾难不如递归CTE清晰。这些方案里核心点都是先问自己要不要顺序、要不要保留空串、数据量多大答案不同选择自然不同。3. 拆分后的实践细节关联、顺序、空值、多字符分隔符很多需求不止拆开看看而是要把拆出来的ID关联到名称表。这时候最常见的错误就是拿拆分结果直接JOIN然后行数暴涨却不知道原因。本质上拆分是把一个单元格变成多行如果关联键上有重复JOIN 结果会按笛卡尔积放大。正确做法是先拆出明细表临时表或子查询明确主键和位置序号再去关联维表。比如MySQL的递归CTE结果(id, pos)可以作为唯一键的候选。顺序保留是我反复强调的点。业务上经常需要知道第一个标签是主分类标签如果没有pos字段拆分结果顺序在关系模型里是不保证的。PostgreSQL 可以用WITH ORDINALITYSQL Server 2022 可以用enable_ordinalMySQL 递归CTE的pos是自己生成的天然可靠。只要设计表的时候把pos保留下来后面按ORDER BY id, pos展示就不会乱。空串处理是下一个高频坑。字符串a,,b拆完之后中间那段的空值怎么处理如果是一个逗号分隔的ID列表中间出现空串往往意味着上游数据异常直接过滤掉可能掩盖问题。我的建议是先用 REPLACE 清理连续分隔符还是保留空串取决于你的语义。如果空串有业务含义比如用户设置了空标签保留如果没有清洗的时候可以把连续逗号替换成单个逗号例如REGEXP_REPLACE(tags, ,, ,)之后再拆。多字符分隔符也很常见比如a||b||c的||分隔。前面几种方案里REGEXP_SUBSTR和string_to_table这类支持正则或多字符模式的函数处理起来没问题但SUBSTRING_INDEX、STRING_SPLIT这类按单字符分隔的工具就不适用了。遇到这种情况绕回递归CTE用LOCATE(分隔符, rest)定位而不是写死位置比如用LOCATE(||, rest)去找每一次的分隔位置。曾经优化过的一个报表任务就是因为上游从逗号换成了竖线分隔符函数行为完全变了排查了大半天。还有一个细节字符串首尾就有分隔符比如,a,b,。按[^,]正则提取的写法Oracle[^,]、PostgreSQL 的regexp_split_to_table在某些版本行为和这个也有差异会忽略首尾空串。如果你关心空串导致的偏移需要先把首尾分隔符去掉再拆或者用按位置切割的方式保证每段都返回。4. 性能问题与写法的底层逻辑性能上第一个原则是优先用数据库内置函数避免循环调用自定义函数。有些人会把拆分逻辑封装成一个自定义函数比如MySQL里的split_str()存储函数然后在SELECT里对每行调用小数据量没问题但一旦几万行起步SQL Server 的标量函数配合逐行拆分性能可以直接掉两个量级。多数情况下用集合式的CTE或内置拆分函数优化器还能生成更合理的执行计划。第二个性能要点是递归CTE的深度和基数。假设一张表有10万行每行标签字段平均有50个标签递归CTE的中间结果就是50万行虽然技术上能跑但对内存和临时表的压力不小。我的习惯做法是需要频繁拆分的业务优先考虑建一张明细映射表比如 product_tag_mapETL的时候一次性拆好查询时直接JOIN。这样把计算成本前置到写入阶段查询侧就完全不需要在SQL里做拆分。毕竟在SQL里做字符串分割再快也快不过一张结构规整的关系表。第三个容易忽略的点是分隔符在数据里原生出现。比如地址字段里用了逗号分隔但个别地址本身就带了逗号。这种情况任何按固定分隔符暴力切的方案都救不了只能先清洗数据或者改分隔符。实测过用两个字符组合做分隔符如~~几乎不会和数据内容冲突比单字符靠谱得多。最后一个性能建议是拆分之前先看执行计划。MySQL里用数字辅助表拆分小表很快但大表 JOIN numbers 会产生一个较大的中间集。把numbers表的连接条件写好或者先按条件过滤原表再拆分效果会好很多。有人在500万行的表上直接做拆分查询跑了十几秒改成先按业务筛选条件过滤到1万行再拆瞬间变毫秒级。这个优化思路比纠结用哪种拆分函数都重要。5. 常见问题与排查技巧实录以下是我在实际项目里遇到频率最高的几类问题整理成速查表方便遇到类似情况时直接对照。异常现象常见原因排查/解决思路拆分后行数暴增CONNECT BY级联重复或 JOIN 关联键有重复用唯一主键隔离递归范围先统计拆分后行数和预期值对比结果顺序错乱SQL ServerSTRING_SPLIT不保证顺序升级到2022用enable_ordinal或改用OPENJSON取key空串被静默丢弃PostgreSQLregexp_split_to_table的行为改用string_to_tableWITH ORDINALITY包含中文/多字符分隔符时切错位置SUBSTRING_INDEX不支持多字符分隔符改用递归CTE配合LOCATE定位分隔符首尾有分隔符导致多出空行正则[^,]忽略首尾空段先TRIM/REPLACE清洗首尾或按位置方式拆分查询极慢循环调用自定义函数拆分前未过滤数据改集合式CTE先过滤原表再拆分考虑预计算明细表结果出现 NULL 行原字段为 NULL无法拆分用WHERE tags IS NOT NULL过滤或用COALESCE兜底再补几条只有实操过才会注意到的避坑经验。第一拆出的结果如果直接用于IN子查询注意别让空串参与匹配。 IN (1,2,3)不会报错但语义上容易产生误判。这也是为什么拆分前最好明确空串策略的原因。第二在MySQL里用SUBSTRING_INDEX嵌套方案时分隔符出现在最后一位的情况比如a,b,会生成一个空字符串作为最后一段。如果你不需要这个空段在递归终止条件里把rest 改成rest AND part 更稳妥。但这样会跳过数据本身的问题属于掩盖问题而非解决问题如果上游生成的数据规则不允许尾分隔符存在更合理的做法是修正上游规则。第三PostgreSQL 的regexp_split_to_table走的是正则引擎分隔符如果包含正则特殊字符比如.或|需要转义。我自己就遇到过一次用.做分隔符把IP字符串切开的需求直接用.去拆结果小数点全部匹配成了通配符行数完全不对。正确写法是regexp_split_to_table(ip, \.)。简单场景下优先用string_to_table它按字面分隔符拆分没有正则转义的负担。第四SQL Server 里STRING_SPLIT输出行的排序不确定性在官方文档里写得清清楚楚但在很多线上代码里还是有人默认它有序。我曾经协助排查过一个报表模块某天的数据顺序偶尔错乱监控了很久才发现是开发早期用了STRING_SPLIT且没有ordinal后来升级到2022加了enable_ordinal 1才稳定。生产库版本升级不是小事如果暂时无法升级就用OPENJSON或者自己写个带序号的递归方案。第五Oracle 的REGEXP_SUBSTR对 NULL 或空串的处理容易让人看走眼。空串在 Oracle 里视为 NULL所以REGEXP_SUBSTR(, [^,], 1, 1)返回的是 NULL。若你的业务里空值和空串有区别拆分前必须用NVL或者LENGTH判断把空串场景单独处理。6. 从拆分出发如何让数据模型少走弯路做久了会发现字符串拆分本身往往不是根本解法。很多时候这种表结构本身就是历史遗留问题的体现——一个字段里塞多个值违反了关系模型的第一范式。与其每次都花精力写拆分SQL不如思考怎么让数据模型更合理。然而现实业务里改表结构、改写入逻辑、做数据迁移的成本高到离谱老系统里的逗号分隔字段通常只能继续凑合着用。这时候把拆分逻辑沉淀成一套标准工具或者固定做法反而是性价比最高的方案。我觉得一个比较务实的做法是在团队内部维护一套字段拆分规范。包括分隔符统一用逗号还是竖线拆出来的结果要不要带顺序号空值怎么处理数据要不要经过清洗后再入库。这些约定可以避免每个开发都凭自己的理解写一套逻辑后续维护就轻松得多。另外如果这类拆分需求出现的频率非常高我会强烈建议在ETL/数仓层面做一次性拆分产出明细表。比如每日凌晨把商品标签拆分结果写进一张标签明细表业务查询直接读明细表SQL简单、索引友好、执行快。这比在报表查询SQL里现场拆分要稳定得多。查询场景、报表场景、线上交易场景三者的性能要求完全不同方案也要跟着场景走不存在一个万能答案。我个人的实操体会是遇到SQL按指定字符分割字符串这类需求先别急着写SQL花几分钟想清楚三个问题数据是历史遗留还是每天产生拆分结果用在什么地方允不允许换一种存储方式这三个问题的答案往往能帮你省下大量的排查时间。字符串分割的SQL写法就那么多真正拉开好坏差距的是写之前有没有想清楚业务和数据的全貌。