用存储过程 拆分字段
原表数据:
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 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)欢迎大家阅读《用存储过程 拆分字段,该怎么处理》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：用存储过程 拆分字段,该怎么处理
