Elegant way with SQL Statement to INSERT based on queried list from another table?

Question

I have a table persons in a MySQL 5.6 database like this:

persons
---------------------------------------------------
| id           | name           | sports          |
---------------------------------------------------
| 1            | Adam           | Football,Hockey |
| 2            | Ben            | Hockey,Tennis   |
| 3            | Charlie        | Basketball      |
| 4            | Dennis         | Hockey,Baseball |
---------------------------------------------------

As you can see, the column sports is a string, but contains a list. The reason sports is a string list is because the table was created from an automation step I cannot influence.

I want to find all Persons who like a certain sports type. Searching through the sports string column with %LIKE% is of course inefficient. I want to leave the table as it is but for the purpose of searching for persons who like a certain sports type with efficiency, I want to fill a new M:N table persons_sports like this:

persons_sports
---------------------------------------------------
| id           | person_id      | sports          |
---------------------------------------------------
| 1            | 1              | Football        |
| 2            | 1              | Hockey          |
| 3            | 2              | Hockey          |
| 4            | 2              | Tennis          |
| 5            | 3              | Basketball      |
| 6            | 4              | Hockey          |
| 7            | 4              | Baseball        |
---------------------------------------------------

Of course I can write a script getting all rows from persons, parsing the sports column and then fill the new table but I wonder if there is a nice and elegant way to do this via a SQL statement on the database server directly?

Precise MySQL version? Max. `sport` values per column amount? — Akina, Sep 10 '20 at 09:44
*I wonder if there is a nice and elegant way to do this via a SQL statement on the database server directly* Use JSON_TABLE() if available in your MySQL version. — Akina, Sep 10 '20 at 09:46
@Akina: Thanks for the question, I've edited my question. It's MySQL 5.6 — Norbert, Sep 10 '20 at 09:48

GMB · Answer 1 · 2020-09-10T09:55:19.553

If you have a table that contains the lists of sports, you can do this with find_in_set():

select p.person_id, s.sport
from persons p
inner join sports s on find_in_set(s.sport, p.sports)

If you don't, then it is more complicated. One option is to use a recursive cte (available in MySQL 8.0) to iteratively split the strings:

with recursive 
    data as (select id, concat(sports, ',') sports from persons),
    persons_sports as (
        select 
            id, 
            substring(sports, 1, locate(',', sports) - 1) sport,
            substring(sports,locate(',', sports) + 1) rest
        from data
        union all
        select
            id,
            substring(rest, 1, locate(',', rest) - 1),
            substring(rest, locate(',', rest) + 1)
        from persons_sports
        where locate(',', rest) > 0
    )
select id, sport from persons_sports

The table `persons_sports` is empty at first. Can this be modified in a way to fill the table? — Norbert, Sep 10 '20 at 10:11
@Norbert: sure: `insert into persons_sports(person_id, sport) select < the above query >` — GMB, Sep 10 '20 at 10:18

Elegant way with SQL Statement to INSERT based on queried list from another table?

1 Answers1