MySQL insert comma delimited variable into multiple rows

Question

I'm trying to create a stored procedure to quickly insert some data into my DB and just having a little trouble figuring out the quickest way to do this.

Into table one I insert a bunch of data about a file and return the new ID of that file.

I then have a separate table that tracks which versions of the app that file is compatible with.

This table has two columns

FileID, Version

Let's say the returned FileID = 1 in this instance.

The Version String will look like this

"1.1, 1.2, 2.1, 2.2"

Essentially I want to split that string and loop it so it ends up as 4 rows like this

FileID	Version
1	1.1
1	1.2
1	2.1
1	2.2

please take a look at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, May 15 '21 at 11:27
Not sure how that helps? I'm not trying to store a comma separated list in a single column. I want to separate out what will be a comma delimited string and store it into separate rows, which as far as I can tell would be best practice? — Ali Beasley, May 15 '21 at 11:44
the are lot of functions on SO that do that thing, look for them and don't save the data in that way — nbk, May 15 '21 at 11:57

score -1 · Answer 1 · answered May 15 '21 at 13:16

You can accomplish this goal with a little bit of creative splitting:

SELECT zz.`file_id`, zz.`word`
          FROM (SELECT DISTINCT `file_id`, LOWER(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX('1.1,1.2,1.3,2.0,2.1,2.1.5,2.2', ',', num.`id`), ',', -1))) as `word`
                  FROM (SELECT (h*1000+t*100+u*10+v+1) as `id`
                          FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                               (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
                               (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
                               (SELECT 0 v UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d) num
                 WHERE num.`id` >= 0) zz
         WHERE zz.`word` NOT IN ('')
         ORDER BY zz.`word`;

This will give you up to 10,000 versions … if you need that many.

MySQL insert comma delimited variable into multiple rows

1 Answers1