如何为标签云设计一个MySql表？

Question

我在我的网站上有一些文章，我想为每篇文章添加标签来描述它们，但是我在设计mysql标签表时遇到了问题。我有两个想法：

1. 每篇文章将拥有一个“标签”字段，并且标签将以“标签1，标签2，标签3”的格式出现。
2. 创建另一个名为“标签”的表，包含字段：标签名称、文章ID。

因此，当我想获取ID为1的文章的标签时，我将运行

SELECT ... FROM tags WHERE `article_id`=1;

但是，我还想通过比较标签知道三篇最相似的文章，因此如果我的文章有标签“php，mysql，erlang”，并且有5篇文章的标签为：“php，mysql”，“erlang，ruby”，“php erlang”，“mysql，erlang，javascript”，我会选择第1、3和4篇文章，因为这些文章与主要文章具有最相似的标签。

另外一个问题是，获取10个“最常用的标签”的最佳方法是什么？

- mfolnovich

Answer 1

通常，对于这种多对多的关系，需要三个表：

• "article" 表
  • 主键 = id
• "tag" 表
  • 主键 = id
  • 包含每个标记的数据:
    • 例如名称
• "tags_articles" 表，作为联接表，仅包含：
  • id_article：指向文章的外键
  • id_tag：指向标记的外键

这样，不会重复任何标记的数据：对于每个标记，在 tag 表中只有一行。

对于每篇文章，您可以拥有多个标记（即在 tags_articles 表中有几行）；当然，对于每个标记，您也可以有多篇文章。

根据这个想法获取文章的标记列表是一个额外的查询问题，类似于:

select tag.*
from tag
    inner join tags_articles on tag.id = tags_articles.id_tag
where tags_articles.id_article = 123

获取三篇“最相似”的文章意味着：

• 选择具有第一篇文章标签的文章
• 仅使用具有最重要数量的相同标签的文章

未经测试，但一个想法可能类似于以下内容：

select article.id, count(*) as nb_identical_tags
from article
    inner join tags_articles on tags_articles.id_article = article.id
    inner join tag on tag.id = tags_articles.id_tag
where tag.name in ('php', 'mysql', 'erlang')
      and article.id <> 123
group by article.id
order by count(*) desc
limit 3

基本上，您需要：

• 为初始文章上的每个标签选择文章id
  • 由于存在内部连接，如果数据库中的一篇文章具有与where子句匹配的两个标签，没有group by子句，则该文章将有两行
  • 当然，您不希望重新选择已经拥有的文章-这意味着必须排除它。
• 但是，由于使用了group by article.id，因此每篇文章只有一行
  • 但是您可以使用count，以查找每篇文章与初始文章有多少个共同标签
• 然后，只需要按标签数量排序，并获取前三行即可。

- Pascal MARTIN

---

是的，但您没有回答我的主要问题，如何获取三篇最相似的文章？ - mfolnovich

@mfolnovich：我已经编辑了我的答案几次，添加了更多关于设计的信息；然后，看到你的评论后，我重新编辑了一下，提出了一个可能的解决方案来获取最相似的3篇文章。 - Pascal MARTIN

Answer 2

首先，您需要使用Pascal MARTIN的建议来设计表格。

至于查找类似文章，以下是一些起点。假设@article_id是您想要查找匹配项的文章，而@tag1、@tag2、@tag3是该文章的标签：

SELECT article_id, count(*)
FROM tags_articles
WHERE article_id <> @article_id
AND tag_id IN (@tag1, @tag2, @tag3)
GROUP BY article_id
ORDER BY count(*) DESC
LIMIT 3

- Eric Petroelje

Answer 3

是的，但你没有回答我的主要问题，如何获取三篇最相似的文章？

答案： 只需在合并表（tags_articles）中查找相同的标签ID。收集它们并创建一个模式。

例如： 文章1有标签：1,2 文章2有标签：2,3,4 文章5有标签：6,7,2 文章7有标签：7,1,2,3

如果您想要文章1的三篇最相似的文章，则必须查找标签1,2。您会发现文章7最相似，而2和5有一些相似之处。

- Kel