mysql - SQL 选项存储和引用

Question

如果我有一张 table :

CREATE TABLE Kids (
    kid_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    kid_name varchar(45) NOT NULL,
    kid_favcolors (text) NULL,
    PRIMARY_KEY(kid_id)
    )

我有一张 table :

CREATE TABLE Colors (
    color_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    color_name varchar(45) NOT NULL,
    PRIMARY_KEY(color_id)
)

通过逗号分隔的 id 引用喜欢的颜色的行:

INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Joe','1,2,3,4,5');
INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Mary','1,2,3');

我将如何创建一个语句来返回 Kid 数据库中的每个条目以及 kid_favcolors 列中引用的所有 ID 的 Kid_name 和 color_name。

例如:

COLORS:

color_id    color_name1           yellow2           green3           blue4           purple5           red6           brown7           black

KIDS:

kid_id      kid_name    kid_favcolors1           Joe         1,2,3,4,52           Mary        1,2,3

and I want to retrieve

kid_id      kid_name    favorite_colors1           Joe         yellow, green, blue, purple, red2           Mary        yellow, green, blue

The queries are being executed on existing data and database structure. If there is no feasible solution, rearranging the structure of the data is possible but I assume would add a considerable amount of time to the solution. Either way, would like some help.

I am able to retrieve the colors from the database using:

SELECT STUFF((SELECT ', ' + color_name FROM colors WHERE color_id IN (1,2,3,4,5) FOR XML PATH('')),1, 2, '') AS colors

        colors
1       yellow, green, blue, purple, red

但是当我尝试更复杂的查询时，我似乎无法通过从 Kids 表中提取 ID 来找到合并上述语句的方法。

SELECT kids.kid_id, kids.kid_name, favorite_colors FROM kids JOIN colors ON colors.id IN kids.kid_favcolors as favorite_colors

虽然它看起来对我有用，但不起作用。不确定我离得远还是真的很近。

Answer 1

在原来的解决方案中，将CSV字符串存储在一个字段中违反了1NF；实际上——根据定义——该表甚至不符合关系。因此，在这种情况下，将字符串视为一个原子数据片段，并在应用层对其进行分解。

要在DB层解决，只需修复设计即可。