SQLite 里那些好用但少有人知道的特性

很多人对 SQLite 的印象还停留在"手机 App 存点配置用的"。其实这些年它加了不少实用的特性，用起来并不比 MySQL 差。下面这些特性，标注了开始支持的版本，用之前可以先查一下 select sqlite_version();。

UPSERT（3.24+）

存在就更新，不存在就插入：

INSERT INTO settings (key, value) VALUES ('site_name', '我的博客')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

excluded 指向本来要插入的那一行。以前要先查再判断，现在一条语句搞定，而且是原子的。

RETURNING（3.35+）

插入或更新后直接返回结果，不用再查一次：

INSERT INTO orders (no, amount) VALUES ('B001', 990) RETURNING id, created_at;

UPDATE orders SET status = 'paid' WHERE no = 'B001' AND status = 'pending' RETURNING id;

第二条特别适合做幂等更新：返回了行，说明这次真的从 pending 改成了 paid；没返回，说明已经被处理过了。

生成列（3.31+）

由其他列计算出来的列，可以建索引：

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  amount INTEGER NOT NULL,           -- 单位：分
  amount_yuan TEXT GENERATED ALWAYS AS (printf('%.2f', amount / 100.0)) VIRTUAL
);

STRICT 表（3.37+）

SQLite 默认是"弱类型"的，往 INTEGER 列里插字符串也不会报错。加上 STRICT 后会严格检查类型：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;

新项目建议直接用，能提前发现不少 bug。

JSON 函数（3.38+ 默认内置）

SELECT data ->> '$.city' AS city FROM profiles;

SELECT * FROM profiles WHERE json_extract(data, '$.vip') = 1;

->> 返回 SQL 值，-> 返回 JSON 文本。灵活字段放在一个 JSON 列里，常用的再用生成列提出来建索引，是很实用的组合。

部分索引

只给满足条件的行建索引：

CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

待支付订单通常只占很小一部分，定时关单任务只查这部分，用部分索引既快又省空间。

窗口函数（3.25+）

SELECT category, title, views,
       RANK() OVER (PARTITION BY category ORDER BY views DESC) AS rk
FROM articles;

每个分类里按阅读量排名，以前要写很绕的子查询。

小结

SQLite 能做的事情比大多数人想象的多。对于单机部署的中小型项目，它往往是最省心的选择。