如何为MySQL中的一行分配多个ids？ [英] How to assign multiple ids to one row in MySQL?

问题描述

假设我们有一张带有这些标签记录的表：

 类别ID 
 
苹果1 
 orange 2 
  

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

 数据catID 
 ... 1 
  

使用这个设置，我们只能在苹果页面中检索这一行，分配苹果和苹果的正确方式是什么？橙色到那行？我需要将 catID 字段从整数更改为varchar，只需添加第二个id即可，该值将为 1,2 然后将查询编辑为如下：

  select * from table where catID LIKE'％1％'
从表中选择*，其中catID LIKE'％2％'
  

而不是

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

我不知道这是否是正确的方式？有人可以告诉你怎么做吗？基本上，我不想复制整行，只是为了添加另一个id。

解决方案

建议，多对多关系在物理模型中由连接表表示。我会做腿部工作，并为你说明：

CATEGORY_ITEM是连接表。它具有由其他两个表迁移的FK组成的复合PK。示例数据...

CATEGORY：

  CATEGORY_ID CATEGORY 
 ----------- -------- 
 1 Apple 
 2橙色
  

ITEM：

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

CATEGORY_ITEM：

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

上面的意思是：Foo是苹果和橙色，酒吧只是苹果。

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

由于您主要搜索给定类别的项目，因此PK中的字段为{CATEGORY_ID，ITEM_ID}，因此底层索引可以满足该查询。确切的解释为什么超出了这个范围 - 如果你有兴趣，我热烈推荐阅读使用索引，卢克！

而且由于 InnoDB使用聚类，这也将把属于同一类别的物品物理上靠近在一起存储，这可能对上述查询的I / O有好处。

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

Let's say we have a table with these records of tags:

Category   ID

apples     1
orange     2

And then we have another table with a row

Data catID
...    1

With this setup we can retrieve this row only in apples page, what is the proper way to assign both apples & orange to that row? Would I need to change catID field from integer to varchar and just add the second id so the value will be 1,2 and then edit the query to something like:

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

instead of

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

I'm not sure if this is the proper way? Could someone tell how you do it? Basically, I don't want to duplicate the whole row, just to add another id to it.

解决方案

As others have already suggested, many-to-many relationship is represented in the physical model by a junction table. I'll do the leg work and illustrate that for you:

The CATEGORY_ITEM is the junction table. It has a composite PK consisting of FKs migrated from the other two tables. Example data...

CATEGORY:

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

ITEM:

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

CATEGORY_ITEM:

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

The above means: "Foo is both Apple and Orange, Bar is only Apple".

The PK ensures any given combination of category and item cannot exist more than once. The category is either connected to the item of isn't - it cannot be connected multiple times.

Since you primarily want to search for items of given category, the order of fields in the PK is {CATEGORY_ID, ITEM_ID} so the underlying index can satisfy that query. The exact explanation why is beyond this scope - if you are interested I warmly recommend reading Use The Index, Luke!.

And since InnoDB uses clustering, this will also store items belonging to the same category physically close together, which may be rather beneficial for I/O of the query above.

(If you wanted to query for categories of the given item, you'd need to flip the order of fields in the index.)

这篇关于如何为MySQL中的一行分配多个ids？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！