SQLite 大概是所有数据库里最“轻”的一个但它的语法在细节上比教科书里的标准 SQL 多了不少自己的脾气。很多人第一次从 MySQL 切到 SQLite 时第一反应是“这不就是 SQL 嘛”可真到写复杂查询、做数据迁移、处理并发写入的时候才发现类型系统完全不同、ALTER TABLE 半残、并发模型另起炉灶。这篇文章我就把 SQLite 语法里容易踩坑、值得深入的那些点按实际开发顺序讲一遍从建表约束到查询优化从迁移备份到锁与事务每一条都是我在桌面工具、移动端离线存储、测试桩数据这些场景里真正用过的写法。适合刚接触嵌入式存储的新手也适合从 MySQL / PostgreSQL 迁移回来的开发者以及正在纠结“要不要为了一个小功能引重型数据库”的人。1. 先认清 SQLite 语法的底子它和普通 SQL 差在哪1.1 没有服务端语法层面就没有“用户”和“权限”SQLite 语法里没有 CREATE USER、没有 GRANT、没有端口号也没有网络连接串。它本质上是一个 C 语言库直接读写文件所以连接语法就是一条文件路径。用各种客户端工具时连接串常常写成sqlite:///path/to/your.db或者直接填.db文件路径这在习惯 MySQL 的人眼里会觉得“这也算数据库”但要说清楚一点MySQL 里那些用户、权限、事务、字符集的概念SQLite 里都用“文件”和“PRAGMA”代替了。例如字符集你根本不用配因为它内部就是 UTF-8 和 UTF-16 两种编码文本排序规则默认是 BINARY不是数据库级配置而是字段级 COLLATE NOCASE / COLLATE RTRIM。我第一次接手一个从 MySQL 迁到 SQLite 的项目时整整排查了半天大小写排序问题最后发现是忘了给字段加COLLATE NOCASE。这就是语法层面的思维转换你不再管理实例而是管理文件结构。1.2 动态类型和“类型亲和性”才是 SQLite 的灵魂标准 SQL 里INTEGER 字段就是整数塞字符串会报错。SQLite 不一样它每个字段有一个“类型亲和性”但实际存储是动态的。建表时你写CREATE TABLE t (a INT, b TEXT, c REAL)往a里塞hello它不会拒绝只会存储成文本等到计算时才按需转换。这个设计乍一看很随意实际是为了嵌入式场景的容错性但也带来了不少隐性坑。SQLite 的类型亲和规则有优先级声明类型亲和性行为特征INT / INTEGER / BIGINTINTEGER尽量转成整型存储转不了就按原样存TEXT / CHAR / CLOBTEXT存文本INTEGER亲和性不生效REAL / FLOA / DOUBLEREAL转成浮点转不了就原样存NUMERIC / DECIMALNUMERIC整型能存就存整型小数存 REAL其他原样BLOB / 无类型BLOB / NONE原样存不做转换最常见的坑是你用strftime生成的日期字符串、用 JSON 生成的文本如果碰到 NUMERIC 亲和性某些旧版本 SQLite 会把2024-01-01尝试转成数字虽然不会报错但比较时行为会很诡异。所以建表阶段就尽量把类型声明写准确别学 MySQL 时代的“万物皆 VARCHAR”到了 SQLite 里反而要“万物皆可 TEXT 或 INTEGER”。1.3 字面量、引号和大小写一个引号引发的血案SQLite 对关键字大小写不敏感select和SELECT一样这个和 MySQL 的体验接近。但字符串字面量必须用单引号WHERE name abc。如果你写了name abc大多数情况下 SQLite 会把双引号里的内容当成标识符列名结果就是no such column: abc。这个细节在迁移代码时特别容易踩。MySQL 默认允许双引号作字符串很多人写习惯了到了 SQLite 里就一片红。还有一个小坑字符串里的单引号需要转义成两个单引号WHERE name OBrien这在拼接动态 SQL 时非常容易漏。至于日期SQLite 没有真正的日期类型日期就是 TEXT、INTEGERUnix 时间戳或 REAL儒略日所以语法层面的NOW()之类是不存在的统一用datetime(now)或strftime(%s, now)。2. 建表、约束与设计一个靠谱的 schema 能省 80% 的麻烦2.1 完整建表语法NOT NULL、DEFAULT、CHECK 一个都别省SQLite 的CREATE TABLE语法比 MySQL 少了很多修饰但核心约束齐全CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, nickname TEXT DEFAULT 匿名, age INTEGER CHECK (age BETWEEN 0 AND 150), created_at TEXT NOT NULL DEFAULT (datetime(now)) );IF NOT EXISTS不是摆设在测试代码和批量初始化场景里它能避免反复执行脚本时报table already exists。CHECK约束在 SQLite 里是会被执行的这一点比很多老开发者印象中强。DEFAULT可以直接写函数表达式比如DEFAULT (lower(hex(randomblob(16))))生成随机 token不用在应用层多写一步。需要强调SQLite 默认不开启外键约束这不是语法不支持而是特性开关。每个连接执行PRAGMA foreign_keys ON;不要写在建库文件里因为它不是持久化设置是会话级设置。我见过好几个项目测试环境里外键明明写了却一直不生效问题都出在这里。2.2 INTEGER PRIMARY KEY 和 AUTOINCREMENT前者才是正道很多从其他数据库过来的人写第一张表就习惯性用id INT AUTO_INCREMENT。SQLite 里AUTOINCREMENT能用但我不推荐你滥用。原因是INTEGER PRIMARY KEY本身就会自动生成自增整数它是表内行号ROWID的别名。AUTOINCREMENT只是一个额外修饰强制“不重用已删除的 id”会多维护一个sqlite_sequence表有额外写放大。如果你删了最后几行普通INTEGER PRIMARY KEY会重用之前的最大值但业务上通常不需要关心这一点。实际使用中我基本只写id INTEGER PRIMARY KEY需要严格不重复的 ID 时另建业务编号字段。还有一个常被忽略的点只有INTEGER PRIMARY KEY必须是 INTEGER 类型且直接主键才能作为 ROWID 别名如果你写成INT PRIMARY KEYSQLite 的解析器也认但规范上建议明确写 INTEGER。如果你的主键不是单列自增而是联合主键或 UUID 文本主键SQLite 会创建一个隐藏的 ROWID 列来组织存储。这时候要不要用WITHOUT ROWID就得权衡了CREATE TABLE order_item ( order_id INTEGER NOT NULL, item_id INTEGER NOT NULL, quantity INTEGER, PRIMARY KEY (order_id, item_id) ) WITHOUT ROWID;WITHOUT ROWID适合数据量大、查询主要走主键的场景因为它把数据直接按主键顺序存在 B 树里省掉一层 ROWID 映射。缺点是没有 ROWID某些依赖_rowid_的旧代码会失效。所以我一般只在比较明确的“大维表”上启用普通业务表保持默认。2.3 UNIQUE、ON CONFLICT 和 UPSERT 的优先级逻辑SQLite 的约束冲突处理比很多数据库更显式。你可以在约束定义后直接指定冲突策略CREATE TABLE t ( code TEXT UNIQUE ON CONFLICT REPLACE, name TEXT NOT NULL ON CONFLICT FAIL );也可以在 INSERT 的时候指定整个语句的冲突策略INSERT OR IGNORE INTO t (code, name) VALUES (A, x); INSERT OR REPLACE INTO t (code, name) VALUES (A, y);冲突策略有五种ROLLBACK、ABORT、FAIL、IGNORE、REPLACE。它们的区别在于ROLLBACK会回滚整个事务ABORT只取消当前语句保留已执行部分FAIL中止当前语句并报告错误但不撤销先前修改IGNORE跳过冲突行REPLACE删除旧行后插入新行。我用INSERT OR REPLACE时特别谨慎因为它本质是 DELETE INSERT会触发触发器如果表上有的话而且会改变主键之外的字段默认值。如果你只想“更新存在的行插入不存在的行”正确姿势是ON CONFLICT DO UPDATE语法也就是标准的 UPSERTINSERT INTO user (id, name, score) VALUES (1, 张三, 90) ON CONFLICT (id) DO UPDATE SET name excluded.name, score excluded.score;这个语法里的excluded是特殊表名指代“当前想要插入的那一行”用来取新值。SQLite 3.24 之后这个语法很稳定强烈建议写成这种形式而不是INSERT OR REPLACE它能保留旧行的其他字段。3. 数据更新的写法细节增删改里的隐藏陷阱3.1 INSERT 的几种标准姿势SQLite 的 INSERT 除了常规的单行、多行写法还支持INSERT INTO ... SELECT这个在做数据迁移时极其好用INSERT INTO new_user (id, name, email) SELECT id, name, email FROM old_user WHERE deleted 0;批量插入时多行写法INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);在 SQLite 里是支持的但要注意默认情况下它在单条语句里插入 500 行以上时内部可能拆成多条事务速度提升没有你以为的那么大。真正要快应该用事务包住循环BEGIN; -- 在程序里循环 insert COMMIT;我实测过Python 的sqlite3模块里如果你不手动BEGIN每执行一条 INSERT 都会隐式开事务上千行数据速度天差地别。手动包一层事务插入速度能提几十倍。3.2 UPDATE / DELETE语法简单风险不小UPDATE t SET name x WHERE id 1;看起来人畜无害但你少了WHERE条件就变成全表更新SQLite 不像控制台工具会提醒你“影响 N 行是否继续”。所以我的习惯是高危语句先 SELECT 出影响范围再执行。SQLite 的客户端工具大多有LIMIT支持但标准的DELETE带LIMIT是不行的必须用WHERE id IN (SELECT ...)绕过去DELETE FROM user_log WHERE id IN ( SELECT id FROM user_log WHERE created_at 2024-01-01 LIMIT 1000 );这种逐批删除一方面避免单次事务锁表时间过长另一方面在嵌入式设备上也能控住 WAL 文件膨胀。3.3 参数绑定别用字符串拼 SQLSQLite 的 SQL 语句支持?、?NNN、:name、name、$name五种参数占位符。实际开发里比语法本身更重要的是绑定习惯。在 Python 里要写cur.execute(INSERT INTO user (name, age) VALUES (?, ?), (张三, 30))而不是cur.execute(fINSERT INTO user (name, age) VALUES ({name}, {age}))后者除了 SQL 注入风险还会因为字符串里的单引号、反斜杠导致各种语法错误。这里注意 SQLite 的参数绑定不受类型亲和影响Python 传None就是 NULL、传int就是 INTEGER、传bytes就是 BLOB省心很多。使用命名参数时INSERT INTO user (name, age) VALUES (:name, :age);可以让同一套语句在复杂数据组装时更可读尤其是参数特别多的场景。4. 查询写法从基础 SELECT 到递归和窗口函数4.1 SELECT 基础宁可多写几行列名别到处 SELECT *SQLite 的SELECT语法很传统但有一个明显的特色它支持SELECT * EXCEPT吗不支持。它没有 PostgreSQL 那种EXCEPT列排除语法所以你想排除某些列只能手写列名。这不是什么大问题但很多人从 PG 迁过来会不习惯。别名写法支持SELECT t.name AS n和SELECT t.name n两种。我建议统一用AS因为 SQLite 里不带AS的别名在复杂查询中容易造成歧义尤其HAVING、ORDER BY和GROUP BY混在一起时。LIMIT/OFFSET的写法上SQLite 支持LIMIT 10 OFFSET 20也支持LIMIT 20, 10MySQL 风格偏移在前。不过这个兼容语法我建议你别用可读性太差而且容易把两个参数写反。4.2 深分页优化OFFSET 撞墙后的键集分页写法经典分页LIMIT 10 OFFSET 100000在 SQLite 里不是报错而是慢。因为它要扫描并跳过前 10 万行。对于桌面工具和移动端这种数据量可控的场景偶尔用 OFFSET 没问题但如果数据量几十万起步推荐用“键集分页”SELECT * FROM user_log WHERE id :last_seen_id ORDER BY id DESC LIMIT 50;每次查询拿最后一条的 id作为下一次的:last_seen_id保证每次都走主键索引深翻页也是毫秒级。我第一次在某数据迁移工具里用这种做法时原来一次 3 秒的翻页直接降到 10ms 以内效果非常直观。4.3 WITH 和 WITH RECURSIVE别把递归当洪水猛兽SQLite 的WITH子句支持公共表表达式CTE语法和标准 SQL 基本一致WITH recent AS ( SELECT * FROM orders WHERE created_at 2024-06-01 ) SELECT customer_id, count(*) FROM recent GROUP BY customer_id;真正有杀伤力的递归写法WITH RECURSIVE可以生成序列、展开树结构WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x 1 FROM cnt WHERE x 10 ) SELECT x FROM cnt;这里有个容易错的地方递归 CTE 必须用UNION ALL而不是UNION而且递归成员的SELECT里必须引用 CTE 自身才叫递归。遇到次数限制时SQLite 默认限制 1000 层递归可以通过PRAGMA recursion_limit调整旧版本没有这个设置时就控制好业务深度。我在做“树形分类表”展开路径时常用递归 CTE 拼出层级路径比循环查库优雅太多。4.4 窗口函数不是 PostgreSQL 专属SQLite 也能用SQLite 3.25 起支持窗口函数ROW_NUMBER()、RANK()、LAG()、LEAD()、SUM() OVER (...)都能用。这个特性在日常报表里的价值极大。例如取每组最新一条SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM login_log ) WHERE rn 1;如果你的 SQLite 库文件比较老比如某些嵌入式系统自带的 3.2x 之前执行这些语法会报no such function: ROW_NUMBER。这时候要么更新系统里的 SQLite 版本要么退回子查询 MAX()的写法。5. 函数、表达式与索引让查询真正跑得快5.1 高频函数处理空值、时间、JSONSQLite 自带的函数数量不算多但高频的那几个足够覆盖业务。处理空值用COALESCE和IFNULLSELECT COALESCE(nickname, 匿名) FROM user;IFNULL(a, b)就是两个参数的COALESCE性能上没什么差别看团队习惯统一就好。时间函数里最常用的是strftime和datetime-- 按天统计订单 SELECT strftime(%Y-%m-%d, created_at) AS day, count(*) FROM orders GROUP BY day; -- 最近 7 天 SELECT * FROM events WHERE event_time datetime(now, -7 days);这里有个细节strftime(%s, now)返回的是 Unix 秒级时间戳但datetime(now)返回的是YYYY-MM-DD HH:MM:SS。很多人把这两个搞混导致WHERE ts datetime(now, -7 days)和WHERE ts strftime(%s, now) - 604800完全不是一回事前者是文本比较后者是整数比较千万别混用。JSON 支持上SQLite 从 3.38 开始默认编译 JSON 函数例如SELECT json_extract(data, $.name) FROM event_log WHERE json_valid(data);这个能力让 SQLite 可以直接处理“灵活字段”不用像老派做法那样拆表。注意 JSON 字段如果是 NULL 或者非法 JSONjson_extract返回 NULL直接用WHERE json_extract(...) xx可能漏数据建议先过滤json_valid。5.2 索引语法普通索引、唯一索引、部分索引、表达式索引SQLite 建立索引的语法很简单CREATE INDEX idx_user_email ON user(email); CREATE UNIQUE INDEX idx_user_email_unique ON user(email); CREATE INDEX idx_user_active ON user(active) WHERE active 1; CREATE INDEX idx_user_name_lower ON user(lower(name));部分索引Partial Index特别适合“大多数数据都一样”的状态字段比如只有active 1时才需要唯一保证或者只有未删除的数据需要建索引能显著减小索引体积。表达式索引适合常用函数过滤的场景比如统一小写匹配用户名。索引不是越多越好SQLite 写放大在嵌入式环境里很明显索引太多会导致插入变慢、文件变大。我的参考标准是单个表索引不超过 5 个且只针对真实慢查询建。可以通过 EXPLAIN 验证而不是靠拍脑袋。5.3 用 EXPLAIN QUERY PLAN 阅读查询计划SQLite 的查询计划可以通过EXPLAIN QUERY PLAN查看EXPLAIN QUERY PLAN SELECT * FROM user WHERE email ab.com;输出里最关键的两个词是SCAN和SEARCHSCAN表示全表扫描或索引全扫数据量大时基本就是慢查询的元凶SEARCH表示走了等值或范围搜索性能通常可接受。比如输出SEARCH user USING INDEX idx_user_email (email?)这说明成功走了索引。如果你看到SCAN user而且表里有几十万行就要考虑加索引或改写查询条件。我调优慢查询时基本就是三步先看执行计划再补索引再复测。多数问题都能在这三步里解决。5.4 PRAGMA 常用命令别只在出问题时才想起来PRAGMA 是 SQLite 特有的一类“配置型指令”语法简单但影响深远PRAGMA journal_mode WAL; -- 提升并发读性能 PRAGMA synchronous NORMAL; -- 降低 fsync 频率性能与安全平衡 PRAGMA busy_timeout 5000; -- 锁等待 5 秒 PRAGMA integrity_check; -- 完整性检查 PRAGMA table_info(user); -- 查看表结构 PRAGMA index_list(user); -- 查看索引列表 PRAGMA optimize; -- 执行优化journal_mode WAL是并发读性能的大杀器但要注意它会产生额外的-wal和-shm文件。如果你把单文件数据库拷贝到别处却忘了带上 WAL 文件数据可能不完整。所以备份和发布时一定要用正规备份语法不能只拷.db文件。5.5 什么时候不该用索引和普遍认知不同SQLite 对很小表比如几百行做全表扫描比走索引更快因为索引查询涉及两次磁盘寻址先索引页再数据页而小表整个数据页可能就在缓存里。另一个场景是频繁更新的大表如果索引列更新非常频繁维护索引的开销可能超过收益。这些都需要结合实际情况判断不要一看到SCAN就紧张。6. 数据库维护与迁移ALTER TABLE 半残的突围之道6.1 ALTER TABLE 只支持有限的几件事SQLite 的ALTER TABLE能力较弱早年只支持改表名和加列不支持删除列、修改列类型、重命名列旧版本不支持新版本 3.25 后支持 RENAME COLUMN。所以很多“教科书式”的迁移操作在 SQLite 里会直接报语法错误。官方推荐的重建表流程我整理为 12 步操作实战中照着走基本没问题先备份VACUUM INTO backup.db或.backup backup.db创建新表new_table定义你真正想要的 schema把旧数据复制过去INSERT INTO new_table (col1, col2, ...) SELECT col1, col2, ... FROM old_table;确认数据量一致SELECT count(*)对比删除旧表DROP TABLE old_table;将新表改名ALTER TABLE new_table RENAME TO old_table;重建索引因为 DROP 会把索引一并删掉重建触发器如果有重建视图如果有PRAGMA foreign_key_check;验证外键完整性PRAGMA integrity_check;验证整体完整性重新打开应用跑一轮回归这个流程看起来繁琐但这是 SQLite 的安全姿势。我经历过几次直接在线上用UPDATE改 schema 导致数据损坏的事故从那以后再也不敢图省事。凡是涉及表结构变更一律走重建流程。6.2 备份文件拷贝之外的正规语法由于 WAL 模式的存在直接拷贝.db文件可能有数据遗漏。SQLite 提供的备份语法有三种常用方式方式一VACUUM INTOVACUUM INTO backup_20240601.db;生成一个完整的、整理过的数据库副本过程不影响主库继续读写。这个语法极其好用是我日常备份的首选。方式二.backup 命令sqlite3 工具sqlite3 source.db .backup backup.db优势是可以带进度输出适合大库备份。方式三ATTACH CREATE TABLE AS SELECTATTACH DATABASE backup.db AS backup; CREATE TABLE backup.snapshot AS SELECT * FROM main.user_log; DETACH DATABASE backup;这种方式适合只备份部分表或者做库间数据搬运。但注意它默认复制数据索引不会跟着走备份完需要重新建索引。6.3 ATTACH DATABASE 的多库操作语法ATTACH 语法允许你在一个连接里挂载多个数据库文件ATTACH DATABASE file2.db AS db2; SELECT * FROM db2.config WHERE key version;这对“从旧库迁移到新库”特别有用可以在同一个连接里直接跨库插入。不过要记住事务是全局的多个库之间不能独立提交且如果一个库在事务执行中被其他进程写入可能触发database is locked错误。跨库操作前最好先确认没有别的高频写入进程。7. 事务、锁与调试并发场景里的语法陷阱7.1 事务语法与锁行为写入是全局串行的SQLite 的事务语法很简单BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 出错时 ROLLBACK;但它和 MySQL 最大的区别是同一时刻只能有一个写事务。SQLite 的锁不是行级锁而是数据库级写锁。也就是说两个进程同时写一张表的不同行照样会冲突。这一点在语法上没有体现但在行为上非常影响架构设计。我实际用下来缓解方案有三个开启 WAL 模式读写并发能力会好很多读不阻塞写写不阻塞读设置PRAGMA busy_timeout 3000让写冲突时等待而不是立即报错应用层实现“单写者”模式比如后台任务串行写入队列。如果你收到database is locked不要反复重试同样的无脑操作先看是不是有事务忘记 COMMIT 或者 ROLLBACK。很多时候是代码里BEGIN之后某个异常分支没处理连接一直握着写锁。7.2 常见语法报错速查与排查思路报错信息最常见原因排查方向no such column: xxx双引号把字符串当成标识符或列名拼写错了检查引号类型字符串一律单引号table has no column named xxxINSERT 或 ALTER TABLE 引用了不存在的列PRAGMA table_info(表名)核对列定义datatype mismatch类型亲和性下值无法转换为目标类型检查传入参数的真实 Python/Java 类型constraint failed: UNIQUE constraint failed: t.a插入数据违反唯一约束先 SELECT 查重复数据再决定 IGNORE 还是 UPDATEdatabase is locked事务未释放或并发写入冲突busy_timeout调大检查是否有未 COMMIT 的事务SQL logic error语法本身有问题通常是 SQL 模板错误打印出完整 SQL手动在客户端执行database disk image is malformed数据库文件损坏立刻用备份恢复平时注意定期备份排查这些问题时单步执行比看报错更有效。SQLite 有官方的命令行工具配合PRAGMA table_info、EXPLAIN QUERY PLAN大多数问题五分钟内能定位。7.3 调试技巧schema 查看、统计信息与慢查询定位SQLite 没有 MySQL 那种EXPLAIN ANALYZE但组合几个 PRAGMA 也能定位问题。先看表结构.schema user再看索引PRAGMA index_list(user);接着看统计信息PRAGMA stats;stats这个命令新版 SQLite会输出表的行数、页数等信息帮助你判断是不是数据量已经大到一个“原本没问题的查询开始慢”的程度。如果确实慢了结合上一节的EXPLAIN QUERY PLAN看完基本能定位瓶颈。还有一个小技巧把慢 SQL 的EXPLAIN QUERY PLAN输出保存下来和加索引之后的输出对比SCAN变SEARCH就是清晰的优化证据。我写优化复盘笔记时常用这个方法比凭感觉描述“变快了”有说服力得多。8. 我绕不开的几个“语法认知”坎SQLite 语法看着跟其他 SQL 接近真正上手后才发现它有一套自己的底层哲学。我最早接手某桌面工具的数据库改造时花了一周才把“动态类型”“无服务端”“单写者”这些概念内化。后来迁移某移动端离线存储模块又一次在ALTER TABLE上吃瘪老老实实走了重建流程。现在回头看这些坎其实都是语法背后的理念差异。一个很实用的建议正式动手前先把 SQLite 官方文档里的SQL Language部分过一遍重点看CREATE TABLE、INSERT、SELECT、PRAGMA这四块。文档不长但比在搜索引擎里零散找答案高效得多。另外尽量让 SQLite 的版本保持较新3.35 之后的版本包括ALTER TABLE DROP COLUMN、RETURNING、ON CONFLICT DO UPDATE等重要语法都有了能少绕很多弯。最后分享一个我一直在用的习惯任何涉及写操作的关键 SQL先在内存数据库里验证一遍:memory:连接。这个连接不需要清理、纯内存运行语法验证和测试非常快。等 SQL 确认没问题再套到真实文件库上执行。用这个方式我踩过的语法坑至少少了一半。SQLite 是个“懂它的人能玩出花”的数据库语法细节都在这了剩下的就靠你在真实项目里多折腾几遍。