如何通过MySQL函数快速将字符串分割并转为多行数据

MySQL 8.0+ 最可靠字符串拆分方案是 JSON_TABLE，需先将逗号分隔字符串转为合法 JSON 数组格式（如 'a,b,c' → '["a","b","c"]'），再通过 PATH '$[*]' 展开；5.7 只能用数字序列配合 SUBSTRING_INDEX 模拟，且须控制分割数并过滤空值。

MySQL 8.0+ 用 JSON_TABLE 拆分字符串最可靠

MySQL 原生不支持类似 PostgreSQL 的 string_to_array，但 8.0+ 版本引入的 JSON_TABLE 是目前最稳妥的拆分方案。它把逗号分隔字符串转成 JSON 数组，再展开为行，避免了自定义函数的权限、维护和兼容性问题。

常见错误是直接对非 JSON 格式字符串调用 JSON_TABLE，导致返回空结果或报错 Invalid JSON text。必须先用 CONCAT 和 REPLACE 构造合法 JSON 数组格式。

• 输入字符串如 'a,b,c'，需先处理为 '["a","b","c"]'
• SQL 中需嵌套：先 REPLACE('a,b,c', ',', '","')，再 CONCAT('["', ..., '"]')
• 注意引号转义：若原始字段含双引号，要提前 REPLACE(col, '"', '"')
• JSON_TABLE 的 PATH 必须写 $[*]，否则无法展开数组

SELECT jt.valFROM (SELECT CONCAT('["', REPLACE('apple,banana,cherry', ',', '","'), '"]') AS json_str) t,     JSON_TABLE(t.json_str, '$[*]' COLUMNS (val TEXT PATH '$')) AS jt;

MySQL 5.7 怎么办？用递归 CTE + SUBSTRING_INDEX 模拟

5.7 不支持 JSON_TABLE 也不支持递归 CTE（那是 8.0 加的），只能靠生成数字序列 + SUBSTRING_INDEX。核心思路是：先构造一个足够长的数字表（比如 1~100），再用每个数字提取第 N 个分割项。

容易踩的坑是没控制最大分割数，导致大量无效行；或者没过滤空值，出现一堆 '' 行。

• 用 SELECT 1 UNION SELECT 2 UNION ... 手动建数字表，或用系统表 information_schema.columns 借行（但行数不确定）
• SUBSTRING_INDEX(col, ',', n) 取前 n 段，再用 SUBSTRING_INDEX(..., ',', -1) 提取最后一段，即第 n 项
• 加 WHERE n <= (LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1) 限制有效项数
• 最后用 HAVING val != '' 或 WHERE TRIM(val) > '' 清掉空值

别碰 CREATE FUNCTION 自定义拆分函数

网上很多教程教写存储函数，比如 split_string() 返回临时表或逐行输出。实际项目中基本不能用：一是多数生产环境禁用 CREATE FUNCTION 权限；二是函数内无法返回结果集（MySQL 函数只能返回单值）；三是即使用过程模拟，也会因无法在 SELECT 中直接调用而被迫嵌套多层子查询，性能差且难调试。

真正需要复用逻辑时，优先封装成视图或 CTE，而不是函数。

• 函数不能返回表，RETURN 只能是标量，所谓“返回多行”都是伪实现
• 用 INSERT ... SELECT 写临时表再查？并发下会冲突，且无法在普通查询中嵌套
• 权限不足时，ERROR 1419: You do not have the SUPER privilege 直接失败

性能和边界情况必须提前验证

字符串拆分看着简单，但真实数据里常有空字段、超长字段、特殊分隔符（比如逗号在引号内）、嵌套结构。这些都会让看似工作的 SQL 突然崩掉。

• 单条字符串超过 1MB？JSON_TABLE 可能触发 max_allowed_packet 限制，需调大该参数
• 分隔符是 ',' 但内容含 'a,b', 'c,d'？原生函数完全无法处理，必须预处理或换 CSV 解析器
• 拆分后行数爆炸（比如某字段含 500 个值）？会导致 JOIN 膨胀，务必加 LIMIT 测试，再考虑是否走应用层处理
• 字符集不一致时，LENGTH() 和 CHAR_LENGTH() 结果不同，影响分割计数——统一用 CHAR_LENGTH()

拆分不是万能解药。如果源数据频繁变更、结构复杂，或者需要做嵌套解析，与其硬刚 MySQL 字符串函数，不如在写入前就按规范存成 JSON 或单独关系表。