how to split single row to multiple rows in mysql-CodePudding

I want to split each single row to two rows in mysql (I want to split num1 and num2 into two rows by comma). My data is like:

datetime1               count    num1    num2
2022-03-16 03:00:00     0        0,1     1,2
2022-03-16 04:00:00     0        0,1     1,2

and now I want data like this:

datetime1                count    num1 num2
2022-03-16 03:00:00      0        0    1
2022-03-16 03:00:00      0        0    2
2022-03-16 03:00:00      0        1    1
2022-03-16 03:00:00      0        1    2
2022-03-16 04:00:00      0        0    1
2022-03-16 04:00:00      0        0    2
2022-03-16 04:00:00      0        1    1
2022-03-16 04:00:00      0        1    2

CodePudding user response：

Since version 8 MySql supports LATERAL

select t.datetime1, t.count, n1.num1, n2.num2
from tbl t
cross join lateral (
   select SUBSTRING_INDEX(t.num1, ',', 1) AS num1
    UNION ALL
   select SUBSTRING_INDEX(t.num1, ',', -1)
   ) n1
cross join lateral (
   select SUBSTRING_INDEX(t.num2, ',', 1) AS num2
    UNION ALL
   select SUBSTRING_INDEX(t.num2, ',', -1)
   ) n2

Demo