MySQL：了解映射表

php mysql mapping categories database-design

2022-08-31 00:22:50

在为具有多对多关系的业务目录构建类别导航系统时，我理解创建映射表是很好的做法。

类别表（类别 Id，类别名称）
业务表（业务 Id，业务名称）
类别映射表（业务 Id，类别 Id ）

当我联接类别表和业务表以创建映射表时，这会给我一个包含所有可能的业务和类别关系的表吗？

我有800个类别和1000个公司列表。然后，请给我一个包含800，000个可能关系的表。如果是这样，我将如何只关注存在的关系？我是否必须遍历所有列表（800，000），将其标记为真或假？

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

答案 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 语句中执行检查（不是真正推荐的，但它有效）。

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)

如果数据不在表中，则只会选择 548， 785;如果该数据已在link_st中，则不会返回任何内容。因此，它将拒绝插入重复值。link_st

如果你有一个表格学校，这取决于一个学生是否可以在多所学校注册（不太可能，但让我们假设），教师是否可以在多所学校注册。非常有可能。

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)

答案 2

当我联接类别表和业务表以创建映射表时，这会给我一个包含所有可能的业务和类别关系的表吗？

是的。

我是否必须遍历所有列表（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