mysql - How to split comma separated text and create table-CodePudding

how to split comma separated string from one column and turn it into several columns?

this is my table:

SELECT id,lik FROM `tbl_users_posts` WHERE id=1;

id      lik
-------------
1       10,11,12,13,14,15

how can i split 'lik' column and get this result?

id      lik
-------------
1       10
1       11
1       12
1       13
1       14
1       15

displays id 1 in the first row and split the 'lik' column into pieces in the second row and displays it one by one

CodePudding user response：

Unfortunately MySQL doesn't have a split string functions. One way is create a temporary table as following with the max values of the largest row:

create temporary table numbers as (
  select 1 as n
  union select 2 as n
  union select 3 as n
  union select 4 as n
  union select 5 as n
  union select 6 as n
  union select 7 as n
  union select 8 as n
);

Then you can use substring_index to accomplish the desired result

select id,
       substring_index( substring_index(lik, ',', n),',', -1) as lik
from tbl_users_posts
join numbers on char_length(lik) - char_length(replace(lik, ',', '')) >= n - 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=84bc1b4e60a7feea5af0d0b568bc7bcb

Edit. Another method if you have MySQL 8 for the lik string to split into thousands of pieces without a loop is create an temporary table using recursive cte as follows:

CREATE TEMPORARY TABLE numbers  WITH RECURSIVE  cte AS 
( select 1 as n
  union all
  select n  1
  from cte
  limit 1000 
)
SELECT * FROM cte;

And then use the same query as above:

select id,
       substring_index( substring_index(lik, ',', n),',', -1) as lik
from tbl_users_posts
join numbers on char_length(lik) - char_length(replace(lik, ',', '')) >= n - 1
order by id asc;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9231202418ce9b17aef8609ad6875fbe

CodePudding user response：

If the lik is a number that can be found in the database you can do:

select p.id, t.lik_id
from table_containing_lik t
  join tbl_users_posts p on find_in_set(t.lik_id, p.lik)
where p.id=1;