SQLite 2018 年新增 SQL 功能详解：布尔判断、窗口函数与 Upsert

SQLite 是一个被大家低估的数据库，甚至有人认为它只是一个不适合生产环境使用的玩具。但事实恰恰相反，SQLite 非常可靠，能处理 TB 级的数据，只是它没有网络层。本文和大家一起看看 SQLite 在过去一年中新增的 SQL 功能。

SQLite “只是”一个库，它不是传统意义上的服务器。因此在某些场合下确实不合适；但在更多场合里，它反而是最合适的选择。SQLite 号称是部署和使用最广泛的数据库引擎。这一点我认为很可能是真的：SQLite 没有版权限制，只要开发者想在文件里用 SQL 存储结构化数据，它往往就是首选方案。

SQLite 的 SQL 方言也非常强大。它比 MySQL 早四年就开始支持 WITH 语句；最近还实现了对窗口函数的支持，只比 MySQL 晚了五个月。

下面来梳理 SQLite 在 2018 年新增的 SQL 功能，也就是从版本 3.22.0 到 3.26.0 之间的变化。主要内容包括：

1. 布尔字面量和判断
2. 窗口函数
3. Filter 子句
4. INSERT … ON CONFLICT（“Upsert”）
5. 重命名列
6. Modern-SQL.com 的后续内容

布尔变量和判断

SQLite 支持“假”布尔值：它接受 Boolean 作为类型名称，但内部仍按整数处理（这一点和 MySQL 很像）。真值 true 和 false 分别对应数值 1 和 0，与 C 语言一致。

从 3.23.0 开始，SQLite 把关键字 true 和 false 分别映射到 1 和 0，并支持 IS [NOT] TRUE | FALSE 判断。目前它还不支持关键字 unknown，开发者可以用空值 null 代替，因为 unknown 和 null 在布尔语义上是一致的。

在 INSERT 和 UPDATE 语句中，字面量 true 和 false 能明显提升 VALUES 和 SET 子句的可读性。

IS [NOT] TRUE | FALSE 判断语句也很有用，它与普通的比较操作含义不同。比较一下：

WHERE c <> FALSE

和：

WHERE c IS NOT FALSE

上面的例子中，如果 c 是 null，那么 c <> FALSE 的结果是 unknown。因为 WHERE 子句只接受结果为 true 的值，会过滤掉结果为 false 或 unknown 的行，于是这些行就从结果中消失了。

相反，如果 c 是 null，c IS NOT FALSE 的判断结果是 true。因此第二个 WHERE 子句会保留 c 为 null 的行。

要达到同样效果，也可以额外处理 null 值：

WHERE c <> FALSE
   OR c IS NULL

这种写法则更长，也有冗余（c 被用了两次）。简单说，可以用 IS NOT FALSE 判断替代 OR … IS NULL 的写法。更详细的内容可参考 “Binary Decisions Based on Three-Valued Results”。

SQLite 对布尔字面量和布尔判断的支持现在已经接近其他开源数据库，唯一的差距是不支持 IS [NOT] UNKNOWN，可以用 IS [NOT] NULL 代替。有意思的是，这部分能力在一些商用产品中反而还不能用。

• 注 0：只支持 true 和 false，不支持 unknown；如需 unknown，可用 null 代替
• 注 1：不支持 IS [NOT] UNKNOWN，如需则用 IS [NOT] NULL 代替

窗口函数

SQLite 3.25.0 引入了窗口函数。如果你了解窗口函数，就会明白这是一件大事。如果你还不了解，建议花时间学习。这篇文章不会具体解释窗口函数，但可以明确的是：它是最重要的“现代”SQL 特性之一。

SQLite 对 OVER 子句的支持与其他数据库非常接近。唯一值得注意的限制是 RANGE 语句不支持数字或间隔距离，只支持 CURRENT ROW 和 UNBOUNDED PRECEDING|FOLLOWING。在发布 SQLite 3.25.0 时，SQL Server 和 PostgreSQL 也有同样的限制，PostgreSQL 11 才解除。

• 注 0：无变化
• 注 1：Range 范围定义不支持 datetime 类型
• 注 2：Range 范围不接受距离关键字，只支持 UNBOUNDED 和 CURRENT ROW

SQLite 对窗口函数的支持在业界是领先的。它不支持的特性，在其他一些主要产品中也同样不支持，例如聚合中的 DISTINCT、width_bucket、RESPECT|IGNORE NULLS、FROM FIRST|LAST 等。

各脚注说明这里不再逐条展开，核心结论是：SQLite 在窗口函数的基础能力和边界限制上，与 PostgreSQL、MySQL 等主流数据库大体相当。

过滤语句

FILTER 子句虽然只是语法糖——你也可以用表达式达到同样效果——但我认为它是必不可少的语法糖，因为它能让 SQL 更容易学习和理解。

看看下面的 SELECT 子句，你觉得哪一个更容易理解？

SELECT SUM(revenue) total_revenue
     , SUM(CASE WHEN product = 1
                THEN revenue
            END
          ) prod1_revenue
   ...

对比：

SELECT SUM(revenue) total_revenue
     , SUM(revenue) FILTER(WHERE product = 1) prod1_revenue
   ...

这个例子很好总结了 FILTER 子句的作用：它是聚合函数的后缀，能在聚合之前按条件过滤行。FILTER 最典型的用例是透视（pivot）技术，比如把实体属性值（EAV）模型中的属性转换为表格的列。想了解更详细的内容，可以参考 filter-Selective Aggregates。

SQLite 从 3.25.0 开始，在配合 OVER 子句的聚合函数中支持了 FILTER，但在配合 GROUP BY 的聚合函数中还不支持。也就是说，你暂时还无法在 SQLite 中用 FILTER 处理上面这类场景，必须继续使用 CASE 表达式。希望 SQLite 能尽快补齐这一点。

INSERT … ON CONFLICT（“Upsert”）

从 3.24.0 开始，SQLite 引入了 “upsert” 概念：它是一个 INSERT 语句，可以优雅地处理主键和唯一约束冲突。你可以选择忽略冲突（在 ON CONFLICT 里什么都不做），或者更新当前行（在 ON CONFLICT 里执行更新）。

这是一个 SQLite 特有的扩展，不是标准 SQL 的一部分，所以在下面的对比矩阵中是灰色。不过 SQLite 遵守与 PostgreSQL 相同的语法来实现该功能 0。标准 SQL 则提供对 MERGE 语句的支持。

与 PostgreSQL 不同，SQLite 在下面这种语句中存在解析问题：

INSERT INTO target
SELECT *
  FROM source
    ON CONFLICT (id)
    DO UPDATE SET val = excluded.val

根据文档，这是因为解析器无法判断关键字 ON 是 SELECT 语句的连接约束，还是 upsert 子句的开头。可以通过给查询添加子句来解决，例如 WHERE TRUE：

INSERT INTO target
SELECT *
  FROM source
 WHERE true
    ON CONFLICT (id)
    DO UPDATE SET val = excluded.val

• 注 0：同时记录 insert、update、delete 和 merge 操作的错误信息（“DML error logging”）
• 注 1：ON CONFLICT 子句不能紧跟在查询的 FROM 子句之后，如有需要，可用 WHERE TRUE 分隔

重命名列

SQLite 引入的另一个特有功能是重命名基表 1 中的列。标准 SQL 不支持这类功能 2。SQLite 沿用了其他产品常见的语法来重命名列：

ALTER TABLE … RENAME COLUMN … TO

• 注 0：请查阅 sp_rename

其他消息

2018 年，SQLite 除了 SQL 语法层面的变化外，还有一些应用程序接口（API）变化。可以查阅 sqlite.com 新闻栏目了解更详细的信息。

脚注

0：SQLite 通常遵循 PostgreSQL 语法，Richard Hipp 称之为 “What Would PostgreSQL Do”（WWPD）。

1：基表指用 CREATE TABLE 语句创建的表；派生表（例如 SELECT 返回的结果集）中的列名可通过 SELECT、FROM 或 WITH 修改。

2：据我所知，或许可以通过可更新视图或派生列来模拟该功能。

原文：https://modern-sql.com/blog/2019-01/sqlite-in-2018