mysql将逗号拼接的弄成多列,如何在MySQL中将逗号分隔的字段扩展为多行

select id, ips from users;

Query result

id ips

1 1.2.3.4,5.6.7.8

2 10.20.30.40

3 111.222.111.222,11.22.33.44

4 1.2.53.43

I'd like to run a query that produces the following output

user_id ip

1 1.2.3.4

1 5.6.7.8

2 10.20.30.40

3 111.222.111.222

3 11.22.33.44

4 1.2.53.43

解决方案

If you don't mind using a cursor, here's an example:

set nocount on;

-- create sample table, @T

declare @T table(id int, ips varchar(128));

insert @T values(1,'1.2.3.4,5.6.7.8')

insert @T values(2,'10.20.30.40')

insert @T values(3,'111.222.111.222,11.22.33.44')

insert @T values(4,'1.2.53.43')

insert @T values(5,'1.122.53.43,1.9.89.173,2.2.2.1')

select * from @T

-- create a table for the output, @U

declare @U table(id int, ips varchar(128));

-- setup a cursor

declare XC cursor fast_forward for select id, ips from @T

declare @ID int, @IPS varchar(128);

open XC

fetch next from XC into @ID, @IPS

while @@fetch_status = 0

begin

-- split apart the ips, insert records into table @U

declare @ix int;

set @ix = 1;

while (charindex(',',@IPS)>0)

begin

insert Into @U select @ID, ltrim(rtrim(Substring(@IPS,1,Charindex(',',@IPS)-1)))

set @IPS = Substring(@IPS,Charindex(',',@IPS)+1,len(@IPS))

set @ix = @ix + 1

end

insert Into @U select @ID, @IPS

fetch next from XC into @ID, @IPS

end

select * from @U