SQLite：文件连接、类型与事务模型

文件、连接与第一条查询

本课面向未用过数据库的读者。数据库把数据、约束和查询规则一起保存；表像有列名的记录集合，主键用来唯一识别记录。SQL 是描述要得到什么结果的语言，终端则负责启动解释 SQL 的程序，两者不是同一种语法。

适用 SQLite 3.37+ 的 3.x 版本族、CPython 3.12+；STRICT 表要求至少 3.37。核对日期：2026-10-17。这里选择能力下限，不声明所有旧补丁都适合生产。官方已记录 WAL-reset 缺陷，WAL 教学实际选型还应采用 3.51.3+ 或含官方修复回移的 3.44.6、3.50.7 等发行补丁，见官方 WAL 文档。应核对 Python 实际链接的 SQLite，而非只看 Python 版本。本轮所有数据库示例未执行，只有源码静态检查。

嵌入式存储与适用场景

SQLite 嵌入式存储把数据库引擎作为应用进程中的库使用，没有必须先启动的数据库服务器。文件访问权限就是重要安全边界；不能为这个文件设置一个数据库网络密码来替代操作系统权限。桌面应用、单机配置和设备缓存适合先评估 SQLite；大量跨主机并发写入则需要重新考虑部署模型。文件通常包含表和索引，但事务期间还有 journal 或 WAL，不能由“一个数据库文件”推导出“随时只复制这一个文件就完整”。官方文件格式

路径是文件位置，连接是应用对引擎持有的操作状态。同一文件可以有多个连接，每个连接有自己的事务、游标和部分 PRAGMA 配置。:memory: 通常是每连接独立的内存库，不能用两个普通内存连接冒充同一个文件的并发实验。

连接模式与目录权限

SQLite 连接模式中，URI 的 mode=ro 只读打开，mode=rw 要求文件已存在，mode=rwc 允许创建。拼错普通路径可能创建空库，随后出现 no such table；排查时先确认绝对路径和 sqlite_schema。只读也可能涉及 WAL 辅助文件及所在目录，权限不能只看主文件。Python 的 uri=True 才会解释 URI 选项；源码用 Path.as_uri 处理空格和 Windows 盘符，不拼接原始路径。Python 3.12 sqlite3

Windows 读者自行运行时使用 PowerShell 的 python，Linux 使用发行方 CPython 的 python3，下文统一写 python，需要按解释器名称替换；路径含空格时必须引用整个参数。不要求在本轮安装或启动任何产品。

CLI 点命令与 SQL

SQLite sqlite3 点命令由命令行工具解释，例如 .tables、.schema expenses、.backup；SELECT 和 PRAGMA 是发送给引擎的 SQL。把 .tables 交给 Python execute 会语法错误。读者在自己的现有环境可用 sqlite3 -readonly ledger.db 打开，再输入 .schema expenses；本轮未运行这些命令。原命令手册的只读检查、CSV 导出和 .output stdout 仍有各自用途。官方 CLI

SELECT 的 WHERE 过滤行，ORDER BY 才规定顺序，LIMIT 只限制数量而不保证顺序。SELECT request, cents FROM expenses WHERE category='food' ORDER BY request LIMIT 10; 先选食物记录再按唯一请求号排列。SQL 字符串使用单引号；程序传入的值必须参数绑定，不把字符串拼到 SQL。

第一次练习与解答

练习：解释为什么 sqlite3 wrong.db 后没有表不证明原库丢失；再说明 .schema 和 SELECT 的不同。先画出终端、CLI、引擎、文件四层。

参考解答：普通打开允许创建新文件，应先核对工作目录和绝对路径，用只读打开降低误创建风险；.schema 是 CLI 帮助查看结构的命令，SELECT 是引擎查询语句。后续完整账本用显式连接模式把这种差别写进程序。

类型、事务与查询成本

前置是表、主键、连接和 SQL，见入门。本篇解释为什么同一语句在不同连接状态下可能成功、等待或失败。适用 SQLite 3.37+ 3.x，核对 2026-10-17；示例未执行。

存储类、类型亲和与 STRICT

SQLite 类型亲和是列对值进行转换的偏好，普通表不等同于其他数据库的严格列类型。值的存储类有 NULL、INTEGER、REAL、TEXT、BLOB；NUMERIC 亲和可能把看似文本的数字转为整数或浮点。声明 VARCHAR(20) 并不自动限制长度。STRICT 表限制可用类型名并检查可无损转换，不代表所有业务约束都自动存在；仍需 NOT NULL、CHECK 和唯一约束。官方类型文档、STRICT 表

金额用整数分而非浮点元，是为了让加法和相等判断明确；仍要检查范围和溢出。日期可约定 UTC ISO 文本或整数时间戳，必须固定单位和时区。NULL 表示未知或缺失，比较应写 IS NULL；WHERE 只保留真，x = NULL 不会得到“缺失值相等”。COUNT(*) 数行，COUNT(note) 忽略 note 为 NULL 的行。

事务、写锁与事务所有权

SQLite BEGIN IMMEDIATE 在事务开始就尝试取得写入资格，适合先读后写的短事务；它不会让两个写者同时执行。BEGIN DEFERRED 延后到访问时再建立读或写事务，先读后升级可能失败。提交让修改成为一个整体，回滚取消本事务内未提交修改；一个连接上事务不能嵌套 BEGIN，局部撤销用 SAVEPOINT。官方事务

账本先取得写入资格，再查询请求号，最后插入或确认重放。这样的顺序把“检查再写入”放进同一事务。函数若接受外部连接，必须约定谁提交；本例由 CLI 独占连接，因而拥有事务。不要把网络请求或用户等待放在持锁区间，失败重试也须重新读取业务条件。

WAL、快照与单写者

SQLite WAL 允许读写并行，但同一数据库仍是单写者。读者保留起始快照，长读事务会拖住 checkpoint 回收进度。checkpoint 是将已提交页合入主文件，不是把未提交事务提交。WAL 需要同机共享内存，不应把数据库文件放到多机共同访问的网络文件系统。官方 WAL

设置 journal_mode 后应读取返回值确认生效。synchronous=FULL 与 NORMAL 在断电持久性上有不同取舍；WAL 并不自动让所有成功返回的写入在硬断电后都存在。timeout 只是最多等待锁一段时间，不能解决逻辑冲突，也不会消除 SQLITE_BUSY。读后升级遇到 SQLITE_BUSY_SNAPSHOT 时要回滚并重做整个事务，不能在旧快照里无限重试一条 UPDATE。

索引、排序与执行计划

SQLite EXPLAIN QUERY PLAN 展示计划形状，不实际执行目标查询。联合索引 expenses(category,request) 可服务 category 等值过滤和 request 顺序读取；只按 request 查询通常不能把这个索引当作同等有效的首列索引。覆盖索引表示结果列已在索引中，减少回表，不是无需读磁盘的保证。索引也增加写入和存储成本。官方查询规划

观察 SCAN、SEARCH、临时排序等现象，再结合数据规模判断；小表全表扫描可以是合理选择，不能断言输出一定含某串固定计划文本。分页应以唯一排序键作边界，如 request 大于上一页末项，而不是在持续写入的数据上依赖大 OFFSET。练习：移除索引后结果是否改变？解答：若 ORDER BY 不变，逻辑结果不应改变，访问成本和计划可能改变。