mysql一条数据分成多列,如何在mysql中将单行拆分为多列

Simply Asking, Is there any function available in mysql to split single row elements in to multiple columns ?

I have a table row with the fields, user_id, user_name, user_location.

In this a user can add multiple locations. I am imploding the locations and storing it in a table as a single row using php.

When i am showing the user records in a grid view, I am getting problem for pagination as i am showing the records by splitting the user_locations. So I need to split the user_locations ( single row to multiple columns).

Is there any function available in mysql to split and count the records by character ( % ).

For Example the user_location having US%UK%JAPAN%CANADA

How can i split this record in to 4 columns.

I need to check for the count values (4) also. thanks in advance.

解决方案

First normalize the string, removing empty locations and making sure there's a % at the end:

select replace(concat(user_location,'%'),'%%','%') as str

from YourTable where user_id = 1

Then we can count the number of entries with a trick. Replace '%' with '% ', and count the number of spaces added to the string. For example:

select length(replace(str, '%', '% ')) - length(str)

as LocationCount

from (

select replace(concat(user_location,'%'),'%%','%') as str

from YourTable where user_id = 1

) normalized

Using substring_index, we can add columns for a number of locations:

select length(replace(str, '%', '% ')) - length(str)

as LocationCount

, substring_index(substring_index(str,'%',1),'%',-1) as Loc1

, substring_index(substring_index(str,'%',2),'%',-1) as Loc2

, substring_index(substring_index(str,'%',3),'%',-1) as Loc3

from (

select replace(concat(user_location,'%'),'%%','%') as str

from YourTable where user_id = 1

) normalized

For your example US%UK%JAPAN%CANADA, this prints:

LocationCount Loc1 Loc2 Loc3

4 US UK JAPAN

So you see it can be done, but parsing strings isn't one of SQL's strengths.