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

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

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

[sql]

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;
    1. 
       

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