How can I parse delimited list in string to rows?

Question

This is my previous question: How can I merge two strings of comma-separated numbers in MySQL?

I tried to use delimited list with these reasons:

1. the data is more than 2000 rows.
2. I have just one day.
3. this is not production level
4. I need to parse the data with my hands.

I thought I don't have any choice. But I found this: SQL split values to multiple rows

So, I got some hopes from it. But it's quite difficult to apply it to my table.

1. Mine is different form. I have multiple columns and I'd like to avoid duplicate row.
2. I rather UPDATE and INSERT than SELECT because changing the formation of table will be easier to manage with this reason:Is storing a delimited list in a database column really that bad?
3. I need to find the non listed numbers. For example, if the time value is 1, 3, 8 then I need 2,4,5,6,7,9,10,11,12,13 (between 1 and 13)

My example table looks like this:

+-----+------+--------+------+-------+-----------+-------------+
| cid | sid  | type   | day  | time  | building  | room_number |
+-----+------+--------+------+-------+-----------+-------------+
|   1 |    1 | daytime   | mon   | 6,7,8 | sky    | 507         |
|   2 |    2 | daytime   | thu   | 2,3,4 | nuri    | 906         |
|   3 |    3 | daytime   | tue   | 6,7,8 | nuri    | 906         |
|   4 |    4 | daytime   | thu   | 6,7   | sky    | 1003        |
|   5 |    5 | daytime   | mon   | 2,3,4 | sky    | 507         |
|   6 |    6 | daytime   | wed   | 6,7,8 | belief    | 1003        |
|   7 |    7 | daytime   | mon   | 2,3,4 | belief    | 905         |
|   8 |    8 | daytime   | fri   | 6,7,8 | truth    | 905         |
|   9 |    9 | daytime   | tue   | 6,7,8 | truth    | 905         |
|  10 |   10 | daytime   | fri   | 2,3,4 | truth    | 905         |
|  11 |   11 | daytime   | wed   | 6,7,8 | truth    | 905         |
|  12 |   12 | daytime   | fri   | 2,3,4 | truth    | 1003        |
|  13 |   13 | daytime   | mon   | 6,7,8 | truth    | 905         |
|  14 |   14 | daytime   | tue   | 2,3,4 | truth    | 905         |
|  15 |   15 | daytime   | tue   | 6,7,8 | sky    | 208         |
|  16 |   16 | daytime   | tue   | 2,3,4 | sky    | 208         |
|  17 |   17 | daytime   | tue   | 2,3,4 | truth    | 1004        |
|  18 |   19 | daytime   | mon   | 2,3,4 | sky    | 208         |
|  19 |   20 | daytime   | thu   | 2,3,4 | truth    | 1003        |
|  20 |   21 | daytime   | wed   | 6,7,8 | sky    | 208         |
|  21 |   22 | night   | tue   | 4,5,6 | nuri    | 405         |
|  22 |   23 | night   | tue   | 1,2,3 | nuri    | 405         |
|  23 |   24 | night   | tue   | 1,2,3 | nuri    | 306         |
|  24 |   25 | night   | thu   | 1,2,3 | nuri    | 205         |
|  25 |   26 | night   | thu   | 4,5,6 | sky    | 306         |
|  26 |   27 | night   | wed   | 1,2,3 | nuri    | 306         |
|  27 |   28 | night   | wed   | 4,5,6 | sky    | 309         |
|  28 |   29 | night   | wed   | 4,5,6 | nuri    | 407         |
|  29 |   30 | night   | tue   | 4,5,6 | nuri    | 306         |
|  30 |   31 | night   | thu   | 1,2,3 | nuri    | 307         |
| 31 |    0 | always   |      |      | sky    | 201         |
| 32 |    0 | always   |      |      | sky    | 202         |
| 33 |    0 | always   |      |      | sky    | 203         |
| 34 |    0 | always   |      |      | sky    | 204         |
| 35 |    0 | always   |      |      | nuri    | 205         |
| 36 |    0 | always   |      |      | nuri    | 206         |
| 37 |    0 | always   |      |      | truth    | 207         |
| ... |    ... | ...   |      |      | ...    | ...         |
| 2000 |    0 | always   |      |      | belief    | 1101         |
+-----+------+--------+------+-------+-----------+-------------+

1. 507|sky building on monday: 2,3,4,6,7,8 are scheduled. So, 1,5,9,10,11,12,13 will be added.
2. 906|nuri building has schedule on thu, tue. which is different day. So, they will not affect each other. So, 1,5,6,7,8,9,10,11,12,13 on thu & 1,2,3,4,5,9,10,11,12,13 on tue.
3. If there's duplicate row, it won't affect.

And my expectation of changed table is this:

+-----+------+--------+------+-------+-----------+-------------+
| cid | sid  | type   | day  | time  | building  | room_number |
+-----+------+--------+------+-------+-----------+-------------+
|   1 |    1 | daytime   | mon   | 1 | sky    | 507         |
|   2 |    1 | daytime   | mon   | 5 | sky    | 507         |
|   3 |    1 | daytime   | mon   | 9 | sky    | 507         |
|   4 |    1 | daytime   | mon   | 10 | sky    | 507         |
|   5 |    1 | daytime   | mon   | 11 | sky    | 507         |
|   6 |    1 | daytime   | mon   | 12 | sky    | 507         |
|   7 |    1 | daytime   | mon   | 13 | sky    | 507         |
|   8 |    2 | daytime   | thu   | 1 | nuri    | 906         |
|   9 |    2 | daytime   | thu   | 5 | nuri    | 906         |
|   10 |    2 | daytime   | thu   | 6 | nuri    | 906         |
|   11 |    2 | daytime   | thu   | 7 | nuri    | 906         |
|   12 |    2 | daytime   | thu   | 8 | nuri    | 906         |
|   13 |    2 | daytime   | thu   | 9 | nuri    | 906         |
|   14 |    2 | daytime   | thu   | 10 | nuri    | 906         |
|   15 |    2 | daytime   | thu   | 11 | nuri    | 906         |
|   16 |    2 | daytime   | thu   | 12 | nuri    | 906         |
|   17 |    2 | daytime   | thu   | 13 | nuri    | 906         |
|   18 |    3 | daytime   | tue   | 1 | nuri    | 906         |
|   19 |    3 | daytime   | tue   | 2 | nuri    | 906         |
|   20 |    3 | daytime   | tue   | 3 | nuri    | 906         |
| ... |    ... | ...   |      |      | ...    | ...         |
| 302 |    0 | always   |      |      | nuri    | 206         |
| 303 |    0 | always   |      |      | truth    | 207         |
| ... |    ... | ...   |      |      | ...    | ...         |
| 4020 |    0 | always   |      |      | belief    | 1101         |
+-----+------+--------+------+-------+-----------+-------------+

The reason why I do this is the origin data is the time schedule of classes. And I want to find empty time so that people can use the class room during the time there's any classes.

Answer 1

Here is an approach using a recusive query to split the strings into rows. You can then use this resultset to identify the missing records: for this, you can cross join the original table with the list of times, and use an anti-left join to pull out missing records:

with recursive 
    cte as (
        select cid, sid, type, day, room_number,
            substring(time, 1, locate(',', time) - 1) time,
            substring(concat(time, ','), locate(',', time) + 1) rest
        from mytable
        union all
        select cid, sid, type, day, room_number,
            substring(rest, 1, locate(',', rest) - 1),
            substring(rest, locate(',', rest) + 1)
        from cte
        where  locate(',', rest) > 0
    ),
    all_times as (
        select 1 time 
        union all select time + 1 from all_times where time < 13
    )
select  t.cid, t.sid, t.type, t.day, at.time, t.building, t.room_number
from all_times at
cross join mytable t
left join cte c on c.cid = t.cid and c.time = at.time
where c.cid is null
order by t.cid, at.time

Demo on DB Fiddle:

Sample data:

cid | sid | type    | day | time  | building | room_number
--: | --: | :------ | :-- | :---- | :------- | ----------:
  1 |   1 | daytime | mon | 6,7,8 | sky      |         507
  2 |   2 | daytime | thu | 2,3,4 | nuri     |         906

Query results:

cid | sid | type    | day | time | building | room_number
--: | --: | :------ | :-- | ---: | :------- | ----------:
  1 |   1 | daytime | mon |    1 | sky      |         507
  1 |   1 | daytime | mon |    2 | sky      |         507
  1 |   1 | daytime | mon |    3 | sky      |         507
  1 |   1 | daytime | mon |    4 | sky      |         507
  1 |   1 | daytime | mon |    5 | sky      |         507
  1 |   1 | daytime | mon |    9 | sky      |         507
  1 |   1 | daytime | mon |   10 | sky      |         507
  1 |   1 | daytime | mon |   11 | sky      |         507
  1 |   1 | daytime | mon |   12 | sky      |         507
  1 |   1 | daytime | mon |   13 | sky      |         507
  2 |   2 | daytime | thu |    1 | nuri     |         906
  2 |   2 | daytime | thu |    5 | nuri     |         906
  2 |   2 | daytime | thu |    6 | nuri     |         906
  2 |   2 | daytime | thu |    7 | nuri     |         906
  2 |   2 | daytime | thu |    8 | nuri     |         906
  2 |   2 | daytime | thu |    9 | nuri     |         906
  2 |   2 | daytime | thu |   10 | nuri     |         906
  2 |   2 | daytime | thu |   11 | nuri     |         906
  2 |   2 | daytime | thu |   12 | nuri     |         906
  2 |   2 | daytime | thu |   13 | nuri     |         906

You can easily turn this to an insert query by just preceding it with with an insert instruction, like:

insert into sometable(cid, sid, type, day, time, building, room_number)
with recursive ...
select ...
from ...
where ...

---

Actually, since the list of values in the csv list is pre-defined, this can very well be done without recursion. You can join the list of days with the table and use find_in_set() to identify the gaps. The upside is that it would work on versions of MySQL / MariaDB that do not support recusive queries:

select  t.cid, t.sid, t.type, t.day, at.time, t.building, t.room_number
from (
    select 1 time union all select 2 time union all select 3 
    union all select 4 union all select 5 union all select 6 
    union all select 7 union all select 8 union all select 9 
    union all select 10 union all select 11 union all select 12 union all select 13
) at
cross join (
    select distinct cid, sid, type, day, building, room_number 
    from mytable
) t
left join mytable t1 on t1.cid = t.cid and find_in_set(at.time, t1.time)
where t1.cid is null
order by t.cid, at.time

Demo