MySQL：理解映射表

Question

当为具有多对多关系的商业目录构建类别导航系统时，最佳做法是创建映射表。

类别表(Category Table) （CategoryId, CategoryName）
商家表(Business Table) （BusinessId, BusinessName）
类别映射表(Category Mapping Table) （BusinessId, CategoryId）

当我将类别表和商家表联接起来创建映射表时，是否会得到一个包含每个可能商家和类别关系的表格？

我有800个类别和1000个商家列表。那么这会给我提供一个包含800,000个可能关系的表格。如果是这样，我如何专注于只存在的关系？我是否必须遍历所有列表（800,000个）并标记它们为真或假？

我一直对此感到非常困惑，所以任何帮助都将不胜感激。

- Richard Bell

Answer 1

使用多对多关系时，处理的唯一现实方法是使用映射表。

假设我们有一个带有教师和学生的学校，一个学生可以有多个老师，反之亦然。

因此，我们创建了3个表：

student
  id unsigned integer auto_increment primary key
  name varchar

teacher
  id unsigned integer auto_increment primary key
  name varchar

link_st
  student_id integer not null
  teacher_id integer not null
  primary key (student_id, teacher_id)

学生表将有1000条记录
教师表将有20条记录
link_st表将有与链接数量相同的记录（不是20x1000，只有实际链接数量）。

选择
您可以使用以下方式选择例如每个老师的学生：

SELECT s.name, t.name 
FROM student
INNER JOIN link_st l ON (l.student_id = s.id)   <--- first link student to the link-table
INNER JOIN teacher t ON (l.teacher_id = t.id)   <--- then link teacher to the link table.
ORDER BY t.id, s.id

通常在这种情况下应该始终使用内连接inner join。

创建链接
当你要将老师分配给学生(或者反过来，都一样)时，只需要执行以下操作：

INSERT INTO link_st (student_id, teacher_id) 
   SELECT s.id, t.id 
   FROM student s 
   INNER JOIN teacher t ON (t.name = 'Jones')
   WHERE s.name = 'kiddo'

这有点滥用内联连接，但只要名称唯一，它就可以工作。
当然，如果您知道id，您也可以直接插入它们。
如果名称不唯一，则会失败，不应使用。
如何避免重复链接
避免重复链接非常重要，如果出现这种情况，将会发生各种糟糕的事情。
如果您想防止将重复链接插入到链接表中，可以在链接上声明一个unique索引（建议）。

ALTER TABLE link_st
  ADD UNIQUE INDEX s_t (student_id, teacher_id); 

或者您可以在插入语句中进行检查（虽然不是很推荐，但它确实有效）。

INSERT INTO link_st (student_id, teacher_id) 
  SELECT s.id, t.id
  FROM student s
  INNER JOIN teacher t ON (t.id = 548)
  LEFT JOIN link_st l ON (l.student_id = s.id AND l.teacher_id = t.id)
  WHERE (s.id = 785) AND (l.id IS NULL)

如果数据已经存在于link_st表中，则此操作仅会选择548,785条数据，否则将不会选择任何数据，并且拒绝插入重复值。

如果您有一个名为schools的表，那么这取决于学生是否可以在多所学校注册（不太可能，但让我们假设），而教师可以在多所学校注册。这是非常可能的。

table school
  id unsigned integer auto_increment primary key
  name varchar

table school_members
  id id unsigned integer auto_increment primary key
  school_id integer not null
  member_id integer not null
  is_student boolean not null

您可以像这样列出学校中的所有学生：

SELECT s.name
FROM school i
INNER JOIN school_members m ON (i.id = m.school_id)
INNER JOIN student s ON (s.id = m.member_id AND m.is_student = true)

- Johan

---

非常感谢Johan提供高度信息量且写得很好的答案。我想我懂了。只有一个快速问题：在INNER JOIN link_st之后，l是什么意思？那只是用于ON子句中的缩写吗？ - Richard Bell

这是大写字母"I"，不是小写字母"L"。它为表设置别名，因此您可以在不使用表名的情况下引用它：l.student_id而不是link_st.student_id。 - phant0m

@phant0m，这是小写 L 而不是大写 I，我为什么要用一个 I 来给一个名叫link的表取别名呢？ - Johan

@phant0m，嗯，使用小写的 l 本身就是一个失败吧。 - Johan

如果您将其扩展并添加一个名为“学校”的表，会发生什么情况？您会将其与现有的link_st表连接起来吗？这样，在查看记录时，您可以转到SCHOOL> TEACHER> STUDENTS。 - Richard Bell

Answer 2

当你试图建模多对多或一对多关系时，应该使用映射表。

例如，在一个通讯录应用程序中，一个特定的联系人可以属于零个、一个或多个类别。如果你设置了业务逻辑，使得一个联系人只能属于一个类别，那么你会这样定义你的联系人：

Contact
--------------
contactid (PK)
name
categoryid (FK)

Category
--------------
categoryid (PK)
categoryname

但如果你想让一个联系人拥有多个电子邮件地址，可以使用映射表：

Contact
--------------
contactid (PK)
name

Category
--------------
categoryid (PK)
categoryname

Contact_Category
--------------
contactid (FK)
categoryid (FK)

接下来，您可以使用SQL检索联系人分配给的类别列表：

select a.categoryname from Category a, Contact b, Contact_Category c where a.categoryid=c.categoryid and b.contactid=c.contactid and b.contactid=12345;

请注意，这里的HTML标签将保留。

select a.categoryname 
from Category a
inner join Contact_Category c on a.categoryid=c.categoryid
inner join Contact b on b.contactid=c.contactid
where b.contactid=12345;

- Matt Healy

---

请避免使用隐式连接。它们容易混淆，容易出错，对您的心理健康也不利。将它们埋在1989年，改用显式连接。 - Johan

Answer 3

当我将类别表和业务表连接起来创建映射表时，这会给我一个包含每个可能的业务和类别关系的表吗？

是的。

我需要遍历所有列表（800,000）并标记它们为真或假吗？

不需要，您需要使用ON子句设置连接条件。

SELECT <columns> FROM categories as c 
INNER JOIN mapping AS m
    ON m.CategoryId = c.CategoryId
INNER JOIN businesses as b
    ON m.BusinessId = b.BusinessId

- phant0m

Answer 4

在映射表中，您只需放置实际关系。所以平均而言，如果一家企业属于2个类别，则在您的示例中，映射表中只会有2000条记录，而不是800,000条。

“当我连接类别表和商业表以创建映射表时”，您不需要连接这两个表来创建映射表。您需要创建一个实际的物理表。

- Gidon Wise