MySQL中的多对多关系 [英] Many-to-Many Relationships in MySQL

问题描述

最近我一直在阅读外键和加入，并且惊喜地发现许多基本概念是我已经付诸实践的东西。例如，在我正在处理的一个项目中，我正在组织单词列表，并为这些集合设置一个表格，如下所示：

 < code``words`表
`word_id` 
`词汇`
`category_id` 
`类别`表
`category_id` 
` category_name` 
  

现在，一般来说这是一对多的关系，使用外键 category_id 放置在一个类别下。让我们暂时假设用户选择添加另一个类别到一个单词，使它成为多对多有没有办法设置我的单词表来处理额外的单词类别，而不会像 category_2 ， category_3 等创建额外的列

解决方案

通常情况下，您有一个单独的表来处理这些映射：

 < code $ Words_Categories` Table 
`word_id` 
`category_id` 
  

这个 Words_Categories 表中的每一对代表从任何单词到任何其他类别的映射。

在这个模式下， Words 表中的code> category_id 字段不需要，因为这两个表都不直接相互引用。

I've been reading up on foreign keys and joins recently, and have been pleasantly surprised that many of the basic concepts are things I'm already putting into practice. For example, with one project I'm currently working on, I'm organizing word lists, and have a table for the sets, like so:

`words` Table
    `word_id`
    `headword`
    `category_id`
`categories` Table
    `category_id`
    `category_name`

Now, generally speaking this would be a one-to-many relationship, with several words being placed under a single category with the foreign key category_id. Let's assume for a moment, however, that a user chooses to add another category to a word, making it many-to-many—Is there a way to set up my words table to handle additional categories for words without creating extra columns like category_2, category_3, etc.?

解决方案

Usually you have a separate table to handle these mappings:

`Words_Categories` Table
    `word_id`
    `category_id`

Each pair in this Words_Categories table represents one possible mapping from any word to any other category.

The category_id field in the Words table becomes unneeded under this scheme, as neither of these tables references each other directly.

这篇关于MySQL中的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！