mysql处理以逗号分开的数据 假如当前有一条数据 id emails 1 baidu@qq.com,baidu@163.com,baidu@139.com 现在想要分别取出baidu@qq.com,baidu@163.com,baidu@139.com 存入另外一个表中 表只有俩个字段id,email 如果用java程序，那么很简单，直接根据id查询出
mysql处理以逗号分开的数据假如当前有一条数据
id emails
1 baidu@qq.com,baidu@163.com,baidu@139.com
现在想要分别取出baidu@qq.com,baidu@163.com,baidu@139.com 存入另外一个表中 表只有俩个字段id,email
如果用java程序，那么很简单，直接根据id查询出这条记录，然后对这个字符做split以逗号分割就可以了，但是sql中没有split函数，如果实现，
以下是用sql写的处理数据的存储过程
drop procedure if exists dealemail
create procedure dealemail(in totalcount int) -- totalcount是count(*)个数，需要处理多少行数据
begin
declare limitcount int(10); -- 保证每次查询仅有一条数据
declare comma int(10); -- emails数据中，的个数
declare ema varchar(500); -- 插入另外一张表的email数据
declare repeatcount int(10); -- 判断是否有重复数据
declare emailsstr varchar(500); -- 本次截取后的字符 如1,2,3 本次操作留下的字符是2，那么emailsstr=2
declare totalemailsstr varchar(1000); -- 所有截取替换操作前的所有字符 如1,2,3 1,2已经操作 totalemailstr=1,2
declare subcount int(10); -- 判断当前是第几次截取
declare appendemails varchar(1000); -- 为email数据最后追加,
set limitcount=0;
-- locate查询字符串第一次出现的位置 left函数 左截取
while(totalcount>0) do
select length(emails)-length(replace(emails,',','')) into comma from t_author where emails!='' limit limitcount,1;
set subcount=1;
set totalemailsstr='';
set appendemails ='';
if(comma<=0) then
select emails into ema from t_author where emails!='' limit limitcount,1;
select count(pid) into repeatcount from t_email where email=ema;
if(repeatcount = 0) then -- 判断当前即将插入数据是否存在
insert into t_email(email) values(ema);
end if;
else
while(comma>=0) do
if(subcount=1) then
/**第一次截取 */
select substr(emails,1,length(left(emails, locate(',',emails)-1))) into ema from t_author where emails!='' limit limitcount,1;
select left(emails, locate(',',emails)) into totalemailsstr from t_author where emails!='' limit limitcount,1;
select count(pid) into repeatcount from t_email where email=ema;
if(repeatcount = 0) then
insert into t_email(email) values(ema);
end if;
set subcount = subcount + 1;
set comma = comma - 1;
else
select concat(emails,',') into appendemails from t_author where emails!='' limit limitcount,1;
select substr(replace(appendemails,totalemailsstr,''),1,length(left(replace(appendemails,totalemailsstr,''), locate(',',replace(appendemails,totalemailsstr,''))-1))) into ema from t_author where emails!='' limit limitcount,1;
select left(replace(appendemails,totalemailsstr,''), locate(',',replace(appendemails,totalemailsstr,''))) into emailsstr from t_author where emails!='' limit limitcount,1;
set totalemailsstr = concat(totalemailsstr,emailsstr);
select count(pid) into repeatcount from t_email where email=ema;
if(repeatcount = 0) then
insert into t_email(email) values(ema);
end if;
set subcount = subcount + 1;
set comma = comma - 1;
end if;
end while;
end if;
set totalcount = totalcount-1;
set limitcount = limitcount+1;
end while;
end;
call dealemail(568);
使用的是mysql5.5
允许对上面代码进行修剪
drop procedure if exists dealemail
create procedure dealemail()
begin
declare limitcount int(10); -- 保证每次查询仅有一条数据
declare comma int(10); -- emails数据中，的个数
declare ema varchar(500); -- 插入另外一张表的email数据
declare searchname varchar(500); -- 插入另外一张表的email数据
declare repeatcount int(10); -- 判断是否有重复数据
declare emailsstr varchar(500); -- 本次截取后的字符 如1,2,3 本次操作留下的字符是2，那么emailsstr=2
declare totalemailsstr varchar(1000); -- 所有截取替换操作前的所有字符 如1,2,3 1,2已经操作 totalemailstr=1,2
declare subcount int(10); -- 判断当前是第几次截取
declare appendemails varchar(1000); -- 为email数据最后追加,
declare totalcount int(10);
set limitcount=0;
select count(1) into totalcount from t_author where emails is not null;
-- locate查询字符串第一次出现的位置 left函数 左截取
while(totalcount>0) do
select length(emails)-length(replace(emails,',','')) into comma from t_author where emails is not null limit limitcount,1;
set subcount=1;
set totalemailsstr='';
set appendemails ='';
if(comma<=0) then
select emails,researchname into ema,searchname from t_author where emails is not null limit limitcount,1;
/**select count(pid) into repeatcount from t_email where email=ema; */
insert into t_email(email,researchname) values(trim(ema),trim(searchname));
else
select concat(emails,',') into appendemails from t_author where emails is not null limit limitcount,1;
while(comma>=0) do
select substr(replace(appendemails,totalemailsstr,''),1,length(left(replace(appendemails,totalemailsstr,''), locate(',',replace(appendemails,totalemailsstr,''))-1))),researchname into ema,searchname from t_author where emails is not null limit limitcount,1;
select left(replace(appendemails,totalemailsstr,''), locate(',',replace(appendemails,totalemailsstr,''))) into emailsstr from t_author where emails is not null limit limitcount,1;
set totalemailsstr = concat(totalemailsstr,emailsstr);
/**select count(pid) into repeatcount from t_email where email=ema; */
insert into t_email(email,researchname) values(trim(ema),trim(searchname));
set subcount = subcount + 1;
set comma = comma - 1;
end while;
end if;
set totalcount = totalcount-1;
set limitcount = limitcount+1;
end while;
end;
call dealemail().syntaxhighlighter{padding-top:20px;padding-bottom:20px;}
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！