`
黄菲菲
  • 浏览: 16547 次
  • 性别: Icon_minigender_2
  • 来自: 武汉
社区版块
存档分类
最新评论

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




[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;
0
1
分享到:
评论

相关推荐

    MySQL中将一列以逗号分隔的值行转列的实现

    一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL如下: select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)...

    mybatis Mapper.xml中传参多选 字符串形式逗号分隔 AND中拼接OR.rar

    标题和描述所提及的问题是关于如何在Mapper.xml文件中处理字符串形式的参数,这些参数由逗号分隔,并在`AND`语句中拼接`OR`子句来实现动态查询。这种场景在处理用户多选过滤条件时非常常见,比如在一个搜索框中,...

    Mysql教程:将txt文本中的数据轻松导入MySQL表中的方法.doc

    首先,我们需要将数据记录按行处理好,并用特定的字符分开,如逗号(,)。例如: aaa,bbb,ccc,ddd,eee fff,ggg,hhh,iii,jjj,kkk 然后,我们需要建立一个PHP脚本文件,例如loaddate.php,用于连接MySQL数据库和将...

    mysql 存储引擎和表类型

    MyISAM表的数据文件和索引文件是分开存储的,不支持事务处理,因此在并发写入和数据完整性方面相对较弱。此外,MyISAM表在磁盘空间占用上相对较小,但遇到表锁定时可能导致性能下降。 2. **InnoDB**:InnoDB是事务...

    MySQL命令大全

    MySQL以YYYY-MM-DD格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列 4.CHAR(M) 型:定长字符串类型,当存储时,总是是用空格填满右边到指定的长度 5.BLOB TEXT类型,最大长度为(2^16-1)个字符。 6...

    MySQL 入门学习 ——基础教程

    创建一个文本文件“mysql.txt”,每行包含一个记录,用定位符(tab)把值分开,并且以在 CREATE TABLE语句中列出的列次序给出,例如: abccs f 1977-07-07 china mary f 1978-12-12 usa tom m 1970-09-02 usa ...

    mysql遇到load data导入文件数据出现1290错误的解决方案

    7. **了解`LOAD DATA INFILE`语法**:在使用`LOAD DATA INFILE`时,数据文件应该符合一定的格式,例如,每行对应数据库表中的一条记录,字段间用分隔符(如制表符、逗号等)分开。例如,`name varchar(10), sex enum...

    MYSQL必知必会笔记

    - 同一表中的数据属于同一类型,而不同类型的通常被分开存储在不同的表中,以便于检索和访问。 - 在一个数据库中,表名必须是唯一的。 - **模式**: - 模式(Schema):描述了数据库和表的布局及特性信息。 - *...

    MySQL查询指令汇总

    **表与表之间的连接**: 如果需要从多个表中检索数据,可以通过逗号`,`将表名分开,并使用适当的 `JOIN` 语句来连接这些表。此外,`WHERE` 子句可以用来定义连接条件。 #### 二、常用操作 - **查看当前数据库**: ...

    北京动力节点MySQL课程讲义_02

    SQL99语法能够清晰地将连接条件与数据查询条件分开,便于理解和维护。 ### 4. 连接查询的分类 #### a. 内连接(INNER JOIN) 内连接是最常用的连接类型,它只会返回两个表中匹配的记录。内连接包括等值连接、非...

    读取txt插入到本地数据库

    2. **解析数据**:读取到的每一行可能包含多列数据,通常需要用分隔符(如逗号、制表符等)将其分开。可以使用`string.Split()`方法进行分割。例如,如果数据是以逗号分隔的: ```csharp string[] columns = line....

    MySQL中三种关联查询方式的简单比较

    这种方式的优点是表的关联条件和数据过滤条件可以分开编写,使得SQL语句逻辑清晰且易于理解。例如: ```sql SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) ``` 在上面的查询中,我们...

    Mysql带And关键字的多条件查询语句

    在MySQL数据库中,进行多条件查询是常见的操作,特别是在处理复杂的数据过滤时。`AND`关键字是用于在`WHERE`子句中连接多个条件表达式的,确保返回的记录必须同时满足所有的条件。以下是对`AND`关键字在多条件查询中...

    python+selenium+unittest环境搭建1

    解决方法是创建PYTHONPATH变量,在这个环境变量中输入相关的路径,不同的路径之间用英文逗号分开。 六、解决 MachineLevelUserCloudPolicyEnrollmentToken 错误 在命令行中运行py文件时,可能会出现...

    php程序设计测试题含答案.docx

    12. 函数参数:多个参数之间必须以逗号符号分开。 13. 数组使用:数组的键名可以是字符串或数字,如果键名相同,后面的值将覆盖前面的值。 14. 运算符优先级:! 运算符的执行顺序从右向左。 15. 程序控制:do…...

    PHP选择题含答案.docx

    12. 在 PHP 中,函数的参数之间必须以逗号符号分开。 13. 在 PHP 中,数组的键名可以是字符串或整型。如果数组的键名是字符串,例如 '1',则它将被视为字符串键名,而不是整型键名。 14. PHP 中的运算符的执行顺序...

    2009达内SQL学习笔记

    为了按多个列排序,列名之间用逗号分开。 2、支持按相对列位置进行排序。 输入 SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3 --(2指price,3指name) 3、升序、降序。默认是升序(asc,...

    PHP+Csv(Excel)通用成绩查询系统

    只需Excel二维表导出为逗号分开的Csv文件,然后通过FTP工具上传。查询速度虽然低于php+txt,却远高于php+excel。 使用用途: 适合修改不频繁、保密性不高的成绩、工资、物业水电费等各种精准查询。 1. 成绩查询系统,...

    易语言程序免安装版下载

    “库文件名”以.lib或.obj为后缀的将被视为静态库,可使用绝对路径或相对路径(相对当前源代码所在目录),如依赖多个静态库请分别列出并以逗号分隔;“在库中的对应命令名”请务必准确填写静态库中公开导出的符号...

Global site tag (gtag.js) - Google Analytics