做笔记类应用时，标签系统几乎是绕不开的功能。相比按文件夹分类的单一归属模式，标签允许多维度的组织方式，一条笔记可以同时属于“工作”和“前端”，也可以属于“学习笔记”。这种多对多关系如果表结构设计不当，后期查询会越来越慢，数据也会逐渐混乱。本文就以SQLite为例，从零设计一个完整的笔记标签系统数据库，覆盖建表、约束、索引和常见查询场景。
一、表结构设计：核心是三张表
一个规范的笔记标签系统最少需要三张表：notes表存储笔记内容，tags表存储标签本身，note_tags表作为中间表维护两者的关联关系。很多人图省事直接在笔记表里加一个tags字段存JSON或逗号分隔字符串，这种做法在小数据量下看不出问题，但一旦要做“查出所有带某标签的笔记”这种操作，就只能全表扫描并逐行解析字符串，性能和可维护性都会崩坏。
标签表建议只存标签的名字和元信息，主键用自增的INTEGER，同时给标签名加唯一约束，防止出现“前端”和“前端 ”（带空格）这种脏数据。下面是完整的建表语句：
-- 开启外键约束，SQLite默认是关闭的，必须手动开启
PRAGMA foreign_keys = ON;
-- 笔记表
CREATE TABLE notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
-- 标签表
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE COLLATE NOCASE,
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
-- 笔记-标签关联表
CREATE TABLE note_tags (
note_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (note_id, tag_id),
FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
这里有几个细节值得注意。COLLATE NOCASE让标签名的唯一性检查忽略大小写，“Work”和“work”会被视为同一个标签，符合大多数用户的使用习惯。关联表的复合主键(note_id, tag_id)天然防止了重复打标签，比如同一条笔记给同一个标签打两次，插入时会直接报唯一约束冲突。另外ON DELETE CASCADE配合外键约束，删除笔记时关联记录自动清理，不需要应用层写额外的清理逻辑。
二、高频查询场景的SQL写法
表建好之后，日常使用中最常见的几类查询包括：给笔记打标签、按标签筛选笔记、统计标签使用次数生成标签云。先看打标签，推荐的做法是先确保标签存在（不存在则创建），再插入关联记录。利用INSERT OR IGNORE可以简化这个流程：
-- 给笔记1打上“前端”标签，标签不存在时自动创建
INSERT OR IGNORE INTO tags (name) VALUES ('前端');
-- 通过子查询拿到标签id再插入关联
INSERT OR IGNORE INTO note_tags (note_id, tag_id)
SELECT 1, id FROM tags WHERE name = '前端';
-- 查询某条笔记的所有标签
SELECT t.name FROM tags t
JOIN note_tags nt ON nt.tag_id = t.id
WHERE nt.note_id = 1
ORDER BY t.name;
-- 查询同时带有“前端”和“SQLite”两个标签的笔记
SELECT n.id, n.title
FROM notes n
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
WHERE t.name IN ('前端', 'SQLite')
GROUP BY n.id
HAVING COUNT(DISTINCT t.name) = 2;
“同时带有多个标签”的查询是最容易写错的地方。直接用WHERE t.name = '前端' AND t.name = 'SQLite'是查不出任何结果的，因为一行数据的name不可能同时等于两个值。正确思路是先按笔记分组，再用HAVING COUNT校验命中的标签数量。如果是“带任意一个标签”的场景，去掉HAVING子句即可。
标签云统计也很简单，统计每个标签被使用的次数并按次数倒序排列：
-- 标签云：按使用次数排序，取前20个 SELECT t.id, t.name, COUNT(nt.note_id) AS usage_count FROM tags t LEFT JOIN note_tags nt ON nt.tag_id = t.id GROUP BY t.id ORDER BY usage_count DESC, t.name ASC LIMIT 20;
注意这里用LEFT JOIN而不是JOIN，这样使用次数为0的标签也能出现在结果里，方便做“清理无用标签”的功能。如果想删除所有没有被任何笔记引用的标签，可以用DELETE FROM tags WHERE id NOT IN (SELECT DISTINCT tag_id FROM note_tags)，不过执行前记得开启事务，误删了还能回滚。
三、索引优化与全文检索的组合
数据量上去之后，查询性能就要靠索引来保障。关联表的复合主键(note_id, tag_id)对“查某笔记的标签”很快，但反方向的“查某标签下的所有笔记”走不了这个索引，需要额外建一个反序索引：CREATE INDEX idx_note_tags_tag ON note_tags(tag_id, note_id)。这样双向查询都能命中索引，即使关联记录达到几十万条，按标签筛选也能保持在毫秒级。
标签名本身的查询已经有唯一索引兜底，但如果笔记数量多且经常按时间线浏览，笔记表的updated_at字段也值得加索引。可以用EXPLAIN QUERY PLAN来验证查询是否走了索引：
-- 验证查询计划 EXPLAIN QUERY PLAN SELECT n.id FROM notes n JOIN note_tags nt ON nt.note_id = n.id WHERE nt.tag_id = 5; -- 输出中出现 USING INDEX idx_note_tags_tag 说明索引生效了
如果还想支持按内容关键词搜索，SQLite自带的FTS5扩展是最佳选择，它比LIKE查询快几个数量级。可以为笔记表建一张FTS5虚拟表，并通过触发器保持同步：
-- 创建FTS5全文索引表
CREATE VIRTUAL TABLE notes_fts USING fts5(
title, content, content='notes', content_rowid='id'
);
-- 用现有数据填充
INSERT INTO notes_fts(rowid, title, content)
SELECT id, title, content FROM notes;
-- 触发器：笔记更新时自动同步索引
CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
INSERT INTO notes_fts(rowid, title, content)
VALUES (new.id, new.title, new.content);
END;
CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
INSERT INTO notes_fts(notes_fts, rowid, title, content)
VALUES ('delete', old.id, old.title, old.content);
END;
-- 全文搜索结合标签筛选
SELECT n.id, n.title
FROM notes n
JOIN notes_fts f ON f.rowid = n.id
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
WHERE notes_fts MATCH '数据库' AND t.name = '前端';
最后一个查询演示了FTS5全文搜索与标签过滤的组合，这也是笔记类应用搜索框背后最常见的查询形态：用户输入关键词的同时勾选了某个标签，两个条件通过JOIN自然叠加。需要注意的是，FTS5默认不支持中文分词，中文场景下可以在创建虚拟表时指定tokenize = 'unicode61'做基础的逐字切分，或者使用trigram分词器，前者适合前缀匹配，后者适合子串搜索，按实际需求选择即可。
整体来说，SQLite虽然轻量，但外键、触发器、窗口函数、FTS5这些能力一个不缺，做一个笔记标签系统的数据层完全够用。关键是把多对多关系的中间表设计规范，把索引建在正确的方向上，后期的功能扩展就会轻松很多。