mysql - 如何规范化具有逗号分隔值的列

Question

我的当前表。只有 id 和 genre 显示其他字段 ommitted

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

我已经创建了 nessecery 表，如何填充它们？

创建名为 genre 的表，其中包含“genreid”和“name”字段

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

我还创建了另一个名为 movie2genre 的表

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

Answer 1

可以拆分字符串。生成一系列数字，涵盖最大数量的字符串。将其与当前表交叉连接并使用 SUBSTRING_INDEX(SUBSTRING_INDEX(genre, ',', some_generated_number), ',', -1) 。这将为您提供每个 id 的所有流派(尽管最后一个将被复制 - 使用 DISTINCT 将其删除)。这可用于填充您的流派表。

在 SQL 中会是:-

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

然后您可以将流派表与您现有的表相结合(使用 FIND_IN_SET)来填充您的 movie2genre 表

填充后，您可以使用一个简单的查询来填充将电影链接到流派的表:-

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, ', ', ','))