1. 为什么我要自己写一个字符串分割函数但凡做过几年数据开发的人大概率都遇到过这种场景前端传过来一串用逗号拼起来的ID比如1001,1002,1003需要在数据库里把它拆成多行做关联查询或者从上游系统同步过来一批标签数据用竖线分隔要拆开做统计分析。这时候你打开数据库一看SQL Server 有STRING_SPLIT但那玩意儿在早期版本里根本不支持输出序号而且对中文逗号、多字符分隔符的处理也不够灵活MySQL 倒是有SUBSTRING_INDEX可它只能取第N个想拆成结果集还得配合数字辅助表写起来又臭又长。我在实际项目里被这个问题反复折磨过好几次。最典型的一次是做一个报表系统业务方要求支持多选筛选前端把选中的部门编码用逗号拼成一个字符串丢过来后端直接把这个字符串塞进IN条件里。结果就是WHERE dept_code IN (1001,1002,1003)这种写法数据库把它当成一个完整的字符串去匹配永远查不出数据。当时年轻第一反应是在应用层拆好再拼SQL但这样又带来了SQL注入的风险和性能问题。后来痛定思痛决定在数据库层面自己实现一套通用的字符串分割函数SQL Server 和 MySQL 各写一份彻底解决这类问题。这篇文章就是把我这些年写过的、改过的、踩过坑的分割函数实现整理出来。不管你是刚入行的数据库新手还是做了多年CRUD想补补底层功底的老手都能从里面找到可以直接抄作业的代码。我会把每个实现的设计思路、参数选择、性能考量、以及实际使用中遇到的坑都讲清楚让你不仅拿到代码还知道为什么这么写。2. 字符串分割的核心设计思路拆解2.1 到底该用函数还是存储过程这是第一个要做的决策。函数和存储过程在数据库里是两种不同的存在选错了后面会很难受。标量函数Scalar Function返回单个值表值函数Table-Valued Function返回一张表。字符串分割的本质是一行变多行所以表值函数是天然的选择。存储过程虽然也能返回结果集但它不能用在SELECT ... FROM里面也没法跟其他表做 JOIN灵活性差了一大截。我早期图省事写过存储过程版本结果业务方要求把分割结果跟订单表关联一下当场就傻眼了只能推倒重来。所以结论很明确优先用表值函数。SQL Server 里叫Table-Valued FunctionMySQL 里没有真正的表值函数但可以用存储过程配合临时表来模拟或者干脆用递归CTE公用表表达式来实现。下面我会分别讲。2.2 分隔符的处理策略分隔符这东西看着简单实际坑很多。单字符分隔符比如逗号、竖线最好处理多字符分隔符比如||、::就麻烦一些。还有一个容易被忽略的点连续分隔符。比如1001,,1003中间有两个逗号你是当成一个空字符串处理还是直接跳过这个取决于业务需求但函数最好提供一个开关参数来控制。我的做法是默认跳过空字符串因为实际业务里连续分隔符往往意味着数据质量问题返回空行反而会干扰后续查询。但如果业务明确需要保留空位比如解析CSV时列位置有意义那就得提供选项。2.3 序号输出的必要性很多人写分割函数只返回拆分后的值不返回序号。这在简单场景下够用但一旦涉及到按原始顺序排列或者取第N个元素没有序号就抓瞎了。比如前端传过来张三,李四,王五你想知道李四是第几个没有序号列就只能靠字符串查找效率极低。所以我的函数设计里序号列是标配。SQL Server 的STRING_SPLIT在2016版本刚出来时就没有序号直到2022版本才加了ordinal参数这就是血的教训。自己写函数一定要把序号带上。2.4 性能与内存的权衡字符串分割本质上是一个循环或者递归的过程。循环次数等于分隔符出现的次数加一。对于短字符串几百个字符怎么实现都无所谓但如果字符串长度上万分隔符上千个性能差异就出来了。SQL Server 里常见的实现方式有三种WHILE循环配合CHARINDEX、递归CTE、以及利用XML或JSON解析。WHILE循环最直观但性能一般递归CTE写起来优雅但默认递归深度限制是100超过就会报错需要加OPTION (MAXRECURSION 0)XML 方式性能最好但代码可读性差而且对特殊字符需要转义。MySQL 8.0 之前没有递归CTE只能用数字辅助表或者存储过程循环。8.0 之后可以用递归CTE写法跟 SQL Server 类似。我下面会给出两种数据库各自最稳妥的实现。3. SQL Server 表值函数完整实现3.1 基础版本WHILE循环实现先看最直观的版本适合理解原理也适合在低版本 SQL Server 上使用。CREATE FUNCTION dbo.fn_SplitString ( InputString NVARCHAR(MAX), Delimiter NVARCHAR(10) ) RETURNS Result TABLE ( Id INT IDENTITY(1,1), Value NVARCHAR(MAX) ) AS BEGIN IF InputString IS NULL OR LEN(InputString) 0 RETURN; DECLARE StartIndex INT 1; DECLARE EndIndex INT; DECLARE Item NVARCHAR(MAX); SET InputString InputString Delimiter; WHILE CHARINDEX(Delimiter, InputString, StartIndex) 0 BEGIN SET EndIndex CHARINDEX(Delimiter, InputString, StartIndex); SET Item SUBSTRING(InputString, StartIndex, EndIndex - StartIndex); IF LEN(Item) 0 INSERT INTO Result (Value) VALUES (Item); SET StartIndex EndIndex LEN(Delimiter); END RETURN; END这个版本有几个关键点值得说。第一我在输入字符串末尾追加了一个分隔符这样循环条件统一不用在循环外单独处理最后一段。第二Delimiter的长度用LEN而不是固定1这样支持多字符分隔符。第三IF LEN(Item) 0这行就是控制是否跳过空字符串的开关如果你需要保留空位把这行去掉即可。调用方式SELECT * FROM dbo.fn_SplitString(1001,1002,1003, ,);结果会返回三行Id 分别是1、2、3Value 分别是1001、1002、1003。3.2 进阶版本支持序号和空值保留基础版本够用但不够灵活。实际项目里我更需要一个带选项参数的版本。CREATE FUNCTION dbo.fn_SplitStringEx ( InputString NVARCHAR(MAX), Delimiter NVARCHAR(10), KeepEmpty BIT 0 ) RETURNS Result TABLE ( Ordinal INT, Value NVARCHAR(MAX) ) AS BEGIN IF InputString IS NULL OR LEN(InputString) 0 RETURN; DECLARE StartIndex INT 1; DECLARE EndIndex INT; DECLARE Item NVARCHAR(MAX); DECLARE Ordinal INT 0; SET InputString InputString Delimiter; WHILE CHARINDEX(Delimiter, InputString, StartIndex) 0 BEGIN SET EndIndex CHARINDEX(Delimiter, InputString, StartIndex); SET Item SUBSTRING(InputString, StartIndex, EndIndex - StartIndex); SET Ordinal Ordinal 1; IF KeepEmpty 1 OR LEN(Item) 0 INSERT INTO Result (Ordinal, Value) VALUES (Ordinal, Item); SET StartIndex EndIndex LEN(Delimiter); END RETURN; END这个版本把序号显式地作为一列返回而且序号是在插入前就递增的所以即使跳过了空字符串序号也不会断。这一点很重要因为有时候你需要知道某个值在原始字符串里的位置。3.3 高性能版本XML解析法当字符串很长、分割次数很多时WHILE循环的性能会成为瓶颈。这时候可以用 XML 方式把字符串转成 XML 节点列表一次性解析。CREATE FUNCTION dbo.fn_SplitStringXml ( InputString NVARCHAR(MAX), Delimiter NVARCHAR(10) ) RETURNS TABLE AS RETURN ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Ordinal, LTRIM(RTRIM(T.c.value(., NVARCHAR(MAX)))) AS Value FROM ( SELECT CAST(x REPLACE( REPLACE( REPLACE(InputString, , amp;), , lt;), Delimiter, /xx) /x AS XML) AS XmlData ) AS T CROSS APPLY XmlData.nodes(/x) AS T2(c) WHERE LTRIM(RTRIM(T.c.value(., NVARCHAR(MAX)))) );这个版本是内联表值函数Inline TVF性能比多语句表值函数好很多因为查询优化器可以把它展开优化。注意里面的REPLACE嵌套是为了处理 XML 特殊字符。必须最先替换否则会把后面替换产生的lt;里的又转义一遍导致结果错误。这个顺序坑了我半个小时。注意XML 方式对分隔符里包含 XML 特殊字符的情况支持不好如果你的分隔符是或者建议还是用 WHILE 版本。3.4 三种实现方式的对比实现方式适用场景优点缺点WHILE循环通用场景、低版本逻辑直观、易调试长字符串性能差带选项WHILE需要控制空值灵活、序号可控同上XML解析高性能要求速度快、内联可优化特殊字符需转义我个人的选择是日常用带选项的 WHILE 版本数据量大的ETL场景用 XML 版本。两者可以共存按需调用。4. MySQL 存储过程与递归CTE实现4.1 MySQL 8.0 递归CTE方案MySQL 8.0 引入了递归CTE这让字符串分割变得优雅很多。核心思路是每次递归找到第一个分隔符切出前面的部分然后把剩下的部分传给下一轮递归。WITH RECURSIVE split_cte AS ( SELECT 1 AS ordinal, SUBSTRING_INDEX(1001,1002,1003, ,, 1) AS value, SUBSTRING(1001,1002,1003, LENGTH(SUBSTRING_INDEX(1001,1002,1003, ,, 1)) 2) AS remainder UNION ALL SELECT ordinal 1, SUBSTRING_INDEX(remainder, ,, 1), IF(LOCATE(,, remainder) 0, SUBSTRING(remainder, LENGTH(SUBSTRING_INDEX(remainder, ,, 1)) 2), ) FROM split_cte WHERE remainder ) SELECT ordinal, value FROM split_cte;这里有几个细节。SUBSTRING_INDEX(str, ,, 1)取第一个分隔符之前的部分。LENGTH(...) 2里的2是因为分隔符本身占一个字符再加1跳过它。如果分隔符是多字符这个偏移量要相应调整。remainder 是递归终止条件。这个写法可以直接用在查询里不需要创建存储过程。但如果你要在多个地方复用还是封装成存储过程更好。4.2 MySQL 存储过程版本MySQL 的存储过程可以返回结果集虽然不能像表值函数那样用在 FROM 子句里但配合临时表也能达到类似效果。DELIMITER $$ CREATE PROCEDURE sp_SplitString( IN p_input VARCHAR(65535), IN p_delimiter VARCHAR(10) ) BEGIN DECLARE v_start INT DEFAULT 1; DECLARE v_end INT; DECLARE v_item VARCHAR(1000); DECLARE v_ordinal INT DEFAULT 0; DROP TEMPORARY TABLE IF EXISTS tmp_split_result; CREATE TEMPORARY TABLE tmp_split_result ( ordinal INT, value VARCHAR(1000) ); IF p_input IS NULL OR LENGTH(p_input) 0 THEN SELECT * FROM tmp_split_result; ELSE SET p_input CONCAT(p_input, p_delimiter); WHILE LOCATE(p_delimiter, p_input, v_start) 0 DO SET v_end LOCATE(p_delimiter, p_input, v_start); SET v_item SUBSTRING(p_input, v_start, v_end - v_start); SET v_ordinal v_ordinal 1; IF LENGTH(v_item) 0 THEN INSERT INTO tmp_split_result VALUES (v_ordinal, v_item); END IF; SET v_start v_end LENGTH(p_delimiter); END WHILE; SELECT * FROM tmp_split_result; END IF; END$$ DELIMITER ;调用方式CALL sp_SplitString(1001,1002,1003, ,);这个存储过程每次调用都会重建临时表所以不用担心数据残留。但要注意临时表是会话级别的同一个会话里多次调用会互相覆盖如果嵌套调用会有问题。实际使用中我建议把结果先落到普通表或者用递归CTE替代。4.3 MySQL 数字辅助表方案在 MySQL 8.0 之前没有递归CTE最常用的方案是数字辅助表。思路是预先准备一张包含连续整数的表然后用SUBSTRING_INDEX配合序号来切分。-- 创建数字辅助表 CREATE TABLE numbers (n INT PRIMARY KEY); INSERT INTO numbers (n) SELECT a.N b.N * 10 c.N * 100 1 FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c ORDER BY 1;这张表包含1到1000的连续整数足够大多数场景使用。然后分割查询这样写SELECT n AS ordinal, SUBSTRING_INDEX(SUBSTRING_INDEX(1001,1002,1003, ,, n), ,, -1) AS value FROM numbers WHERE n LENGTH(1001,1002,1003) - LENGTH(REPLACE(1001,1002,1003, ,, )) 1;这个写法的精髓在于SUBSTRING_INDEX嵌套。内层取前N段外层取最后一段就得到了第N个元素。WHERE条件计算分隔符数量加一就是总段数。这个方案性能很好因为不需要循环纯集合操作。提示数字辅助表的行数要大于最大可能的分割段数。如果字符串可能包含上千个分隔符辅助表就要建到上千行。我一般建到10000行用交叉连接很容易生成。4.4 两种数据库实现的差异对照对比项SQL ServerMySQL函数类型表值函数可用于FROM存储过程或CTE递归支持递归CTE默认深度1008.0支持递归CTE分隔符处理CHARINDEX定位LOCATE定位序号输出IDENTITY或ROW_NUMBER变量递增或ROW_NUMBER性能优化XML解析、内联TVF数字辅助表5. 实操过程中的关键细节与避坑经验5.1 分隔符长度导致的偏移量错误这是最容易踩的坑。很多人在计算下一个起始位置时习惯性地写EndIndex 1默认分隔符只有一个字符。但如果分隔符是||加1就错了会漏掉一个字符。正确的做法是EndIndex LEN(Delimiter)。我在一个项目里用::做分隔符就是因为这个加1的问题导致所有偶数位置的数据都少了一个字符排查了半天才发现。5.2 空字符串与NULL的区别LEN()返回0LEN(NULL)返回NULL。在IF判断里NULL会导致条件为假所以如果输入是NULL函数会直接返回空结果集这是符合预期的。但如果你在函数内部对NULL做了拼接操作比如SET InputString InputString Delimiter结果还是NULL循环条件CHARINDEX返回NULL循环体不会执行最终返回空结果集。这个行为是安全的但最好在函数开头显式判断NULL并返回。5.3 递归CTE的深度限制SQL Server 的递归CTE默认最大递归深度是100。如果你的字符串有超过100个分隔符就会报错The statement terminated. The maximum recursion 100 has been exhausted。解决办法是在查询末尾加OPTION (MAXRECURSION 0)0表示不限制。但要注意不限制递归深度有栈溢出的风险对于超长字符串还是用WHILE循环或者数字辅助表更稳妥。5.4 临时表在存储过程中的并发问题MySQL 存储过程里用临时表存分割结果在同一个会话里多次调用会互相干扰。比如你在一个存储过程里调用了分割过程然后又调用另一个也用了同名临时表的过程数据就乱了。我的做法是临时表名加上随机后缀或者干脆用递归CTE替代存储过程。如果非要用临时表记得在过程开头DROP TEMPORARY TABLE IF EXISTS过程结束时不删让会话结束时自动清理。5.5 性能实测数据参考我在一台测试机上做过对比输入字符串包含1000个逗号分隔的数字SQL Server 三种实现方式的耗时大致如下实现方式耗时毫秒备注WHILE循环约120稳定但偏慢XML解析约15最快递归CTE约80受递归深度影响MySQL 方面数字辅助表方案约20毫秒存储过程循环约150毫秒递归CTE约60毫秒。数据仅供参考实际性能跟硬件和字符串特征有关但趋势是一致的集合操作优于循环内联优于多语句。6. 常见问题排查与速查表6.1 分割结果少了最后一段这是最经典的问题。原因通常是循环终止条件写成了CHARINDEX(Delimiter, InputString, StartIndex) 0但没有在输入字符串末尾补分隔符。最后一段后面没有分隔符CHARINDEX返回0循环退出最后一段就丢了。解决办法就是在循环前执行SET InputString InputString Delimiter。6.2 中文分隔符乱码如果分隔符是中文逗号在NVARCHAR类型下没问题但如果输入字符串是VARCHAR类型而数据库排序规则不是中文就可能出现乱码。建议函数参数统一用NVARCHAR调用时字符串前加N前缀比如N10011002。6.3 分割后需要去重有时候业务要求分割后去重。这个不要在函数里做因为函数应该保持单一职责。在调用函数后加DISTINCT或者GROUP BY即可。比如SELECT DISTINCT Value FROM dbo.fn_SplitString(a,b,a,c, ,);6.4 常见问题速查表问题现象可能原因解决方法结果少最后一段未补末尾分隔符循环前拼接分隔符多字符分隔符切错偏移量加1而非加长度用LEN(Delimiter)递归报错深度超限默认100层限制加OPTION (MAXRECURSION 0)中文乱码字符集不匹配统一用NVARCHAR/N前缀空字符串干扰未过滤空值加LEN判断或KeepEmpty参数临时表数据串会话内多次调用加随机后缀或改用CTE6.5 一个容易被忽略的边界情况如果输入字符串本身就是分隔符比如,按照我的实现会返回一个空结果集因为跳过了空字符串。但有些业务期望返回两个空字符串。这个没有对错取决于约定。我的建议是在函数文档里写清楚行为让调用方知道。如果确实需要保留把KeepEmpty参数设为1即可。7. 实际项目中的应用场景扩展7.1 多选筛选条件的动态SQL回到开头提到的报表筛选场景。有了分割函数可以这样写SELECT o.* FROM orders o INNER JOIN dbo.fn_SplitString(DeptCodes, ,) d ON o.dept_code d.Value;这样就把一个字符串参数变成了一个可以JOIN的结果集既避免了SQL注入又利用了索引。比在应用层拼SQL优雅得多。7.2 标签数据的行转列与列转行用户标签通常存成tag1|tag2|tag3的形式。用分割函数可以轻松拆成多行然后跟标签维度表关联做标签统计。反过来用STRING_AGGSQL Server或GROUP_CONCATMySQL可以把多行合并回字符串。这两个函数配合使用行转列和列转行就都齐了。7.3 批量导入时的数据清洗从外部系统导入数据时经常遇到一个字段里塞了多个值的情况。比如地址字段省,市,区。用分割函数拆开后可以分别校验省市区是否合法把不合法的数据挑出来。这比在应用层用代码拆效率高得多尤其是数据量大的时候。7.4 与JSON函数的配合现在很多数据库都支持JSON类型。如果上游传过来的是JSON数组比如[1001,1002]其实不需要分割函数直接用JSON解析函数就行。但如果传过来的是1001,1002这种字符串还是得用分割函数。我一般会在ETL层做个判断能JSON就JSON不能就分割。8. 我个人的一些使用体会写了这么多年分割函数最大的体会是不要过度设计。我见过有人写了一个支持正则表达式分隔符、支持转义字符、支持嵌套引号的分割函数代码上千行结果项目里从来没用过那些高级功能反而因为逻辑复杂出了好几个bug。对于90%的场景一个支持多字符分隔符、能跳过空值、带序号的简单函数就足够了。另一个体会是函数命名要规范。我习惯用fn_前缀表示函数sp_前缀表示存储过程。虽然 SQL Server 里sp_前缀是系统存储过程保留的自定义的用sp_会有轻微性能损失但团队里统一了命名规范维护起来方便很多。如果在意性能可以用usp_或者proc_前缀。最后说一个实际踩过的坑。有一次我在一个高并发的系统里用了多语句表值函数做分割结果发现CPU占用很高。排查后发现是表值函数里的INSERT INTO Result操作在每次调用时都会分配表变量数据量大时内存压力很大。后来改成内联表值函数XML版本问题就解决了。所以如果你的分割函数会被频繁调用优先考虑内联表值函数而不是多语句的。对于MySQL我现在的首选是递归CTE代码短、可读性好、性能也不错。只有在MySQL 5.7及以下版本才用数字辅助表。存储过程版本我基本已经弃用了因为临时表的并发问题太烦人而且调用方式不如CTE灵活。如果你正在做数据仓库或者ETL开发建议把分割函数作为基础工具库的一部分提前建好。等到用的时候直接调用比临时写要省事得多。而且这种函数一旦写好基本不用改属于一次投入长期受益的典型。