SQLite

并发性（最大的陷阱）

• 一次只能有一个写入器——并发写入会排队或失败；不适合高写入负载场景
• 启用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 开启时生效

类型系统

• 类型亲和性，而非严格类型——INTEGER列可以接受"hello"而不报错
• STRICT表强制执行类型——但仅限SQLite 3.37+版本（2021年）
• 没有原生的DATE/TIME类型——使用TEXT存储ISO8601格式或INTEGER存储Unix时间戳
• BOOLEAN类型不存在——使用INTEGER 0/1；TRUE/FALSE只是别名
• REAL是8字节浮点数——存在与其他浮点数相同的精度问题

模式变更

• ALTER TABLE功能非常有限——可以添加列、重命名表/列；基本上就这些
• 无法更改列类型、添加约束或删除列（直到3.35版本）
• 变通方案：创建新表、复制数据、删除旧表、重命名——用事务包裹操作
• ALTER TABLE ADD COLUMN添加的列不能包含PRIMARY KEY、UNIQUE约束，也不能设置NOT NULL（除非有默认值）

性能编译指示

• PRAGMA optimize在关闭长连接之前——更新查询规划器统计信息
• PRAGMA cache_size=-64000 设为64MB缓存——负值表示千字节；默认值非常小PRAGMA synchronous=NORMAL
• 配合WAL模式——安全性与速度的良好平衡PRAGMA temp_store=MEMORY
• 将临时表存入内存——加速排序和临时结果处理清理与维护

删除数据不会缩小文件——

• VACUUM重写整个数据库，回收空间VACUUM
• 临时需要双倍磁盘空间——确保有足够容量PRAGMA auto_vacuum=INCREMENTAL
• 配合PRAGMA incremental_vacuum——无需完全重写即可部分回收空间批量删除后务必执行清理，否则文件将持续膨胀
• 备份安全

切勿在数据库开启时复制文件——若存在写入操作会导致损坏

• 请使用
• Use.backup命令在sqlite3中——或sqlite3_backup_*API
• WAL模式：-wal和-shm必须与主文件原子性地复制
• VACUUM INTO 'backup.db'创建独立副本（3.27+）

索引

• 覆盖索引有效——添加额外列以避免表查找
• 支持部分索引（3.8+）：CREATE INDEX ... WHERE 条件
• 表达式索引（3.9+）：CREATE INDEX ON t(lower(name))
• EXPLAIN QUERY PLAN显示索引使用情况——比PostgreSQL的EXPLAIN更简单

事务

• 默认自动提交——每条语句是独立的事务；批量插入时速度慢
• 批量插入：开始；插入...；插入...；提交—快10至100倍
• 开始排他事务用于排他锁——阻塞所有其他连接
• 通过以下方式实现嵌套事务保存点名称/释放名称/回滚到名称

常见错误

• 在具有并发用户的Web应用中使用SQLite——一个写入者会阻塞所有其他操作；应改用PostgreSQL
• 假设ROWID是稳定的——清理可能会改变ROWID；应使用显式的整数主键
• 未设置busy_timeout——在任何并发操作下都可能出现随机的SQLITE_BUSY错误
• 内存数据库':memory:'——每个连接获得不同的数据库；应使用file::memory:?cache=shared以实现共享