MySQL实现字符串分割与批量拆分插入
由于MySQL未内置直接切割字符串的Split函数，在处理包含大量分隔符的脏数据时，往往需要借助自定义函数与存储过程配合。本文介绍一种基于SUBSTRING_INDEX函数计算索引位置、结合循环结构将单条长记录拆解为多条明细记录的实操方案，适用于数据清洗及ETL场景。
核心原理：利用 SUBSTRING_INDEX 模拟分割
MySQL 中强大的 SUBSTRING_INDEX 函数可以作为分割的基础。它可以根据指定的分隔符和出现次数，截取字符串的一部分。要实现类似 Split 的效果，关键在于精准计算出每个子串的起始位置和长度。CHAR_LENGTH 函数用于获取字符串字符数，通过对比不同分割点的长度差，即可确定单个元素的长度。
以下是一个典型的自定义函数 str_for_substr，其目的是从以特定分隔符（如 “###”）连接的字符串中提取第 N 个元素。
第一步：创建取数自定义函数
目的：根据传入的索引值 num 和原始字符串 str，提取出对应的子串片段。 入口：MySQL 命令窗口或 Navicat 等客户端编辑器。 重要参数：
num：要取出的元素索引，从 0 开始计数。str：待分割的源字符串。
drop function if exists str_for_substr;
CREATE
DEFINER = `root`@`%` FUNCTION `str_for_substr`(`num` int, `str` varchar(5000))
RETURNS varchar(100) no sql
BEGIN
/*函数功能: 把带逗号的字符串分割取出
参数: num 要取出的字符串的索引值, 以0开始
str 以逗号分割的字符串
扩展: 将###替换成其他符合，即可完成不同分隔符拆分字符串，亦可以把分隔符作为参数
*/
SET @str_for_substr =
SUBSTRING(
SUBSTRING_INDEX(str, '###', num + 1),
CASE num
WHEN 0 THEN
CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num)
) + 1
ELSE
CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num)
) + 4
END,
CASE num
WHEN 0 THEN
CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num + 1)
) - CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num)
)
ELSE
CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num + 1)
) - CHAR_LENGTH(
SUBSTRING_INDEX(str, '###', num)
) - 1
END
);
RETURN @str_for_substr;
END;第二步：构建存储过程实现批量处理
目的：遍历源表中的每一行数据，识别其中包含的分隔符数量，循环调用上述函数并将拆分后的数据逐行写入目标表中。
入口：MySQL 存储过程管理器或 SQL 脚本。
完成状态：运行无报错，且目标表 test1 中的数据量等于所有源数据拆分后产生的总行数。
drop procedure if exists split_str;
CREATE
DEFINER = `root`@`%` PROCEDURE `split_str`()
BEGIN
DECLARE cnt int default 0;
DECLARE num int default 0;
DECLARE i int default 0;
DECLARE j int default 0;
declare str varchar(4000);
# 统计当前截取字段表的数据量
select count(1) into cnt from test;
# 循环查询每一条数据，此处可以换成游标处理
while i<= cnt do
select query_knowledge_list_string into str from test limit i,1;
# 查询每条数据###出现的次数
SET num = (LENGTH(str) - LENGTH(REPLACE(str, '###', ''))) / 3;
while j <= num do
# 循环插入截取后的数据
INSERT INTO test1 (knowledge_name, subject_name, grade_name)
select str_for_substr(j,str), subjectName, grade_name
from test limit i,1;
set j = j + 1;
end while;
# 循环值归零
set j = 0;
set i = i + 1;
end while;
END;在实际应用前，建议对涉及的表数据进行备份，因为涉及全表循环插入操作，在大数据量下可能会产生性能瓶颈。如果源表数据量极大，建议将外层循环改为游标（Cursor）方式处理。
进阶技巧：简单字段的非标准符号分割
除了拆分复杂的多字段列表，日常处理中还经常遇到需要在字符串前剔除特定前缀的场景。例如，在同步 Hive 数仓数据时，字段前常混入带有编号的顿号（如 “1、语文”），而业务只需要保留内容部分。
由于 MySQL 没有直接的 Split 返回数组的功能，我们可以组合使用 INSTR() 和 SUBSTR() 来动态定位截取点。首先检查分隔符是否存在，若存在则通过偏移量跳过前缀进行截取。
以下是该场景下的标准 SQL 写法：
select year, case when INSTR(subtype,'、')>0 then SUBSTR(subtype,INSTR(subtype,'、')+1) else subtype END subtype from xxx_table_name
执行结果会自动过滤掉顿号及其前面的数字编码，使数据更加规整。
综上，通过自定义函数配合存储过程，可以有效解决 MySQL 环境下的复杂字符串切片需求；而对于简单的单字段前缀清理，则推荐使用 INSTR 结合 SUBSTR 实现轻量级处理。以上就是关于MySQL字符串分割功能的详细内容，更多关于MySQL高级应用及数据清洗的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
