这个（规范化的）数据库结构是否允许我按照我的意愿按标签进行搜索？

Question

我正在尝试建立一个包含以下三个表的规范化 MySQL 数据库。第一个表包含可以通过各种标签描述的项目列表。第三个表包含用于描述第一个表中的项目的各种标签。中间的表将其他两个表相互关联。在每个表的情况下，id 是一个自动递增的主键（每个都用作中间表中的外键）

+---------------+---------------------+---------------+
|   Table 1     |      Table 2        |   Table 3     |
+---------------+---------------------+---------------+
|id        item |id   item_id   tag_id|id          tag|
+---------------+---------------------+---------------+
| 1      spaniel| 1         1        4| 1         bird|
| 2        tabby| 2         1       23| 4          pet|
| 3      chicken| 3         1       41|23          dog|
| 4     goldfish| 4         2        4|24          cat|
|               | 5         2       24|25      reptile|
|               | 6         3        1|38         fish|
|               | 7         3       40|40    delicious|
|               | 8         4        4|41        cheap|
|               | 9         4       38|42    expensive|
|               |10         4       41|               |
|               |                     |               |
+---------------+---------------------+---------------+

我想对三个表运行一个或多个标签的查询以返回与 ALL 匹配的项目的标签。

例如，查询“pet”将返回项目 (1)spaniel、(2)tabby 和 (4)goldfish，因为它们都被标记为“pet”。一起查询“cheap”和“pet”将返回 (1)spaniel 和 (4)goldfish，因为它们都被标记为“cheap”和“pet”。虎斑猫不会被返回，因为它只被标记为“pet”而不是“cheap”（在我的世界虎斑猫很昂贵：P）

查询“cheap”，“pet”和“dog”只会返回（1）Spaniel，因为它是唯一匹配所有三个标签的标签。

无论如何，这是期望的行为。我有两个问题。

1. 这是根据我的预期目的设置表格的最佳方式吗？我是 对正常化的想法仍然陌生 数据库，我正在挑选这个 继续 - 任何有关效率的意见或 即使这是一个合适的布局 对于我的数据库来说会很多 赞赏。

2. 如果上述设置可行，我该如何构建一个 单个 MySQL 查询来实现我的 预期目的？*（即，对于 系列标签，仅返回 与所有指定项匹配的项目 标签）。我尝试过做各种各样的事情 JOIN/UNIONs 但没有一个是 给我想要的效果（通常 返回与任何一个匹配的所有项目 标签）．我花了一些时间 查阅MySQL手册 在线但我感觉我失踪了 概念上的东西。

*我说单个查询，因为我当然可以运行一系列简单的 WHERE/JOIN 查询，每个标签一个，然后在 PHP 或事后对返回的项目进行组合/排序，但这似乎是一种愚蠢且低效的做法它。我觉得如果有适当的设置，我应该能够通过单个 MySQL 查询来完成此操作。

Answer 1

您的架构看起来相当不错。连接表中不需要 ID 列，只需从其他表的 ID 列创建主键（尽管请参阅 Marjan Venema 的评论和 我是否应该使用复合主键？ 对此有其他看法）。以下示例显示如何创建表、添加一些数据以及执行您请求的查询。

创建表，并添加外键约束。简而言之，外键约束有助于确保数据库的完整性。在此示例中，如果 item 和 tag 表中没有匹配的项目，它们会阻止将项目插入到联接表 (item_tag) 中：

CREATE  TABLE IF NOT EXISTS `item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `item` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `tag` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `item_tag` (
  `item_id` INT UNSIGNED NOT NULL ,
  `tag_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`item_id`, `tag_id`) ,
  INDEX `fk_item_tag_item` (`item_id` ASC) ,
  INDEX `fk_item_tag_tag` (`tag_id` ASC) ,
  CONSTRAINT `fk_item_tag_item`
    FOREIGN KEY (`item_id` )
    REFERENCES `item` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_item_tag_tag`
    FOREIGN KEY (`tag_id` )
    REFERENCES `tag` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

插入一些测试数据：

INSERT INTO item (item) VALUES
('spaniel'),
('tabby'),
('chicken'),
('goldfish');

INSERT INTO tag (tag) VALUES
('bird'),
('pet'),
('dog'),
('cat'),
('reptile'),
('fish'),
('delicious'),
('cheap'),
('expensive');

INSERT INTO item_tag (item_id, tag_id) VALUES
(1,2),
(1,3),
(1,8),
(2,2),
(2,4),
(3,1),
(3,7),
(4,2),
(4,6),
(4,8);

选择所有项目和所有标签：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id;

+----+----------+-----------+
| id | item     | tag       |
+----+----------+-----------+
|  1 | spaniel  | pet       |
|  1 | spaniel  | dog       |
|  1 | spaniel  | cheap     |
|  2 | tabby    | pet       |
|  2 | tabby    | cat       |
|  3 | chicken  | bird      |
|  3 | chicken  | delicious |
|  4 | goldfish | pet       |
|  4 | goldfish | fish      |
|  4 | goldfish | cheap     |
+----+----------+-----------+

选择具有特定标签的项目：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag = 'pet';

+----+----------+-----+
| id | item     | tag |
+----+----------+-----+
|  1 | spaniel  | pet |
|  2 | tabby    | pet |
|  4 | goldfish | pet |
+----+----------+-----+

选择具有一个或多个标签的项目。请注意，这将返回带有 cheap 或 pet 标签的商品：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet');

+----+----------+-------+
| id | item     | tag   |
+----+----------+-------+
|  1 | spaniel  | pet   |
|  1 | spaniel  | cheap |
|  2 | tabby    | pet   |
|  4 | goldfish | pet   |
|  4 | goldfish | cheap |
+----+----------+-------+

上面的查询会生成您可能不想要的答案，如以下查询突出显示的那样。在这种情况下，没有带有 house 标记的项目，但此查询仍然返回一些行：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'house');

+----+----------+-------+
| id | item     | tag   |
+----+----------+-------+
|  1 | spaniel  | cheap |
|  4 | goldfish | cheap |
+----+----------+-------+

您可以通过添加 GROUP BY 和 HAVING：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'house')
GROUP BY item.id HAVING COUNT(*) = 2;

Empty set (0.00 sec)

GROUP BY 导致具有相同 id（或您指定的任何列）的所有项目被分组到一行中，从而有效地删除重复项。 HAVING COUNT 将结果限制为匹配分组行数等于 2 的结果。这可确保仅返回具有两个标签的项目 - 请注意，该值必须与 IN 子句中指定的标签数量相匹配。下面是一个生成某些内容的示例：

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet')
GROUP BY item.id HAVING COUNT(*) = 2;

+----+----------+-----+
| id | item     | tag |
+----+----------+-----+
|  1 | spaniel  | pet |
|  4 | goldfish | pet |
+----+----------+-----+

请注意，在前面的示例中，项目已分组在一起，这样您就不会得到重复项。在这种情况下，不需要 tag 列，因为这只会混淆结果 - 您已经知道有哪些标签，因为您已请求带有这些标签的项目。因此，您可以通过从查询中删除 tag 列来稍微简化一下：

SELECT item.id, item.item
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet')
GROUP BY item.id HAVING COUNT(*) = 2;

+----+----------+
| id | item     |
+----+----------+
|  1 | spaniel  |
|  4 | goldfish |
+----+----------+

您可以更进一步，使用 GROUP_CONCAT 提供匹配标签的列表。如果您想要一个具有一个或多个指定标签（但不一定是全部）的项目列表，这可能会很方便：

SELECT item.id, item.item, GROUP_CONCAT(tag.tag) AS tags
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet', 'bird', 'cat')
GROUP BY id;

+----+----------+-----------+
| id | item     | tags      |
+----+----------+-----------+
|  1 | spaniel  | pet,cheap |
|  2 | tabby    | pet,cat   |
|  3 | chicken  | bird      |
|  4 | goldfish | pet,cheap |
+----+----------+-----------+

上述架构设计的一个问题是可能会输入重复的项目和标签。也就是说，您可以将bird插入tag表中任意多次，但这并不好。解决此问题的一种方法是将 UNIQUE INDEX 添加到 item 和 tag 列。这具有帮助加快依赖这些列的查询的额外好处。更新后的 CREATE TABLE 命令现在如下所示：

CREATE  TABLE IF NOT EXISTS `item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `item` VARCHAR(255) NOT NULL ,
  UNIQUE INDEX `item` (`item`) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `tag` VARCHAR(255) NOT NULL ,
  UNIQUE INDEX `tag` (`tag`) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

现在，如果您尝试插入重复值，MySQL 将阻止您这样做：

INSERT INTO tag (tag) VALUES ('bird');
ERROR 1062 (23000): Duplicate entry 'bird' for key 'tag'

Answer 2

是的。这称为关系划分。这里讨论了各种技术 http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

一种方法是使用双重否定。 IE。选择表 1 中列表中没有标签的所有记录 'cheap','pet' 在表 2 中没有关联记录

SELECT t1.id, t1.item
FROM Table1 t1
WHERE NOT EXISTS
(
    SELECT * FROM  
    table3 t3 WHERE tag IN ('cheap','pet')
    AND NOT EXISTS (
        SELECT * FROM table2 t2
        WHERE t2.tag_id = t3.id
        AND t1.id=t2.item_id
    )
)

Answer 3

1. 这个映射表概念非常标准，并且看起来在这里实现得很好。我唯一要改变的是去掉表 2 中的 ID；你会用它做什么？只需在项目 ID 和标签 ID 上为表 2 创建一个联合键即可。

2. 实际上，选择一个项目与所有标签匹配的位置是很困难的。试试这个：

   SELECT item_id,COUNT(tag_id) FROM Table2 WHERE tag_id IN (your set here) GROUP BY item_id

如果计数等于集合中标签 ID 的数量，则您已找到匹配项。

Answer 4

您可以尝试这样的操作：

select item, count(*) 'NrMatches'
from #table1 i
inner join #table2 l ON i.id = l.item_id
inner join #table3 t on l.tag_id = t.id
where t.tag IN ('cheap', 'pet', 'dog')
group by item
having count(*) = (select count(*) from #table3 
                   where tag IN ('cheap', 'pet', 'dog'))

这意味着您的搜索词会出现两次，但它基本上可以满足您的需求。

Answer 5

不确定其他人可能已经提到过这一点，但第二个表中的 id 列是多余的。您可以只创建一个连接主键：

PRIMARY KEY (item_id, tag_id)

否则，它是一个 bretty 标准 m:n 数据库方案，并且应该可以正常工作。

Answer 6

感谢大家非常详细和有用的回复。关于使用“WHERE tag IN ('tag_1'...'tag_x')”与 COUNT 结合使用来选择与所有标签匹配的项目正是我之前所缺少的。

关于使用复合主键的输入也非常有帮助 - 我觉得在中间表上使用唯一的 ID 键没有意义，但从未意识到我可以使用复合键。

再次感谢您！你们太棒了！