日记应用通常会保存大量自由文本，但纯文本难以支撑情绪趋势分析。SQLite非常适合这类单人或多端同步场景：它不需要单独进程，所有表和索引都封装在单个文件中，同时支持触发器、全文搜索和结构化查询。把情感倾向、情绪标签作为一等字段写入日记表之后，回顾过去一周或一个月的心情变化就变成了标准SQL聚合统计。
一、设计适合情感分析的表结构
一个基础但实用的日记表至少包含正文、写作时间和情感分类字段。情感分类可以拆成两个维度：一个是连续的情感倾向分数，例如从-1到1之间取值，负数表示负面情绪，正数表示正面情绪；另一个是离散的情绪标签，比如焦虑、平静、兴奋、疲惫。连续分数便于绘制折线图，离散标签便于按类别过滤和统计。
下面SQL创建日记主表。注意sentiment_score字段使用CHECK约束限制范围，避免外部脚本写入异常值后污染后续统计。正文使用TEXT类型，暂时不建立普通索引，因为后续会引入FTS5全文检索。
CREATE TABLE diary_entries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entry_date TEXT NOT NULL,
content TEXT NOT NULL,
sentiment_score REAL NOT NULL DEFAULT 0 CHECK (sentiment_score BETWEEN -1 AND 1),
emotion_tag TEXT NOT NULL DEFAULT '平静',
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
CREATE INDEX idx_diary_date ON diary_entries(entry_date);
CREATE INDEX idx_diary_emotion ON diary_entries(emotion_tag);
上面把entry_date设计为TEXT类型，并约定使用YYYY-MM-DD格式。这样做的好处是SQLite的字符串比较和日期函数可以无缝配合，date()函数直接作用于文本日期，无需为时区转换额外操心。如果日记可能跨多天记录，也可以改成datetime文本格式。
情绪标签最好集中管理，避免同一含义出现多个写法。可以再建一张emotion_tags字典表，并用外键约束日记表。不过对于个人项目，为了减少联表查询，直接在日记表中保存标签字符串往往更简单。若需要统计标签分布，一条GROUP BY就能完成。
二、用触发器自动维护情绪统计表
每次写入或更新日记后，如果让应用层同时更新日统计表，很容易漏改或产生不一致。SQLite的触发器可以在数据库内部完成这件事。下面先创建按日期聚合的统计表，再创建两个触发器，分别处理插入和删除。
CREATE TABLE daily_emotion_stats (
stat_date TEXT PRIMARY KEY,
total_count INTEGER NOT NULL DEFAULT 0,
positive_count INTEGER NOT NULL DEFAULT 0,
negative_count INTEGER NOT NULL DEFAULT 0,
neutral_count INTEGER NOT NULL DEFAULT 0,
avg_score REAL NOT NULL DEFAULT 0
);
CREATE TRIGGER trg_diary_insert
AFTER INSERT ON diary_entries
BEGIN
INSERT INTO daily_emotion_stats (stat_date, total_count, positive_count, negative_count, neutral_count, avg_score)
VALUES (
NEW.entry_date,
1,
CASE WHEN NEW.sentiment_score > 0.1 THEN 1 ELSE 0 END,
CASE WHEN NEW.sentiment_score < -0.1 THEN 1 ELSE 0 END,
CASE WHEN NEW.sentiment_score BETWEEN -0.1 AND 0.1 THEN 1 ELSE 0 END,
NEW.sentiment_score
)
ON CONFLICT(stat_date) DO UPDATE SET
total_count = total_count + 1,
positive_count = positive_count + CASE WHEN NEW.sentiment_score > 0.1 THEN 1 ELSE 0 END,
negative_count = negative_count + CASE WHEN NEW.sentiment_score < -0.1 THEN 1 ELSE 0 END,
neutral_count = neutral_count + CASE WHEN NEW.sentiment_score BETWEEN -0.1 AND 0.1 THEN 1 ELSE 0 END,
avg_score = (avg_score * (total_count - 1) + NEW.sentiment_score) / total_count;
END;
触发器里使用ON CONFLICT(stat_date) DO UPDATE实现了插入或累加的逻辑。这样应用层只需要向diary_entries表插入一条记录，统计表会自动更新。删除日记时也要同步扣减，否则统计会虚高。删除触发器逻辑类似，但要注意避免把统计行删成负数，删除后若统计行总量归零，可以直接删除该行。
CREATE TRIGGER trg_diary_delete
AFTER DELETE ON diary_entries
BEGIN
UPDATE daily_emotion_stats
SET total_count = total_count - 1,
positive_count = positive_count - CASE WHEN OLD.sentiment_score > 0.1 THEN 1 ELSE 0 END,
negative_count = negative_count - CASE WHEN OLD.sentiment_score < -0.1 THEN 1 ELSE 0 END,
neutral_count = neutral_count - CASE WHEN OLD.sentiment_score BETWEEN -0.1 AND 0.1 THEN 1 ELSE 0 END,
avg_score = CASE WHEN total_count - 1 = 0 THEN 0 ELSE (avg_score * total_count - OLD.sentiment_score) / (total_count - 1) END
WHERE stat_date = OLD.entry_date;
DELETE FROM daily_emotion_stats WHERE stat_date = OLD.entry_date AND total_count = 0;
END;
当触发器中涉及OLD和NEW关键字时，要注意它们只在对应的触发器类型中可用。AFTER INSERT中NEW表示新插入的行，AFTER DELETE中OLD表示被删除的行。更新操作需要单独创建AFTER UPDATE触发器，同时处理旧值和新值，实现先扣除旧统计再累加新统计。
三、使用FTS5做全文检索与情感标签联动
日记正文很长，普通LIKE查询虽然简单，但在数据量大时性能较差，而且无法处理分词、近义词和相关性排序。SQLite自带的FTS5扩展能够建立倒排索引，适合输入关键词快速找到相关日记。创建虚拟表时，可以只索引正文和情绪标签，也可以把日期一起放进索引。
CREATE VIRTUAL TABLE diary_fts USING fts5(
content,
emotion_tag,
content='diary_entries',
content_rowid='id'
);
CREATE TRIGGER trg_diary_ai AFTER INSERT ON diary_entries BEGIN
INSERT INTO diary_fts(rowid, content, emotion_tag) VALUES (NEW.id, NEW.content, NEW.emotion_tag);
END;
CREATE TRIGGER trg_diary_ad AFTER DELETE ON diary_entries BEGIN
INSERT INTO diary_fts(diary_fts, rowid, content, emotion_tag) VALUES ('delete', OLD.id, OLD.content, OLD.emotion_tag);
END;
CREATE TRIGGER trg_diary_au AFTER UPDATE ON diary_entries BEGIN
INSERT INTO diary_fts(diary_fts, rowid, content, emotion_tag) VALUES ('delete', OLD.id, OLD.content, OLD.emotion_tag);
INSERT INTO diary_fts(rowid, content, emotion_tag) VALUES (NEW.id, NEW.content, NEW.emotion_tag);
END;
使用外部内容表模式时，需要手动同步虚拟表。上面的三个触发器分别处理插入、删除和更新，保持diary_fts与主表一致。这样查询时可以直接使用MATCH语法，例如SELECT * FROM diary_fts WHERE diary_fts MATCH '焦虑 AND 工作'。FTS5默认使用Unicode61分词器，对中文的支持有限，如果需要更准确的中文分词，可以在创建虚拟表时指定tokenize='unicode61'或引入第三方分词器。对于个人项目，英文和按字匹配已经能满足多数需求。
情感标签和全文搜索可以组合使用。比如想找出最近一个月里提到工作并且情绪标签为焦虑的日记，可以先在虚拟表中用MATCH筛选候选ID，再联回主表按日期和标签过滤。这种联表查询不会丢失FTS5的相关性排序，只需要在最终ORDER BY中使用子查询返回的rank即可。
四、用Python批量写入与情感打分
实际项目中不可能手工插入大量日记和情感数据，通常会用脚本读取原始文本、调用情感分析模型，再把结果批量写入SQLite。下面的Python示例使用sqlite3标准库和一个简单的情感词典打分函数，演示如何将纯文本转换为结构化记录。
import sqlite3
import re
POSITIVE_WORDS = {'开心', '顺利', '期待', '满足', '放松'}
NEGATIVE_WORDS = {'焦虑', '疲惫', '烦躁', '孤独', '压力'}
def score_sentiment(text):
pos = len(re.findall('|'.join(POSITIVE_WORDS), text))
neg = len(re.findall('|'.join(NEGATIVE_WORDS), text))
total = pos - neg
if total > 1:
return total / (pos + neg + 1), '正面'
elif total < -1:
return total / (pos + neg + 1), '负面'
else:
return total / (pos + neg + 1), '中性'
def insert_diary(db_path, date, content):
conn = sqlite3.connect(db_path)
score, tag = score_sentiment(content)
conn.execute(
'INSERT INTO diary_entries (entry_date, content, sentiment_score, emotion_tag) VALUES (?, ?, ?, ?)',
(date, content, max(-1, min(1, score)), tag)
)
conn.commit()
conn.close()
if __name__ == '__main__':
insert_diary('diary.db', '2026-03-01', '今天工作顺利，心情放松，对下周的项目充满期待。')
示例中score_sentiment函数通过统计正面词和负面词出现次数来计算情感分数，实际项目可以替换为BERT、TextBlob等更精确的模型。为了让分数始终落在CHECK约束范围内，写入前使用max(-1, min(1, score))做了截断。批量写入时建议开启事务，把多条INSERT放在同一个事务里提交，SQLite的写入速度会提升一个数量级。
如果日记数据已经存在于CSV或旧数据库，可以先用pandas读取，再通过executemany批量执行。注意executemany依然会触发每行上的触发器，所以统计表和FTS虚拟表也会自动同步，不需要额外代码。这正体现了把统计逻辑交给数据库的好处。
最后一层是备份与迁移。SQLite数据库就是一个文件，备份时停止写入后直接复制文件即可，或者使用VACUUM INTO生成一致性快照。对于个人日记这类低频写入场景，每天定时把diary.db复制到云盘或NAS，就能同时保存原始文本和情感分析结果。