How can I normalize a a column that has comma separated values

Question

My Current table. Only id and genre showing other fields ommited

    +---------+----------------------+
    | id      | genre                |
    +---------+----------------------+
    | 1849012 | Animation, Short     |
    | 2016229 | Comedy, Crime, Drama |
    |  224412 | Drama, Family        |
    +---------+----------------------+

I have created the nessecery tables, how can I populate them?

Created table called genre with 'genreid' and 'name' fields

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| genreid | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+.

I have also created another table called movie2genre

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| movieid | int(11) | YES  |     | NULL    |       |
| genreid | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

Probably best bet is to populate the genre table before you do the translate. — Hogan, Oct 30 '14 at 14:11
Create a stored procedure that will "explode" the `genre`, then search the `genre` table for the id, if it's not there, insert a record, then insert into `movie2genre` — ʰᵈˑ, Oct 30 '14 at 14:12
Is using some external language acceptable? For example pull data into PHP, parse genre's and then populate new tables? — Dexa, Oct 30 '14 at 14:13
@hogan that is possible. but how do I populate the movie2genre and update genre column in the original table — 124697, Oct 30 '14 at 14:13
@dexa i use java but it will be extra time writing the java and it will be much slower for 600k rows — 124697, Oct 30 '14 at 14:14
http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql — HLGEM, Oct 30 '14 at 14:14
Isn't this one time job? And why would you update genre column in the original table? — Dexa, Oct 30 '14 at 14:15
You say it is possible and then you describe something else. This is what I suggest. Write a set of scripts to parse the table for genre names (this could even be a select to an excel file.) then get the distinct list (you could do this in excel) insert those in the table. Now the process of adding to the movie2genre table is much easier because you just have to search for the genre's id not also insert it. — Hogan, Oct 30 '14 at 14:15
linked questions is not the same. linked question asks for mapping 1row->1row, 1column->3columns; this questions asks to map 1row->Nrows — Dima Tisnek, Oct 30 '14 at 14:47

score 3 · Accepted Answer · answered Nov 03 '14 at 09:29

It is possible to split a string up. Generate a series of numbers that covers the max number of strings. Cross join this against your current table and use SUBSTRING_INDEX(SUBSTRING_INDEX(genre, ',', some_generated_number), ',', -1) . This will give you all the genres for each id (although the last one will be duplicated - use DISTINCT to remove it). This can be used to populate your genre table.

In SQL that would be:-

INSERT INTO genre (genreid, name) 
SELECT DISTINCT NULL, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(genre, ',', sub0.aCnt), ',', -1)) 
FROM current_table 
CROSS JOIN 
( 
    SELECT units.a + tens.a * 10 AS aCnt 
    FROM 
    (
        SELECT 0 AS a 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
    ) units
    CROSS JOIN
    (
        SELECT 0 AS a 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
    ) tens 
) sub0

Then you can join the genre table against your existing table (using FIND_IN_SET) to populate your movie2genre table

Once that is populated you can use a simple query to populate the table linking movies to genres:-

INSERT INTO movie2genre 
SELECT current_table.id, genre.id 
FROM current_table 
INNER JOIN genre 
ON FIND_IN_SET(genre.name, REPLACE(current_table.genre, ', ', ','))

How can I normalize a a column that has comma separated values

1 Answers1