日记应用和普通笔记工具有明显区别：日记按天产生，写入不算频繁，但用户会频繁按日期回看、按心情或标签过滤，并且希望数据完全归自己掌握。这些需求落到存储层，意味着需要一个无需网络、支持复杂查询、事务完整且方便备份的单文件数据库。SQLite正好覆盖这些点。它把表结构、索引、日志都写进一个.db文件，应用程序通过本地API访问，不需要额外数据库进程。
一、SQLite相比纯文本和JSON文件更适合日记数据
很多日记应用最早会用纯文本或JSON文件保存内容，例如每天一个.md文件，或者把全部日记加载到一个data.json里。这种方式实现简单，但数据量超过几百篇后问题会集中出现：按标签查询需要遍历全部文件，正文全文搜索只能靠读进内存再匹配，修改一篇日记可能触发整个JSON文件重写。更麻烦的是，一旦写入中途应用崩溃，JSON文件可能损坏，因为普通文件写入不具备事务回滚能力。
SQLite的ACID特性可以避免上述问题。每次插入、更新都包裹在事务里，要么全部生效，要么全部回滚。数据库内部使用页级锁和WAL日志，即使应用闪退，已提交的日记也不会丢失。表结构上，日记可以作为一行记录保存，标题、正文、心情、天气、创建时间和修改时间都是独立列，SQLite的B树索引可以基于创建时间、心情等字段快速过滤，而不像文件系统只能按文件名或目录粗略分类。下面是一张基础表：
CREATE TABLE diary_entries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
body TEXT NOT NULL,
mood TEXT,
weather TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX idx_diary_created ON diary_entries(created_at DESC);
CREATE INDEX idx_diary_mood ON diary_entries(mood);
如果使用JSON文件，想查询某个月所有心情为开心的日记，需要加载整个文件并逐条判断；在SQLite中只需一条SELECT语句，配合created_at范围条件就能快速返回结果。这种查询能力随着日记数量增长优势会更明显。
二、日记表结构设计与中文全文检索
基础表适合存单篇日记正文，但真实产品通常还需要标签、天气、位置、附件等多个维度。把这些字段全部塞进一张宽表虽然能跑，但维护起来不够灵活。推荐把标签做成多对多关系，附件单独建表，日记主体保持稳定。例如：
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE diary_tags (
diary_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (diary_id, tag_id),
FOREIGN KEY (diary_id) REFERENCES diary_entries(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
这样既能避免在日记表里存逗号分隔的标签字符串，也能通过关联表快速找出同一标签下的所有日记。天气、位置这类变化较少的字段可以继续留在主表，用普通索引支持筛选。附件如果直接存数据库BLOB，备份会变大，一般建议把图片、音频放在应用沙盒目录，数据库只保存相对路径和文件类型，方便后续做懒加载或同步到对象存储。
全文检索方面，SQLite官方提供FTS5虚拟表，可以给正文建立倒排索引。对于英文文本，默认分词器足够；但中文不像英文天然有空格分词，直接使用unicode61会把句子拆成单字，搜索词组效果不好。较新的SQLite支持trigram分词器，适合中文子串匹配，建表方式如下：
CREATE VIRTUAL TABLE diary_fts USING fts5(
title,
body,
tokenize = 'trigram'
);
INSERT INTO diary_fts(rowid, title, body)
SELECT id, title, body FROM diary_entries;
SELECT rowid FROM diary_fts WHERE diary_fts MATCH '湖边散步';
使用FTS5后，搜索某个关键词不需要扫描整张diary_entries表，而是直接从倒排索引里取匹配行。需要注意FTS5虚拟表和原表的数据同步：可以在应用层写入日记后同时更新FTS表，也可以通过触发器自动维护。如果日记正文较长，建议只在FTS里索引标题和正文前几千字，降低索引体积。
三、写入性能与备份恢复策略
日记应用写入压力通常不高，但用户可能一次导入大量历史数据，或者在大文件里快速连续输入。默认的SQLite回滚日志模式下，写操作会锁定数据库，读操作在写入时可能等待。建议在初始化时开启WAL模式：
PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;
WAL模式下写操作先写入-wal文件，不影响读事务读取旧快照，读性能更稳定。对于移动端，还可以设置PRAGMA cache_size和PRAGMA temp_store，减少磁盘IO。批量导入日记时，把所有插入放在一个事务里比每条自动提交快几个数量级，因为SQLite不用每行都同步日志。
备份不能直接复制正在写入的数据库文件，容易得到一个不一致的副本。SQLite提供在线备份接口和VACUUM INTO语句，可以在应用运行时生成一致性快照。Windows路径示例：
VACUUM INTO 'C:\backup\diary_2024.db';
如果应用允许用户手动导出备份，可以执行这个语句，再把生成的文件复制到用户选择的目录。自动备份则推荐使用SQLite官方sqlite3_backup API，它能边写边备份且不会阻塞主线程太久。备份文件可以进一步做压缩和加密。
四、隐私加密与后续同步的底层设计
日记属于高度隐私数据，本地数据库如果不加密，设备丢失后内容可能被轻易读取。SQLite本身不内置加密，但可以使用SQLCipher扩展。SQLCipher在SQLite基础上增加页级加密，打开数据库前需要提供密钥。接入后通常先执行PRAGMA key = '你的密钥'，再执行建表查询。该密钥不要硬编码在源码里，可以通过系统Keychain或Keystore保存，或者由用户主密码派生。
如果需要多端同步，SQLite只是本地缓存，不建议直接同步.db文件。更好的做法是让数据库记录足够多的元数据，配合服务端增量同步。例如给每条日记增加deleted标记、updated_at时间戳和sync_state字段，应用每次修改后更新这些字段，同步时只上传变更记录。表结构可以这样演进：
ALTER TABLE diary_entries ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0; ALTER TABLE diary_entries ADD COLUMN sync_state TEXT NOT NULL DEFAULT 'pending';
多端同步时，服务端以updated_at和id作为冲突处理依据。如果两端同时修改同一篇日记，可以选择最后写入覆盖，或保留冲突副本让用户手动合并。这个策略与存储层关系密切，因为SQLite的时间戳字段和本地事务能保证每次修改都有清晰版本，不至于出现文件同步工具常见的重复文件或丢失内容。
数据库结构升级也要预留机制。SQLite提供PRAGMA user_version，应用启动时读取该值，根据版本号依次执行迁移脚本。比如从无FTS升级到有FTS时，先创建虚拟表，再回填数据，最后把版本号加一。这样后续迭代增加标签、同步字段或加密能力时，不会破坏用户已有的日记数据。