并发性（最易踩坑之处）

• 同一时间仅允许一个写入者——并发写入将排队或失败；不适用于高写入负载场景
• 启用 WAL 模式：PRAGMA journal_mode=WAL——支持写入期间同时读取，性能大幅提升
• 设置忙等待超时：PRAGMA busy_timeout=5000——在返回 SQLITE_BUSY 前等待 5 秒，而非立即失败
• WAL 模式需要 -wal 和 -shm 文件——复制主数据库文件时切勿遗漏这两个文件
• 使用 BEGIN IMMEDIATE 尽早获取写锁——可避免“先读后写”模式下的死锁

外键（默认禁用！）

• PRAGMA foreign_keys=ON 需在每个连接中显式启用——该设置不会持久化到数据库文件中
• 若未启用，外键约束将被静默忽略——导致数据完整性破坏
• 使用前务必检查：PRAGMA foreign_keys 返回 0 或 1
• ON DELETE CASCADE 仅在 foreign_keys 启用时生效

类型系统

• 采用类型亲和性（type affinity），而非严格类型——例如 INTEGER 列可无报错地接受字符串值 "hello"
• STRICT 表可强制类型检查——但仅 SQLite 3.37+（2021 年起）支持
• 无原生 DATE/TIME 类型——建议使用 TEXT 存储 ISO8601 格式字符串，或使用 INTEGER 存储 Unix 时间戳
• 无 BOOLEAN 类型——应使用 INTEGER 的 0/1 表示；TRUE/FALSE 仅为别名
• REAL 类型为 8 字节浮点数——存在与其他浮点类型相同的精度问题

模式变更

• ALTER TABLE 功能极为有限——仅支持添加列、重命名表或列；基本仅此而已
• 无法修改列类型、添加约束，也无法删除列（SQLite 3.35 之前）
• 变通方案：新建表 → 迁移数据 → 删除旧表 → 重命名新表——整个过程需包裹在事务中
• ALTER TABLE ADD COLUMN 不支持直接添加 PRIMARY KEY、UNIQUE 或 NOT NULL 约束（除非同时指定 DEFAULT 值）

性能优化 pragma 设置

• PRAGMA optimize——在关闭长期运行的连接前调用，用于更新查询规划器统计信息
• PRAGMA cache_size=-64000——设置 64MB 缓存（负值单位为 KB；默认值极小）
• PRAGMA synchronous=NORMAL（配合 WAL 模式使用）——在安全性与速度之间取得良好平衡
• PRAGMA temp_store=MEMORY——将临时表置于内存中，提升排序及临时结果处理速度

VACUUM 与维护

• 删除数据后数据库文件大小不会自动缩小——VACUUM 将重写整个数据库并回收磁盘空间
• VACUUM 执行期间需临时占用约 2 倍磁盘空间——请确保磁盘容量充足
• PRAGMA auto_vacuum=INCREMENTAL 配合 PRAGMA incremental_vacuum——可执行部分空间回收，无需全量重写
• 批量删除后务必执行 VACUUM，否则数据库文件将持续膨胀

备份安全性

• 切勿在数据库打开状态下直接复制文件——若此时发生写入操作，极易导致损坏
• 应使用 sqlite3 命令行工具的 .backup 命令，或调用 sqlite3_backup_* API 进行备份
• WAL 模式下：-wal 和 -shm 文件必须与主数据库文件原子性地一并复制
• VACUUM INTO 'backup.db' 可生成独立、完整的数据库副本（SQLite 3.27+ 支持）

索引

• 覆盖索引（covering index）有效——在索引中包含额外列，可避免回表查询
• 支持部分索引（partial index，SQLite 3.8+）：CREATE INDEX ... WHERE condition
• 支持表达式索引（expression index，SQLite 3.9+）：CREATE INDEX ON t(lower(name))
• EXPLAIN QUERY PLAN 显示索引使用情况——比 PostgreSQL 的 EXPLAIN 更简洁直观

事务

• 默认启用自动提交（autocommit）——每条语句均为独立事务；批量插入时性能极差
• 批量插入优化：BEGIN; INSERT...; INSERT...; COMMIT——速度可提升 10–100 倍
• BEGIN EXCLUSIVE 获取独占锁——会阻塞所有其他连接的读写操作
• 通过 SAVEPOINT name / RELEASE name / ROLLBACK TO name 实现嵌套事务

常见错误

• 将 SQLite 用于面向多并发用户的 Web 应用——单一写入者会阻塞全部请求；此类场景应选用 PostgreSQL
• 误认为 ROWID 永远稳定——VACUUM 可能改变 ROWID；如需稳定主键，请显式定义 INTEGER PRIMARY KEY
• 未设置 busy_timeout——任何并发场景下均可能出现随机的 SQLITE_BUSY 错误
• 内存数据库 ':memory:'——每个连接拥有独立的内存数据库实例；如需共享，应使用 file::memory:?cache=shared