把一个字符串拆分插入到临时表中

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_commUsed_update_99`(p_ID_in varchar(4000),p_TD_ID_in varchar(10))
BEGIN
CREATE TEMPORARY TABLE tmp_table (SID VARCHAR(15) NOT NULL);

END;

Mysql 中把一个存储过程的参数p_ID字符串根据逗号拆分插入到临时表中

P_ID = ‘sss,www,tttt,ffff’

tmp_table

SID
Sss
www
tttt
ffff

9 个解决方案

#1

用SQL语句直接拆分不行？

#2

写一个存储过程，或者函数，把字符串进行切割插入表变量或临时表中返回就行了

#3

i 循环中使用 SUBSTRING_INDEX(SUBSTRING_INDEX(p_ID_in,p_TD_ID_in ,i),p_TD_ID_in ,-1)即可

#4

对啊，如何切割啊

#5

写个循环，一段段截取，或者直接拼成一个sql，prepare执行

#6

create temporary table if not exists tmp_table
(ServiceID VARCHAR(15) NOT NULL);
SELECT @IDepAirport='SELECT '''+ replace('jfhhffjj,gkggk,jk',',',''' UNION ALL SELECT ''')+''''
INSERT tmp_table call(@IDepAirport);

以前是在sql server上是可以的，但是mysql就不知道了

#7

以前sql server的代码，
DECLARE @DepCityCode TABLE( cityCode NVARCHAR(10))

set @NewDepAirport='aaa,bb,ccc'

SELECT @IDepAirport='SELECT '''+replace(@NewDepAirport,',',''' UNION ALL SELECT ''')+''''
INSERT @DepCityCode EXEC(@IDepAirport)

这样就会把'aaa,bb,ccc'拆分插入到表
@DepCityCode
cityCode
aaa
bbb
ccc

这个代码在mysql中如何去改啊

#8

mysql> create procedure sp_testsubstring(a varchar(100))
    -> begin
    ->   declare c int;
    ->   select length(a)-length(replace(a,',','')) into c;
    ->   select c,a;
    ->   while c > 0 do
    ->      select substring_index(a,',',1) as t;
    ->      set a=substring(a,instr(a,',')+1,length(a));
    ->      set c=c-1;
    ->   end while;
    ->   select a as t;
    -> end
    -> /
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_testsubstring('a,b,c')/
+------+-------+
| c    | a     |
+------+-------+
|    2 | a,b,c |
+------+-------+
1 row in set (0.00 sec)

+------+
| t    |
+------+
| a    |
+------+
1 row in set (0.00 sec)

+------+
| t    |
+------+
| b    |
+------+
1 row in set (0.00 sec)

+------+
| t    |
+------+
| c    |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

#9

引用 8 楼的回复:

mysql> create procedure sp_testsubstring(a varchar(100))
    -> begin
    ->   declare c int;
    ->   select length(a)-length(replace(a,',','')) into c;
    ->   select c,a;
    ->   while c > ……

不对啊，怎么是在一个字段上

我要的是分开的
ttt
aaaa
bbbb
ccccc
ddddd

#1