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

陪她去流浪 桃子 2015年07月14日 编辑 阅读次数：6032

忙了一下午，终于为博客加上了为文章打标签（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的文章同时拥有tag1和tag2。

数据库表的设计

文章表：

1
2
3
4
5
6

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

标签表：

1
2
3
4
5
6

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

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

文章标签表：

1
2
3
4
5
6
7

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 的文章的所有标签：

1
2
3
4

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 下面的所有文章列表：

1
2
3
4
5
6

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 的文章相关联的文章列表（包含自身）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

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

参考

标签：博客日志 · 数据库 · 标签 · 系统设计

相关文章