mysql - 如何在 MySQL 中为一行分配多个 id？

Question

假设我们有一个包含这些标签记录的表:

Category   ID

apples     1
orange     2

然后我们有另一个表有一行

Data catID
...    1

使用此设置，我们只能在苹果页面中检索这一行，将苹果和橙子都分配给该行的正确方法是什么？我是否需要将 catID 字段从 integer 更改为 varchar 并仅添加第二个 id，以便该值为 1,2 然后将查询编辑为类似以下内容:

 select * from table where catID LIKE '%1%' 
 select * from table where catID LIKE '%2%'

代替

  select * from table where catID='1'
  select * from table where catID='2'

我不确定这是不是正确的方法？有人能告诉你怎么做吗？基本上，我不想复制整行，只是向其中添加另一个 id。

Answer 1

正如其他人已经建议的那样，多对多关系在物理模型中由联结表表示。我会做腿部工作并为您说明:

CATEGORY_ITEM 是联结表。它有一个复合 PK，由从其他两个表迁移的 FK 组成。示例数据...

类别:

CATEGORY_ID CATEGORY
----------- --------
1           Apple
2           Orange

项目:

ITEM_ID     NAME
-------     ----
1           Foo
2           Bar

CATEGORY_ITEM:

CATEGORY_ID ITEM_ID
----------- -------
1           1
2           1
1           2

上面的意思是:“Foo 既是 Apple 又是 Orange，Bar 只是 Apple”。

PK 确保任何给定的类别和项目组合不能存在多次。该类别要么连接到不是的项目 - 它不能连接多次。

由于您主要想搜索给定类别的项目，因此 PK 中字段的顺序是 {CATEGORY_ID, ITEM_ID} 因此基础索引可以满足该查询。确切的解释为什么超出了这个范围 - 如果您有兴趣，我强烈建议您阅读 Use The Index, Luke! .

自 InnoDB uses clustering ，这也会将属于同一类别的项目物理上靠近地存储在一起，这可能对上述查询的 I/O 相当有益。

(如果您想查询给定项目的类别，您需要翻转索引中字段的顺序。)