[博客日志] 文章标签系统的设计实现

忙了一下午,终于为博客加上了为文章打标签(Tagging)的功能。

自从我把文章的固定链接改成纯ID之后,就越发觉得标签(tag)比分类(category)重要。因为: 分类一般是一对多模式。就是一个分类下可以有很多文章。一篇文章一般不属于多个分类(WordPress可以)。 而标签却是多对多模式。也就是说,一篇文章可以拥有多个标签,同时一个标签下也可以有多篇文章。

分类把文章限制得太死,标签可以把不属于一个分类下的文章联系起来,尽管这两篇文章可能不完全属于一个分类。 比如有一篇文章《解决PHP中MySQL的某某问题》,那么具体要放到PHP和MySQL中哪个分类呢?都不好。最好是打两个标签!

数据库设计

数据库模式最早是借鉴了WordPress的设计(因为读了它的很多代码,借鉴了很多)。

一共三个数据表:文章表、标签表、文章标签表。

文章表

一篇文章一条数据库记录,就像下面这样的格式:

文章编号 文章标题 文章内容
1 title1 content1
2 title2 content2

标签表

标签表记录了所有的标签。每一条标签是一个独立的实体,有自己的编号。

标签编号 标签名
1 tag1
2 tag2

文章标签表

这个表记录每篇文章所拥有的标签。它把前面两个表结合起来:

文章标签编号 文章编号 标签编号
1 1 1
2 2 1
3 2 2

上表说明:编号为1的文章拥有标签tag1,编号为2的文章同时拥有tag1tag2

数据库表的设计

文章表:

CREATE TABLE `posts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(128) NOT NULL,
    `content` TEXT NOT NULL,
    PRIMARY KEY (`id`)
);

标签表

CREATE TABLE `tags` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY `name` (`name`)
);

标签的名字应该唯一,所以加了唯一索引。

文章标签表

CREATE TABLE `post_tags` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id` INT UNSIGNED NOT NULL,
    `tag_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY `post_id__tag_id` (`post_id`,`tag_id`)
);

同样,一篇文章不应有多个相同的标签,所以给文章编号和标签两个字段加了复合索引。

数据库查询功能实现

标签系统实现后,可以至少实现两个功能:

  • 列举出某篇文章的所有标签
  • 列举出某个(或多个)标签下的所有文章
  • 列举出与某篇文章标签相关的文章列表

列举文章标签

以下示例查询编号为 2 的文章的所有标签:

SELECT t.* FROM tags AS t
INNER JOIN post_tags AS pt
ON t.id = pt.tag_id
WHERE pt.post_id = 2;

查询结果:

+----+------+
| id | name |
+----+------+
|  1 | tag1 |
|  2 | tag2 |
+----+------+

根据标签查文章

以下示例查询 标签1 下面的所有文章列表:

SELECT p.* FROM posts AS p
INNER JOIN post_tags AS pt
ON p.id = pt.post_id
INNER JOIN tags AS t
ON pt.tag_id = t.id
WHERE t.name = 'tag1';

查询结果:

+----+--------+----------+
| id | title  | content  |
+----+--------+----------+
|  1 | title1 | content1 |
|  2 | title2 | content2 |
+----+--------+----------+

查询相关文章

以下示例联表查询编号为 1 的文章相关联的文章列表(包含自身):

SELECT p.*,count(p.id) AS relevance
FROM posts AS p
INNER JOIN post_tags AS pt
ON p.id = pt.post_id
INNER JOIN tags AS t
ON pt.tag_id = t.id
WHERE t.id IN (
    SELECT t.id
    FROM tags AS t
    INNER JOIN post_tags AS pt
    ON t.id = pt.tag_id
    WHERE pt.post_id = 1
)
GROUP BY p.id
ORDER BY relevance DESC;

这条语句写得有点复杂,不过仍然容易理解。以下是输出结果:

+----+--------+----------+-----------+
| id | title  | content  | relevance |
+----+--------+----------+-----------+
|  1 | title1 | content1 |         1 |
|  2 | title2 | content2 |         1 |
+----+--------+----------+-----------+

参考

发表于:2015年07月14日 ,阅读量:1089 ,标签:博客日志 · 数据库 · 标签 · 系统设计

版权声明:若非特别注明,本站所有文章均为作者原创,转载请务必注明原文地址。