用存储过程 拆分字段

ora-0600 2012-04-20 08:58:42
原表数据:
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
数据量很大,写一个存储过程实现上述的转化,怎么写效率高一点
...全文
223 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
rucypli 2012-04-20
  • 打赏
  • 举报
回复
楼上的函数很好,就是这个函数
nicenight 2012-04-20
  • 打赏
  • 举报
回复
推测楼主想要这样一个函数。居家旅行常备无患。

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
nicenight 2012-04-20
  • 打赏
  • 举报
回复
推测楼主想要这样一个函数。居家旅行常备无患。

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

yumenfeiyu945 2012-04-20
  • 打赏
  • 举报
回复
可以把原表的c2字段通过一些处理拼成一条批量插入的sql,再动态执行

select replace(concat('insert into t2 values(@id,\'',replace(c2,',','\'),(@id,\''),'\')'),'@id',c1) from t1;
ora-0600 2012-04-20
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 的回复:]

直接用SQL语句+辅助表不行吗?
[/Quote]
能说具体些吗?刚学Mysql,还不是很清楚,谢谢
wwwwb 2012-04-20
  • 打赏
  • 举报
回复
直接用SQL语句+辅助表不行吗?
ora-0600 2012-04-20
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 的回复:]

可以把原表的c2字段通过一些处理拼成一条批量插入的sql,再动态执行
SQL code

select replace(concat('insert into t2 values(@id,\'',replace(c2,',','\'),(@id,\''),'\')'),'@id',c1) from t1;
[/Quote]

在存储过程中怎么动态执行 select 查询出来的insert语句呀?请教指点
1、易于使用:资源附带的示例代码Demo和示例DML数据,有详细的函数说明和注释,以及请求demo示例,方便用户快速上手。 2、自定义分隔符:支持用户自定义分隔符,如英文逗号、分号等,满足各种复杂的字段拆分需求。 3、灵活扩展:提供函数接口,支持用户根据实际需求进行二次开发和功能扩展。 4、易于集成:函数设计简洁,易于集成到现有的Oracle数据库系统中,无需复杂的配置和改造。 5、优化查询性能:通过避免复杂的子查询和嵌套循环,我们的函数显著提升了数据处理的速度,减少了数据库资源的消耗,让您的应用运行更加流畅。 6、灵活的数据检索:不仅能拆分字段,还能根据您的需求检索出特定的数据项,无论是查找、统计还是进一步处理,都能轻松应对。 7、高效字段拆分:此函数能够迅速将字段中的多个值按逗号或其他自定义分隔符拆分成独立的行,极大地简化了对复合字段的操作过程。 使用场景: 商品分类管理:当您的商品分类信息存储在一个字段中时,此函数可以帮助您快速拆分并检索特定分类下的所有商品,极大地提高了商品管理的效率。 用户权限分配:在用户权限系统中,如果权限信息被压缩存储,使用本函数可以轻松地拆分权限字段

56,875

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧