mysql - 效率问题 - 从一个字段中选择数值数据

Question

我有一对表，我需要在 Table1 中搜索与 Table2 上的关联 ID 匹配的数值。例如:

表1

ID | Item
1    Cat
3    Frog
9    Dog
11   Horse

表2

Category | Contains
Group 1   1
Group 2   3|9
Group 3   3|9|11

最初我认为 LIKE 会起作用，但如果我搜索“1”，我最终会匹配“11”。我查看了 SET，但 MySQL 文档指出最大元素数为 64，并且 Table1 中有超过 200 行项目。我可以用一个字符(例如“|1|”)包装每个项目 id，但这似乎不是很有效。每个组都会有独特的元素(例如，同一组中不会有两只猫)。

我找到了similar topic因为我的问题和答案之一建议制作另一张 table ，但我不明白这是如何工作的。一个新表到底包含什么？

我的另一个选择是将 Contains 分成 6 个单独的列，因为一个组中永远不会有超过 6 个项目，但是我不确定如何在不依赖于 6 个 OR 的情况下搜索所有 6 列查询:

Category |  C1  |  C2  |  C3  |  C4 (etc)
Group 1    1      null   null   null
Group 2    3       9     null   null
Group 3    3       9      11    null

SELECT * FROM Table2 WHERE C1 = '1' OR C2 = '1' OR C3 = '1' etc.

我不确定处理此问题最有效的方法是什么。我可以使用那些在标准化此类数据方面更有经验的人的一些建议。谢谢。

Answer 1

我认为最好创建另一个表来标准化您的数据，但是您提出的建议并不完全是我的建议。

实际上，您正在建模的是 table1 和 table2 之间的多对多关系。这意味着 table1 中的一行可以与 table2 中的多行关联，反之亦然。

为了创建这种关系，您需要第三个表，我们现在可以将其称为 rel_table1_table2。

rel_table1_table2 将仅包含两个关联表的主键值，在本例中似乎是 table1.ID 和 table2.Category。

当您想要将 table1 中的行与 table2 中的行关联时，您需要分别使用 table1 和 table2 中的主键值向 rel_table1_table2 添加一行。

示例:

INSERT INTO rel_table1_table2 (ID, Category) VALUES (1, "Group 1")

当您需要找出哪些项目属于某个类别时，您只需查询关联表即可，例如:

SELECT i.Item from table1 t1 join rel_table1_table2 r on t1.ID=r.ID join table2 t2 on r.Category=t2.Category WHERE t2.Category="Group 3"

这有意义吗？