假如当前有一条数据
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
[b]允许对上面代码进行修剪[/b]
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()
[b]如果数据量大,mysql会执行很长时间,现在提供一种快速的办法,再次更新,嘻嘻
使用的时游标,网上很多说使用游标处理数据不要超过1W,否则会很慢,但是我现在还不知道更好的办法,只能先用这个,后续有好的,再次更新,然后修改mysql不自动提交,这样效率会更好。不多说了,贴代码[/b]
set autocommit=0;
drop PROCEDURE if EXISTS dealEmail;
CREATE PROCEDURE dealEmail()
BEGIN
DECLARE comma INT(10); -- emails数据中,的个数
DECLARE ema VARCHAR(500); -- 插入另外一张表的email数据
DECLARE searchname VARCHAR(500); -- 插入另外一张表的email数据
DECLARE emailsStr VARCHAR(500); -- 本次截取后的字符 如1,2,3 本次操作留下的字符是2,那么emailsStr=2
DECLARE totalemailsStr VARCHAR(1600); -- 所有截取替换操作前的所有字符 如1,2,3 1,2已经操作 totalemailStr=1,2
DECLARE subcount INT(10); -- 判断当前是第几次截取
DECLARE appendEmails VARCHAR(1600); -- 为email数据最后追加,
DECLARE id INT(10);
DECLARE beforeid INT(10);
DECLARE cur CURSOR for select pid from t_author;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET id=0;
open cur;
FETCH cur into id;
WHILE id !=0 DO
SELECT LENGTH(emails)-LENGTH(REPLACE(emails,',','')) into comma from t_author where pid=id;
set subcount=1;
set totalemailsStr='';
set appendEmails ='';
if(comma<=0) THEN
SELECT emails,researchname into ema,searchname from t_author where pid=id;
INSERT into t_email(email,researchname) values(trim(ema),trim(searchname));
ELSE
SELECT concat(emails,',') into appendEmails from t_author where pid=id;
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 pid=id;
SELECT left(REPLACE(appendEmails,totalemailsStr,''), LOCATE(',',REPLACE(appendEmails,totalemailsStr,''))) into emailsStr from t_author where pid=id;
set totalemailsStr = concat(totalemailsStr,emailsStr);
INSERT into t_email(email,researchname) values(trim(ema),trim(searchname));
set subcount = subcount + 1;
set comma = comma - 1;
END WHILE;
end IF;
set beforeid = id;
FETCH cur into id;
END WHILE;
CLOSE cur;
commit;
END
DELETE from t_email;
commit;
call dealEmail();
SELECT count(*) from t_email;
分享到:
相关推荐
一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL如下: select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)...
注意:修改一行数据的多个列值时,SET 子句的每个值用逗号分开即可。 修改表中的数据 【实例 1】在 tb_courses_new 表中,更新所有行的 course_grade 字段值为 4,输入的 SQL 语句和执行结果如下所
Mybatis Mapper.xml中字符串形式传参,逗号分隔 AND中拼接OR。
MySQL以YYYY-MM-DD格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列 4.CHAR(M) 型:定长字符串类型,当存储时,总是是用空格填满右边到指定的长度 5.BLOB TEXT类型,最大长度为(2^16-1)个字符。 6...
当用UPDATE修改多个表时,要修改的表名之间用逗号分开,字段名因为涉及到多个表,用“表名.字段名”表示,多表连接条件须在WHERE子句中指定。 【例】 将book表中的数量减10,book1表中的单价加10。 UPDATE book,book1...
MySQL以YYYY-MM-DD格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列 4.CHAR(M) 型:定长字符串类型,当存储时,总是是用空格填满右边到指定的长度 5.BLOB TEXT类型,最大长度为65535(2^16-1)个字符...
创建一个文本文件“mysql.txt”,每行包含一个记录,用定位符(tab)把值分开,并且以在 CREATE TABLE语句中列出的列次序给出,例如: abccs f 1977-07-07 china mary f 1978-12-12 usa tom m 1970-09-02 usa ...
语句的截取得到表名,将"("和")"中间的字符串以逗号分开得到列 字符串,再以空格分解得到列名,类型和约束条件。 通过对DBFUtils.createDBF()函数的调⽤实现表的创建操作,将约束条件作为记录添加到data⽬录下的...
为了按多个列排序,列名之间用逗号分开。 2、支持按相对列位置进行排序。 输入 SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3 --(2指price,3指name) 3、升序、降序。默认是升序(asc,...
“库文件名”以.lib或.obj为后缀的将被视为静态库,可使用绝对路径或相对路径(相对当前源代码所在目录),如依赖多个静态库请分别列出并以逗号分隔;“在库中的对应命令名”请务必准确填写静态库中公开导出的符号...
只需Excel二维表导出为逗号分开的Csv文件,然后通过FTP工具上传。查询速度虽然低于php+txt,却远高于php+excel。 使用用途: 适合修改不频繁、保密性不高的成绩、工资、物业水电费等各种精准查询。 1. 成绩查询系统,...