mysql处置以逗号分开的数据

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查询出

现在想要分别取出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()

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com