SQLite 教学工作区 · 课程 0002 · 约 20 分钟 · 前置:0001 · 2026-09-24

从别人的表读出设计,再写一张自己的表

为什么是这一步

使命里要求「建库建表」和「讲清什么时候该用」。 0001 你只是在看别人的库;从这一节开始,你动手设计。 先读别人的设计,是因为一张跑了很久、还在长数据的表,它的每一列几乎都是被真实问题逼出来的 —— 比教科书上的示例诚实得多。

第一步:带着四个问题重读 schema

打开副本(和 0001 一样,不碰原库):

sqlite3 -readonly /tmp/sqlite-练习/ctx.db ".schema memories"

然后按下面四类去认。左边是真实存在的列,右边是它在解决的问题:

再看两处"藏在下面"的设计

① 组合唯一键 —— 去重身份的真正落点

UNIQUE(project_path, category, normalized_hash)

单看 normalized_hash 你不知道它防什么;配上这条 UNIQUE 才完整: 同一个项目、同一个分类下,同一份内容只能有一行。 这就是为什么它必须有 NOT NULL —— 参与唯一键的列不允许为空,否则去重会出现漏洞。

② 三个索引,全以 project_path 开头

idx_memories_project_status_category   ON memories(project_path, status, category)
idx_memories_project_status_expires    ON memories(project_path, status, expires_at)
idx_memories_project_category_hash     ON memories(project_path, category, normalized_hash)

这不是巧合:这个库同时服务多个项目,所有查询都先按项目切分。 索引的列顺序 = 查询的入口顺序 —— 这一条 0004 会专门练。

③ 用数据库自己拦非法写入(顺手看一眼,现在不用懂)

CREATE TRIGGER memories_authority_guard_insert BEFORE INSERT ON memories
  WHEN (…) BEGIN SELECT RAISE(ABORT, 'context.db memory writes are managed by the Rust module'); END;

写保护不一定写在应用层 —— SQLite 允许把规则放在库里,任何程序绕过应用直接写都会被拒。

证据:约束是常态,不是装饰。这个库全部表的建表语句里, NOT NULL 出现 380 次、PRIMARY KEY 93 次、UNIQUE 与 CHECK 各 12 次。

第二步:写一张自己的表

在内存库里练(关掉就没了,不会污染任何东西):

sqlite3 :memory:

然后粘这一段 —— 我们给「每日笔记索引」建表:

CREATE TABLE daily_notes (
  id         INTEGER PRIMARY KEY,                       -- 注意:没写 AUTOINCREMENT
  note_date  TEXT    NOT NULL,                          -- 'YYYY-MM-DD'
  project    TEXT    NOT NULL,
  topic      TEXT    NOT NULL,
  word_count INTEGER NOT NULL DEFAULT 0,
  status     TEXT    NOT NULL DEFAULT 'draft'
                     CHECK (status IN ('draft', 'done')),
  created_at INTEGER NOT NULL,
  UNIQUE (note_date, project)                           -- 同一天同一个项目只有一篇
);

插两行正常数据:

INSERT INTO daily_notes (note_date, project, topic, created_at)
VALUES ('2026-09-24', '掌握SQLite', '打开真库', 1758672000),
       ('2026-09-24', '参与Zephyr开源社区', 'PR #119887', 1758672000);

再故意犯两个错 —— 这就是这节课的反馈环:

-- ① 同一天同一个项目再插一篇
INSERT INTO daily_notes (note_date, project, topic, created_at)
VALUES ('2026-09-24', '掌握SQLite', '重复的', 1758672000);
-- Error: UNIQUE constraint failed: daily_notes.note_date, daily_notes.project

-- ② 状态写成没定义过的值
INSERT INTO daily_notes (note_date, project, topic, status, created_at)
VALUES ('2026-09-25', '掌握SQLite', 'x', 'finished', 1758672000);
-- Error: CHECK constraint failed: status IN ('draft', 'done')

看到这两条报错,你才算真的用上了约束:错误在写入口就被挡住,而不是三个月后在报表里发现脏数据。

一个常见误解:AUTOINCREMENT

INTEGER PRIMARY KEY 本身就会自动分配编号,不需要 AUTOINCREMENT。 后者只多保证一件事:被删除的编号永不复用 —— 代价是多一张 sqlite_sequence 表、每次插入多一点开销。 官方文档的原话是它「通常不需要」。上面 memories 表用了它,是因为对那套系统来说"编号复用"会污染历史引用; 而你的每日笔记表不需要。

自测(选项一样长,别从长度上猜)