SQLite

核心用法

SQLite 是一个零配置、服务器端的嵌入式关系数据库，无需独立进程即可运行。本技能文档系统梳理了生产环境必备的核心技术点：

并发与WAL模式 — SQLite 最大痛点。仅支持单写多读，默认模式会阻塞读操作。启用 PRAGMA journal_mode=WAL 可实现读写并发，配合 PRAGMA busy_timeout=5000 避免 SQLITE_BUSY 错误。注意 WAL 会生成 -wal 和 -shm 辅助文件，备份时必须同步复制。

数据完整性 — 外键约束默认关闭！每个连接必须执行 PRAGMA foreign_keys=ON，否则级联删除等约束完全失效。建议连接后立即检查返回值确认开启。

类型与Schema — 传统表为"类型亲和"机制，INTEGER 列可存入字符串而不报错。SQLite 3.37+ 支持 STRICT 严格表模式强制类型校验。无原生布尔/日期类型，需用 INTEGER 0/1 或 ISO8601 文本存储。

性能调优 — 关键配置组合：cache_size 调至 64MB 以上、synchronous=NORMAL 配合 WAL 平衡安全与速度、temp_store=MEMORY 加速排序。批量操作务必显式包裹 BEGIN...COMMIT，速度提升 10-100 倍。长期连接关闭前执行 PRAGMA optimize 更新查询规划器统计信息。

维护操作 — DELETE 不会释放文件空间，需定期 VACUUM 重写数据库（需 2 倍磁盘空间）。3.27+ 版本可用 VACUUM INTO 直接生成独立备份副本。

显著优点

• 零运维成本：无服务进程、无权限配置、单文件即数据库
• WAL 模式突破：读写并发能力满足大多数桌面/移动端场景
• 现代特性演进：3.35+ 支持 DROP COLUMN，3.37+ 支持 STRICT 类型，持续活跃开发
• 高级索引：表达式索引、部分索引、覆盖索引完整支持

潜在缺点与局限

• 写并发瓶颈：单写锁设计天然不适合高并发写入场景（如多用户 Web 后端），应果断迁移至 PostgreSQL
• 有限 ALTER 能力：修改列类型、添加约束仍需重建表流程
• 类型系统混乱：历史遗留的类型亲和机制易导致隐式转换 Bug
• ROWID 不可靠：VACUUM 会重新分配，依赖它做主键会导致数据关联断裂

适合人群

• 桌面应用、移动 App（iOS/Android）、IoT 设备本地存储开发者
• 中小型数据分析、测试环境、嵌入式系统工程师
• 需要嵌入数据库但无 DBA 资源的独立开发者和小团队

常规风险

• 并发陷阱：未启用 WAL + busy_timeout 的生产环境必现随机失败
• 静默数据损坏：外键关闭时违反约束无警告、类型亲和导致脏数据入库
• 备份失误：直接复制打开中的数据库文件或遗漏 WAL 文件导致不可恢复损坏
• 内存数据库误用：:memory: 每个连接隔离，共享内存需用 file::memory:?cache=shared 语法

SQLite 最佳实践深度评估

SQLite 作为全球部署最广的嵌入式数据库，以轻量、零配置、单文件架构著称。本 Skill 系统梳理了 SQLite 生产环境的核心技术要点，涵盖并发控制、数据完整性、性能优化等关键领域。

核心用法

并发与 WAL 模式：SQLite 仅支持单写多读架构。启用 PRAGMA journal_mode=WAL 是生产环境必修课，允许读取与写入并行执行，避免传统 rollback journal 的锁竞争。配合 PRAGMA busy_timeout=5000 可将写冲突从立即失败转为优雅等待。关键认知：WAL 模式产生 -wal 和 -shm 辅助文件，备份时必须原子性复制三者。

外键约束陷阱：SQLite 的外键默认关闭，需每个连接执行 PRAGMA foreign_keys=ON。此设计导致大量开发者误以为启用了数据完整性，实则约束被静默忽略。级联删除（ON DELETE CASCADE）同样依赖此开关。

类型系统真相：SQLite 采用"类型亲和性"而非严格类型，INTEGER 列可插入文本而不报错。3.37+ 版本支持 STRICT 表模式强制类型校验。BOOLEAN 类型不存在，仅用 0/1 整数模拟；日期时间需以 ISO8601 TEXT 或 Unix 时间戳 INTEGER 存储。

模式变更限制：ALTER TABLE 能力极其受限——无法删除列、修改类型或添加非空约束（无默认值时）。标准迁移流程：建新表→数据迁移→删旧表→重命名，全程事务包裹。

性能关键配置：PRAGMA cache_size=-64000 将缓存从默认 2MB 提升至 64MB；PRAGMA synchronous=NORMAL 在 WAL 下实现安全与速度的平衡；批量插入必须使用显式事务包裹，速度可提升 10-100 倍。

显著优点

• 生产级可靠性：内容源自 SQLite 官方文档与社区最佳实践，技术准确性高
• 痛点覆盖全面：直击 WAL 模式、外键默认关闭、类型系统等高频踩坑点
• 可操作性极强：每条建议附带具体 SQL 语句，即拿即用
• 架构决策清晰：明确区分 SQLite 适用边界（低并发、嵌入式）与 PostgreSQL 迁移信号

潜在局限

• 版本依赖标注不足：部分特性（如 STRICT 表、VACUUM INTO）需 3.27+/3.37+，但未强调版本兼容性检查
• 平台差异简略：Windows 与 Unix 在文件锁定行为上有微妙差异，未深入展开
• 高级场景缺失：未涉及加密（SQLCipher）、FTS 全文检索、R*Tree 空间索引等扩展
• 故障排查薄弱：缺少 SQLITE_BUSY/SQLITE_LOCKED 等错误的系统化诊断流程

适合人群

• 嵌入式/IoT 开发者：需要在资源受限环境部署数据库
• 移动应用开发者：iOS/Android 原生 SQLite 调优
• 中小规模 Web 后端：单节点、低并发写入场景（<1000 TPS）
• 数据分析从业者：需要轻量级本地数据处理管道

常规风险

• 并发瓶颈误用：高并发 Web 场景若坚持使用 SQLite，将遭遇写队列阻塞，此时应迁移至 PostgreSQL/MySQL
• 备份数据丢失：直接复制 .db 文件而忽略 -wal/-shm 文件，或复制时数据库处于写入状态，将导致备份损坏
• ROWID 依赖风险：未显式定义 INTEGER PRIMARY KEY 时，VACUUM 操作会重写 ROWID，破坏外键关联或应用缓存
• 内存数据库隔离：:memory: 模式每个连接独立，多连接共享需使用 file::memory:?cache=shared URI 语法