博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

MySQL 5.7.16 字符串拆分 -> 单列变多行记录(转发）

Posted on 2018-01-02 15:52  moss_tan_jun  阅读(2223)  评论(0)  编辑  收藏  举报

http://blog.csdn.net/mchdba/article/details/53889803

-------------------------------------------------------------------------------

  1. mysql> create table t1(c1 varchar(32),c2 varchar(32));  
  2. Query OK, 0 rows affected (0.02 sec)  
  3.   
  4. mysql> INSERT INTO t1 SELECT 'man' c1,'123,234,567,789' c2 UNION ALL SELECT 'guo','-12,-87,-09,132' UNION ALL SELECT 'fang','1024,2387,1209,1032';  
  5. Query OK, 3 rows affected (0.02 sec)  
  6. Records: 3  Duplicates: 0  Warnings: 0  
  7.   
  8. mysql> select * from t1;  
  9. +------+---------------------+  
  10. | c1   | c2                  |  
  11. +------+---------------------+  
  12. | man  | 123,234,567,789     |  
  13. | guo  | -12,-87,-09,132     |  
  14. | fang | 1024,2387,1209,1032 |  
  15. +------+---------------------+  
  16. 3 rows in set (0.00 sec)  
  17.   
  18. mysql>  



 

需要达到的效果是，左边变成右边,按照逗号拆分出来,单列变成多行记录，而且保留原来的其它列数据，如下图所示：

 

 #,blog原地址：http://blog.csdn.net/mchdba/article/details/53889803，作者mchdba，谢绝转载

 

这个复杂的实现，单条的sql或者几条sql无法达到，需要临时表存储中间数据，所以编写存储过程，间接实现，存储过程如下：

 

[sql] view plain copy
 
  1. DELIMITER $$  
  2. USE `test`$$  
  3. DROP PROCEDURE IF EXISTS `proc_cur_split_str`$$  
  4. USE `test`$$  
  5. CREATE PROCEDURE test.proc_cur_split_str()  
  6. BEGIN  
  7.     DECLARE v_c1 VARCHAR(20);  
  8.     DECLARE v_c2 VARCHAR(60);  
  9.     DECLARE _done INT DEFAULT 0;   
  10.       
  11.     DECLARE cur_strs CURSOR FOR SELECT c1,c2 from test.t1 ;  
  12.     DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET _done=1; END;  
  13.      DROP TABLE IF EXISTS test.ZZ_2;     
  14.      CREATE TABLE test.ZZ_2(C1 VARCHAR(20),CN VARCHAR(60));  
  15.     OPEN cur_strs;  
  16.         FETCH cur_strs INTO v_c1, v_c2;  
  17.         WHILE _done != 1  DO   
  18.             DROP TABLE IF EXISTS test.ZZ_1;  
  19.             CREATE TABLE ZZ_1(CN VARCHAR(60));   
  20.             SET @b=v_c2;  
  21.              SET @a = CONCAT(CONCAT("insert into test.ZZ_1 values('",REPLACE(@b,',',"'),('")),"')");  
  22.             -- SELECT @a;  
  23.              PREPARE stmt1 FROM @a;    
  24.             EXECUTE stmt1;    
  25.             -- SELECT v_c1,t.* FROM test.ZZ_1 t;  
  26.             INSERT INTO ZZ_2 SELECT v_c1,t.* FROM test.ZZ_1 t;  
  27.               
  28.             COMMIT;     
  29.             -- SELECT * FROM test.ZZ_2;  
  30.             FETCH cur_strs INTO v_c1, v_c2;  
  31.     END WHILE;  
  32.     CLOSE cur_strs;        
  33.     SELECT * FROM test.ZZ_2;    
  34. END;        
 



执行存储过程，得到执行结果，符合心理预期，单列C1变成多行，并且保留了原来的其它字段c1的数据值，如下所示，