面试题手册

SQLite 的索引本质上是在表数据之外维护一棵 B-Tree，让查询可以少扫很多行。没有索引时，WHERE user_id = ? 可能要从头读到尾；有合适索引时，SQLite 可以沿着树快速定位候选记录。问题在于索引不是越多越好，它会占空间，也会拖慢写入，因为每次 INSERT、UPDATE、DELETE 都要同步维护索引页。先用执行计划确认有没有命中优化 SQLite 查询不要靠感觉，先看 EXPLAIN QUERY PLAN。它会告诉你是全表扫描，还是使用了某个索引。比如下面这个查询，如果 orders(user_id, created_at) 上有复合索引，通常能同时服务过滤和排序。EXPLAIN QUERY PLANSELECT id, totalFROM ordersWHERE user_id = 42ORDER BY created_at DESCLIMIT 20;CREATE INDEX idx_orders_user_createdON orders(user_id, created_at DESC);看到 SCAN TABLE 不一定就是坏事，小表全扫可能比走索引更快。真正要警惕的是百万级表上频繁出现全表扫描，或者查询返回很少数据却读了大量页面。复合索引要看最左前缀SQLite 的复合索引遵循类似“从左往右”的使用方式。(user_id, status, created_at) 能很好支持 user_id = ? AND status = ?，也能支持只按 user_id 查；但如果查询只有 status = ?，这个索引通常帮不上太多。索引列顺序要按过滤条件、选择性和排序需求一起定，不是把所有列随手堆进去。CREATE INDEX idx_task_user_status_timeON task(user_id, status, updated_at DESC);如果 status 只有“open/closed”两个值，单独给它建索引可能收益很低。低选择性字段更适合放在高选择性字段后面，或者和其他条件组成复合索引。覆盖索引和部分索引很实用覆盖索引指查询需要的列都在索引里，SQLite 不必再回表读取数据页。对于高频列表页，这能明显减少 I/O。部分索引则只索引满足条件的行，适合软删除、状态过滤这类场景。CREATE INDEX idx_article_published_listON article(category_id, published_at DESC, title)WHERE deleted_at IS NULL AND status = 'published';部分索引的坑在于查询条件必须能让优化器判断它适用。如果你的 SQL 写法和索引条件对不上，比如状态值经过函数转换，可能不会命中。追问哪些字段最值得建索引？优先考虑高频出现在 WHERE、JOIN、ORDER BY、GROUP BY 里的字段。还要看选择性，用户 ID、订单号这类区分度高的列通常收益大，性别、布尔状态这类列单独建索引可能很鸡肋。取舍是读多写少的表可以多一点索引，写入密集的日志表要克制。建完以后一定用执行计划验证，而不是只看 SQL 看起来像会命中。为什么加了索引查询还是慢？可能是索引列顺序不对、条件里用了函数、LIKE 前面有通配符，或者返回数据太多导致回表成本很高。比如 WHERE date(created_at) = '2026-01-01' 会让普通 created_at 索引很难发挥作用。可以改成范围条件：created_at >= '2026-01-01' AND created_at < '2026-01-02'。边界是优化器会按统计信息估算成本，数据分布变化后需要重新分析。SQLite 需要手动更新统计信息吗？需要，尤其是大量导入、删除或数据分布明显变化后。可以执行 ANALYZE; 让 SQLite 收集索引统计信息，帮助优化器选择更合适的执行计划。新版本也可以用 PRAGMA optimize; 做轻量维护。踩坑点是测试库数据很少时计划很好，线上数据一多就完全不同，所以压测数据要尽量接近真实分布。索引太多会带来什么问题？首先是写入变慢，每次写表都要更新多个索引。其次是数据库文件变大，缓存命中率下降，备份和迁移也更慢。还有一个隐蔽问题是优化器可选路径太多，但并不代表每条路径都好。实践里可以定期查出重复或长期不用的索引，保留能覆盖核心查询的那几组。

SQLite 的并发能力要先说清一个前提：它不是服务器数据库，而是把数据库引擎嵌进应用进程，多个连接最终都在争用同一个数据库文件。所以它的设计目标不是让几百个写请求同时冲进去，而是让读操作尽量轻、写操作尽量安全。实际项目里，只要写入不是持续高峰，SQLite 的多读单写模型通常够用；一旦把它当成 MySQL 那样承接高并发写入，就会很快遇到 database is locked。SQLite 的锁是怎么变化的默认回滚日志模式下，SQLite 常见锁状态可以理解为从宽到严逐步升级：SHARED 允许多个连接读，RESERVED 表示某个连接准备写，PENDING 阻止新的读进入，EXCLUSIVE 才真正独占写入。读连接之间不互相阻塞，写连接之间只能排队。最容易踩坑的是长时间读事务：一个后台导出任务开着游标不关，写事务提交时就可能一直等锁。PRAGMA journal_mode;PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 5000;busy_timeout 不是提升并发的魔法，它只是让连接在遇到锁时等一会儿，而不是立刻报错。如果写事务本身很慢，等待时间再长也只是把问题往后拖。WAL 为什么常被推荐WAL（Write-Ahead Logging）把写入先追加到 -wal 文件，读连接可以继续读旧快照，写连接也不用立刻覆盖主数据库文件。这样读写冲突明显减少，尤其适合“读很多、偶尔写”的桌面应用、移动端缓存和小型服务。取舍也很明确：WAL 仍然只有一个写者，而且会多出 wal/shm 文件，需要定期 checkpoint。PRAGMA wal_checkpoint(TRUNCATE);PRAGMA synchronous = NORMAL;synchronous=NORMAL 在 WAL 下通常是性能和安全的折中；如果是账务落库或断电风险很敏感，仍应评估 FULL。不要为了跑分把可靠性配置关到最低。写事务要短，连接要克制SQLite 并发优化最有效的办法不是开更多连接，而是缩短写锁占用时间。批量写入要包进事务，避免每条语句都单独提交；但事务里不要夹杂网络请求、文件上传或复杂计算。写入前先准备好数据，拿到锁后只做数据库操作。BEGIN IMMEDIATE;INSERT INTO logs(message, created_at) VALUES ('ok', unixepoch());UPDATE counters SET value = value + 1 WHERE name = 'daily';COMMIT;BEGIN IMMEDIATE 会提前抢写锁，适合已经确定要写的场景。好处是失败更早，不会在做了一半业务逻辑后才发现提交不了；代价是会更早挡住其他写者。追问WAL 开了以后是不是就能高并发写入？不能。WAL 主要改善读写并发，让读者不必因为写者追加日志而停下来，但同一时间仍然只有一个写事务能提交。它适合读多写少或短写事务场景，不适合订单撮合、聊天消息洪峰这类持续写入压力。踩坑点是只改了 journal_mode=WAL，却把大批量导入放在一个很长事务里，结果其他写请求仍然全部排队。为什么经常遇到 database is locked？常见原因是事务没有及时提交、查询游标没有关闭、多个进程同时写，或者把网络调用放进事务中。SQLite 不会替你拆分写锁，它只会按文件锁规则等待或失败。排查时可以先加 PRAGMA busy_timeout=5000 缓解瞬时冲突，再检查哪段代码持有连接太久。边界是 busy timeout 只能处理短暂竞争，不能解决架构级写入过载。BEGIN DEFERRED、IMMEDIATE 和 EXCLUSIVE 怎么选？DEFERRED 是默认模式，直到真正读写时才拿锁，适合不确定是否写入的普通逻辑。IMMEDIATE 一开始就拿写意向锁，适合明确要写且希望早失败的业务。EXCLUSIVE 会更强势地独占数据库，通常只在迁移、批量维护或离线工具里使用。取舍在于锁越早越强，失败越可控，但对其他连接越不友好。多线程里能共享同一个 SQLite 连接吗？要看驱动和编译配置，但工程上更稳的是每个线程或请求使用独立连接，并通过连接池控制数量。共享连接容易出现游标交叉、事务边界混乱和锁释放不及时。连接也不是越多越好，SQLite 的瓶颈常常是写锁而不是连接数。移动端和桌面应用里，单写队列加多个只读连接通常更容易维护。

优化 SQLite 性能，先别急着改一堆 PRAGMA。大多数慢查询来自三个地方：没有合适索引、事务拆得太碎、一次取了太多无用数据。SQLite 很快，但它不是魔法；把查询路径、写入批次和文件维护做好，通常比盲目调参数更稳定。EXPLAIN QUERY PLANSELECT id, title FROM postsWHERE user_id = ? AND status = 'published'ORDER BY created_at DESCLIMIT 20;CREATE INDEX idx_posts_user_status_timeON posts(user_id, status, created_at DESC);BEGIN;INSERT INTO logs(message, created_at) VALUES (?, datetime('now'));INSERT INTO logs(message, created_at) VALUES (?, datetime('now'));COMMIT;PRAGMA journal_mode = WAL;PRAGMA synchronous = NORMAL;PRAGMA cache_size = -20000;PRAGMA temp_store = MEMORY;ANALYZE;PRAGMA optimize;第一步是看执行计划。EXPLAIN QUERY PLAN 能告诉你查询是在 SCAN 全表，还是在 SEARCH 索引。看到全表扫描不一定立刻加索引，小表扫描可能比走索引还便宜；但如果这是高频查询，且过滤列、排序列很稳定，就应该设计组合索引。组合索引的顺序要贴近查询条件，常见写法是等值过滤在前，范围和排序在后。第二步是控制写入成本。SQLite 每次事务提交都涉及日志和同步，如果一万条数据拆成一万次提交，慢是正常的。批量导入时用一个事务包起来，性能差距会非常明显。事务也不能太长，尤其不要在事务里做网络请求或复杂计算，否则其他写入会被锁住。第三步才是配置。WAL 适合改善读写并发，synchronous=NORMAL 常用于在可靠性和速度之间折中，cache_size 可以用负数按 KiB 设置缓存上限。ANALYZE 会更新统计信息，让查询规划器更懂数据分布；较新的 SQLite 也推荐周期性执行 PRAGMA optimize;。VACUUM 能回收大量删除后的空洞，但它会重建数据库文件，最好放在低峰期。追问索引是不是越多越好？不是。索引能加速读取，但每次插入、更新、删除都要维护索引，索引太多会拖慢写入并占用更多磁盘。取舍上，只给高频查询、明确过滤或排序的字段建索引，临时低频报表不一定值得。踩坑点是给每个单列都建索引，却忽略真实查询使用的是多列条件，结果优化器仍然选不到理想路径。为什么批量插入一定要用事务？因为 SQLite 的提交成本比单条 INSERT 本身更贵，事务能把多次写入合并成一次提交。比如导入几千行日志，如果每行自动提交一次，会反复刷日志和申请锁；放进 BEGIN/COMMIT 后，写入会顺很多。边界是单个事务也别无限大，移动端或内存紧张环境可以按几千条一批提交。踩坑点是异常时忘记 ROLLBACK，连接留在事务状态里，后续操作都变得奇怪。EXPLAIN QUERY PLAN 看到全表扫描就一定有问题吗？不一定。表很小、过滤条件选择性很差、或者查询本来就要读大部分数据时，全表扫描可能是合理的。判断时要结合数据量、调用频率和响应时间，而不是只看 SCAN 两个字。真正要警惕的是大表高频查询反复扫描，或者排序时出现临时 B-tree，这通常说明索引没覆盖过滤和排序。优化前后都要重新看计划，避免凭感觉调。WAL、cachesize、tempstore 这些 PRAGMA 怎么选？WAL 适合有并发读写的应用，纯一次性脚本不一定需要。cache_size 可以提升热点页命中率，但设置过大可能挤压应用内存，尤其是多进程同时打开数据库时。temp_store=MEMORY 能减少临时排序和中间表落盘，但大排序会吃内存。取舍原则是先用默认值跑基准，再针对瓶颈改一项测一项，不要把网上配置整段复制进生产。VACUUM 和 ANALYZE 什么时候执行？ANALYZE 适合在大批量导入、删除或新建索引后执行，它帮助查询规划器更新统计信息。VACUUM 适合大量删除后回收文件体积，或者需要重建数据库文件时执行，但它成本更高，会占用时间和磁盘空间。边界是频繁 VACUUM 不会让普通查询神奇变快，反而可能影响正常业务。比较稳的做法是在低峰维护窗口执行，并在执行前确认有足够磁盘空间和备份。

SQLite 的 WAL，全称 Write-Ahead Logging，意思是写操作先追加到 WAL 日志文件，而不是立刻修改主数据库文件。默认回滚日志模式更像“先准备撤销方案，再改原文件”；WAL 模式则是“先把变更写到旁边日志，合适时再合并”。这个变化让读写冲突少很多：读连接继续看主库和某个时间点之前的 WAL，写连接把新内容追加到 -wal 文件。PRAGMA journal_mode = WAL;PRAGMA synchronous = NORMAL;PRAGMA wal_autocheckpoint = 1000;PRAGMA wal_checkpoint(TRUNCATE);启用 WAL 后，同目录通常会出现 database.db-wal 和 database.db-shm。-wal 保存尚未合并回主库的页面，-shm 用来协调连接之间的共享状态。checkpoint 会把 WAL 中的有效页面写回主数据库，自动 checkpoint 默认大约在 WAL 达到 1000 页时触发，也可以手动执行。WAL 的优势主要有三点。第一，读不会轻易阻塞写，写也不会轻易阻塞读，适合读多写少但偶尔写入的 Web、小服务、桌面应用和移动应用。第二，写入通常是追加写，比频繁改主库页面更顺。第三，崩溃恢复路径清晰，已提交事务在 WAL 里，SQLite 可以据此恢复到一致状态。但 WAL 不是“打开就永远更快”。SQLite 同一时间仍然只有一个写事务，WAL 提升的是读写并发，不是把单写者变成多写者。长时间未结束的读事务可能让 checkpoint 无法推进，-wal 文件越长越大，最后读性能和磁盘占用都会受影响。追问WAL 和默认回滚日志模式怎么取舍？如果应用存在并发读取，同时又有持续写入，WAL 通常更合适。默认回滚日志模式更保守，文件形态简单，对一次性脚本、低并发工具或旧环境也够用。取舍关键不是哪个更高级，而是连接数、读事务时长和文件系统是否适配。踩坑点是把数据库放在 NFS、SMB 这类网络盘上再开 WAL，锁行为异常时很难复现。checkpoint 是什么？为什么 WAL 文件会变大？checkpoint 会把 WAL 里已经提交的页面合并回主数据库，然后让 WAL 可以复用或截断。WAL 变大通常不是写入异常，而是有读事务一直占着旧快照，SQLite 不能清掉它还可能需要的页面。边界在于 checkpoint 不是越频繁越好，太频繁增加 I/O，太少又会让 WAL 膨胀。实际项目可以保留自动 checkpoint，再在低峰期执行 PRAGMA wal_checkpoint(TRUNCATE);。synchronous=NORMAL 安全吗？在 WAL 模式下，synchronous=NORMAL 是常见折中，它减少同步刷盘次数，换来更好的写入性能。代价是极端断电或系统崩溃时，最近事务的持久性保证弱于 FULL。金融记账、不可丢事件日志更适合 FULL，普通缓存、内容索引、本地配置库可以考虑 NORMAL。不要直接用 OFF 追性能，除非这些数据完全可重建。WAL 能解决 database is locked 吗？它能减少读写互相阻塞导致的锁等待，但不能消灭所有 database is locked。SQLite 仍然只有一个写者，如果某个写事务做大量计算、网络请求或批量更新，其他写入照样会等。正确做法是缩短事务时间，批量写入时快速完成，并设置合理的 busy timeout。常见坑是在事务里调用外部接口，锁被白白占住，WAL 也救不了。移动端和桌面端默认都应该开 WAL 吗？多数本地应用可以优先考虑 WAL，因为它能改善界面读取和后台写入同时发生时的卡顿。边界是数据库文件必须和 -wal、-shm 一起被正确备份、迁移或上传，否则只复制主库可能拿不到最新数据。若应用需要频繁把单个 db 文件发给别人，回滚日志模式反而省心。落地时要把备份流程、关闭连接和 checkpoint 策略一起设计，而不是只改一行 PRAGMA。

SQLite 常用约束包括 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 和 DEFAULT。它们的价值不是让建表语句更复杂，而是在数据写入时拦住明显错误。应用层校验会受版本、入口和脚本影响，数据库约束才是最后一道防线。PRAGMA foreign_keys = ON;CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, age INTEGER CHECK(age >= 0), status TEXT NOT NULL DEFAULT 'active');CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount REAL NOT NULL CHECK(amount > 0), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE RESTRICT);NOT NULL 表示字段必须有值，适合邮箱、订单金额、状态这类缺失后无法解释的数据。UNIQUE 保证业务唯一性，比如邮箱或外部订单号，但 SQLite 允许唯一列里出现多个 NULL，所以必填唯一字段要同时写 NOT NULL UNIQUE。INTEGER PRIMARY KEY 在 SQLite 里会关联 rowid，查询和插入都直接；复合主键适合关联表，但外键引用和查询条件会更啰嗦。FOREIGN KEY 负责引用完整性，常见坑是 SQLite 需要每个连接执行 PRAGMA foreign_keys = ON;。CHECK 适合金额大于 0、状态取值范围这类稳定规则，不适合经常变化的产品策略。DEFAULT 只是缺省值，不是校验，它能减少插入语句重复，但不能掩盖真实缺失的数据。追问PRIMARY KEY 和 UNIQUE 有什么区别？PRIMARY KEY 是一行记录的主身份，通常会被其他表引用。UNIQUE 只是说明某个业务字段不能重复，比如邮箱或 SKU。取舍上，主键最好稳定、短小、无业务含义；业务字段即使唯一，也可能因为手机号换绑、邮箱修改而变化。踩坑点是 UNIQUE 列可以有多个 NULL，这和很多人理解的“唯一”不一样。外键约束是不是一定要开？有明确父子关系时建议开启，尤其是订单、明细、用户这类不能孤立存在的数据。不开外键会让批量导入更省事，但孤儿记录会长期污染统计和删除逻辑。边界在于外键开关是连接级的，连接池、测试脚本和命令行工具都要设置。常见坑是开发连接开了外键，迁移脚本新建连接没开，结果约束没有真正生效。CHECK 约束适合放哪些规则？CHECK 适合放稳定、局部、只依赖本行字段的规则，比如价格不能小于 0。它不适合放需要查询其他表、调用外部服务或依赖复杂时间窗口的规则。取舍原则是：违反后一定代表数据无效的规则放数据库，只是运营策略的规则放应用层。踩坑点是后期补复杂约束可能需要重建表，迁移成本比一开始写清楚更高。DEFAULT 能不能代替 NOT NULL？不能。DEFAULT 只在插入时省略该列才生效，显式写入 NULL 时仍可能存进去。稳妥写法是 status TEXT NOT NULL DEFAULT 'active'，既有默认值，也禁止空值。边界是默认值必须代表真实合理的业务状态，不要为了省事把未知数据伪装成正常数据。约束会不会影响写入性能？会有一点影响，尤其是 UNIQUE、外键和复杂 CHECK 都要额外判断。多数业务里，这点成本低于脏数据带来的修复成本。性能敏感场景可以给外键列补索引，批量导入时使用事务，并在导入后做一致性检查。不要为了插入快就删掉约束，除非你有同等可靠的校验和修复链路。

SQLite 备份先判断是否在线SQLite 只有一个数据库文件，看起来复制一下就能备份。但安全备份要先判断数据库是否正在写入。离线数据库直接复制通常没问题；在线数据库如果正在写事务，简单 cp 可能拿到不一致的文件。启用 WAL 模式时还要注意 -wal 和 -shm 文件，漏掉 WAL 可能丢最新数据。什么时候可以直接复制？如果应用已经停止，或者能保证没有写入，文件复制最简单：cp app.db app.backup.dbWAL 模式下更稳的做法是先 checkpoint，再复制主库文件：sqlite3 app.db "PRAGMA wal_checkpoint(FULL);"cp app.db app.backup.db取舍是文件复制速度快、恢复简单，但不适合持续写入的在线库。最常见的坑是用 cron 直接复制正在写的数据库，恢复时才发现文件损坏或少了一段数据。在线备份用什么？SQLite 命令行的 .backup 使用 backup API，可以在数据库仍可读写时得到一致备份：sqlite3 app.db ".backup 'app.backup.db'"恢复时可以在停应用后替换原文件，也可以恢复到新库：cp app.backup.db app.dbsqlite3 restored.db ".restore 'app.backup.db'".backup 的优点是不用手动处理 WAL 细节，适合作为常规备份方案。代价是大库备份仍然会占 I/O，最好放在低峰期执行，并把备份文件同步到另一块磁盘或对象存储。VACUUM INTO 和 .dump 怎么选？VACUUM INTO 会创建一个压实后的数据库文件，适合归档或清理大量删除后的碎片：VACUUM INTO 'app.compact.backup.db';它比普通备份更重，需要额外空间和 I/O，不适合分钟级高频执行。日常备份优先 .backup，低峰维护或归档前再考虑 VACUUM INTO。.dump 会导出 SQL 脚本，适合审查、迁移和救援，但大库恢复会慢：sqlite3 app.db ".dump" > app.sqlsqlite3 restored.db < app.sql简单记法是：要快恢复用 .backup，要可读迁移用 .dump，要顺便压缩整理用 VACUUM INTO。如何确认备份可用？备份后至少跑完整性检查：sqlite3 app.backup.db "PRAGMA integrity_check;"返回 ok 只是第一步。更可靠的是定期恢复到临时环境，检查核心表行数、最新数据时间和关键业务查询。很多事故不是备份文件不存在，而是权限、路径、属主或恢复脚本有问题。备份方案必须包含恢复演练，否则只能算“复制文件”。一个保守脚本可以这样写：set -euo pipefailDB="/data/app.db"OUT="/backup/app_$(date +%Y%m%d_%H%M%S).db"sqlite3 "$DB" ".backup '$OUT'"sqlite3 "$OUT" "PRAGMA integrity_check;"追问SQLite 可以直接复制文件备份吗？可以，但最好在没有写入时做。WAL 模式还要先 checkpoint，避免漏掉 WAL 里的事务。取舍是复制最简单，但在线写入时风险高。踩坑点是只复制主文件，恢复后发现最新数据不见了。.backup 和 VACUUM INTO 有什么区别？.backup 关注在线一致性复制，更适合常规备份。VACUUM INTO 会生成压实后的新库，适合归档和碎片整理。取舍是后者更耗 I/O 和空间。边界是线上高峰不要频繁跑 VACUUM INTO。.dump 能当日常灾备吗？不太建议，它导出可读 SQL，但大库恢复慢。它适合迁移、审计，或者文件损坏时尽量救数据。取舍是可读性换来了恢复时间。踩坑是只保留 .dump，真正故障时恢复窗口超标。怎么验证备份没有坏？先跑 PRAGMA integrity_check;，返回 ok 才算基本可用。还应定期恢复到临时库，跑关键业务查询。取舍是验证会增加耗时，但能提前发现问题。边界是文件存在不代表可以恢复。备份应该保留多少份？要看数据变更频率和能接受丢多久的数据。常见做法是保留近期高频备份，再保留每周或每月归档。取舍是保留越多越占空间，但回滚选择更多。最大坑是原库和备份都放在同一块磁盘上。

SQLite 内存数据库是什么？SQLite 内存数据库把整个数据库放在内存里运行，不写入磁盘文件。最常见的连接名是 :memory:，它仍然支持表、索引、事务、视图和触发器，只是连接关闭后数据就消失。这个特性很适合临时计算和测试，但不适合保存任何不能丢的数据。sqlite3 :memory:import sqlite3conn = sqlite3.connect(':memory:')它通常比磁盘库快，因为少了磁盘 I/O。但别把它当成性能万能药：SQL 写得差、没有事务、缺索引、应用层循环查询，照样会慢。另一个边界是内存容量，导入大文件时必须控制数据量，否则进程可能直接被内存打爆。适合哪些场景？第一是测试。单元测试可以快速建表、插入数据、跑断言，连接关闭后自然清理，不容易污染下一条用例。第二是临时数据处理，比如导入 CSV 后在内存里去重、聚合，再把结果写回磁盘库。第三是会话内缓存，例如只在当前任务中使用的结构化中间结果。cur = conn.cursor()cur.execute('CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)')cur.execute('INSERT INTO users(name) VALUES (?)', ('Alice',))conn.commit()如果缓存丢失会影响业务正确性，就不要只放内存数据库。它更像一张可以用 SQL 操作的临时工作台，而不是稳定的存储层。还有一种常见用法是命令行工具：启动后加载少量配置和输入数据，处理完输出结果，整个过程不留下临时文件，失败后也可以从源数据重新跑。多连接能共享吗？普通 :memory: 属于单个连接。两个连接都写 :memory:，得到的是两个完全不同的数据库。测试里常见的坑是初始化表用一个连接，业务代码用另一个连接，于是报 no such table。如果确实需要多连接共享，可以使用 URI，并保持至少一个连接存活：conn1 = sqlite3.connect('file:memdb1?mode=memory&cache=shared', uri=True)conn2 = sqlite3.connect('file:memdb1?mode=memory&cache=shared', uri=True)共享模式的取舍是协作方便，但生命周期更敏感。最后一个连接关闭后，数据库仍然会消失；连接池如果自动回收全部连接，也会把数据一起清掉。如何和磁盘库配合？可以在内存连接里 ATTACH 磁盘数据库，把正式数据读进来处理，再写回去。这个方式适合一次性任务，不适合长期承担核心存储链路。ATTACH DATABASE 'app.db' AS disk;CREATE TABLE temp_result ASSELECT user_id, COUNT(*) AS order_countFROM disk.ordersGROUP BY user_id;INSERT INTO disk.user_stats(user_id, order_count)SELECT user_id, order_count FROM temp_result;如果处理结果需要保存，可以在连接还活着时使用 backup API 或命令行备份。不要等进程退出后再想恢复，内存库没有文件可找。sqlite3 ":memory:" ".backup backup.db"追问:memory: 能被多个连接共享吗？普通 :memory: 不能共享，每个连接都是独立数据库。要共享需要 file:memdb1?mode=memory&cache=shared 这样的 URI。取舍是共享模式便于多连接测试，但必须管理连接生命周期。踩坑点是 ORM 自动新建连接，导致查不到初始化好的表。内存数据库适合生产缓存吗？适合做进程内临时缓存，不适合做必须恢复的核心缓存。它的优势是 SQL 能力完整，筛选和聚合方便。取舍是重启即丢，跨进程共享也麻烦。边界是缓存丢了不能影响业务正确性。为什么用了内存数据库还是慢？瓶颈可能不在磁盘，而在 SQL 和提交方式。逐条提交、缺少索引、循环查询都会拖慢内存库。取舍是内存库减少 I/O，但不能替代查询优化。常见踩坑是忘记用事务批量写入。内存数据库关闭后还能恢复吗？没有提前备份就不能恢复。可以在连接存活时用 backup API 保存到文件。取舍是备份能保留结果，但流程更复杂。边界很简单：重要数据从一开始就应该写磁盘库。

SQLite FTS5 解决什么问题？SQLite FTS5 是内置的全文搜索模块，适合在本地数据库里搜索文章、笔记、日志、商品名和离线文档。它和 LIKE '%关键词%' 不一样：LIKE 经常扫表，FTS5 会把文本拆成 token 并建立倒排索引，所以长文本检索更快。代价也明显，FTS 表会多占磁盘，写入时还要维护索引；如果只是按 id、状态、邮箱精确查询，普通索引更合适。如何创建和查询 FTS 表？CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);INSERT INTO articles_fts(title, content)VALUES ('SQLite Tutorial', 'SQLite is a lightweight database engine.');SELECT rowid, titleFROM articles_ftsWHERE articles_fts MATCH 'SQLite';短语搜索要加双引号，前缀搜索用 *，布尔搜索可以写 AND、OR、NOT。但不要把用户输入原样拼进 MATCH，特殊字符会触发语法错误，也可能让用户构造出你没预期的查询。实际项目通常会限制搜索语法，或者把普通关键词和高级搜索分开处理。SELECT rowid, title FROM articles_ftsWHERE articles_fts MATCH '"lightweight database"';SELECT rowid, title FROM articles_ftsWHERE articles_fts MATCH 'data*';外部内容表怎么同步？更常见的做法是普通表保存真实数据，FTS 表只保存索引。这样业务字段、约束和状态仍然在主表里，搜索结果再通过 rowid 回表。CREATE TABLE articles( id INTEGER PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL);CREATE VIRTUAL TABLE articles_fts USING fts5( title, content, content='articles', content_rowid='id');INSERT INTO articles_fts(articles_fts) VALUES('rebuild');后续要用触发器同步插入、更新和删除。最容易踩坑的是只更新主表，忘了更新 FTS 索引，搜索结果就会出现旧内容或幽灵数据。CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN INSERT INTO articles_fts(rowid,title,content) VALUES(new.id,new.title,new.content);END;排名和优化怎么做？FTS5 可以用 bm25() 排序，分数通常越小越相关。页面展示时可以用 snippet() 截取摘要，用 highlight() 标记命中词，但输出到前端前仍要考虑 XSS。SELECT a.id, a.title, bm25(articles_fts) AS rankFROM articles_fts JOIN articles a ON a.id = articles_fts.rowidWHERE articles_fts MATCH 'sqlite search'ORDER BY rankLIMIT 20;INSERT INTO articles_fts(articles_fts) VALUES('optimize');中文搜索是边界问题。SQLite 默认 tokenizer 更适合英文，中文没有天然空格，可能需要应用层分词后写入搜索字段。FTS5 适合单机、嵌入式、中小型搜索；如果要拼写纠错、同义词、多节点和复杂权限过滤，就该考虑专门搜索引擎。还有一个实际边界是备份和迁移：FTS 虚拟表可以重建，真正重要的是主表数据和建表 SQL。大库迁移时先导入主表，再执行 rebuild，通常比搬运一份已经膨胀的索引更稳。追问FTS5 和 LIKE 查询有什么区别？FTS5 建倒排索引，适合长文本关键词检索；LIKE '%词%' 很容易扫表。取舍是 FTS5 写入更重，也会额外占空间。边界是短字段精确查询不必用 FTS。踩坑是把所有模糊查询都迁到 FTS，反而让简单查询变复杂。外部内容表为什么要触发器？外部内容表不会自动和主表同步。触发器能在插入、更新、删除时维护索引，避免搜索结果过期。取舍是同步自动化了，但写入路径更难调试。最常见的坑是更新时没先删除旧索引，导致命中重复或内容错位。SQLite FTS5 能直接做好中文搜索吗？能用，但默认效果通常不如英文。中文分词需要额外处理，可以在应用层分词后写入搜索字段。取舍是实现更复杂，但召回更稳定。边界是如果还要拼音、纠错、同义词，FTS5 会比较吃力。bm25 分数越高越相关吗？在 SQLite FTS5 里通常不是，bm25() 分数越小越相关。这个细节用反后，排序会把不相关内容排前面。取舍是 bm25 很轻量，但不懂业务热度和发布时间。实际项目常把 bm25 和业务权重一起计算。

SQLite 的安全防护不能只盯着一条 PRAGMA key。它是嵌入式数据库，没有内置账号、角色和授权语句，也不会像服务端数据库那样替你隔离网络访问。真正的安全边界通常在三层：数据库文件谁能读、应用代码如何执行 SQL、密钥和备份怎么管理。只要数据库文件能被随便拷走，SQL 写得再漂亮也挡不住离线分析。追问SQLite 本身有没有用户权限控制？没有传统意义上的 CREATE USER、GRANT、REVOKE，权限模型要放在应用层和文件系统层。SQLite 的定位是进程内数据库文件，优势是部署简单、依赖少，代价是它不负责多用户访问控制。取舍是服务端应用必须自己做鉴权，系统层面限制数据库文件只能被应用用户读取。常见踩坑是把 .db 文件放在 Web 静态目录、共享下载目录或移动端可直接导出的位置。chmod 600 app.dbchown appuser:appuser app.db数据库文件要不要加密？如果数据库可能离开受控服务器，或者运行在桌面端、移动端、边缘设备上，加密就很有必要。常见方案是 SQLCipher，打开数据库后先设置密钥，再访问表。边界在于加密保护的是“文件被拷走后的离线读取”，不能阻止已经拿到应用进程权限的人通过正常接口读数据。密钥不要硬编码在仓库里，移动端应放 Keychain、Keystore，服务端应放环境变量或密钥管理系统。PRAGMA key = '从安全存储读取的密钥';PRAGMA cipher_page_size = 4096;PRAGMA kdf_iter = 256000;怎么防 SQL 注入？所有外部输入都应该走参数化查询，不要用字符串拼接 SQL。SQLite 支持预编译语句，Python、Node、Go、Java 的驱动也都有绑定参数的能力。取舍是动态表名、排序字段这类位置不能直接绑定参数，需要用白名单映射。最常见的坑是 WHERE 条件用了参数化，但 ORDER BY、LIMIT 或表名又拼回去了，攻击面仍然存在。cursor.execute("SELECT id, name FROM users WHERE email = ?", (email,))allowed_sort = {"created": "created_at", "name": "name"}sort_col = allowed_sort.get(sort_key, "created_at")cursor.execute(f"SELECT id, name FROM users ORDER BY {sort_col} LIMIT ?", (limit,))哪些 PRAGMA 配置值得开启？foreign_keys 应该在每个连接打开后启用，否则外键约束可能不会按预期生效。secure_delete 可以让删除的数据被覆盖，适合包含敏感信息的库，但会带来额外写入成本。可用版本里建议关闭 trusted_schema，减少恶意 schema 影响应用自定义函数的风险。边界是 PRAGMA 只能补强数据库行为，不能替代权限设计、参数化查询和输入校验。PRAGMA foreign_keys = ON;PRAGMA secure_delete = ON;PRAGMA trusted_schema = OFF;PRAGMA journal_mode = WAL;备份、WAL 和日志会泄露数据吗？会，而且经常比主库更容易被忽略。SQLite 使用 WAL 模式时，除了 app.db，还会有 app.db-wal、app.db-shm，备份和权限检查要把它们一起算进去。取舍是 WAL 并发读写体验更好，但文件数量增加，运维脚本不能只复制一个 db 文件。日志里也不要打印完整 SQL 参数，尤其是 token、手机号、身份证号和地址。sqlite3 app.db ".backup 'backup/app.db'"chmod 600 backup/app.dbSQLite 安全不是一个开关，而是文件权限、加密、参数化查询、约束、备份和密钥管理的组合。服务端项目优先守住文件和应用层权限，客户端项目优先减少敏感数据落盘。这样即使某一层出问题，也不至于让整个数据库裸奔。

SQLite 触发器是在表或视图发生 INSERT、UPDATE、DELETE 时自动执行的一段 SQL。它常用来做审计日志、轻量数据校验、维护冗余字段，或者配合视图实现写入转发。好处是规则离数据很近，不容易被某个业务入口漏掉；坏处是逻辑藏在数据库里，排查问题时经常被忽略。实际项目里，触发器适合放稳定、短小、强约束的逻辑，不适合承载复杂业务流程。CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, updated_at TEXT);CREATE TABLE audit_log(id INTEGER PRIMARY KEY, table_name TEXT, op TEXT, row_id INTEGER, created_at TEXT);CREATE TRIGGER users_audit_updateAFTER UPDATE ON usersFOR EACH ROWBEGIN INSERT INTO audit_log(table_name, op, row_id, created_at) VALUES ('users', 'UPDATE', NEW.id, datetime('now'));END;追问BEFORE 和 AFTER 触发器怎么选？BEFORE 更适合做校验，发现非法数据就阻止写入；AFTER 更适合做日志、同步、汇总这类依赖最终写入结果的动作。SQLite 里不要照搬其他数据库的写法，例如 SET NEW.updated_at = ... 不是合法语法。取舍是能用 CHECK、外键、唯一索引解决的，优先用内置约束。否则规则分散在约束、触发器和业务代码里，后期很难判断到底是谁拦住了写入。CREATE TRIGGER users_name_requiredBEFORE INSERT ON usersFOR EACH ROWWHEN NEW.name IS NULL OR length(trim(NEW.name)) = 0BEGIN SELECT RAISE(ABORT, 'name is required');END;如何用触发器维护更新时间？SQLite 不能在 BEFORE UPDATE 里直接给 NEW.updated_at 赋值，很多人会在这里踩坑。可用做法是用 AFTER UPDATE 再更新同一行，但一定要加 WHEN 条件，避免无意义的循环更新。这个方案简单，代价是一次业务更新会多一次写操作。高写入频率场景下，更建议应用层显式写入 updated_at，触发器只作为兜底。CREATE TRIGGER users_touch_updated_atAFTER UPDATE ON usersFOR EACH ROWWHEN NEW.updated_at IS OLD.updated_atBEGIN UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;END;触发器适合做审计日志吗？适合，但要控制日志内容和写入成本。触发器做审计的优势是所有入口都会被记录，不管写入来自接口、脚本还是迁移工具。取舍在于日志表会不断变大，影响备份体积和查询性能。建议只记录主键、操作类型、关键字段差异和时间，不要把整行大字段都塞进去。CREATE TRIGGER users_audit_deleteAFTER DELETE ON usersFOR EACH ROWBEGIN INSERT INTO audit_log(table_name, op, row_id, created_at) VALUES ('users', 'DELETE', OLD.id, datetime('now'));END;触发器会不会递归执行？SQLite 有递归触发器开关，可以用 PRAGMA recursive_triggers 查看或设置，但项目里不要依赖模糊默认值。更稳的方式是在触发器上写清楚 WHEN 条件，让它只在必要时执行。边界是触发器里更新同一张表最容易形成循环，尤其是维护计数、更新时间这类逻辑。只要出现“触发器更新表，更新又触发同一个触发器”，就应该重新审视设计。PRAGMA recursive_triggers = OFF;SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';DROP TRIGGER IF EXISTS users_audit_update;什么时候不应该用触发器？如果逻辑依赖外部服务、权限上下文、复杂分支或用户操作意图，就不适合放进触发器。触发器看不到完整业务语义，只看到某一行数据发生了变化。短期把复杂业务藏进去会少写代码，长期会让调试变成猜谜。比较稳的边界是：数据库完整性和审计可以放触发器，业务流程编排留在应用层。SQLite 触发器最好小而明确：校验就校验，审计就审计，别顺手塞进一整段业务。它能让数据规则更可靠，也可能让问题更隐蔽，关键在于把边界画清楚。

SQLite 视图是把一段 SELECT 查询保存成一个可复用的虚拟表。它不存储查询结果，只保存查询定义；每次查询视图时，SQLite 都会展开这段 SQL，再去读取底层表。它适合封装复杂 JOIN、统一统计口径、隐藏敏感字段，也适合给应用层一个更稳定的查询接口。但要先说清楚：普通视图不是缓存，底层查询慢，视图通常也不会变快。CREATE VIEW user_order_summary ASSELECT u.id, u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amountFROM users uLEFT JOIN orders o ON o.user_id = u.idWHERE u.status = 'active'GROUP BY u.id, u.name;SELECT * FROM user_order_summary WHERE order_count >= 3;追问视图和表有什么区别？表保存真实数据，视图只保存查询定义，这是最核心的区别。查询视图时，SQLite 会把它当成一段可复用 SQL 来执行，所以性能主要取决于底层表、索引和查询条件。取舍在于视图让代码更清楚、更稳定，但不能替代合理建表和索引。常见踩坑是把大聚合视图当报表缓存，结果页面每打开一次就重新扫一遍订单表。SQLite 视图能不能更新？默认不能直接对普通视图执行 INSERT、UPDATE、DELETE，因为 SQLite 不一定知道这些写入该落到哪张表。简单场景可以用 INSTEAD OF 触发器，把对视图的写入转发到底层表。这个方案适合字段映射明确的单表视图，不适合带聚合、分组或复杂 JOIN 的视图。边界很清楚：一旦视图里出现 GROUP BY、COUNT() 这类结果，更新语义就容易变得含糊。CREATE VIEW active_user_names ASSELECT id, name FROM users WHERE status = 'active';CREATE TRIGGER active_user_names_updateINSTEAD OF UPDATE ON active_user_namesBEGIN UPDATE users SET name = NEW.name WHERE id = OLD.id;END;视图能提升查询性能吗？普通 SQLite 视图通常不能提升性能，因为它不是物化视图，也不会自动保存中间结果。真正决定速度的是底层 SQL 能不能走索引，例如 JOIN 字段、WHERE 字段、排序字段是否建了合适索引。取舍是视图负责抽象和复用，性能优化仍要回到底层查询计划。若高频统计确实很慢，可以维护一张汇总表，但这会带来同步和一致性成本。CREATE INDEX idx_orders_user_id ON orders(user_id);CREATE INDEX idx_users_status ON users(status);什么时候适合用视图隐藏字段？当应用只需要公开信息，而不应该读取密码哈希、手机号、内部备注时，视图很有用。比如只暴露 id、name、avatar_url，查询代码天然看不到敏感列。要注意 SQLite 没有内置用户权限体系，视图本身不是完整安全边界。真正的边界还要靠数据库文件权限、应用层鉴权和接口校验配合。CREATE VIEW public_users ASSELECT id, name, avatar_url FROM users WHERE deleted_at IS NULL;修改视图定义要注意什么？SQLite 没有通用的 ALTER VIEW，通常要先 DROP VIEW 再重新 CREATE VIEW。这很直接，但要先检查依赖它的查询、触发器和迁移脚本。取舍是重建最快，风险是旧代码可能还在读取被删除的列。稳妥做法是先新增兼容视图或保留旧列，等应用发布完成后再清理。SQLite 视图适合解决“SQL 太散、字段不该暴露、查询口径要统一”的问题。它让数据库接口更干净，但不会神奇地让慢查询变快；只要记住这一点，视图通常会是一个很稳的抽象层。

SQLite 连接池要先问“该不该用”，再问“怎么实现”。SQLite 是嵌入式数据库，数据库引擎就在应用进程里，连接本质上是打开本地文件和维护一组状态，并不像 MySQL、PostgreSQL 那样要跨网络建立远程会话。连接池真正有价值的场景，是 Web 服务、多线程任务或频繁短连接脚本需要统一管理连接、事务、超时和关闭流程。它能减少连接管理混乱，但解决不了 SQLite 的单写者并发边界；连接越多，写冲突有时反而越明显。import sqlite3from queue import Queue, Emptyclass SQLitePool: def __init__(self, path, size=4): self.pool = Queue(maxsize=size) for _ in range(size): conn = sqlite3.connect(path, timeout=5, check_same_thread=False) conn.execute('PRAGMA journal_mode=WAL') conn.execute('PRAGMA foreign_keys=ON') self.pool.put(conn)如果只是为了减少样板代码，也可以不写完整连接池，而是写一个连接管理器，在进程启动时创建连接、退出时关闭。连接池适合有明确并发任务和资源上限的服务，不适合给所有 SQLite 项目套模板。判断标准很现实：没有观测到连接创建成本、泄漏风险或并发等待，就先保持简单。如果部署在网络文件系统上，还要额外谨慎，SQLite 对文件锁语义很敏感，连接池无法修复底层锁实现不可靠的问题。连接池还要配合监控使用，至少记录获取连接等待时间、池中空闲数量、写事务耗时和锁错误次数。没有这些指标时，调大池子或调长超时都只是猜。对嵌入式应用来说，简单稳定往往比抽象完整更重要，连接池必须服务于明确问题，而不是为了看起来像服务端架构。追问SQLite 为什么不像服务端数据库那样天然需要连接池？SQLite 没有独立数据库服务器，连接成本通常比远程数据库低很多，很多桌面应用、移动应用或 CLI 工具，一个连接配合事务就够了。连接池的收益更多来自资源治理：限制并发连接数、统一 PRAGMA、避免频繁打开关闭、集中处理异常。取舍点在应用形态，单线程脚本加连接池是过度设计，多线程 Web API 每个请求都开关连接则可能需要池化。边界是连接池不能把 SQLite 变成高并发写数据库，同一时间写事务仍然会排队。踩坑是照搬服务端数据库经验，把池子开很大，最后得到的不是吞吐提升，而是更多锁等待。连接池大小应该怎么设置？不要按请求并发数机械设置连接数，SQLite 不是连接越多越快。读多写少并启用 WAL 时，可以让多个读连接并发工作，但写入仍然需要控制事务长度和排队方式。一个常见起点是 2 到 8 个连接，然后根据等待时间、database is locked 频率和查询耗时调整。取舍是小池子更容易暴露排队，大池子更容易制造锁竞争和资源占用。踩坑是没有设置获取连接超时，池子耗尽后请求一直卡住，看起来像数据库慢，其实是连接泄漏或事务太长。def acquire(pool, timeout=2): try: return pool.get(timeout=timeout) except Empty: raise TimeoutError('SQLite connection pool exhausted')def release(pool, conn): pool.put(conn)多线程使用 SQLite 连接有哪些坑？Python 的 sqlite3 默认限制连接只能在创建它的线程使用，所以不少示例会设置 check_same_thread=False。但这只是取消驱动层检查，不代表同一个连接可以被多个线程同时随便操作。更稳的方式是一个连接同一时刻只借给一个请求或任务，事务期间不让连接流转，用完立刻归还。取舍是这种封装会多一些代码，但能换来清晰的所有权和异常边界。边界也要承认：如果业务需要大量并发写入、复杂事务隔离和连接级权限管理，迁移到服务端数据库通常比硬调 SQLite 连接池更省事。如何避免连接泄漏和脏事务？连接池最怕借出去后没有归还，或者异常发生时事务没回滚就回到池里。获取连接后要用 try/finally 保证释放，执行写操作时要明确 commit 或 rollback。归还前可以检查连接状态，必要时回滚未完成事务，避免下一个请求接到“带着上个请求现场”的连接。踩坑是把连接对象传到很多层函数里，异常路径越来越多，最后没人确定谁负责释放。比较稳的做法是封装成上下文管理器，让借还、提交、回滚都集中在一个地方。from contextlib import contextmanager@contextmanagerdef pooled_conn(pool): conn = acquire(pool) try: yield conn conn.commit() except Exception: conn.rollback() raise finally: release(pool, conn)WAL、busy_timeout 和连接池怎么配合？WAL 模式能让读写并发体验更好，读连接通常不阻塞写连接追加日志，适合服务型应用。busy_timeout 或连接参数里的 timeout 可以让遇到锁时等待一会儿，而不是立刻抛出 database is locked。取舍是等待不是万能药，写事务太长时只会把请求堆起来，用户看到的就是慢。边界很明确：写事务要短，批量写入要集中，长时间计算不要占着事务和连接。踩坑是把网络请求、文件上传、复杂计算放在事务中间，连接池再大也会被慢事务拖住。

SQLite 预编译语句的核心作用有两个：减少重复解析 SQL，以及把 SQL 结构和外部输入分开。它不是“性能优化时才用的高级技巧”，而是写数据库代码时应该默认采用的方式。只要 SQL 里有用户输入，就不要拼字符串；只要同一条 SQL 会反复执行，就应该让驱动或底层 SQLite 复用 prepared statement。这样能降低 SQL 注入风险，也能让批量写入、循环查询和日志记录更稳定。import sqlite3conn = sqlite3.connect('app.db')conn.execute('CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT, email TEXT)')stmt = 'INSERT INTO users(name, email) VALUES (?, ?)'conn.executemany(stmt, [('Alice', 'a@example.com'), ('Bob', 'b@example.com')])conn.commit()在应用框架里，很多 execute() 调用背后已经会准备语句，但这不等于可以忽略参数绑定。你仍然要检查 ORM 或数据库库最终生成的 SQL，尤其是原生查询、搜索条件和排序字段。预编译语句更像安全底线，不是性能银弹；索引设计、事务范围和错误处理同样要一起看。对高频路径来说，最好用压测数据比较普通执行、批量执行和事务批量写入的差别，别只凭感觉判断优化是否有效。另外，预编译语句不能替代输入校验，业务层仍然要检查长度、枚举值和数值范围。否则虽然 SQL 注入被挡住了，错误数据仍然会被安全地写进数据库，后面排查更麻烦。最后还要注意日志，不要把绑定参数里的敏感信息原样打印出来。安全查询如果配上不安全日志，仍然会泄露邮箱、手机号或令牌。追问预编译语句为什么能防 SQL 注入？因为 SQL 模板先被解析，参数值后绑定，用户输入不会再被当成 SQL 语法解释。比如输入里带引号、分号或 OR 1=1，参数绑定会把它当成普通值，而不是条件表达式的一部分。取舍是它只能保护“值”的位置，不能直接绑定表名、列名、排序方向这类 SQL 结构。踩坑是很多人把 WHERE name = ? 用对了，却把 ORDER BY {user_input} 拼进去，这仍然有风险。结构部分要用白名单映射，而不是寄希望于参数绑定替你处理所有动态 SQL。allowed_sort = {'name': 'name', 'created': 'created_at'}sort_col = allowed_sort.get(user_sort, 'created_at')rows = conn.execute(f'SELECT * FROM users ORDER BY {sort_col} DESC').fetchall()问号占位符和命名占位符怎么选？问号占位符最简单，适合参数少、顺序一眼能看清的 SQL。命名占位符适合条件多、同一个参数出现多次，或者团队希望 SQL 可读性更强的场景。取舍点是维护成本：问号写错顺序不容易被肉眼发现，命名参数多写一点字但更清楚。边界是不同语言驱动支持的占位符风格不完全一样，Python、Java、Node.js 的写法不能随便混用。踩坑是复制示例时只改了 SQL，没改参数结构，运行时才发现绑定数量或名称对不上。conn.execute( 'SELECT * FROM users WHERE name = :name AND status = :status', {'name': 'Alice', 'status': 'active'})预编译语句一定会提升性能吗？不一定，单次查询的差异通常很小，因为真正耗时可能在磁盘 I/O、索引选择或返回大量结果。它在循环执行、批量插入和高频短查询里更有价值，可以减少重复解析和编译 SQL 的开销。取舍是别为了“预编译”缓存一堆很少使用的语句，长期占资源还增加管理复杂度。更关键的是配合事务使用批量写入，否则每插一行提交一次，预编译也救不了性能。踩坑是只优化语句对象，不看索引和事务，最后瓶颈还在原地。with conn: conn.executemany( 'INSERT INTO logs(level, message) VALUES (?, ?)', [('info', 'start'), ('warn', 'slow query')] )C API 里的 prepare、bind、step、reset 是什么关系？sqlite3_prepare_v2() 把 SQL 编译成语句对象，sqlite3_bind_*() 给占位符绑定参数，sqlite3_step() 执行并逐行返回结果。执行完如果还要复用同一个语句，需要 sqlite3_reset() 回到可执行状态，必要时再 sqlite3_clear_bindings() 清掉旧参数。最后必须 sqlite3_finalize() 释放资源，否则长时间运行的进程会出现句柄泄漏。取舍是底层 API 给了更细的控制，但也要求你自己管理生命周期。边界是语句对象通常不要跨线程乱传，尤其是连接线程模式和编译选项不明确时，按连接隔离使用更稳。sqlite3_stmt *stmt;sqlite3_prepare_v2(db, "SELECT name FROM users WHERE id=?", -1, &stmt, 0);sqlite3_bind_int(stmt, 1, 42);if (sqlite3_step(stmt) == SQLITE_ROW) { const unsigned char *name = sqlite3_column_text(stmt, 0);}sqlite3_finalize(stmt);使用预编译语句还有哪些常见坑？第一个坑是把 LIKE '%?%' 写成带问号的字符串，结果占位符不会生效，正确做法是把 % 放进参数值里。第二个坑是动态 IN 条件，不能把一个逗号拼接字符串当成单个参数传进去，需要按元素生成对应数量的占位符。第三个坑是批量写入不包事务，性能差到像没优化一样。取舍是动态 SQL 有时无法完全避免，但要把“结构白名单”和“值参数绑定”分开处理。边界很简单：预编译语句负责安全绑定值，不负责替你判断哪些 SQL 结构可以暴露给用户。

SQLite JSON 扩展适合处理“结构有变化，但还没必要拆成一堆表”的数据，比如配置、埋点属性、第三方回调、用户偏好。它让 SQLite 能直接创建、校验、提取和修改 JSON，但不要因此把 SQLite 当成完整的文档数据库。更稳的设计是：原始 JSON 保留一份，稳定且高频查询的字段提成列或生成列，真正的关系和约束仍然交给普通表结构。这样既能接住变化，也不会让每次查询都在一大段文本里反复解析。CREATE TABLE webhooks ( id INTEGER PRIMARY KEY, payload TEXT NOT NULL CHECK (json_valid(payload)), event_type TEXT GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED);CREATE INDEX idx_webhooks_event_type ON webhooks(event_type);实际落地时，可以把 JSON 字段分成三类：原样留存的审计数据、偶尔展示的扩展属性、必须参与查询的业务字段。第一类只要校验合法性，第二类用函数读取即可，第三类最好拆列或用生成列加索引。这个分层能避免两个极端：一边是过早把所有字段拆得很碎，另一边是所有查询都压在 json_extract 上。还有一个边界是兼容性，线上环境、移动端系统库和开发机的 SQLite 版本可能不一致，JSON 操作符和函数支持范围要提前确认。这点很容易漏掉。追问SQLite 里的 JSON 到底是什么类型？大多数情况下，JSON 在 SQLite 里仍然以 TEXT 保存，JSON 函数负责校验、解析和提取。它不像某些服务端数据库的 JSONB 那样天然带有专门存储格式和复杂索引能力，所以不能期待任意深层路径查询都很快。取舍在数据规模和访问方式：少量配置、低频展示没问题；百万级事件表按 JSON 字段筛选，就要提前设计索引。踩坑是没有加 CHECK (json_valid(payload))，脏数据进入表后，某个后台任务执行到 json_extract 才突然失败。边界也很清楚：需要外键、唯一约束、范围查询的核心字段，不要长期只放在 JSON 里。json_extract、-> 和 ->> 应该怎么选？json_extract() 最通用，路径写法明确，兼容大量旧代码和示例。->、->> 写起来更短，风格接近其他数据库，->> 更适合拿标量文本值来比较或展示。取舍主要看 SQLite 版本和团队习惯：如果要兼容旧环境，用函数更稳；如果版本可控，操作符能让简单查询更清爽。踩坑是返回值类型容易被忽略，JSON 字符串、数字和 JSON 文本在比较时并不总是按你想的方式转换。建议在关键过滤条件里显式保持类型一致，必要时用 CAST 或生成列固定下来。SELECT json_extract(payload, '$.user.id') AS user_id, payload ->> '$.type' AS event_typeFROM webhooksWHERE payload ->> '$.type' = 'payment.succeeded';JSON 数组怎么展开查询？数组通常用 json_each() 或 json_tree() 展开，它们会把 JSON 内容临时变成可查询的行。json_each() 适合展开一层数组或对象，json_tree() 会递归遍历更深结构，但成本也更高。取舍是查询表达力和性能：临时分析可以直接展开，线上接口最好先用普通条件缩小范围再展开。踩坑是对大表无条件展开数组，一行十个标签就会把结果放大十倍，排序和分页都可能变慢。边界是高频标签、分类、权限这类字段，如果经常被筛选，拆成关联表通常比每次展开 JSON 更可靠。SELECT w.id, tag.value AS tagFROM webhooks AS w, json_each(w.payload, '$.tags') AS tagWHERE w.event_type = 'article.updated' AND tag.value = 'sqlite';JSON 字段过滤慢应该怎么优化？先确认这个字段是否稳定、是否真的高频查询。如果稳定，可以用生成列提取并建索引；如果只是内部优化，也可以建表达式索引。生成列的好处是字段含义清楚，多处查询能复用；表达式索引少一个可见列，但要求查询表达式和索引表达式保持一致。踩坑是路径、类型转换或函数写法稍有不同，查询计划可能不用索引，所以优化后一定要看 EXPLAIN QUERY PLAN。边界是 JSON 深层结构频繁变化时，过早建很多索引会拖慢写入，还会让迁移非常麻烦。CREATE INDEX idx_webhooks_user_idON webhooks(json_extract(payload, '$.user.id'));EXPLAIN QUERY PLANSELECT * FROM webhooksWHERE json_extract(payload, '$.user.id') = 1001;什么时候不该把数据塞进 JSON？强关系、强约束、高频 JOIN 的数据不适合长期放在 JSON 里，比如订单金额、库存数量、用户主键关系。JSON 很适合接住变化快的边缘字段，但不适合替代表设计，否则后面会在校验、索引、统计和迁移上付出更多成本。取舍可以按问题判断：这个字段要不要被约束、排序、聚合、关联？如果答案经常是“要”，它就应该是列或关联表。踩坑是早期为了省事全塞 JSON，后期报表和风控要查时，只能在一堆文本函数里补性能。把 JSON 当缓冲区，而不是当所有数据的最终归宿，通常更稳。

SQLite 生成列适合把“总是由同一行其他字段推出来”的值写进表结构，比如订单总价、拼接后的姓名、从 JSON 里提取出来的状态。它不是为了少写一行查询，而是为了让计算规则固定在数据库层，避免应用服务、后台脚本、数据导出工具各算一遍还算出不同结果。使用前先问两个问题：这个值是否只依赖本行字段？这个值是否会被频繁查询、过滤或排序？如果两个答案都是“是”，生成列通常值得考虑；如果答案里出现当前时间、其他表、权限上下文或复杂业务状态，就不要勉强塞进去。CREATE TABLE orders ( id INTEGER PRIMARY KEY, price NUMERIC NOT NULL, quantity INTEGER NOT NULL, total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED);CREATE INDEX idx_orders_total ON orders(total);还有一个容易被忽略的细节：生成列适合承载稳定规则，不适合承载临时补丁。上线前最好用几条边界数据验证表达式，比如空字符串、NULL、负数、异常 JSON 路径。这样可以提前发现类型转换和约束冲突，避免迁移后才发现历史数据无法写入新表。追问VIRTUAL 和 STORED 到底怎么选？VIRTUAL 不把结果落盘，每次读取时重新计算，所以表文件更小，写入路径也更轻。STORED 会在写入或更新依赖列时计算并保存结果，查询时像普通列一样读取，更适合高频过滤、排序和索引。取舍点在读写比例：订单报表经常按 total 查区间，STORED 更稳；资料页偶尔展示 first_name || ' ' || last_name，VIRTUAL 就够。踩坑是 STORED 不是“可手动修正的缓存列”，它仍然不能直接 INSERT 或 UPDATE，算错了只能改源字段、改表达式或做表迁移。另一个边界是磁盘空间，移动端本地库如果有大量派生文本，STORED 可能会让数据库膨胀得很快。生成列表达式有哪些限制？生成列表达式必须是确定性的，同样输入应该得到同样输出，所以 random()、datetime('now') 这类函数不适合放进去。它不能包含子查询、聚合函数，也不能跨行引用数据，因为生成列描述的是“本行如何派生出另一个字段”。有些版本和资料对引用其他生成列的描述容易让人误会，工程里最好让生成列直接引用基础列，迁移和排查都更简单。边界判断可以很朴素：如果这个值要看别的表、统计多行、依赖当前时间，改用视图、触发器、普通列加应用逻辑会更安全。踩坑是把复杂业务规则藏进 schema 后，代码评审只看应用层很难发现真正的计算来源。生成列能不能优化 JSON 查询？能，而且这是 SQLite 里很实用的用法。直接在 WHERE json_extract(data,'$.status') = 'paid' 上过滤，数据量上来后通常会反复解析 JSON；把稳定字段提成生成列，再建索引，查询计划更清楚。CREATE TABLE events ( id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK (json_valid(data)), status TEXT GENERATED ALWAYS AS (json_extract(data, '$.status')) STORED);CREATE INDEX idx_events_status ON events(status);取舍是灵活性会下降，JSON 路径一改，生成列表达式、索引和历史数据迁移都要跟着处理。适合提取状态、用户 ID、事件类型这类少数稳定字段，不适合把一整份 JSON 拆成几十个生成列。实际项目里还要注意类型，JSON 里数字和字符串混用时，索引命中和比较结果可能让人意外。生成列可以后期随便修改吗？不能把它当普通字段那样随手改表达式。SQLite 的 ALTER TABLE 能力有限，很多生成列变更需要新建表、复制基础列、重建索引和约束，再替换旧表。这个过程最好放在事务里，并在升级前备份；移动端或桌面端应用尤其要考虑升级中断后如何恢复。踩坑是复制数据时把生成列也写进 INSERT 列表，SQLite 会拒绝，因为生成列的值应该由表达式自动计算。边界是大表迁移会锁库并消耗时间，如果只是临时查询需求，视图或表达式索引可能比改表更轻。BEGIN;CREATE TABLE orders_new ( id INTEGER PRIMARY KEY, price NUMERIC NOT NULL, quantity INTEGER NOT NULL, total NUMERIC GENERATED ALWAYS AS (price * quantity * 1.1) STORED);INSERT INTO orders_new(id, price, quantity)SELECT id, price, quantity FROM orders;DROP TABLE orders;ALTER TABLE orders_new RENAME TO orders;COMMIT;项目里什么时候不该用生成列？如果业务规则经常变，生成列会把变化推进数据库迁移流程，成本可能比应用层计算更高。涉及汇率、权限、库存快照、用户时区这类上下文不同结果不同的值，也不适合做生成列，因为同一行在不同场景下可能不该只有一个答案。另一个坑是为了“看起来规范”把所有派生字段都做成生成列，结果表结构越来越厚，新增需求都要改 schema。比较稳的边界是：规则稳定、只依赖本行、经常查询，并且团队愿意把它当长期维护的 schema。这样用生成列，它会减少重复逻辑；反过来用，它会变成隐藏业务规则的地方。

SQLite 没有专门的日期时间类型，这是理解它日期函数的第一步。你可以把时间存成 TEXT、INTEGER 或 REAL，然后用 date()、time()、datetime()、julianday()、strftime()、unixepoch() 这些函数处理。最常见、也最容易维护的做法，是把业务时间统一存成 UTC Unix 时间戳，或者存成 ISO 8601 格式的 TEXT。关键不是哪一种绝对最好，而是全项目统一。混用本地时间、UTC、字符串和秒级时间戳，后面查 bug 会非常痛苦，尤其跨设备同步时很难判断到底是谁转换错了。SQLite 支持哪些时间表示？TEXT 通常写成 YYYY-MM-DD HH:MM:SS，可读性好，按字典序排序也能和时间顺序一致。INTEGER 通常存 Unix 时间戳，计算和比较方便，适合移动端同步、过期判断。REAL 存 Julian day，日常业务较少直接使用。CREATE TABLE events ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, start_at INTEGER NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now')));CREATE INDEX idx_events_start_at ON events(start_at);如果你经常和服务端交换数据，INTEGER 秒级或毫秒级时间戳更稳定；如果运营、排查、手工导出很多，TEXT 可读性更好。无论选哪种，都要写进团队约定。常用日期函数怎么用？date() 返回日期，time() 返回时间，datetime() 返回日期时间，strftime() 负责格式化。now 表示当前时间，SQLite 内部按 UTC 处理，再根据修饰符转换。SELECT date('now');SELECT time('now');SELECT datetime('now');SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');SELECT unixepoch('now');日期计算通过修饰符完成：SELECT date('now', '+7 days');SELECT datetime('now', '+3 hours', '-30 minutes');SELECT julianday('now') - julianday('2026-01-01');SELECT datetime(1739869440, 'unixepoch');julianday() 适合算两个时间之间差多少天，unixepoch() 更适合和后端接口、移动端本地存储配合。查询日期范围怎么写才走索引？最常见的性能坑，是在 WHERE 里给列套函数。比如下面这种写法直观，但可能让索引失效：-- 不推荐：对列调用 date()SELECT * FROM ordersWHERE date(order_date) = date('now');更好的做法是把范围边界算好，再直接比较列：-- TEXT 日期SELECT * FROM ordersWHERE order_date >= datetime('now', 'start of day') AND order_date < datetime('now', 'start of day', '+1 day');-- INTEGER 时间戳SELECT * FROM ordersWHERE paid_at >= unixepoch('now', 'start of day') AND paid_at < unixepoch('now', 'start of day', '+1 day');这样数据库更容易使用 order_date 或 paid_at 上的索引。数据量小的时候差距不明显，等订单表涨到几十万行，这个写法差距会很明显。时区应该怎么处理？SQLite 的 datetime('now') 返回 UTC 时间。localtime 会转成本地时区，但移动端、服务器、用户设备的本地时区可能不同。业务存储层建议统一 UTC，展示层再转本地时间。SELECT datetime('now'); -- UTCSELECT datetime('now', 'localtime'); -- 本地时间SELECT datetime('now', '+8 hours'); -- 固定偏移，不等于完整时区规则不要把 +8 hours 当成时区系统。它只是固定偏移，处理不了夏令时，也不知道历史时区变更。真正复杂的时区展示，交给应用层的时间库更稳。生成列能不能帮忙优化？如果你存的是完整时间，但经常按日期查，可以考虑生成列或额外冗余一个日期字段。这样既保留完整时间，又能让日期查询走索引。CREATE TABLE orders ( id INTEGER PRIMARY KEY, paid_at TEXT NOT NULL, paid_date TEXT GENERATED ALWAYS AS (date(paid_at)) STORED);CREATE INDEX idx_orders_paid_date ON orders(paid_date);取舍点是写入复杂度和存储空间。高频查询、低频写入的表很适合；高频写入的日志表就要评估额外索引带来的成本。追问SQLite 日期应该存 TEXT 还是 INTEGER？TEXT 可读性好，排查和导出方便，格式统一时也能排序。INTEGER 时间戳比较和计算更直接，适合移动端同步、过期判断、跨语言传输。取舍看谁是主要消费者：人经常看就偏 TEXT，程序高频算就偏 INTEGER。边界是必须统一时区和精度，秒和毫秒混用会制造很隐蔽的 bug。为什么不建议在 WHERE 里写 date(column)？因为对列调用函数后，数据库往往不能直接使用普通索引，只能先算每一行的日期再比较。数据少时看不出来，数据多时会变成全表扫描。更好的做法是把目标日期转换成起止范围，再让列和常量比较。这个坑在“查今天订单”“查本月记录”里非常常见。UTC、本地时间和 +8 hours 有什么区别？UTC 是统一存储基准，本地时间是根据运行环境的时区规则转换出来的结果。+8 hours 只是固定加 8 小时，不理解地区、夏令时和历史规则。业务存储建议用 UTC，展示时用应用层按用户时区格式化。踩坑点是服务端和客户端都以为自己存的是本地时间，最后同一条记录相差 8 小时。如何计算两个时间相差多少天？如果是 TEXT 日期，可以用 julianday(end) - julianday(start)。如果是 INTEGER 时间戳，可以直接相减再除以 86400。取舍在于精度和可读性：日期报表用 julianday 方便，秒级倒计时用时间戳更清楚。边界是跨夏令时的“自然日”和“24 小时”不是同一件事，业务要先定义清楚。生成列适合所有日期查询吗？不适合。生成列适合某个派生值被频繁查询，比如 paid_date、月份、年份。它会增加写入成本和存储空间，低频查询没必要为了它复杂化表结构。另一个边界是 SQLite 版本：生成列需要较新的 SQLite，移动端要确认系统内置版本或随包 SQLite 是否支持。上线前别只在开发机 SQLite 版本上测试。

SQLite 窗口函数适合做“保留明细行的统计”。普通 GROUP BY 会把多行压成一行，而窗口函数会在每一行旁边补一个计算结果。比如每个员工仍然是一行，但旁边多出部门排名、部门平均工资、累计销售额。SQLite 从 3.25.0 开始支持窗口函数。它常用于报表、排行榜、增长率、移动平均、每组 Top N。只要你发现自己为了“既要明细又要汇总”写了很多自连接，窗口函数就值得考虑。它能把原本拆成多条 SQL 的报表逻辑收回到一条查询里，既减少应用层循环，也更容易让数据库统一排序和过滤。基本语法怎么看？窗口函数写在 OVER (...) 后面，里面通常有三件事：按什么分组、按什么排序、窗口范围有多大。SELECT department, name, salary, RANK() OVER ( PARTITION BY department ORDER BY salary DESC ) AS dept_rankFROM employees;PARTITION BY department 表示每个部门单独排名，ORDER BY salary DESC 表示按工资从高到低。它不会减少行数，所以很适合列表页和分析报表。ROWNUMBER、RANK、DENSERANK 怎么选？三个排名函数最容易混。ROW_NUMBER 不管分数是否相同，都给连续序号；RANK 遇到并列会跳号；DENSE_RANK 遇到并列不跳号。SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_no, RANK() OVER (ORDER BY score DESC) AS rank_no, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_noFROM students;如果你要“取每个分类前 3 条记录”，通常用 ROW_NUMBER，因为它能保证每组最多 3 行。如果业务要求并列第三都算第三名，那就用 RANK 或 DENSE_RANK，但结果行数可能超过 3。LAG 和 LEAD 怎么算环比？LAG 取前一行，LEAD 取后一行。它们常用来计算增长额、增长率、状态变化。SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND( (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2 ) AS growth_rateFROM monthly_revenue;这里用了 NULLIF(..., 0)，否则上一期收入为 0 时会出现除零问题。第一行没有上一期，结果是 NULL，这是正常边界，不要随手填 0，除非业务明确要求。窗口帧决定了累计还是移动窗口帧用来控制当前行能看到哪些行。累计求和通常从第一行到当前行，移动平均则看当前行和前几行。SELECT date, sales, SUM(sales) OVER ( ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total, AVG(sales) OVER ( ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3FROM sales_data;ROWS 按物理行数算，RANGE 按排序值范围算。多数日常报表用 ROWS 更直观，尤其日期可能缺天、金额可能重复时，RANGE 的结果容易和预期不一样。性能优化要从排序和分区下手窗口函数通常离不开排序，排序就是成本。优先给 PARTITION BY 和 ORDER BY 中的列设计复合索引。比如部门内按工资排名，可以考虑 (department, salary DESC)。CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);但索引不是万能的。窗口函数如果要扫大量行，仍然会消耗内存和临时排序空间。报表类 SQL 可以先用 CTE 缩小数据范围，再做窗口计算；实时接口则要谨慎，别把复杂分析直接放在高并发路径上。追问窗口函数和 GROUP BY 最大区别是什么？GROUP BY 会改变结果粒度，把多行聚合成一行；窗口函数保留原始行，只是在每行旁边加计算结果。要看部门平均工资和员工明细，用窗口函数更自然。要只看每个部门一个汇总值，用 GROUP BY 更简单。踩坑点是把窗口函数当 GROUP BY 用，结果重复行很多，还以为数据错了。每组 Top N 应该用 RANK 还是 ROW_NUMBER？如果业务要求每组严格返回 N 条，用 ROW_NUMBER。如果并列名次都要保留，用 RANK 或 DENSE_RANK。取舍在于“行数稳定”还是“排名语义准确”。排行榜页面经常要保留并列，后台批处理取样通常更需要固定行数。为什么窗口函数里 ORDER BY 很重要？没有稳定排序，排名、前后行、累计值都可能不稳定。即使按日期排序，也要考虑同一天多条记录的情况，必要时加上主键做第二排序字段。边界是 LAG 和 LEAD：排序不唯一时，上一行到底是哪一行可能每次执行都不一样。生产 SQL 里不要依赖数据库“刚好按插入顺序返回”。移动平均为什么推荐 ROWS 而不是 RANGE？ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 明确表示当前行和前两行，共最多三行。RANGE 按排序值范围算，遇到重复日期、重复金额时会把同值行一起纳入，结果可能突然变大。取舍上，按最近 N 条记录算就用 ROWS，按最近 N 天或金额区间算才考虑 RANGE。很多报表误差就来自把这两个概念混在一起。窗口函数慢了怎么排查？先用 EXPLAIN QUERY PLAN 看是否出现大范围扫描和临时排序。再检查过滤条件能否提前放进 CTE 或子查询，减少参与窗口计算的行数。必要时给分区和排序列加复合索引，但别为了一个低频报表加太多索引拖慢写入。边界是超大数据分析，SQLite 可以做，但不一定应该在 App 或接口请求里实时做。

SQLite 的 CTE，也就是公用表表达式，最直接的价值是把一段复杂查询拆成几个有名字的中间结果。它只在当前 SQL 语句里有效，不会真的创建一张持久表。SQLite 从 3.8.3 开始支持 CTE，后续版本还补充了更多优化能力。如果一个 SQL 已经套了两三层子查询，或者你需要写递归查询，CTE 往往比继续堆括号更好读。但它不是性能灵药，有些场景会被优化器内联，有些场景可能被物化，真正上线前还是要看查询计划。尤其在移动端或嵌入式场景里，少一次无谓扫描就能少一次明显卡顿。基础 CTE 怎么写？最基本的写法是 WITH 名称 AS (...) SELECT ...。比如先算每个部门的平均工资，再筛出高于 60000 的部门：WITH dept_avg AS ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department)SELECT department, avg_salaryFROM dept_avgWHERE avg_salary > 60000ORDER BY avg_salary DESC;这段 SQL 用子查询也能写，但 CTE 的好处是中间结果有名字，读代码的人不用在嵌套括号里来回找。多个 CTE 可以连续定义，后面的 CTE 能引用前面的 CTE。WITH paid_orders AS ( SELECT * FROM orders WHERE status = 'paid' ), user_total AS ( SELECT user_id, SUM(amount) AS total FROM paid_orders GROUP BY user_id )SELECT * FROM user_total WHERE total >= 1000;递归 CTE 能解决什么问题？递归 CTE 用 WITH RECURSIVE，通常由两部分组成：锚点查询负责给出起始行，递归查询负责一层层往下找。组织架构、分类树、菜单树、图路径、数字序列都可以用它。WITH RECURSIVE org(id, name, manager_id, depth) AS ( SELECT id, name, manager_id, 0 FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, org.depth + 1 FROM employees e JOIN org ON e.manager_id = org.id WHERE org.depth < 10)SELECT * FROM org ORDER BY depth, id;这里的 WHERE org.depth < 10 不是装饰，它是安全边界。真实数据里可能有环，比如 A 的上级是 B，B 的上级又被错误地改成 A。没有终止条件，递归查询会消耗大量资源。CTE 和子查询怎么取舍？如果查询只用一次、逻辑也短，子查询没问题。如果中间结果会被多处引用，或者你希望按步骤表达业务逻辑，CTE 更清楚。CTE 还适合和窗口函数配合，比如先汇总月销售额，再计算移动平均。WITH monthly_sales AS ( SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY month)SELECT month, total, AVG(total) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avgFROM monthly_sales;如果数据量很大，并且中间结果会被反复使用，临时表有时比 CTE 更合适。临时表可以建索引，也能拆开执行和排查；代价是多了写入和生命周期管理。写 CTE 时有哪些边界？CTE 只在当前语句中可见，下一条 SQL 就不能用了。递归 CTE 必须有明确停止条件。SQLite 的递归查询默认也有深度限制，别把它当成无限图计算引擎。还有一个常见误解：用了 CTE 就一定更快。事实上，CTE 更像可读性工具。是否更快取决于索引、过滤条件、SQLite 版本和优化器决策。遇到慢查询时，用 EXPLAIN QUERY PLAN 看它是否走了预期索引，比凭感觉改写更可靠。EXPLAIN QUERY PLANWITH duplicate_emails AS ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1)SELECT u.*FROM users uJOIN duplicate_emails d ON u.email = d.email;追问CTE 会不会真的创建临时表？不一定。SQLite 可能把 CTE 内联成子查询，也可能选择物化成临时结果，取决于版本、引用次数和查询形态。不要把 CTE 理解成固定的临时表语法，它更像给优化器的一段命名查询。边界是性能敏感 SQL：如果你依赖某种执行方式，必须看 EXPLAIN QUERY PLAN，不能只看语法。递归 CTE 为什么一定要写终止条件？递归查询会一轮接一轮地产生新行，如果数据有环或条件写错，就可能跑很久。终止条件可以是深度限制、路径去重、目标节点命中，也可以组合使用。比如图遍历时常用 instr(path, next_id) = 0 避免回到旧节点。踩坑点是 ID 为数字时拼接字符串容易误判，生产场景最好加分隔符。CTE 和视图有什么区别？CTE 只在单条 SQL 中存在，适合一次性拆分复杂查询。视图是数据库对象，适合多个查询复用同一段逻辑。取舍上，如果逻辑和当前查询强绑定，用 CTE；如果多处都要用，并且希望统一维护，用视图。边界是视图会隐藏复杂度，排查性能问题时可能比显式 CTE 更绕。大数据量下 CTE 慢怎么办？先看查询计划，确认过滤条件和 JOIN 条件有没有走索引。再判断中间结果是否过大，是否需要把高选择性的过滤提前。若同一个中间结果被多次引用，可以考虑落到临时表并加索引。不要盲目把所有子查询改成 CTE，很多慢 SQL 的根因是缺索引或排序范围太大。递归 CTE 能不能用来做图最短路径？可以做简单路径探索，但不适合复杂的大规模图算法。SQLite 不是图数据库，递归 CTE 缺少专门的剪枝和优先队列机制，数据量上来后会很吃力。小型权限树、菜单树、分类树没问题；社交关系、路线规划这类场景要谨慎。真正需要最短路径时，更建议在应用层或专门的图计算工具里处理。

SQLite 在移动开发里常见，是因为它刚好踩中了移动端本地数据的几个硬需求：不需要单独部署服务、文件就是数据库、离线可用、系统和生态支持成熟。iOS、Android、Flutter、React Native 都能用 SQLite，只是上层封装不同。它适合保存用户资料、缓存列表、草稿箱、离线队列、搜索索引和少量分析数据。它不适合替代服务端数据库，也不适合在多设备之间自动解决复杂冲突。移动端用 SQLite 的关键，不是会不会 CREATE TABLE，而是能不能处理升级、并发、同步和数据安全。表结构应该怎么设计？移动端数据库要尽量围绕页面和同步模型设计。过度规范化会让查询依赖很多 JOIN，在低端设备上更容易卡顿；完全反规范化又会让更新和冲突处理很痛苦。比较稳的做法是：核心实体单独建表，列表页常用字段适度冗余。CREATE TABLE notes ( id TEXT PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, updated_at INTEGER NOT NULL, sync_state TEXT NOT NULL DEFAULT 'pending', deleted_at INTEGER);CREATE INDEX idx_notes_updated ON notes(updated_at);CREATE INDEX idx_notes_sync ON notes(sync_state);时间字段建议用 Unix 时间戳或标准 ISO 字符串，并全项目统一。同步状态不要靠“有没有上传过”猜，明确记录 pending、synced、failed 会省很多排查时间。移动端写入为什么必须重视事务？单条插入没问题，批量写入如果不用事务，SQLite 会频繁落盘，速度差距可能非常明显。事务还能保证应用崩溃、电量耗尽时不留下半批数据。db.beginTransaction()try { val stmt = db.compileStatement( "INSERT OR REPLACE INTO notes(id,title,body,updated_at,sync_state) VALUES(?,?,?,?,?)" ) for (note in notes) { stmt.bindString(1, note.id) stmt.bindString(2, note.title) stmt.bindString(3, note.body) stmt.bindLong(4, note.updatedAt) stmt.bindString(5, "synced") stmt.executeInsert() stmt.clearBindings() } db.setTransactionSuccessful()} finally { db.endTransaction()}不要用字符串拼接 SQL，移动端一样会遇到引号、换行、emoji、恶意输入。预编译语句既安全，也能减少重复解析 SQL 的开销。离线同步怎么做才不乱？最简单的模型是“本地先写，后台再同步”。用户编辑时先写 SQLite，并把记录标为 pending；网络恢复后上传变更，成功后标为 synced。如果服务端返回更新后的版本号或时间戳，也要一起落库。冲突处理要提前定规则。常见方案有：服务端时间戳覆盖、本地优先、字段级合并、保留冲突副本。选择哪一种取决于业务：购物车可以合并数量，文档编辑不能简单覆盖，金融类数据则更不应该在端上自行合并。性能和稳定性要看哪些点？开启 WAL 模式可以改善读写并发，尤其是一边读列表、一边后台同步的场景。分页查询要避免大 OFFSET，数据量上来后可以改成基于游标的分页。PRAGMA journal_mode = WAL;SELECT * FROM notesWHERE updated_at < ?ORDER BY updated_at DESCLIMIT 30;索引不是越多越好。读多写少的缓存表可以多建一点索引，写入频繁的事件表则要克制。每个索引都会增加写入成本和数据库体积，移动端存储空间、闪存寿命、冷启动时间都要考虑。数据安全和升级怎么处理？敏感数据不要明文裸放。可以用系统 Keychain/Keystore 保存密钥，再配合 SQLCipher 等方案加密数据库。注意密钥轮换、备份恢复和性能开销，别只在 demo 里跑通就上线。版本升级必须写成可重复、可追踪的迁移脚本。Android 的 onUpgrade、iOS 的迁移框架或 Room/GRDB 的 migration 都应该按版本递增执行，不能简单 DROP TABLE。线上用户可能从很旧的版本直接升级到最新版本，跳版本迁移是移动端最常见的坑。追问SQLite、Room/Core Data、Realm 应该怎么选？直接用 SQLite 控制力最强，但样板代码多，适合对 SQL 和迁移有明确要求的团队。Room、GRDB 这类封装保留 SQL 能力，同时减少游标和对象映射代码，是多数业务 App 的折中选择。Core Data 和 Realm 更偏对象模型，开发体验好，但复杂查询、跨端一致性和迁移细节要额外评估。取舍关键不是谁更先进，而是团队是否能长期维护它的迁移和调试成本。移动端为什么常建议开启 WAL？WAL 让读和写更容易并行，后台同步写入时，前台列表读取不容易被锁住。代价是会多出 wal/shm 文件，数据库备份、导出和清理时要一起考虑。低端设备或异常退出后，WAL 文件可能短时间变大，需要合理 checkpoint。不要只开 PRAGMA journal_mode=WAL 就不管，最好观察真实设备上的文件大小和锁等待。离线同步冲突应该在端上解决还是服务端解决？简单业务可以端上先做本地体验，最终以服务端规则为准。复杂业务最好让服务端生成权威版本，客户端只负责展示冲突和提交用户选择。边界在于数据是否可逆、是否涉及金额或权限：越重要的数据，越不能让客户端“猜”。常见踩坑是用 updated_at 谁大谁赢，结果用户在两个设备上分别编辑不同字段，后保存的把先保存的全部覆盖。数据库升级最危险的地方是什么？最危险的是只测试相邻版本升级，没有测试跨版本升级。真实用户可能半年没更新，一次从 v3 升到 v9，中间每个迁移都要能顺序执行。另一个坑是迁移脚本里改了列名或索引，却没有处理旧数据默认值，导致新版本查询直接崩溃。上线前至少准备几份老版本数据库样本跑自动化迁移测试。日期和主键在移动端有什么讲究？跨端同步时，自增整数主键容易和服务端 ID、其他设备数据冲突，客户端生成 UUID 或雪花类 ID 更稳。日期字段要统一 UTC 时间戳或 ISO 字符串，不要一会儿本地时区、一会儿服务端时区。踩坑最多的是夏令时和手动改系统时间，排序、过期判断、增量同步都会受影响。业务上真正需要可信时间时，应以服务端时间为准。

SQLite 扩展机制的核心作用，是在不改 SQLite 源码的前提下，把业务需要的函数、聚合、排序规则或虚拟表挂进数据库引擎。常见场景包括：给 SQL 增加一个字符串清洗函数、接入全文搜索或空间索引、把外部文件伪装成表查询，甚至接入加密、压缩、地理计算这类 SQLite 默认不负责的能力。扩展有哪些使用方式？SQLite 扩展主要有两种接入方式：动态加载和静态链接。动态加载适合桌面工具、内部脚本、可控的服务端环境；静态链接适合移动端、嵌入式或安全策略比较严格的应用。动态加载通常这样写：SELECT load_extension('/path/to/my_extension');SELECT my_function('hello');不少运行环境默认会关闭扩展加载，因为它本质上是在进程里加载本地动态库。命令行工具可以用 .load，C API 里要显式调用 sqlite3_enable_load_extension(db, 1)。如果是线上应用，别把扩展路径暴露给用户输入，否则很容易变成安全事故。自定义标量函数怎么写？标量函数是一进一出或多进一出的函数，最容易落地。下面这个例子把字符串转成小写，重点是入口函数名必须符合约定：sqlite3_xxx_init。#include <sqlite3ext.h>SQLITE_EXTENSION_INIT1static void lower_safe(sqlite3_context *ctx, int argc, sqlite3_value **argv) { if (argc != 1 || sqlite3_value_type(argv[0]) == SQLITE_NULL) { sqlite3_result_null(ctx); return; } const unsigned char *s = sqlite3_value_text(argv[0]); char buf[256]; snprintf(buf, sizeof(buf), "%s", s); for (char *p = buf; *p; ++p) *p = (char)tolower(*p); sqlite3_result_text(ctx, buf, -1, SQLITE_TRANSIENT);}int sqlite3_myext_init(sqlite3 *db, char **err, const sqlite3_api_routines *api) { SQLITE_EXTENSION_INIT2(api); return sqlite3_create_function(db, "lower_safe", 1, SQLITE_UTF8, 0, lower_safe, 0, 0);}实际开发里要注意两个坑：一是返回字符串时别把栈内存交给 SQLite 后马上失效，通常用 SQLITE_TRANSIENT；二是参数类型要检查，NULL、BLOB、非法 UTF-8 都可能出现。聚合函数和虚拟表适合什么场景？聚合函数适合“多行合成一个结果”，比如自定义中位数、百分位数、业务评分。它由 step 和 final 两段组成，step 逐行收集状态，final 输出结果。typedef struct { double sum; int n; } AvgCtx;static void avg_step(sqlite3_context *ctx, int argc, sqlite3_value **argv) { AvgCtx *p = sqlite3_aggregate_context(ctx, sizeof(AvgCtx)); if (sqlite3_value_type(argv[0]) != SQLITE_NULL) { p->sum += sqlite3_value_double(argv[0]); p->n++; }}static void avg_final(sqlite3_context *ctx) { AvgCtx *p = sqlite3_aggregate_context(ctx, 0); p && p->n ? sqlite3_result_double(ctx, p->sum / p->n) : sqlite3_result_null(ctx);}虚拟表更重，它不是补一个函数，而是实现一套表接口。FTS5、RTREE、CSV 虚拟表都属于这个方向。只有当你确实想把外部数据源当表查，或者需要深度参与查询计划时，才值得写虚拟表；普通业务逻辑用函数或预处理数据更省心。常用内置扩展怎么取舍？FTS5 适合全文搜索，JSON1 适合处理 JSON 字段，RTREE 适合二维范围检索，STAT4 能改善部分复杂查询的统计信息。不要一看到扩展就自己写 C，SQLite 已经内置或可编译开启的扩展优先级更高。移动端尤其要谨慎。iOS 和 Android 对动态库加载、包体大小、审核策略都有约束，很多团队会选择静态编译 SQLite，并在构建阶段打开需要的扩展。服务端脚本则可以动态加载，但要锁死扩展目录和文件白名单。追问动态加载和静态链接该怎么选？动态加载的优势是灵活，扩展可以单独升级，适合内部工具、数据清洗脚本和可控服务器。静态链接的优势是可部署性和安全性更好，适合移动端、嵌入式设备和不允许运行时加载动态库的环境。取舍点在于“是否需要运行时替换扩展”：如果不需要，静态链接通常更稳。踩坑最多的是本地能 load，线上容器或 App 沙盒里找不到动态库路径。自定义函数为什么要特别注意内存管理？SQLite 的 C API 不会替你判断返回值生命周期。比如把栈上的 char buf[256] 返回出去，如果没有用 SQLITE_TRANSIENT，调用结束后结果可能变成脏数据。更稳的做法是明确告诉 SQLite 复制结果，或者使用 SQLite 提供的内存分配函数。边界情况还包括超长输入、NULL 参数和编码不一致，这些都要在函数入口处理。什么情况下不该写 SQLite 扩展？如果逻辑只服务一两个查询，而且可以在应用层完成，通常不必写扩展。扩展会增加编译、发布、跨平台调试成本，尤其 C 代码崩溃会直接拖垮宿主进程。只有当逻辑需要贴近查询执行、复用频率很高，或者应用层处理会造成大量数据搬运时，扩展才划算。很多性能问题先用索引、CTE、临时表就能解决，不要过早上虚拟表。虚拟表开发最容易踩什么坑？虚拟表的坑主要在 xBestIndex。如果没有正确告诉优化器哪些约束可用、成本大概多少，SQLite 可能全表扫描你的外部数据源，慢到像卡死。另一个边界是事务和并发：外部数据源未必支持 SQLite 的事务语义，写入型虚拟表尤其麻烦。建议先做只读虚拟表，并用 EXPLAIN QUERY PLAN 观察查询计划。扩展会影响数据库安全性吗？会，而且影响很直接。动态扩展是本地代码，权限和宿主进程一样，不能把 load_extension 开给不可信 SQL。线上系统一般禁用任意路径加载，只允许白名单扩展，甚至直接静态链接。另一个踩坑点是 SQL 注入：即使扩展函数本身安全，拼接 SELECT load_extension(...) 的路径也可能被利用。