mysql - 在MySQL中创建一对多关系

Question

我有两张表，一张包含任意行的书籍，一张包含特定行的主题和 ID。

mysql> describe books;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| title | varchar(250) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+


mysql> describe subjects;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| subjects | varchar(250) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

我想将一本书与多个主题 ID 相关联，是否可以使用任何数组类型，以便图书表中的一列包含不同主题 ID 的数组？

然后我可以在 books 表上进行查询以查看哪些书与某个主题相关吗？

Answer 1

这是多对多关系，而不是一对多关系。 (一本书可以有多个主题，一个主题可以有几本书。)

规范模式是实现第三个表来存储书籍和主题之间的“关系”。

作为最小关系表的示例:

CREATE TABLE books_subjects
( subjects_id  INT(11) NOT NULL COMMENT 'PK, FK ref subjects(id)'
, books_id     INT(11) NOT NULL COMMENT 'PK, FK ref books(id)'
, PRIMARY KEY (subjects_id, books_id)
);

对于InnoDB，我们可以定义外键约束:

ALTER TABLE books_subjects ADD CONSTRAINT FK_books_subjects_books
  FOREIGN KEY (books_id) REFERENCES books(id);

ALTER TABLE books_subjects ADD CONSTRAINT FK_books_subjects_subjects
  FOREIGN KEY (subjects_id) REFERENCES subjects(id);

这有效地将多对多关系解析为两个一对多关系。

一本图书可以有多个books_subjects，但一个books_subjects只与一本图书相关。

一个subjects可以有多个books_subjects，但一个books_subjects只与一个subjects相关。

<小时/>

检索与特定主题相关的书籍的查询将涉及到books_subjects表的JOIN操作。

例如，要查找特定主题的所有书籍:

SELECT b.id
     , b.title
  FROM books b
  JOIN books_subjects j
    ON j.books_id = b.id
  JOIN subjects s
    ON s.id = j.subjects_id
 WHERE s.subjects = 'Psychology'
 ORDER BY b.title

查找没有任何主题的书籍，例如

SELECT b.id
     , b.title
  FROM books b
  LEFT
  JOIN books_subjects j
    ON j.books_id = b.id
 WHERE j.books_id IS NULL
 ORDER BY b.title

<小时/>

注意:我通常对表使用单数名称。因此，我的列名称和外键名称中的“s”字符会少得多。我可能在打字时省略了一两个“s”。