需求：MYSQL表中一个字段用逗号分隔，需要查出来split拆分并依次存入另一个表的一个字段中



需求：表中一个字段用逗号分隔，需要查出来split拆分并依次存入另一个表的一个字段中

假设问题是这丫那个的，table1中id=2的name值为'xiaoming,xiaohua,xiaowang,xiaohui'，我们要取出这个name字段值，并且根据逗号拆分出来，存储到table2中的name字段？

-----------------table1--------------------

id            name

1             xiaomig

2             xiaoming,xiaohua,xiaowang,xiaohui

---------------------table2------------------------

id           name        

如果要实现这个功能，MYSQL自带函数没有像java中的字符串拆分split函数，所以需要我们自己实现类似的功能函数。

分析：

1 首先需要知道拆分完毕之后的字符串数组的个数 len 。可以根据MYSQL自带的 lengh(param) 函数和 replace(param, oldparam, newparam) 函数结合实现。

代码如下：

 length(str)-length(replace(str, splitstr, ''))+1

2 然后根据计算出来的 len，循环该字段的字符串 str ，依次取出拆分出来的字符串。据 reverse() 函数和 substring_index(param, splitparam, index) 函数结合实现，该方法非常巧妙，

代码如下：

reverse(substring_index( reverse(substring_index(str, splitstr, index)), splitstr, 1))

两次截取，两次反转，即可根据传入的 index 得到每次我们要取出的字符串。

根据代码执行顺序，优先执行最里面的substring_index函数，依次向外执行。

模拟代码执行为：

当 index = 1 时，

reverse(substring_index( reverse(substring_index('xiaoming,xiaohua,xiaowang,xiaohui', ',', 1)), ',', 1)) ->

reverse(substring_index( reverse('xiaoming'), ',', 1)) ->

reverse(substring_index('gnimoaix'), ',', 1)) ->

reverse('gnimoaix')->

xiaoming

当 index = 2 时，

reverse(substring_index( reverse(substring_index('xiaoming,xiaohua,xiaowang,xiaohui', ',', 2)), ',', 1)) ->

reverse(substring_index( reverse('xiaoming,xiaohua'), ',', 1)) ->

reverse(substring_index('auhoaix,gnimoaix'), ',', 1)) ->

reverse('auhoaix')->

xiaohua

当 index = 3 时，

reverse(substring_index( reverse(substring_index('xiaoming,xiaohua,xiaowang,xiaohui', ',', 3)), ',', 1)) ->

reverse(substring_index( reverse('xiaoming,xiaohua,xiaowang'), ',', 1)) ->

reverse(substring_index('gnawoaix,auhoaix,gnimoaix'), ',', 1)) ->

reverse('gnawoaix')->

xiaowang

当 index = 4 时，

reverse(substring_index( reverse(substring_index('xiaoming,xiaohua,xiaowang,xiaohui', ',', 4)), ',', 1)) ->

reverse(substring_index( reverse('xiaoming,xiaohua,xiaowang,xiaohui'), ',', 1)) ->

reverse(substring_index('iuhoaix,gnawoaix,auhoaix,gnimoaix'), ',', 1)) ->

reverse('iuhoaix')->

xiaohui

由此即可获得所有拆分后的字符串了

根据以上两步，并使用function和procedure做了实现，代码如下：

-- 修改结束符，防止在mysql命令行中默认分号直接运行
delimiter $$

-- 创建一个计算拆分后字符串的个数函数
drop function if exists calc_length $$
create function calc_length(str varchar(200), splitstr varchar(5)) returns int(11)
begin 
return length(str)-length(replace(str, splitstr, ''))+1;
end $$

-- 创建一个模拟的split拆分字符串的函数
drop function if exists split_string $$
create definer='root'@'localhost' function split_string(str varchar(200), splitstr varchar(5), strindex int) returns varchar(255)
begin
declare result varchar(255) default '';
set result =reverse(substring_index( reverse(substring_index(str, splitstr, strindex)), splitstr, 1));
return result;
end $$

-- 创建一个存储过程
drop procedure if exists proce_split $$
create procedure proce_split()
begin
declare cnt int default 0;
declare i int default 0;
declare str varchar(2000) default '';
select name into str from table1 where id =2;
set cnt = calc_length(str, ',');

drop table if exists table2;
create table2(id int not null, name varchar(255) not null) default charset=utf8;

while i < cnt 
do 
set i = i+1;
insert into table(id, name) values(i, split_string(str,',', i));
end while;
end $$

我们登录mysql，使用命令： mysql -u 用户名 -p 

登录后我们运行上面的代码，完成之后，我们查看table2中的数据

输入代码：

delimiter ;
call proce_split();
select * from table2;

结果如下：

-----------------------table2--------------------------

id               name

1                xiaoming

2                xiaohua

3                xiaowang

4                xiaohui

问题就这样解决了，当然，实际情况可能比这个要负责，我们只需在这个基础上添加其他部分即可， 如在保存拆分好的name值时还要保存其他age等字段，我们就修改存储过程里的insert table2中的结构就可以了，在这里记录下这个问题，方便以后有需要时查找使用，嘿嘿