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

时间:2021-01-08 15:08:49


需求:表中一个字段用逗号分隔,需要查出来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中的结构就可以了,在这里记录下这个问题,方便以后有需要时查找使用,嘿嘿