MySQL 存储过程实现分割(类似split)字段并分别插入表中

MySql本身针对字符串的操作中,并不提供split函数,所以如果想要在mysql中分割一个字符串,就比较麻烦了。

就像这次碰到的需求是:在用户的账号表中,有一个字段“QQ”,用于存储用户的QQ号,当用户有多个QQ号的时候,是用“;”分隔的,类似于“1234567;7654321;234617”这种样子。后面为了更好的管理用户的联系信息,专门独立出一张表,用于存储用户的各种联系信息,现在要做的就是将用户的多个QQ号分别插入到联系信息表中,实现一个QQ号一条记录。

想来想去,想要实现只能靠存储过程了,总不能把这些信息导出来,然后在文本文档里做成多个sql语句吧?这种方法也算一条可以走的路。但我们有更好的办法,那就是存储过程。

使用存储过程实现的主要思路如下:

1、先将所有用”;”分隔的QQ号取出来,并放入游标中:

DECLARE cursor_qq CURSOR FOR SELECT unified_account_id, qq FROM idb_account where status='1' and qq like '%;%';

2、循环游标,将QQ字段包装成insert语句,这里用到了replace 和 concat 这两个函数,包装后的SQL语句类似于“ insert into table_name ( column1, column2, column3 ….) values (value11, value12, value13 …), (value21, value22, value23…) ……”:

 SET v_values =  REPLACE(v_qq, ';', CONCAT('\', REPLACE(UUID(), \'-\', \'\'),', v_unified_account_id, ', \'QQ\'', ', \'0\'', ', \'1\'', ', now()', ', now()' , '),(\'' ));
 SET v_values = CONCAT('(\'', v_values, CONCAT('\', REPLACE(UUID(), \'-\', \'\'),', v_unified_account_id, ', \'QQ\'', ', \'0\'', ', \'1\'', ', now()', ', now()' , ')' ));
 SET v_insert = CONCAT('INSERT INTO idb_account_contact_info(info, id, unified_account_id, type, is_primary, status, modify_time, create_time) values ', v_values);

3、用prepare预定义第2步生成的insert语句,然后执行。

 SET @sql = v_insert;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

4、调用上面的存储过程

call PROCESS_QQ;

 

附完整代码:

DELIMITER $$
delete from idb_account_contact_info where type='QQ' and info like '%;%';
DROP PROCEDURE IF EXISTS `PROCESS_QQ`;
CREATE PROCEDURE PROCESS_QQ()
BEGIN
DECLARE v_finished int default 0;
DECLARE v_unified_account_id int(8) default 0;
DECLARE v_qq varchar(128) default '';
DECLARE v_values varchar(1024);
DECLARE v_insert varchar(1024);
DECLARE cursor_qq CURSOR FOR SELECT unified_account_id, qq FROM idb_account where status='1' and qq like '%;%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

OPEN cursor_qq;

split_qq:LOOP
 FETCH cursor_qq INTO v_unified_account_id, v_qq;
 IF v_finished = 1 THEN 
  LEAVE split_qq;
 END IF;
 SET v_values =  REPLACE(v_qq, ';', CONCAT('\', REPLACE(UUID(), \'-\', \'\'),', v_unified_account_id, ', \'QQ\'', ', \'0\'', ', \'1\'', ', now()', ', now()' , '),(\'' ));
 SET v_values = CONCAT('(\'', v_values, CONCAT('\', REPLACE(UUID(), \'-\', \'\'),', v_unified_account_id, ', \'QQ\'', ', \'0\'', ', \'1\'', ', now()', ', now()' , ')' ));
 SET v_insert = CONCAT('INSERT INTO idb_account_contact_info(info, id, unified_account_id, type, is_primary, status, modify_time, create_time) values ', v_values);
 SET @sql = v_insert;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

END LOOP split_qq;
CLOSE cursor_qq;

END$$

DELIMITER ;

call PROCESS_QQ;
DROP PROCEDURE PROCESS_QQ;