服务端6月1日 00:42
SQLite 有哪些核心特点？适合放在哪些项目里？SQLite 的核心特点可以用一句话概括：它把一个完整的关系型数据库做成了可嵌入的本地库。你不需要启动数据库服务器，也不需要配置账号和端口，应用直接读写一个数据库文件。这种设计让 SQLite 在移动应用、桌面软件、IoT 设备、命令行工具和小型网站里非常常见。
## 零配置和单文件是最大优势
SQLite 不需要安装服务，数据库通常就是一个 `.db` 文件。备份、复制、随应用分发都很方便，开发环境也更容易复现。比如你可以直接用命令行打开文件：
```bash
sqlite3 local.db
.tables
.schema user
```
单文件也有边界。它很适合本地磁盘，不适合多个机器通过网络文件系统同时写。网络文件锁不稳定时，轻则报锁错误，重则出现一致性风险。
## 小而完整，不等于玩具数据库
SQLite 支持事务、索引、视图、触发器、外键、窗口函数、CTE、JSON 函数和全文搜索扩展。对于很多中小型应用，它提供的能力已经足够。它的延迟很低，因为应用和数据库之间没有网络往返；查询本地小数据集时，体验往往比远程数据库更快。
```sql
PRAGMA foreign_keys = ON;
CREATE TABLE note (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX idx_note_updated ON note(updated_at DESC);
```
外键默认是否开启要看连接配置，实际项目里建议每次建连后显式设置。很多“SQLite 不可靠”的抱怨，最后都能追到约束没开、事务没包、锁没处理。
## 它适合什么，不适合什么
适合 SQLite 的场景通常有几个共同点：部署环境简单，数据主要归单个应用或单个用户所有，写入并发不高，希望减少运维依赖。移动端 App 的离线数据、本地搜索索引、浏览器插件配置、桌面软件项目文件、小型内部工具都很典型。
不适合的场景也很明确：大量用户同时写入、多个应用服务器共享同一个写库、需要细粒度数据库权限、需要内置复制和故障转移。SQLite 可以服务线上系统的一部分，但不应该被硬塞进所有数据库角色里。
## 追问
### SQLite 为什么说是 serverless？
这里的 serverless 不是云函数那个意思，而是没有独立数据库服务器进程。数据库引擎作为库链接到应用里，应用通过文件系统直接访问数据文件。好处是部署极简、延迟低，坏处是连接管理、访问权限和并发边界要由应用自己承担。取舍很清楚：少一层服务，就少一层运维，也少了一些集中治理能力。
### SQLite 的性能到底好不好？
在本地读、小事务写和中小数据量场景下，SQLite 性能很好，尤其省掉网络开销后很占优势。慢通常出现在没有索引、频繁单条提交、长事务占锁或把它放到不适合的网络存储上。批量写入时用事务包起来，差距会非常明显。边界是高并发写入和复杂跨用户查询不是它的主战场。
### 单文件数据库备份是不是直接复制就行？
没有写入时直接复制通常可以；有写入时，最好使用 SQLite 的在线备份 API，或者通过 `.backup` 命令。WAL 模式下还要注意主数据库文件、`-wal` 和 `-shm` 文件之间的一致性。踩坑点是只复制了 `.db`，漏掉 WAL 里的最新提交，恢复后发现数据少了一截。保守做法是先 checkpoint，再按推荐方式备份。
### SQLite 适合团队协作系统吗？
如果团队协作只是少量用户、低频写入、单机部署，可以评估。只要变成多实例 Web 服务、多人同时编辑、需要权限审计和高可用，就更适合 MySQL 或 PostgreSQL。SQLite 的优势是简单，不是替代所有服务器数据库。项目早期可以用它降低复杂度，但要提前想好数据增长和迁移边界。标签
Sqlite
SQLite 是一个开源软件库，它实现了独立、无服务器、零配置、事务性 SQL 数据库引擎。SQLite 是世界上部署最广泛的 SQL 数据库引擎。
服务端6月1日 00:42
SQLite 和 MySQL、PostgreSQL 有什么区别？怎么选？SQLite、MySQL、PostgreSQL 都是关系型数据库，但它们解决的问题不一样。SQLite 是嵌入式数据库，数据通常就是一个文件；MySQL 和 PostgreSQL 是客户端/服务器架构，有独立进程负责连接、权限、缓存、并发和后台维护。选型时不要只问“谁更快”，更应该问“谁更适合这个运行环境”。
## 架构差异决定使用边界
SQLite 的优势是零配置、低延迟、部署简单。应用只要能读写文件，就能打开数据库，非常适合移动端、桌面软件、浏览器扩展、本地缓存、测试环境和小型内部工具。MySQL、PostgreSQL 的优势是多用户、高并发、权限体系、复制、监控和运维生态，适合 Web 后端和多人共享业务系统。
```bash
sqlite3 app.db '.schema'
sqlite3 app.db 'SELECT count(*) FROM user;'
```
这两条命令就能直接查看 SQLite 文件里的结构和数据。换成 MySQL 或 PostgreSQL，你通常要先连接服务、认证用户，再访问指定库。
## 并发模型差异很关键
SQLite 支持多读单写，WAL 模式能让读写更少互相阻塞，但同一时间仍然只有一个写者。MySQL 和 PostgreSQL 可以通过行锁、MVCC、连接池和后台进程处理更复杂的并发写入。也就是说，SQLite 并不是“低端”，而是把复杂性让给了应用和文件系统。
```sql
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
```
如果你的系统写入频率低、主要是本地读查询，SQLite 会非常舒服。如果有大量用户同时下单、评论、发送消息，服务器数据库更稳。
## 功能和运维能力也不同
PostgreSQL 在复杂 SQL、JSON、GIS、窗口函数、扩展生态上很强；MySQL 在 Web 生态、主从复制和常见业务场景里成熟；SQLite 的强项是小而完整，支持事务、索引、触发器、视图、全文搜索扩展，但没有内置用户权限、网络服务和复杂运维能力。
备份也不同。SQLite 可以复制文件，但最好在没有写入或使用在线备份 API 时做；服务器数据库通常有 dump、复制和 PITR 方案。把 SQLite 文件放到网络文件系统上多人写，是常见也危险的坑。
## 追问
### SQLite 能不能用在线上 Web 服务？
可以，但要看写入模型。只读多、写入少的小服务、文档站、配置中心、边缘节点缓存都可以考虑 SQLite。需要持续高并发写入、复杂权限、多实例共享写库时，就不适合硬扛。取舍是 SQLite 能显著降低部署成本，但你要接受单写者和文件级运维边界。
### 为什么 PostgreSQL 更适合复杂业务？
PostgreSQL 有更完整的并发控制、查询优化器、类型系统、扩展机制和运维工具。复杂报表、地理信息、全文搜索、JSON 查询和多租户权限都能在数据库层处理得更系统。SQLite 也能做不少 SQL，但很多企业级能力需要应用自己补。边界是如果你的应用只是本地存储或轻量服务，用 PostgreSQL 反而可能增加维护负担。
### 从 SQLite 迁移到 MySQL/PostgreSQL 难吗？
中小项目通常可迁，但不要低估类型、SQL 方言和并发假设的差异。SQLite 的动态类型、`INTEGER PRIMARY KEY`、日期存储约定、部分 PRAGMA 都不能原样照搬。建议一开始就避免太多 SQLite 专属写法，关键表加清晰约束。踩坑点是迁移时才发现历史数据里混了文本数字、超长字段和不合法日期。
### 为什么很多测试环境喜欢用 SQLite？
它启动快、没有服务依赖、数据文件容易重建，很适合单元测试和本地开发。问题是测试通过不代表线上数据库一定没问题，因为锁模型、类型严格度和 SQL 方言可能不同。如果生产用 PostgreSQL，核心查询最好仍用 PostgreSQL 跑集成测试。SQLite 做测试替身的边界，是验证业务流程方便，但不能完全验证数据库行为。服务端6月1日 00:42
SQLite 为什么说没有固定数据类型？类型亲和性怎么用？SQLite 的数据类型最容易让从 MySQL、PostgreSQL 转过来的人困惑：它声明了列类型，但真正决定存储方式的往往是值本身。SQLite 把值分成五种存储类：`NULL`、`INTEGER`、`REAL`、`TEXT`、`BLOB`。列声明会产生“类型亲和性”，它会尝试转换数据，但默认不会像强类型数据库那样严格拒绝所有不匹配的值。
## 存储类和类型亲和性不是一回事
存储类描述值怎么落盘，类型亲和性描述列倾向于把值转成什么。比如声明 `INTEGER` 的列更倾向于存整数，声明 `TEXT` 的列更倾向于存字符串，但你仍可能插入看似不符合直觉的值。
```sql
CREATE TABLE demo (
id INTEGER PRIMARY KEY,
price NUMERIC,
note TEXT,
raw BLOB
);
INSERT INTO demo(price, note) VALUES ('12.30', 123);
SELECT typeof(price), typeof(note) FROM demo;
```
`NUMERIC` 可能把 `'12.30'` 转成整数或浮点，`TEXT` 可能把数字转成文本。这个灵活性让 SQLite 很适合本地缓存和格式不完全稳定的数据，但也要求应用层更自律。
## STRICT 表让类型更可控
如果你希望 SQLite 更像传统强类型数据库，可以使用 `STRICT` 表。它会让类型检查更严格，减少脏数据进入表的机会。
```sql
CREATE TABLE user_profile (
id INTEGER PRIMARY KEY,
age INTEGER NOT NULL,
name TEXT NOT NULL,
created_at TEXT NOT NULL
) STRICT;
```
`STRICT` 的取舍是清晰换灵活。新项目里，如果表结构稳定、数据要长期维护，建议优先考虑；如果是临时导入、弱结构日志或兼容旧数据，就要评估迁移成本。
## 日期、布尔值和金额怎么存
SQLite 没有独立的 `DATE`、`BOOLEAN`、`DECIMAL` 存储类，通常用约定解决。日期可以存 ISO-8601 文本、Unix 时间戳整数或 Julian day 浮点数；布尔值常用 `0/1`；金额最好用整数分、厘，避免浮点误差。
```sql
CREATE TABLE invoice (
id INTEGER PRIMARY KEY,
paid INTEGER NOT NULL CHECK (paid IN (0, 1)),
amount_cent INTEGER NOT NULL CHECK (amount_cent >= 0),
created_at TEXT NOT NULL
);
```
这里的 `CHECK` 很关键。SQLite 的灵活类型系统不代表可以放弃约束，越是长期运行的本地数据库，越需要把边界写进 schema。
## 追问
### SQLite 声明 VARCHAR(20) 会限制长度吗？
默认不会像很多人期待的那样自动限制到 20 个字符。SQLite 会识别它的 TEXT 亲和性，但括号里的长度更多是兼容 SQL 写法。要限制长度，应显式加 `CHECK (length(name) <= 20)`。踩坑点是从 MySQL 迁移表结构后，以为长度限制还在，结果线上写入了超长文本。
### INTEGER PRIMARY KEY 有什么特殊之处？
在 SQLite 里，`INTEGER PRIMARY KEY` 通常是 rowid 的别名，插入和查询都很高效。它和普通 `INT PRIMARY KEY` 不完全一样，类型名字必须准确匹配 `INTEGER` 才有这个特殊语义。`AUTOINCREMENT` 也不是必需品，它会避免复用旧 rowid，但带来额外开销。取舍是多数场景用 `INTEGER PRIMARY KEY` 就够了，只有强依赖永不复用 ID 时才考虑 `AUTOINCREMENT`。
### 金额为什么不建议用 REAL？
`REAL` 是二进制浮点数，很多十进制小数无法精确表示。用于展示统计问题不大，用于账务累计就可能出现几分钱误差。更稳的做法是用 `INTEGER` 存最小货币单位，比如分或厘，展示时再格式化。边界是科学计算或传感器数据可以用 `REAL`，但业务金额最好别赌浮点精度。
### 动态类型会不会让数据质量失控？
会，如果没有约束和应用层校验。SQLite 的灵活性适合快速迭代，但长期项目需要 `NOT NULL`、`UNIQUE`、`CHECK`、外键和 `STRICT` 表一起兜底。外键还要确认开启：`PRAGMA foreign_keys = ON;`。经验上，越靠近核心业务的表越应该严格，越靠近缓存和临时数据的表可以灵活一点。服务端6月1日 00:42
SQLite 事务是怎么工作的？BEGIN 模式该怎么选？SQLite 的事务用来保证一组操作要么全部成功，要么全部撤销。它支持 ACID，但实现方式和服务器数据库不完全一样：事务最终落在数据库文件、回滚日志或 WAL 日志、文件锁这些机制上。理解这一点很重要，因为很多线上问题不是 SQL 写错，而是事务边界太长、提交太频繁或锁模式选错。
## 一次事务从 BEGIN 到 COMMIT
最基本的事务写法很简单：
```sql
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
```
如果第二条语句失败，就应该 `ROLLBACK`，否则账户状态可能不一致。SQLite 会用日志保证回滚能力：回滚日志模式会先保存旧页面，WAL 模式会先追加新记录。两者目标一样，都是让崩溃恢复时能回到一致状态。
## 三种 BEGIN 模式的差异
`BEGIN DEFERRED` 是默认模式，开始时不立刻拿写锁，第一次读或写时再决定。`BEGIN IMMEDIATE` 会立刻获取写意向锁，避免业务执行到一半才发现无法写。`BEGIN EXCLUSIVE` 更强，会阻止其他连接读写，在 WAL 模式下表现会略有差异，但仍应谨慎使用。
```sql
BEGIN DEFERRED; -- 默认，适合普通读写
BEGIN IMMEDIATE; -- 明确要写，提前抢写锁
BEGIN EXCLUSIVE; -- 维护、迁移、离线任务慎用
```
应用代码里，`IMMEDIATE` 常用于库存扣减、计数器更新这类必须写的逻辑。它不是更高级，而是失败更早、更可控。
## 提交频率影响性能和可靠性
SQLite 每次提交都要确保事务持久化，过于频繁的单条提交会产生大量同步开销。批量导入时，最常见的优化就是把多条写入放到一个事务里。
```sql
BEGIN IMMEDIATE;
INSERT INTO event_log(type, payload) VALUES ('click', '{}');
INSERT INTO event_log(type, payload) VALUES ('view', '{}');
COMMIT;
```
但事务也不能无限大。一个包含几十万行写入的事务会长时间占用写锁，WAL 文件也可能膨胀。比较稳的做法是分批提交，比如每 500 或 1000 条一批，根据设备磁盘和业务延迟调参数。
## 追问
### SQLite 的事务隔离级别是不是和 MySQL 一样？
不完全一样。SQLite 默认提供接近串行化的隔离效果，读事务看到的是一致快照，写事务同一时间只能有一个。它没有像 MySQL 那样暴露多种行级隔离级别给你切换。取舍是模型简单、可靠，但并发写入能力不如服务器数据库。
### 什么时候应该用 SAVEPOINT？
`SAVEPOINT` 适合在大事务里做局部回滚，比如导入 1000 条记录，其中某几条格式错误不想拖垮整批。它比嵌套事务更贴近 SQLite 的实际能力，因为 SQLite 的普通 `BEGIN` 不能简单嵌套。示例是 `SAVEPOINT one; ...; ROLLBACK TO one; RELEASE one;`。踩坑点是 `ROLLBACK TO` 后还要 `RELEASE` 或继续处理外层事务，否则事务状态容易混乱。
### 事务里能不能做耗时业务逻辑？
尽量不要。事务开始后，你持有的读锁或写锁会影响其他连接，尤其是写锁会让后续写入排队。正确做法是先在事务外完成参数校验、网络请求和文件处理，事务内只执行必要 SQL。边界是某些校验必须和写入保持一致，比如唯一性检查，这时要用约束和短事务配合，而不是把整段业务都塞进去。
### COMMIT 成功就一定不会丢数据吗？
这取决于 `journal_mode`、`synchronous` 和底层文件系统。默认安全配置下，SQLite 会尽力保证提交后的持久性；但如果为了性能把同步级别调低，断电时就可能损失最近事务。WAL 加 `synchronous=NORMAL` 是常见折中，适合大多数应用缓存和本地业务数据。对强一致账务类数据，应保守配置并做崩溃恢复测试。服务端6月1日 00:42
SQLite 索引如何影响查询性能？什么时候反而会变慢？SQLite 的索引本质上是在表数据之外维护一棵 B-Tree，让查询可以少扫很多行。没有索引时，`WHERE user_id = ?` 可能要从头读到尾；有合适索引时，SQLite 可以沿着树快速定位候选记录。问题在于索引不是越多越好，它会占空间，也会拖慢写入，因为每次 `INSERT`、`UPDATE`、`DELETE` 都要同步维护索引页。
## 先用执行计划确认有没有命中
优化 SQLite 查询不要靠感觉，先看 `EXPLAIN QUERY PLAN`。它会告诉你是全表扫描，还是使用了某个索引。比如下面这个查询，如果 `orders(user_id, created_at)` 上有复合索引，通常能同时服务过滤和排序。
```sql
EXPLAIN QUERY PLAN
SELECT id, total
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
```
看到 `SCAN TABLE` 不一定就是坏事，小表全扫可能比走索引更快。真正要警惕的是百万级表上频繁出现全表扫描，或者查询返回很少数据却读了大量页面。
## 复合索引要看最左前缀
SQLite 的复合索引遵循类似“从左往右”的使用方式。`(user_id, status, created_at)` 能很好支持 `user_id = ? AND status = ?`，也能支持只按 `user_id` 查；但如果查询只有 `status = ?`，这个索引通常帮不上太多。索引列顺序要按过滤条件、选择性和排序需求一起定，不是把所有列随手堆进去。
```sql
CREATE INDEX idx_task_user_status_time
ON task(user_id, status, updated_at DESC);
```
如果 `status` 只有“open/closed”两个值，单独给它建索引可能收益很低。低选择性字段更适合放在高选择性字段后面，或者和其他条件组成复合索引。
## 覆盖索引和部分索引很实用
覆盖索引指查询需要的列都在索引里，SQLite 不必再回表读取数据页。对于高频列表页，这能明显减少 I/O。部分索引则只索引满足条件的行，适合软删除、状态过滤这类场景。
```sql
CREATE INDEX idx_article_published_list
ON 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;` 做轻量维护。踩坑点是测试库数据很少时计划很好，线上数据一多就完全不同，所以压测数据要尽量接近真实分布。
### 索引太多会带来什么问题？
首先是写入变慢，每次写表都要更新多个索引。其次是数据库文件变大，缓存命中率下降，备份和迁移也更慢。还有一个隐蔽问题是优化器可选路径太多，但并不代表每条路径都好。实践里可以定期查出重复或长期不用的索引，保留能覆盖核心查询的那几组。服务端6月1日 00:42
SQLite 如何处理并发读写？WAL 模式能解决什么问题？SQLite 的并发能力要先说清一个前提：它不是服务器数据库，而是把数据库引擎嵌进应用进程，多个连接最终都在争用同一个数据库文件。所以它的设计目标不是让几百个写请求同时冲进去，而是让读操作尽量轻、写操作尽量安全。实际项目里，只要写入不是持续高峰，SQLite 的多读单写模型通常够用；一旦把它当成 MySQL 那样承接高并发写入，就会很快遇到 `database is locked`。
## SQLite 的锁是怎么变化的
默认回滚日志模式下，SQLite 常见锁状态可以理解为从宽到严逐步升级：`SHARED` 允许多个连接读，`RESERVED` 表示某个连接准备写，`PENDING` 阻止新的读进入，`EXCLUSIVE` 才真正独占写入。读连接之间不互相阻塞，写连接之间只能排队。最容易踩坑的是长时间读事务：一个后台导出任务开着游标不关，写事务提交时就可能一直等锁。
```sql
PRAGMA journal_mode;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
```
`busy_timeout` 不是提升并发的魔法，它只是让连接在遇到锁时等一会儿，而不是立刻报错。如果写事务本身很慢，等待时间再长也只是把问题往后拖。
## WAL 为什么常被推荐
WAL（Write-Ahead Logging）把写入先追加到 `-wal` 文件，读连接可以继续读旧快照，写连接也不用立刻覆盖主数据库文件。这样读写冲突明显减少，尤其适合“读很多、偶尔写”的桌面应用、移动端缓存和小型服务。取舍也很明确：WAL 仍然只有一个写者，而且会多出 wal/shm 文件，需要定期 checkpoint。
```sql
PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA synchronous = NORMAL;
```
`synchronous=NORMAL` 在 WAL 下通常是性能和安全的折中；如果是账务落库或断电风险很敏感，仍应评估 `FULL`。不要为了跑分把可靠性配置关到最低。
## 写事务要短，连接要克制
SQLite 并发优化最有效的办法不是开更多连接，而是缩短写锁占用时间。批量写入要包进事务，避免每条语句都单独提交；但事务里不要夹杂网络请求、文件上传或复杂计算。写入前先准备好数据，拿到锁后只做数据库操作。
```sql
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 的瓶颈常常是写锁而不是连接数。移动端和桌面应用里，单写队列加多个只读连接通常更容易维护。服务端6月1日 00:42
如何优化 SQLite 性能？索引、事务和 PRAGMA 怎么用？优化 SQLite 性能，先别急着改一堆 PRAGMA。大多数慢查询来自三个地方：没有合适索引、事务拆得太碎、一次取了太多无用数据。SQLite 很快，但它不是魔法；把查询路径、写入批次和文件维护做好，通常比盲目调参数更稳定。
```sql
EXPLAIN QUERY PLAN
SELECT id, title FROM posts
WHERE user_id = ? AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;
CREATE INDEX idx_posts_user_status_time
ON 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、cache_size、temp_store 这些 PRAGMA 怎么选？
WAL 适合有并发读写的应用，纯一次性脚本不一定需要。`cache_size` 可以提升热点页命中率，但设置过大可能挤压应用内存，尤其是多进程同时打开数据库时。`temp_store=MEMORY` 能减少临时排序和中间表落盘，但大排序会吃内存。取舍原则是先用默认值跑基准，再针对瓶颈改一项测一项，不要把网上配置整段复制进生产。
### VACUUM 和 ANALYZE 什么时候执行？
`ANALYZE` 适合在大批量导入、删除或新建索引后执行，它帮助查询规划器更新统计信息。`VACUUM` 适合大量删除后回收文件体积，或者需要重建数据库文件时执行，但它成本更高，会占用时间和磁盘空间。边界是频繁 `VACUUM` 不会让普通查询神奇变快，反而可能影响正常业务。比较稳的做法是在低峰维护窗口执行，并在执行前确认有足够磁盘空间和备份。服务端6月1日 00:42
SQLite WAL 模式是什么？为什么能提升并发读写？SQLite 的 WAL，全称 Write-Ahead Logging，意思是写操作先追加到 WAL 日志文件，而不是立刻修改主数据库文件。默认回滚日志模式更像“先准备撤销方案，再改原文件”；WAL 模式则是“先把变更写到旁边日志，合适时再合并”。这个变化让读写冲突少很多：读连接继续看主库和某个时间点之前的 WAL，写连接把新内容追加到 `-wal` 文件。
```sql
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。服务端6月1日 00:42
SQLite 支持哪些约束类型？实际建表时怎么取舍？SQLite 常用约束包括 `NOT NULL`、`UNIQUE`、`PRIMARY KEY`、`FOREIGN KEY`、`CHECK` 和 `DEFAULT`。它们的价值不是让建表语句更复杂，而是在数据写入时拦住明显错误。应用层校验会受版本、入口和脚本影响，数据库约束才是最后一道防线。
```sql
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` 都要额外判断。多数业务里，这点成本低于脏数据带来的修复成本。性能敏感场景可以给外键列补索引，批量导入时使用事务，并在导入后做一致性检查。不要为了插入快就删掉约束，除非你有同等可靠的校验和修复链路。服务端6月1日 00:06
SQLite 数据库如何备份和恢复才安全？## SQLite 备份先判断是否在线
SQLite 只有一个数据库文件，看起来复制一下就能备份。但安全备份要先判断数据库是否正在写入。离线数据库直接复制通常没问题；在线数据库如果正在写事务，简单 `cp` 可能拿到不一致的文件。启用 WAL 模式时还要注意 `-wal` 和 `-shm` 文件，漏掉 WAL 可能丢最新数据。
## 什么时候可以直接复制？
如果应用已经停止，或者能保证没有写入，文件复制最简单：
```bash
cp app.db app.backup.db
```
WAL 模式下更稳的做法是先 checkpoint，再复制主库文件：
```bash
sqlite3 app.db "PRAGMA wal_checkpoint(FULL);"
cp app.db app.backup.db
```
取舍是文件复制速度快、恢复简单，但不适合持续写入的在线库。最常见的坑是用 cron 直接复制正在写的数据库，恢复时才发现文件损坏或少了一段数据。
## 在线备份用什么？
SQLite 命令行的 `.backup` 使用 backup API，可以在数据库仍可读写时得到一致备份：
```bash
sqlite3 app.db ".backup 'app.backup.db'"
```
恢复时可以在停应用后替换原文件，也可以恢复到新库：
```bash
cp app.backup.db app.db
sqlite3 restored.db ".restore 'app.backup.db'"
```
`.backup` 的优点是不用手动处理 WAL 细节，适合作为常规备份方案。代价是大库备份仍然会占 I/O，最好放在低峰期执行，并把备份文件同步到另一块磁盘或对象存储。
## `VACUUM INTO` 和 `.dump` 怎么选？
`VACUUM INTO` 会创建一个压实后的数据库文件，适合归档或清理大量删除后的碎片：
```sql
VACUUM INTO 'app.compact.backup.db';
```
它比普通备份更重，需要额外空间和 I/O，不适合分钟级高频执行。日常备份优先 `.backup`，低峰维护或归档前再考虑 `VACUUM INTO`。
`.dump` 会导出 SQL 脚本，适合审查、迁移和救援，但大库恢复会慢：
```bash
sqlite3 app.db ".dump" > app.sql
sqlite3 restored.db < app.sql
```
简单记法是：要快恢复用 `.backup`，要可读迁移用 `.dump`，要顺便压缩整理用 `VACUUM INTO`。
## 如何确认备份可用？
备份后至少跑完整性检查：
```bash
sqlite3 app.backup.db "PRAGMA integrity_check;"
```
返回 `ok` 只是第一步。更可靠的是定期恢复到临时环境，检查核心表行数、最新数据时间和关键业务查询。很多事故不是备份文件不存在，而是权限、路径、属主或恢复脚本有问题。备份方案必须包含恢复演练，否则只能算“复制文件”。
一个保守脚本可以这样写：
```bash
set -euo pipefail
DB="/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` 才算基本可用。还应定期恢复到临时库，跑关键业务查询。取舍是验证会增加耗时，但能提前发现问题。边界是文件存在不代表可以恢复。
### 备份应该保留多少份？
要看数据变更频率和能接受丢多久的数据。常见做法是保留近期高频备份，再保留每周或每月归档。取舍是保留越多越占空间，但回滚选择更多。最大坑是原库和备份都放在同一块磁盘上。服务端6月1日 00:06
SQLite 内存数据库如何使用？适合哪些场景？## SQLite 内存数据库是什么？
SQLite 内存数据库把整个数据库放在内存里运行，不写入磁盘文件。最常见的连接名是 `:memory:`，它仍然支持表、索引、事务、视图和触发器，只是连接关闭后数据就消失。这个特性很适合临时计算和测试，但不适合保存任何不能丢的数据。
```bash
sqlite3 :memory:
```
```python
import sqlite3
conn = sqlite3.connect(':memory:')
```
它通常比磁盘库快，因为少了磁盘 I/O。但别把它当成性能万能药：SQL 写得差、没有事务、缺索引、应用层循环查询，照样会慢。另一个边界是内存容量，导入大文件时必须控制数据量，否则进程可能直接被内存打爆。
## 适合哪些场景？
第一是测试。单元测试可以快速建表、插入数据、跑断言，连接关闭后自然清理，不容易污染下一条用例。第二是临时数据处理，比如导入 CSV 后在内存里去重、聚合，再把结果写回磁盘库。第三是会话内缓存，例如只在当前任务中使用的结构化中间结果。
```python
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，并保持至少一个连接存活：
```python
conn1 = sqlite3.connect('file:memdb1?mode=memory&cache=shared', uri=True)
conn2 = sqlite3.connect('file:memdb1?mode=memory&cache=shared', uri=True)
```
共享模式的取舍是协作方便，但生命周期更敏感。最后一个连接关闭后，数据库仍然会消失；连接池如果自动回收全部连接，也会把数据一起清掉。
## 如何和磁盘库配合？
可以在内存连接里 `ATTACH` 磁盘数据库，把正式数据读进来处理，再写回去。这个方式适合一次性任务，不适合长期承担核心存储链路。
```sql
ATTACH DATABASE 'app.db' AS disk;
CREATE TABLE temp_result AS
SELECT user_id, COUNT(*) AS order_count
FROM disk.orders
GROUP BY user_id;
INSERT INTO disk.user_stats(user_id, order_count)
SELECT user_id, order_count FROM temp_result;
```
如果处理结果需要保存，可以在连接还活着时使用 backup API 或命令行备份。不要等进程退出后再想恢复，内存库没有文件可找。
```bash
sqlite3 ":memory:" ".backup backup.db"
```
## 追问
### `:memory:` 能被多个连接共享吗？
普通 `:memory:` 不能共享，每个连接都是独立数据库。要共享需要 `file:memdb1?mode=memory&cache=shared` 这样的 URI。取舍是共享模式便于多连接测试，但必须管理连接生命周期。踩坑点是 ORM 自动新建连接，导致查不到初始化好的表。
### 内存数据库适合生产缓存吗？
适合做进程内临时缓存，不适合做必须恢复的核心缓存。它的优势是 SQL 能力完整，筛选和聚合方便。取舍是重启即丢，跨进程共享也麻烦。边界是缓存丢了不能影响业务正确性。
### 为什么用了内存数据库还是慢？
瓶颈可能不在磁盘，而在 SQL 和提交方式。逐条提交、缺少索引、循环查询都会拖慢内存库。取舍是内存库减少 I/O，但不能替代查询优化。常见踩坑是忘记用事务批量写入。
### 内存数据库关闭后还能恢复吗？
没有提前备份就不能恢复。可以在连接存活时用 backup API 保存到文件。取舍是备份能保留结果，但流程更复杂。边界很简单：重要数据从一开始就应该写磁盘库。服务端6月1日 00:06
SQLite FTS5 全文搜索如何建表、查询和优化？## SQLite FTS5 解决什么问题？
SQLite FTS5 是内置的全文搜索模块，适合在本地数据库里搜索文章、笔记、日志、商品名和离线文档。它和 `LIKE '%关键词%'` 不一样：LIKE 经常扫表，FTS5 会把文本拆成 token 并建立倒排索引，所以长文本检索更快。代价也明显，FTS 表会多占磁盘，写入时还要维护索引；如果只是按 id、状态、邮箱精确查询，普通索引更合适。
## 如何创建和查询 FTS 表？
```sql
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, title
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';
```
短语搜索要加双引号，前缀搜索用 `*`，布尔搜索可以写 `AND`、`OR`、`NOT`。但不要把用户输入原样拼进 `MATCH`，特殊字符会触发语法错误，也可能让用户构造出你没预期的查询。实际项目通常会限制搜索语法，或者把普通关键词和高级搜索分开处理。
```sql
SELECT rowid, title FROM articles_fts
WHERE articles_fts MATCH '"lightweight database"';
SELECT rowid, title FROM articles_fts
WHERE articles_fts MATCH 'data*';
```
## 外部内容表怎么同步？
更常见的做法是普通表保存真实数据，FTS 表只保存索引。这样业务字段、约束和状态仍然在主表里，搜索结果再通过 `rowid` 回表。
```sql
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 索引，搜索结果就会出现旧内容或幽灵数据。
```sql
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。
```sql
SELECT a.id, a.title, bm25(articles_fts) AS rank
FROM articles_fts JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'sqlite search'
ORDER BY rank
LIMIT 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 和业务权重一起计算。服务端6月1日 00:06
SQLite 数据库怎么做安全防护才够用？SQLite 的安全防护不能只盯着一条 `PRAGMA key`。它是嵌入式数据库，没有内置账号、角色和授权语句，也不会像服务端数据库那样替你隔离网络访问。真正的安全边界通常在三层：数据库文件谁能读、应用代码如何执行 SQL、密钥和备份怎么管理。只要数据库文件能被随便拷走，SQL 写得再漂亮也挡不住离线分析。
## 追问
### SQLite 本身有没有用户权限控制？
没有传统意义上的 `CREATE USER`、`GRANT`、`REVOKE`，权限模型要放在应用层和文件系统层。SQLite 的定位是进程内数据库文件，优势是部署简单、依赖少，代价是它不负责多用户访问控制。取舍是服务端应用必须自己做鉴权，系统层面限制数据库文件只能被应用用户读取。常见踩坑是把 `.db` 文件放在 Web 静态目录、共享下载目录或移动端可直接导出的位置。
```bash
chmod 600 app.db
chown appuser:appuser app.db
```
### 数据库文件要不要加密？
如果数据库可能离开受控服务器，或者运行在桌面端、移动端、边缘设备上，加密就很有必要。常见方案是 SQLCipher，打开数据库后先设置密钥，再访问表。边界在于加密保护的是“文件被拷走后的离线读取”，不能阻止已经拿到应用进程权限的人通过正常接口读数据。密钥不要硬编码在仓库里，移动端应放 Keychain、Keystore，服务端应放环境变量或密钥管理系统。
```sql
PRAGMA key = '从安全存储读取的密钥';
PRAGMA cipher_page_size = 4096;
PRAGMA kdf_iter = 256000;
```
### 怎么防 SQL 注入？
所有外部输入都应该走参数化查询，不要用字符串拼接 SQL。SQLite 支持预编译语句，Python、Node、Go、Java 的驱动也都有绑定参数的能力。取舍是动态表名、排序字段这类位置不能直接绑定参数，需要用白名单映射。最常见的坑是 WHERE 条件用了参数化，但 ORDER BY、LIMIT 或表名又拼回去了，攻击面仍然存在。
```python
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 只能补强数据库行为，不能替代权限设计、参数化查询和输入校验。
```sql
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、手机号、身份证号和地址。
```bash
sqlite3 app.db ".backup 'backup/app.db'"
chmod 600 backup/app.db
```
SQLite 安全不是一个开关，而是文件权限、加密、参数化查询、约束、备份和密钥管理的组合。服务端项目优先守住文件和应用层权限，客户端项目优先减少敏感数据落盘。这样即使某一层出问题，也不至于让整个数据库裸奔。服务端6月1日 00:06
SQLite 触发器怎么写，哪些场景要慎用？SQLite 触发器是在表或视图发生 `INSERT`、`UPDATE`、`DELETE` 时自动执行的一段 SQL。它常用来做审计日志、轻量数据校验、维护冗余字段，或者配合视图实现写入转发。好处是规则离数据很近，不容易被某个业务入口漏掉；坏处是逻辑藏在数据库里，排查问题时经常被忽略。实际项目里，触发器适合放稳定、短小、强约束的逻辑，不适合承载复杂业务流程。
```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_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
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`、外键、唯一索引解决的，优先用内置约束。否则规则分散在约束、触发器和业务代码里，后期很难判断到底是谁拦住了写入。
```sql
CREATE TRIGGER users_name_required
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.name IS NULL OR length(trim(NEW.name)) = 0
BEGIN
SELECT RAISE(ABORT, 'name is required');
END;
```
### 如何用触发器维护更新时间？
SQLite 不能在 `BEFORE UPDATE` 里直接给 `NEW.updated_at` 赋值，很多人会在这里踩坑。可用做法是用 `AFTER UPDATE` 再更新同一行，但一定要加 `WHEN` 条件，避免无意义的循环更新。这个方案简单，代价是一次业务更新会多一次写操作。高写入频率场景下，更建议应用层显式写入 `updated_at`，触发器只作为兜底。
```sql
CREATE TRIGGER users_touch_updated_at
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;
```
### 触发器适合做审计日志吗？
适合，但要控制日志内容和写入成本。触发器做审计的优势是所有入口都会被记录，不管写入来自接口、脚本还是迁移工具。取舍在于日志表会不断变大，影响备份体积和查询性能。建议只记录主键、操作类型、关键字段差异和时间，不要把整行大字段都塞进去。
```sql
CREATE TRIGGER users_audit_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log(table_name, op, row_id, created_at)
VALUES ('users', 'DELETE', OLD.id, datetime('now'));
END;
```
### 触发器会不会递归执行？
SQLite 有递归触发器开关，可以用 `PRAGMA recursive_triggers` 查看或设置，但项目里不要依赖模糊默认值。更稳的方式是在触发器上写清楚 `WHEN` 条件，让它只在必要时执行。边界是触发器里更新同一张表最容易形成循环，尤其是维护计数、更新时间这类逻辑。只要出现“触发器更新表，更新又触发同一个触发器”，就应该重新审视设计。
```sql
PRAGMA recursive_triggers = OFF;
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';
DROP TRIGGER IF EXISTS users_audit_update;
```
### 什么时候不应该用触发器？
如果逻辑依赖外部服务、权限上下文、复杂分支或用户操作意图，就不适合放进触发器。触发器看不到完整业务语义，只看到某一行数据发生了变化。短期把复杂业务藏进去会少写代码，长期会让调试变成猜谜。比较稳的边界是：数据库完整性和审计可以放触发器，业务流程编排留在应用层。
SQLite 触发器最好小而明确：校验就校验，审计就审计，别顺手塞进一整段业务。它能让数据规则更可靠，也可能让问题更隐蔽，关键在于把边界画清楚。服务端6月1日 00:06
SQLite 视图怎么用，什么时候不该用？SQLite 视图是把一段 `SELECT` 查询保存成一个可复用的虚拟表。它不存储查询结果，只保存查询定义；每次查询视图时，SQLite 都会展开这段 SQL，再去读取底层表。它适合封装复杂 JOIN、统一统计口径、隐藏敏感字段，也适合给应用层一个更稳定的查询接口。但要先说清楚：普通视图不是缓存，底层查询慢，视图通常也不会变快。
```sql
CREATE VIEW user_order_summary AS
SELECT u.id, u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE 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()` 这类结果，更新语义就容易变得含糊。
```sql
CREATE VIEW active_user_names AS
SELECT id, name FROM users WHERE status = 'active';
CREATE TRIGGER active_user_names_update
INSTEAD OF UPDATE ON active_user_names
BEGIN
UPDATE users SET name = NEW.name WHERE id = OLD.id;
END;
```
### 视图能提升查询性能吗？
普通 SQLite 视图通常不能提升性能，因为它不是物化视图，也不会自动保存中间结果。真正决定速度的是底层 SQL 能不能走索引，例如 JOIN 字段、WHERE 字段、排序字段是否建了合适索引。取舍是视图负责抽象和复用，性能优化仍要回到底层查询计划。若高频统计确实很慢，可以维护一张汇总表，但这会带来同步和一致性成本。
```sql
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_status ON users(status);
```
### 什么时候适合用视图隐藏字段？
当应用只需要公开信息，而不应该读取密码哈希、手机号、内部备注时，视图很有用。比如只暴露 `id`、`name`、`avatar_url`，查询代码天然看不到敏感列。要注意 SQLite 没有内置用户权限体系，视图本身不是完整安全边界。真正的边界还要靠数据库文件权限、应用层鉴权和接口校验配合。
```sql
CREATE VIEW public_users AS
SELECT id, name, avatar_url FROM users WHERE deleted_at IS NULL;
```
### 修改视图定义要注意什么？
SQLite 没有通用的 `ALTER VIEW`，通常要先 `DROP VIEW` 再重新 `CREATE VIEW`。这很直接，但要先检查依赖它的查询、触发器和迁移脚本。取舍是重建最快，风险是旧代码可能还在读取被删除的列。稳妥做法是先新增兼容视图或保留旧列，等应用发布完成后再清理。
SQLite 视图适合解决“SQL 太散、字段不该暴露、查询口径要统一”的问题。它让数据库接口更干净，但不会神奇地让慢查询变快；只要记住这一点，视图通常会是一个很稳的抽象层。服务端6月1日 00:06
SQLite 连接池该不该用，如何避免锁和连接泄漏？SQLite 连接池要先问“该不该用”，再问“怎么实现”。SQLite 是嵌入式数据库，数据库引擎就在应用进程里，连接本质上是打开本地文件和维护一组状态，并不像 MySQL、PostgreSQL 那样要跨网络建立远程会话。连接池真正有价值的场景，是 Web 服务、多线程任务或频繁短连接脚本需要统一管理连接、事务、超时和关闭流程。它能减少连接管理混乱，但解决不了 SQLite 的单写者并发边界；连接越多，写冲突有时反而越明显。
```python
import sqlite3
from queue import Queue, Empty
class 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` 频率和查询耗时调整。取舍是小池子更容易暴露排队，大池子更容易制造锁竞争和资源占用。踩坑是没有设置获取连接超时，池子耗尽后请求一直卡住，看起来像数据库慢，其实是连接泄漏或事务太长。
```python
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。归还前可以检查连接状态，必要时回滚未完成事务，避免下一个请求接到“带着上个请求现场”的连接。踩坑是把连接对象传到很多层函数里，异常路径越来越多，最后没人确定谁负责释放。比较稳的做法是封装成上下文管理器，让借还、提交、回滚都集中在一个地方。
```python
from contextlib import contextmanager
@contextmanager
def 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`。取舍是等待不是万能药，写事务太长时只会把请求堆起来，用户看到的就是慢。边界很明确：写事务要短，批量写入要集中，长时间计算不要占着事务和连接。踩坑是把网络请求、文件上传、复杂计算放在事务中间，连接池再大也会被慢事务拖住。服务端6月1日 00:06
SQLite 预编译语句为什么更安全，参数绑定怎么用？SQLite 预编译语句的核心作用有两个：减少重复解析 SQL，以及把 SQL 结构和外部输入分开。它不是“性能优化时才用的高级技巧”，而是写数据库代码时应该默认采用的方式。只要 SQL 里有用户输入，就不要拼字符串；只要同一条 SQL 会反复执行，就应该让驱动或底层 SQLite 复用 prepared statement。这样能降低 SQL 注入风险，也能让批量写入、循环查询和日志记录更稳定。
```python
import sqlite3
conn = 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。
```python
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，没改参数结构，运行时才发现绑定数量或名称对不上。
```python
conn.execute(
'SELECT * FROM users WHERE name = :name AND status = :status',
{'name': 'Alice', 'status': 'active'}
)
```
### 预编译语句一定会提升性能吗？
不一定，单次查询的差异通常很小，因为真正耗时可能在磁盘 I/O、索引选择或返回大量结果。它在循环执行、批量插入和高频短查询里更有价值，可以减少重复解析和编译 SQL 的开销。取舍是别为了“预编译”缓存一堆很少使用的语句，长期占资源还增加管理复杂度。更关键的是配合事务使用批量写入，否则每插一行提交一次，预编译也救不了性能。踩坑是只优化语句对象，不看索引和事务，最后瓶颈还在原地。
```python
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 给了更细的控制，但也要求你自己管理生命周期。边界是语句对象通常不要跨线程乱传，尤其是连接线程模式和编译选项不明确时，按连接隔离使用更稳。
```c
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 结构可以暴露给用户。服务端6月1日 00:06
SQLite JSON 扩展怎么用，什么时候该拆列或建索引？SQLite JSON 扩展适合处理“结构有变化，但还没必要拆成一堆表”的数据，比如配置、埋点属性、第三方回调、用户偏好。它让 SQLite 能直接创建、校验、提取和修改 JSON，但不要因此把 SQLite 当成完整的文档数据库。更稳的设计是：原始 JSON 保留一份，稳定且高频查询的字段提成列或生成列，真正的关系和约束仍然交给普通表结构。这样既能接住变化，也不会让每次查询都在一大段文本里反复解析。
```sql
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` 或生成列固定下来。
```sql
SELECT
json_extract(payload, '$.user.id') AS user_id,
payload ->> '$.type' AS event_type
FROM webhooks
WHERE payload ->> '$.type' = 'payment.succeeded';
```
### JSON 数组怎么展开查询？
数组通常用 `json_each()` 或 `json_tree()` 展开，它们会把 JSON 内容临时变成可查询的行。`json_each()` 适合展开一层数组或对象，`json_tree()` 会递归遍历更深结构，但成本也更高。取舍是查询表达力和性能：临时分析可以直接展开，线上接口最好先用普通条件缩小范围再展开。踩坑是对大表无条件展开数组，一行十个标签就会把结果放大十倍，排序和分页都可能变慢。边界是高频标签、分类、权限这类字段，如果经常被筛选，拆成关联表通常比每次展开 JSON 更可靠。
```sql
SELECT w.id, tag.value AS tag
FROM webhooks AS w, json_each(w.payload, '$.tags') AS tag
WHERE w.event_type = 'article.updated'
AND tag.value = 'sqlite';
```
### JSON 字段过滤慢应该怎么优化？
先确认这个字段是否稳定、是否真的高频查询。如果稳定，可以用生成列提取并建索引；如果只是内部优化，也可以建表达式索引。生成列的好处是字段含义清楚，多处查询能复用；表达式索引少一个可见列，但要求查询表达式和索引表达式保持一致。踩坑是路径、类型转换或函数写法稍有不同，查询计划可能不用索引，所以优化后一定要看 `EXPLAIN QUERY PLAN`。边界是 JSON 深层结构频繁变化时，过早建很多索引会拖慢写入，还会让迁移非常麻烦。
```sql
CREATE INDEX idx_webhooks_user_id
ON webhooks(json_extract(payload, '$.user.id'));
EXPLAIN QUERY PLAN
SELECT * FROM webhooks
WHERE json_extract(payload, '$.user.id') = 1001;
```
### 什么时候不该把数据塞进 JSON？
强关系、强约束、高频 JOIN 的数据不适合长期放在 JSON 里，比如订单金额、库存数量、用户主键关系。JSON 很适合接住变化快的边缘字段，但不适合替代表设计，否则后面会在校验、索引、统计和迁移上付出更多成本。取舍可以按问题判断：这个字段要不要被约束、排序、聚合、关联？如果答案经常是“要”，它就应该是列或关联表。踩坑是早期为了省事全塞 JSON，后期报表和风控要查时，只能在一堆文本函数里补性能。把 JSON 当缓冲区，而不是当所有数据的最终归宿，通常更稳。服务端6月1日 00:06
SQLite 生成列该怎么用，VIRTUAL 和 STORED 如何取舍？SQLite 生成列适合把“总是由同一行其他字段推出来”的值写进表结构，比如订单总价、拼接后的姓名、从 JSON 里提取出来的状态。它不是为了少写一行查询，而是为了让计算规则固定在数据库层，避免应用服务、后台脚本、数据导出工具各算一遍还算出不同结果。使用前先问两个问题：这个值是否只依赖本行字段？这个值是否会被频繁查询、过滤或排序？如果两个答案都是“是”，生成列通常值得考虑；如果答案里出现当前时间、其他表、权限上下文或复杂业务状态，就不要勉强塞进去。
```sql
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；把稳定字段提成生成列，再建索引，查询计划更清楚。
```sql
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 会拒绝，因为生成列的值应该由表达式自动计算。边界是大表迁移会锁库并消耗时间，如果只是临时查询需求，视图或表达式索引可能比改表更轻。
```sql
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。这样用生成列，它会减少重复逻辑；反过来用，它会变成隐藏业务规则的地方。服务端5月31日 23:58
SQLite 日期时间函数怎么用？时间存储和查询怎么避坑？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，日常业务较少直接使用。
```sql
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 处理，再根据修饰符转换。
```sql
SELECT date('now');
SELECT time('now');
SELECT datetime('now');
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
SELECT unixepoch('now');
```
日期计算通过修饰符完成：
```sql
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 里给列套函数。比如下面这种写法直观，但可能让索引失效：
```sql
-- 不推荐：对列调用 date()
SELECT * FROM orders
WHERE date(order_date) = date('now');
```
更好的做法是把范围边界算好，再直接比较列：
```sql
-- TEXT 日期
SELECT * FROM orders
WHERE order_date >= datetime('now', 'start of day')
AND order_date < datetime('now', 'start of day', '+1 day');
-- INTEGER 时间戳
SELECT * FROM orders
WHERE 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，展示层再转本地时间。
```sql
SELECT datetime('now'); -- UTC
SELECT datetime('now', 'localtime'); -- 本地时间
SELECT datetime('now', '+8 hours'); -- 固定偏移，不等于完整时区规则
```
不要把 `+8 hours` 当成时区系统。它只是固定偏移，处理不了夏令时，也不知道历史时区变更。真正复杂的时区展示，交给应用层的时间库更稳。
## 生成列能不能帮忙优化？
如果你存的是完整时间，但经常按日期查，可以考虑生成列或额外冗余一个日期字段。这样既保留完整时间，又能让日期查询走索引。
```sql
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 版本上测试。