用存储过程 拆分字段
原表数据:
C1 C2
--- --------------
1 aa,bbb,cccc,ddd
2 eee,fff,gggg,hhhh

转化成格式
C1 c2
--- ----------
1 aa
1 bbb
1 cccc
1 ddd
2 eee
2 fff
2 gggg
2 hhhh
数据量很大，写一个存储过程实现上述的转化，怎么写效率高一点

[解决办法]
直接用SQL语句+辅助表不行吗？
[解决办法]
可以把原表的c2字段通过一些处理拼成一条批量插入的sql，再动态执行

SQL code

select replace(concat('insert into t2 values(@id,\'',replace(c2,',','\'),(@id,\''),'\')'),'@id',c1) from t1;
[解决办法]
推测楼主想要这样一个函数。居家旅行常备无患。

SQL code
mysql> DELIMITER $$mysql> DROP PROCEDURE IF EXISTS `split`$$Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE PROCEDURE `split`(    ->     str VARCHAR(9999),    ->     sep CHAR(1)    -> )    -> BEGIN    ->     DECLARE strlen INT;    ->     DECLARE last_index INT;    ->     DECLARE cur_index INT;    ->     DECLARE cur_char VARCHAR(200);    ->     DECLARE len INT;    ->     SET cur_index=1;    ->     SET last_index=0;    ->     SET strlen=LENGTH(str);    ->     DROP TABLE IF EXISTS tmp_tb_split;    ->     CREATE TEMPORARY TABLE tmp_tb_split(    ->         id INT AUTO_INCREMENT,    ->         VALUE VARCHAR(20),    ->         PRIMARY KEY (`ID`),    ->         UNIQUE KEY `ID` (`ID`)    ->     ) ;    ->     WHILE(cur_index<=strlen) DO    ->     BEGIN    ->         IF SUBSTRING(str FROM cur_index FOR 1)=sep OR cur_index=strlen THEN    ->             SET len=cur_index-last_index-1;    ->             IF cur_index=strlen THEN    ->                SET len=len+1;    ->             END IF;    ->             INSERT INTO tmp_tb_split(`value`)VALUES(SUBSTRING(str FROM (last_index+1) FOR len));    ->             SET last_index=cur_index;    ->         END IF;    ->         SET cur_index=cur_index+1;    ->     END;    ->     END WHILE;    ->    ->     SELECT * FROM tmp_tb_split;    -> END$$Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;mysql> CALL split('a,b,c',',');+----+-------+| id | VALUE |+----+-------+|  1 | a     ||  2 | b     ||  3 | c     |+----+-------+3 rows in set (0.23 sec)Query OK, 0 rows affected, 1 warning (0.23 sec)mysql>mysql>mysql> DELIMITER $$mysql>mysql> DROP PROCEDURE IF EXISTS `split`$$Query OK, 0 rows affected (0.00 sec)mysql>mysql> CREATE PROCEDURE `split`(    ->     str VARCHAR(9999),    ->     sep CHAR(1)    -> )    -> BEGIN    ->     DECLARE strlen INT;    ->     DECLARE last_index INT;    ->     DECLARE cur_index INT;    ->     DECLARE cur_char VARCHAR(200);    ->     DECLARE len INT;    ->     SET cur_index=1;    ->     SET last_index=0;    ->     SET strlen=LENGTH(str);    ->     DROP TABLE IF EXISTS tmp_tb_split;    ->     CREATE TEMPORARY TABLE tmp_tb_split(    ->         id INT AUTO_INCREMENT,    ->         VALUE VARCHAR(20),    ->         PRIMARY KEY (`ID`),    ->         UNIQUE KEY `ID` (`ID`)    ->     ) ;    ->     WHILE(cur_index<=strlen) DO    ->     BEGIN    ->         IF SUBSTRING(str FROM cur_index FOR 1)=sep OR cur_index=strlen THEN    ->             SET len=cur_index-last_index-1;    ->             IF cur_index=strlen THEN    ->                SET len=len+1;    ->             END IF;    ->             INSERT INTO tmp_tb_split(`value`)VALUES(SUBSTRING(str FROM (last_index+1) FOR len));    ->             SET last_index=cur_index;    ->         END IF;    ->         SET cur_index=cur_index+1;    ->     END;    ->     END WHILE;    ->    ->     SELECT value FROM tmp_tb_split;    -> END$$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> CALL split('a,b,c',',');+-------+| value |+-------+| a     || b     || c     |+-------+3 rows in set (0.38 sec)Query OK, 0 rows affected (0.38 sec) 
 
[解决办法]
楼上的函数很好，就是这个函数