在MySQL中创建一对多的关系

Question

我有两个表，一个有任意行的书籍，另一个有特定的主题和I行。

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    |       |
+----------+--------------+------+-----+---------+-------+

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

然后，我可以在图书表上查询哪些书与某一主题相关吗？

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可以有多个books_subjects，但是books_subjects正好与一个books相关。

一个subjects可以有多个books_subjects，但是books_subjects正好与一个subjects相关。

检索与特定subjects相关的subjects的查询将涉及到对books_subjects表的连接操作。

例如，查找特定subjects的所有subjects

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

寻找没有任何books的subjects。

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“字符会少得多。我在打字时可能遗漏了一两个字。