mysql把按照特定格式的单个字段拆分为多条数据

原始数据：

id	zz	zzmc
5	1,2,3,4

id	zz	zzmc
5	1	恶心
5	2	头痛

 

 

 

 

 

 

mysql 函数1：

CREATE FUNCTION `func_get_split_string`(  
f_string varchar(255),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN  
  declare result varchar(255) default '';  
  set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));  
  return result;  
END

函数2：

CREATE FUNCTION `func_get_split_string_total`(f_string varchar(255),f_delimiter varchar(5)) RETURNS int(11)
BEGIN  
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));  
END

存储过程1：

CREATE PROCEDURE `pro_insert_zz`(IN f_string varchar(255),IN f_delimiter varchar(5),IN f_herid int)
BEGIN
      
      declare cnt int default 0;
      declare i int default 0;
	  declare zz INT DEFAULT 0;
	  DECLARE zz_t VARCHAR(32) DEFAULT '';
      set cnt = func_get_split_string_total(f_string,f_delimiter);
      while i < cnt
      do
        set i = i + 1;
				SET zz = func_get_split_string(f_string,f_delimiter,i);
				CASE zz
					WHEN 1 THEN
						SET zz_t = '无症状';
					WHEN 2 THEN
						SET zz_t = '头疼';
					WHEN 3 THEN
						SET zz_t = '头晕';
					WHEN 4 THEN
						SET zz_t = '心悸';
					WHEN 5 THEN
						SET zz_t = '胸闷';
					WHEN 6 THEN
						SET zz_t = '胸痛';
					WHEN 7 THEN
						SET zz_t = '慢性咳嗽';
					WHEN 8 THEN
						SET zz_t = '咳痰';
					WHEN 9 THEN
						SET zz_t = '呼吸困难';
					WHEN 10 THEN
						SET zz_t = '多饮';
					WHEN 11 THEN
						SET zz_t = '多尿';
					WHEN 12 THEN
						SET zz_t = '体重下降';
					WHEN 13 THEN
						SET zz_t = '乏力';
					WHEN 14 THEN
						SET zz_t = '关节肿痛';
					WHEN 15 THEN
						SET zz_t = '视力模糊';
					WHEN 16 THEN
						SET zz_t = '手脚麻木';
					WHEN 17 THEN
						SET zz_t = '尿急';
					WHEN 18 THEN
						SET zz_t = '尿痛';
					WHEN 19 THEN
						SET zz_t = '便秘';
					WHEN 20 THEN
						SET zz_t = '腹泻';
					WHEN 21 THEN
						SET zz_t = '恶心呕吐';
					WHEN 22 THEN
						SET zz_t = '眼花';
					WHEN 23 THEN
						SET zz_t = '耳鸣';
					WHEN 24 THEN
						SET zz_t = '乳房胀痛';
					WHEN 25 THEN
						SET zz_t = '其他';
					ELSE
						SET zz_t = '';
					END CASE;
-- 执行插入的sql
					-- INSERT INTO table(id,zz,zzmc) VALUES(f_herid,zz,zz_t);
      end while;
				-- SELECT * FROM tmp_print;
    END

存储过程2：

CREATE PROCEDURE `pro_zz`()
BEGIN
	-- ID
	DECLARE h_id INT DEFAULT 0;	
	--  症状
	DECLARE zhengzhuang VARCHAR(64) DEFAULT '';   
	-- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;

	DECLARE cur_account CURSOR FOR (SELECT id,  zz FROM table1);
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	OPEN cur_account;
		read_loop : LOOP
			FETCH cur_account INTO her_id,zhengzhuang;
			IF done THEN
         LEAVE read_loop;
      END IF;
			 CALL pro_insert_zz(zhengzhuang,',',her_id);
		END LOOP;
	CLOSE cur_account;
END

直接调用最后一个过程

转载于:https://my.oschina.net/u/2245043/blog/1820963