本地优先笔记应用的SQLite存储设计：数据模型、附件去重与备份

SQLite本地优先笔记应用

于 2026-10-02 09:13:28 修改

·本内容遵循CC 4.0 BY-SA版权协议

先说一个结论：本地优先（Local-first）的笔记应用，存储层选 SQLite 基本是当下最不后悔的决定。它不像 JSON 文件那样随便写写就乱，也没有客户端数据库那么重的部署负担。但真正把 SQLite 用好的地方，恰恰不在"能存数据"，而在数据模型怎么设计、多空间怎么隔离、附件怎么去重、备份怎么做才不丢数据。这篇就拆一拆我在这类应用中实际踩过的存储设计坑，以及最终沉淀下来的一套可以照抄的工程方案。

1. 为什么是SQLite：本地优先应用的存储基座选型逻辑

1.1 本地优先应用对存储层的要求

本地优先应用和传统 C/S 应用的最大区别是：数据库跑在用户自己的设备上，而不是某个你完全可控的服务器里。这意味着存储层首先要满足几件事。

第一，进程内嵌，零运维。用户装了一个笔记软件，不应该还要去启动一个数据库服务。SQLite 是一个库，不是服务器，应用启动时调 sqlite3_open 就直接打开文件，不需要监听端口，不需要账号密码，不需要常驻进程。这一点在桌面端、移动端都极其重要。

第二，单文件可备份。本地优先的核心理念之一，就是用户的所有数据都在本地，随时可以复制一份带走。SQLite 的整个数据库就是一个 .db 文件，配合 WAL 模式之后，备份方式也比较清晰。相比之下，如果你用 LevelDB、RocksDB 这类嵌入式 KV，数据是分片在多个文件里的，备份要小心翼翼地把整个目录打快照，否则很容易缺文件。

第三，事务和并发。笔记应用的操作很碎，写一个字、插一张图、改一个标签，都是高频小事务。如果没有事务，任何一次崩溃都可能留下半截数据。SQLite 在单机场景下提供了完整的 ACID 事务，配合 WAL 模式，读写不互相阻塞。

第四，可查询。笔记类数据迟早要支持全文搜索、标签过滤、按日期聚合、反向链接统计。如果用纯文件方案，这些功能全要从零写。SQLite 自带 SQL，还有 FTS5 全文检索扩展，一条 SQL 就能把"搜到笔记"这件事搞定。

1.2 为什么默认选项几乎总是SQLite

我做选型时会先排除几个方向。

用 JSON 文件直接存笔记？开发初期很爽，但后面会面临地狱级的迁移问题。一次改动数据结构，就得写一段迁移代码，把所有用户的 JSON 文件重新洗一遍；笔记一多，文件加载和保存的时间会指数级增长；多窗口编辑时还得自己处理文件锁和合并冲突。

用 IndexedDB？它只在浏览器环境里比较自然。Electron 应用虽然能用，但数据被埋在 Chromium 的 LevelDB 目录下，用户没法直接拿到一个干净的数据文件。导出备份时，你得走一遍异步 API，把整个库导出来，体验和 SQLite 的单文件直接复制差远了。

用 Realm / Couchbase Lite？这些都是正经的移动端嵌入式数据库，能力很强，但有一个共性弊端：太重。对于一个笔记应用而言，SQLite 的轻量程度是它们比不了的，而且 SQLite 是默认随系统或者随语言运行时提供的，LibSQL 版本也兼容。你不需要引一个几千行的 SDK，只需要 sqlite3 这个库就够了。

我用一个表格总结一下我当时对比的结论：

方案	事务	备份	全文检索	迁移难度	适用场景
JSON 文件	无	容易	自研	高	几十条配置级数据
IndexedDB	有	困难	需扩展	中	纯 Web 应用
Realm	有	中	有	中	移动端复杂对象
SQLite	有	容易	FTS5 内置	低	本地数据密集型应用

最终选 SQLite，不是因为它功能最强，而是它在"轻量、可靠、可迁移、可查询"这四个维度上的综合分最高。

1.3 单DB文件的边界：什么时候应该拆库

SQLite 单文件确实好用，但也不是无限扩张。我见过有人把几十 GB 的多媒体附件全部塞进库里的做法，这基本是灾难。因为 SQLite 文件一旦巨大，VACUUM 时的临时空间会翻倍，热备份也要拷贝巨量数据，同步冲突时的处理粒度也会变大。

所以我的原则是：结构化数据（笔记正文、标签、元数据、索引）放进 SQLite；二进制附件放进内容寻址目录，只在库里存 hash 和路径。这个原则在后面的附件设计里会细讲。单 DB 文件的边界，应该由"文件体积膨胀"和"备份窗口"来定义，而不是一味追求"所有东西都在一个库里"。

2. 笔记应用的数据模型：核心表结构、索引与FTS全文检索

2.1 从用户操作倒推实体关系

先别急着建表，把用户的实际操作列出来，让表结构服务于操作，而不是为了建模而建模。一个常规笔记应用里，用户会做这些事：

创建/编辑/删除笔记，笔记有标题、正文、创建时间、更新时间。
笔记可以放在不同空间（Space，类似工作区/知识库）里。
笔记里可以插入图片、PDF、音频等附件。
笔记可以打标签，一个笔记多个标签。
想全局搜索标题和正文，希望在输入关键词时快速列出结果。
删除笔记时附件不要立刻消失，但又不能一直躺在磁盘上。

基于这些操作，核心表可以拆成五张：spaces、notes、blobs、attachments、tags，再加一个用于全文检索的 FTS 虚拟表。这个数量不算多，但已经能覆盖绝大多数笔记应用的场景。

2.2 建表SQL与约束解读

下面是我在一版实际项目里用的简化 DDL，去掉了审计字段和软删除，保留核心逻辑。

SQL

PRAGMA foreign_keys = ON;

CREATE TABLE spaces (

id TEXT PRIMARY KEY,

name TEXT NOT NULL,

parent_id TEXT REFERENCES spaces(id) ON DELETE CASCADE,

created_at INTEGER NOT NULL,

updated_at INTEGER NOT NULL

);

CREATE TABLE notes (

id TEXT PRIMARY KEY,

space_id TEXT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,

title TEXT NOT NULL DEFAULT '',

body TEXT NOT NULL DEFAULT '',

created_at INTEGER NOT NULL,

updated_at INTEGER NOT NULL

);

CREATE INDEX idx_notes_space_time ON notes(space_id, updated_at DESC);

CREATE TABLE blobs (

sha256 TEXT PRIMARY KEY,

media_type TEXT NOT NULL,

size INTEGER NOT NULL,

ref_count INTEGER NOT NULL DEFAULT 0,

created_at INTEGER NOT NULL

);

CREATE TABLE attachments (

id TEXT PRIMARY KEY,

note_id TEXT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,

blob_hash TEXT NOT NULL REFERENCES blobs(sha256),

file_name TEXT NOT NULL,

position INTEGER NOT NULL DEFAULT 0

);

CREATE INDEX idx_attachments_note ON attachments(note_id);

CREATE INDEX idx_attachments_blob ON attachments(blob_hash);

CREATE TABLE tags (

id TEXT PRIMARY KEY,

name TEXT NOT NULL UNIQUE COLLATE NOCASE

);

CREATE TABLE note_tags (

note_id TEXT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,

tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,

PRIMARY KEY (note_id, tag_id)

);

几个容易被忽略的设计点。

主键全部用 TEXT 而不是自增 INTEGER。对本地优先应用来说，ID 必须在离线状态下就能生成，而且之后多端同步时不能冲突。自增整数在多设备场景会产生 ID 冲突，UUID 或者 ULID 更稳妥。我用的是 ULID，因为它在时间上排序友好，索引局部性比随机 UUID 好。

时间戳统一用 INTEGER 存 Unix 毫秒，而不是 ISO 字符串。字符串比较在 SQLite 里其实也能用，但毫秒整数更省空间、排序更快，也方便前端直接 new Date(ms)。注意不要混用秒和毫秒，这类 bug 非常隐蔽。

外键约束务必打开。SQLite 默认 foreign_keys 是关闭的，除非每条连接都执行 PRAGMA foreign_keys = ON。不打开的话，上面这些 ON DELETE CASCADE 全部不会生效。用户删除一个空间，下面的笔记不会自动删，附件引用也会变成悬空状态。

2.3 全文检索与标签索引的坑

笔记正文是长文本，用 LIKE '%关键词%' 做搜索，数据量一大就慢得无法接受。SQLite 官方提供的 FTS5 扩展是正解。

SQL

CREATE VIRTUAL TABLE notes_fts USING fts5(

title,

body,

content='notes',

content_rowid='rowid',

tokenize='unicode61'

);

FTS5 有两种常见使用方式。一种是直接向虚拟表插入数据，和普通表一样维护，简单但数据双写，且容易和主表不一致。另一种是外部内容表（external content），通过 content='notes' 关联普通表，再在触发器里同步更新。

我更推荐外部内容表。因为笔记正文可能很大，双写一份数据会浪费磁盘，而且外部内容表可以利用 SQLite 的 delete 和 rebuild 命令做重建。注意，上面这个建表方式依赖 notes 表有 rowid，所以我实际上会在 notes 表里额外建一个隐藏 rowid 或直接改用 INTEGER PRIMARY KEY 来作为内部 ID，业务 ID 用 TEXT 字段，两套 ID 分开。

同步 FTS 索引需要触发器：

SQL

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN

INSERT INTO notes_fts(rowid, title, body) VALUES (new.rowid, new.title, new.body);

END;

CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN

INSERT INTO notes_fts(notes_fts, rowid, title, body) VALUES ('delete', old.rowid, old.title, old.body);

END;

CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN

INSERT INTO notes_fts(notes_fts, rowid, title, body) VALUES ('delete', old.rowid, old.title, old.body);

INSERT INTO notes_fts(rowid, title, body) VALUES (new.rowid, new.title, new.body);

END;

搜索时用 MATCH 语法：

SQL

SELECT n.id, n.title, snippet(notes_fts, 1, '[', ']', '…', 12) AS snippet

FROM notes_fts

JOIN notes n ON n.rowid = notes_fts.rowid

WHERE notes_fts MATCH :query

ORDER BY rank

LIMIT 50;

标签这块我踩过的坑是 COLLATE NOCASE 和中文无关，但对英文标签很有必要。Tags 表通过 UNIQUE COLLATE NOCASE 保证 Apple 和 apple 不会同时出现。查询某个标签下的笔记，走 note_tags 的联合主键即可。如果笔记量极大，还可以在 note_tags 上增加 tag_id 前缀索引。

3. 空间隔离的工程方案：单库共享与多库强隔离怎么选

3.1 空间（工作区）这个概念的存储含义

很多笔记软件会提供"空间"这个概念，比如工作区、知识库、Notebook。用户用空间的目的是把不同项目、不同用途的笔记分开，避免混在一起。但"分开"在存储层到底意味着什么，工程上的选择比产品概念复杂得多。

我归纳了一下，实际只有三种做法：

单 SQLite 文件 + 行级空间 ID；
一个空间一个独立 SQLite 文件；
单文件 + 每个空间单独 schema（SQLite 里可以用 ATTACH 实现类似效果）。

这三种各有清晰的适用场景，没有绝对最优。

3.2 单库+space_id：共享与检索的利器

我默认推荐的做法，就是前面 DDL 里展示的：notes 表带 space_id，spaces 表维护空间层级。所有查询都先按空间过滤，再按业务条件过滤。

这种方案的好处非常直接。

一是跨空间句柄更容易。你可以一条 SQL 搜整个库，也可以只搜某个空间。如果拆成多个数据库文件，"全部空间联合搜索"就需要遍历所有文件，再在内存里合并结果，排序分页都会变麻烦。

二是附件去重可以全局计算。后面会讲到内容寻址附件，同一张图片在 A 空间和 B 空间各引用一次，只需要存一份文件。如果每个空间一个独立库，附件去重就只能限定在空间内部，跨空间重复图片会存多份。

三是迁移和备份简单。一个 .db 文件就是整个工作区集合。用户说"备份我的所有数据"，你只需要复制一个文件；用户说"只导出某个空间"，你可以在应用层先查出该空间的笔记，再写入一个新的 SQLite 文件。

行级隔离要注意的地方是：所有涉及数据变动的 SQL 都要带 space_id 条件，不能只按笔记 ID 更新。因为笔记 ID 是全局唯一的，不带空间条件也不会更新错，但会白白丢掉一次安全过滤的机会。为了在数据库层强制隔离，我建议为关键表加复合主键或复合外键，比如 notes 表的主键可以是 (space_id, id)，这样跨空间操作时在数据库层就会被约束挡住。

3.3 多库文件方案：物理隔离的代价

多库方案的诱惑来自"彻底隔离"。每个空间一个库文件，空间删除就是把整个文件删掉，不需要逐行清理；加密时也可以给不同空间设置不同口令；一个空间的数据损坏也不会波及其他空间。

但代价同样明显。

跨空间搜索要从多个库里分别查询，再在应用层合并排序。跨空间引用几乎不可能做：比如 A 空间引用 B 空间的某条笔记，需要维护跨库 ID 映射，脆弱且复杂。附件去重只在本空间有效。多库文件会让"整库备份"这个本地优先杀手锏失去意义，因为用户必须清楚哪些文件属于哪些空间，备份时还要自己挑选一堆文件。

还有一个小坑：SQLite 的 ATTACH DATABASE 可以让你在同一个连接里挂载多个库文件，于是你可以模拟"一个连接查所有空间"。但 ATTACH 不能跨连接，你必须非常小心地管理连接生命周期。而且 ATTACH 后的多库事务，并不像单库事务那样具备完整的原子性保证，崩溃恢复时两个库文件可能不在同一个时间点上。

3.4 我的折中：单库默认+按需导出独立文件

经过实测，我最终采用方案是：默认单库 + space_id 行级隔离，同时提供"导出空间为独立 SQLite 文件"的功能。

导出不是简单地把空间里的笔记查询出来再插入到新库。要保证附件有效，新库里得重建一份 blobs 表，只包含被导出笔记引用的 hash。用一条 SQL 就能查出来：

SQL

SELECT DISTINCT b.sha256, b.media_type, b.size

FROM attachments a

JOIN notes n ON n.id = a.note_id

JOIN blobs b ON b.sha256 = a.blob_hash

WHERE n.space_id = :spaceId;

然后在导出库中重建 notes、attachments、tags、blobs，把附件目录里对应的二进制文件复制到目标附件目录。这个导出文件可以单独分享或归档，不影响原库。

反过来，导入时把外部文件的附件逐一计算 SHA-256，如果原库里已经有相同 hash，直接增加引用计数，不重新复制文件。这个去重逻辑在导入场景下非常省事。

4. 附件用内容寻址：去重、校验、引用计数的一次到位

4.1 内容寻址的直觉：粘贴同一张图两次会发生什么？

笔记应用里最常见的附件操作，就是把一张图片粘贴进多篇笔记。如果用文件名作为附件标识，你会在 attachments 目录里得到两个完全相同的文件，比如 20240101_xyz.png 和 20240101_abc.png，内容一模一样，磁盘上却存了两份。

内容寻址附件（Content-Addressed Storage）的解决办法是：给文件内容算一个唯一哈希，用哈希作为文件标识。哈希一样，就说明内容一样，在存储层只保留一份文件。这比让用户去手动去重靠谱得多。

我做内容寻址时用的算法是 SHA-256。虽然 SHA-256 理论上存在碰撞风险，但对本地笔记应用来说，碰撞概率低到可以忽略，而且 SHA-256 有现成的标准库实现，性能也够。

4.2 文件落盘、原子写入与孤儿回收

附件文件的落盘路径是有讲究的。我建议用 objects/ab/cdef/abcdef1234... 这种两级分片方式，而不是把所有文件直接放到一个扁平目录。因为当附件数量上万时，扁平目录的 inode 和目录项查询效率都会下降，而且分片目录也方便人眼排查、按前缀归档。

写入一个附件时的流程是：

先把二进制流写入临时文件 temp/upload-{uuid}；
计算 SHA-256 和字节数；
将临时文件移动（rename）到 objects/{前两位}/{再两位}/{完整hash}；
更新数据库 blobs 表的 ref_count；
如果目标文件已经存在，直接删除临时文件。

移动操作用 rename，在同一个文件系统内是原子性的，应用崩溃时不会留下半个文件。这里有个细节：临时目录和对象目录最好在同一个磁盘分区，否则 rename 跨设备会退化为 copy+delete，就失去原子性了。

在计算阶段就写临时文件而不是直接写最终路径，是因为哈希还没算完，你不知道最终文件名是什么。而且如果网络下载或粘贴过程中断，临时文件可以随时清掉，不会污染正式对象库。

4.3 附件引用计数与清理策略

有了内容寻址，数据库里的 blobs 表必须维护引用计数。每次往某篇笔记插入附件，就对对应 blob 的 ref_count 加一；每次把附件从笔记中移除，就减一；当 ref_count 减到 0 时，这个 blob 才变成可删除的孤儿。

我见过不少实现图省事，不在数据库里记录引用计数，而是定期扫附件目录，对比所有笔记里的 attachment 引用，然后把没有引用的文件删掉。这种做法在数据量小的时候没问题，但存在两个隐患：一是扫描整个附件目录的 IO 开销不可忽略；二是如果某篇笔记此时正在编辑、还没保存引用记录，就会被误判成孤儿，导致附件被误删。

所以我的经验是：引用计数必须作为事务的一部分同步更新。删除附件这一步，在同一个 SQLite 事务里完成 DELETE FROM attachments WHERE id = :attachmentId 和 UPDATE blobs SET ref_count = ref_count - 1 WHERE sha256 = :hash。事务提交后，再把 ref_count > 0 的文件保留，把 ref_count = 0 的文件移入回收站目录，延迟 7 天物理删除。这样即便用户后悔想找回附件，也有反悔的机会。

如果确实需要做孤儿清理，可以这样查：

SQL

SELECT b.sha256

FROM blobs b

LEFT JOIN attachments a ON a.blob_hash = b.sha256

WHERE b.ref_count = 0

AND a.id IS NULL

AND b.created_at < :givenTime;

清理脚本要限制一次只处理一批，比如 100 个，避免长事务锁住 UI 写入。

5. WAL、备份与编码细节：本地笔记应用的数据安全三件套

5.1 WAL模式和 synchronous 怎么配

SQLite 默认的 journal 模式是 DELETE 模式，每次写事务结束都要把修改写回主库文件，写放大明显，而且在多线程监听文件变化时容易触发误报。笔记应用建议直接开启 WAL 模式。

SQL

PRAGMA journal_mode = WAL;

PRAGMA synchronous = NORMAL;

PRAGMA wal_autocheckpoint = 1000;

WAL 模式下，写操作先追加到 -wal 文件里，读操作直接读主库文件加 -wal 的合并视图，读写之间不阻塞，这在笔记应用"后台同步 + 前台编辑 + 全文搜索同时进行"的场景下非常有用。

synchronous = NORMAL 在 WAL 模式下已经足够安全。SQLite 官方文档也说明，WAL 模式下 NORMAL 和 FULL 的区别主要在于系统崩溃可能导致数据库损坏的概率，而应用崩溃不会造成损坏。NORMAL 的性能明显更好，所以我默认用 NORMAL。

但要注意一个坑：WAL 模式会额外产生 -wal 和 -shm 两个文件。你在做备份时不能只复制主 .db 文件，否则会丢掉 -wal 里还没合并进去的最新事务。最好的做法是用 SQLite 的 Online Backup API，或者先执行 PRAGMA wal_checkpoint(FULL)，再复制文件。

5.2 备份：能在应用运行时直接拷贝db文件吗？

直接拷贝正在运行中的 SQLite 数据库文件，理论上可能得到损坏或不一致的备份，即使 WAL 模式也不能保证完全安全。正确做法有三种。

第一种是用 VACUUM INTO。这是新版 SQLite 提供的命令，直接把当前数据库的一致性快照导出到一个新文件：

SQL

VACUUM INTO '/path/to/backup.db';

它不阻塞并发读写，而且只生成一个文件，不管 WAL 再怎么膨胀，VACUUM INTO 出来的都是合并后的完整状态。对于笔记应用，定时备份用这一条命令最简单。

第二种是使用 SQLite Online Backup API，在语言层调用，比如 Python 的 sqlite3.Connection.backup() 或 Node 的 db.backup()。它会在备份过程中自动处理 WAL 和事务，是程序化备份最稳的方案。

第三种是 PRAGMA wal_checkpoint(FULL) 后再复制冻结文件。这样得到的主文件是完整的，但实现时必须保证在 checkpoint 和复制文件期间没有新的写事务进来，否则仍然可能不一致。我不推荐自己处理这个，直接用 VACUUM INTO 更好。

我在实际备份方案里会做全量快照和增量导出两层。全量快照每天一次，VACUUM INTO 到备份目录；增量导出每次完成一次较大的编辑操作后，把受影响笔记输出为 Markdown 加附件，存到用户选定的目录。这样即使主库文件损坏，用户丢失的也只是最近几小时的结构化数据，笔记正文大概率还能通过导出的文件找回。

5.3 中文乱码为什么还会出现在SQLite里

SQLite 本身是 Unicode 安全的，数据存储格式不挑编码。乱码问题几乎都出在读写两侧的编码约定不统一。

比如在 Windows 上，某些应用写入时用的连接没有设置 PRAGMA encoding = "UTF-8"，或者从 CSV 导入时源文件是 GBK 编码，在应用层没有转成 UTF-8 就直接写入，最终读出来就是乱码。再比如使用老旧的 SQLite 驱动，默认把字符串按本地 ANSI 编码处理，中文就变成 ??? 了。

我的经验是在每个连接建立后，都显式执行这两条：

SQL

PRAGMA encoding = "UTF-8";

PRAGMA foreign_keys = ON;

encoding 只能在数据库创建时设置，对已存在的库执行会报错。所以新库初始化时就要定好 UTF-8，旧库如果已经出现乱码，只能导出数据再重建库，没有快捷修复命令。还有一个更隐蔽的问题：SQLite 的字符串比较默认是二进制比较，中文排序不会按拼音或笔画来。如果需要按中文排序，可以引入 ICU 扩展或用应用层排序，但一般笔记列表我更喜欢按 updated_at 排序，不依赖中文排序规则。

查看和调试 SQLite 文件时，Db Browser for SQLite 和 DBeaver 都能直观看表结构、执行 SQL、查看 FTS 索引。还有一个命令行神器是 sqlite3 自带工具，特别适合写脚本批量验证数据一致性，比如我经常这样检查孤儿附件：

BASH

sqlite3 notes.db "SELECT COUNT(*) FROM blobs WHERE ref_count = 0;"

用这些工具做定期巡检，基本能覆盖日常问题。

如果未来本地笔记应用要增加多端同步，内容寻址方案还能带来额外收益。同步引擎判断"这个附件是否已经存在于远端"，只需要比较 SHA-256，而不需要传输整个文件。这一点在移动端弱网环境下特别友好。只要本地存储底子打得稳，上层功能加得再多，都不会突然返工重做存储层。

我个人做完这套方案后的体会是：存储设计最怕临时起意，刚开始觉得"先能用就行"，等到附件多了、空间多了、搜索慢了，再回头改存储层，成本比重新写一个应用还高。SQLite 只是一个工具，真正决定数据质量的，是表结构怎么设计、隔离粒度怎么选、附件怎么寻址、备份怎么做。这四个问题想清楚了，本地优先应用的存储底子就稳了。