原表数据:
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
数据量很大,写一个存储过程实现上述的转化,怎么写效率高一点
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
数据量很大,写一个存储过程实现上述的转化,怎么写效率高一点
解决方案 »
- 我用的mysqlcc,我想看别人执行过哪些语句怎么看?
- mysql下无法显示数据库
- 去掉重复数据的情况
- 使用mysql-front添加外键 失败(如图)
- 你们创建mysql数据表的时候一般用什么编码格式?
- 请问查询记录时如果字段名是两个单词组成,而且两个单词中间有空格,这样的查询语句该如何写?
- Mysql结果已经返回但是sql还没有执行完
- show processlist 中的连接数数量 比 mysql数据库连接池最大连接数的限制要多
- 删除1.7W条数据要30S-60S如何优化?
- 消息 102,级别 15,状态 1,第 1 行 '-' 附近有语法错误。
- 求教sqlyog中文乱码问题
- 关于MySQL查询优化
[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]
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
在存储过程中怎么动态执行 select 查询出来的insert语句呀?请教指点