How to expand comma-separated field into multiple rows in 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.开发者_JAVA技巧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

I don't think this is something you want to do using a query, but you'd rather do this in your presentation logic. The database is just for storing and retrieving data. Formatting data and presenting it is something to do in your presentation layer, usually combined with PHP/ASP.NET/something else.

This has come up several times before. Here's one answer:

https://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows