用存储过程 拆分字段

Question

原表数据:
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
数据量很大，写一个存储过程实现上述的转化，怎么写效率高一点

Answer 1

直接用SQL语句+辅助表不行吗？

Answer 2

能说具体些吗？刚学Mysql，还不是很清楚，谢谢

Answer 3

可以把原表的c2字段通过一些处理拼成一条批量插入的sql，再动态执行select replace(concat('insert into t2 values(@id,\'',replace(c2,',','\'),(@id,\''),'\')'),'@id',c1) from t1;

Answer 4

推测楼主想要这样一个函数。居家旅行常备无患。
[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 TH
EN
    ->             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 (l
ast_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 TH
EN
    ->             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 (l
ast_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)
[/code]当然这个自定义函数效率可不保证，建议大量数据使用导出后另外处理，再导入。如果是 linux 系统，下面的脚本可以参考：
[code]
[nicenight@CSDN]# cat a.txt 
1       aa,bbb,cccc,ddd
2       eee,fff,gggg,hhhh[nicenight@CSDN]# awk '{split($2, ar, ","); for(s in ar) printf("%s\t%s\n", $1, ar[s]);}' a.txt 
1       ddd
1       aa
1       bbb
1       cccc
2       hhhh
2       eee
2       fff
2       gggg
[/code]

Answer 5

推测楼主想要这样一个函数。居家旅行常备无患。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 TH
EN
    ->             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 (l
ast_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 TH
EN
    ->             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 (l
ast_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)
当然这个自定义函数效率可不保证，建议大量数据使用导出后另外处理，再导入。如果是 linux 系统，下面的脚本可以参考：[nicenight@CSDN]# cat a.txt 
1       aa,bbb,cccc,ddd
2       eee,fff,gggg,hhhh[nicenight@CSDN]# awk '{split($2, ar, ","); for(s in ar) printf("%s\t%s\n", $1, ar[s]);}' a.txt 
1       ddd
1       aa
1       bbb
1       cccc
2       hhhh
2       eee
2       fff
2       gggg

Answer 6

在存储过程中怎么动态执行 select 查询出来的insert语句呀?请教指点