SQLite 3.35.0 发布，带来多个实用的新特性

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net








文章目录

        支持内置的 SQL 数学函数
        支持 ALTER TABLE DROP COLUMN 语句
        扩展 UPSERT 的功能
        DML 语句支持 RETURNING 子句
        VACUUM 优化
        通用表表达式优化
        TEMP 触发器和视图
        查询优化器增强
        CLI 增强
        缺陷修复
        总结

大家好，我是只谈技术不剪发的 Tony 老师。

最流行的嵌入式数据库 SQLite 开发团队于 2021 年 3 月 12 日发布了 SQLite 3.35.0 版本。该版本增加了多个 SQL 语句以及查询优化器相关的一些新特性，本文给大家逐一进行分析。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
支持内置的 SQL 数学函数

SQLite 3.35.0 在核心源代码中增加内置的 SQL 数学函数支持，我们只需要在编译时使用-DSQLITE_ENABLE_MATH_FUNCTIONS选项，而且默认的编译配置中已经启用。也就是说，我们不再需要通过编译 extension-functions.c 文件获得这些数学函数。

目前支持的数学函数包括：

    acos(X)
    acosh(X)
    asin(X)
    asinh(X)
    atan(X)
    atan2(X,Y)
    atanh(X)
    ceil(X)
    ceiling(X)
    cos(X)
    cosh(X)
    degrees(X)
    exp(X)
    floor(X)
    ln(X)
    log(B,X)
    log(X)
    log10(X)
    log2(X)
    mod(X,Y)
    pi()
    pow(X,Y)
    power(X,Y)
    radians(X)
    sin(X)
    sinh(X)
    sqrt(X)
    tan(X)
    tanh(X)
    trunc(X)

这些函数的参数可以是整数、浮点数或者可以转换为整数或实数的字符串或二进制串。如果任何参数为 NULL，或者无法转换为数字的字符串或二进制串，函数将会返回 NULL。另外，如果存在数值域错误，例如计算负数的平方根或者计算大于 1.0 或小于 -1.0 数值的反余弦值，函数也会返回 NULL。

sqlite> select sqlite_version();
3.35.0
sqlite> .nullvalue [NULL]
sqlite> select sqrt(null), sqrt(-1);
[NULL]|[NULL]



另外，这些函数的返回值通常是近似结果。例如，函数 pi() function 的返回结果为 3.141592653589793115997963468544185161590576171875，比实际在大概小了 1.22465e-16 ，但这是 IEEE754 双精度浮点数中最接近 Pi 的数值。

关于这些数学函数的具体介绍可以参考官方文档。
支持 ALTER TABLE DROP COLUMN 语句

SQLite 新版本增加了 ALTER TABLE DROP COLUMN 语句，可以用于删除表中的已有字段。该命令将会删除表中指定名称的字段，并且重新写入表的内容以清除该字段的数据。例如：

sqlite> create table t(id int, col1 int);
sqlite> insert into t values(1,1);
sqlite> alter table t drop column col1;
sqlite> select * from t;
1



该命令只能删除没有被其他对象引用的字段，同时也不能删除 PRIMARY KEY 字段或者 UNIQUE 约束字段。以下情况可能会导致该命令执行失败：

    被删除字段是 PRIMARY KEY 或者主键的一部分。
    被删除字段用于 UNIQUE 约束
    被删除字段存在索引。
    被删除字段出现在某个部分索引的 WHERE 子句中。
    被删除字段出现在表级 CHECK 约束或者其他字段的 CHECK 约束中。
    被删除字段被外键约束引用。
    被删除字段被用于生成列的表达式中。
    被删除字段被用于触发器或者视图中。

关于 ALTER TABLE DROP COLUMN 语句的实现原理，可以参考官方文档。
扩展 UPSERT 的功能

新版本的 SQLite 在以下两个方面对 UPSERT 语句进行了增强：

    允许多个 ON CONFLICT 子句，它们按照顺序进行判断；
    最后的 ON CONFLICT 子句可以忽略冲突的目标，仍然使用 DO UPDATE 命令进行更新操作。

例如：

sqlite> create table t(id int primary key, col1 int unique, col2 text);
sqlite> insert into t values (1, 1, 'sqlite');
sqlite> insert into t values (2, 1, 'sqlite3')
   ...> on conflict(id) do nothing
   ...> on conflict(col1) do update set col2 = excluded.col2;
sqlite> select * from t;
1|1|sqlite3



关于 UPSERT 功能的详细介绍，可以参考官方文档。
DML 语句支持 RETURNING 子句

SQLite 新版本为 INSERT、UPDATE 以及 DELETE 语句提供了 RETURNING 子句，可以返回插入、更新后或者删除的数据。这个扩展功能来自 PostgreSQL。例如：

CREATE TABLE t0(
  a INTEGER PRIMARY KEY,
  b DATE DEFAULT CURRENT_TIMESTAMP,
  c INTEGER
);
sqlite> INSERT INTO t0(c)
   ...> VALUES(random()),(random()),(random())
   ...> RETURNING *;
1|2021-03-15 19:50:08|-2232026377363516485
2|2021-03-15 19:50:08|-6973867001075546289
3|2021-03-15 19:50:08|-2021925416820049180



在以上 INSERT 语句中，SQLite 为所有字段生成了默认的数值。RETURNING 子句可以将这些值返回给应用程序，避免了应用再次查询数据库的操作。

对于 INSERT 和 UPDATE 语句，RETURNING 子句返回的是修改之后的数据。对于 DELETE 语句，RETURNING 子句返回的是删除之前的数据。例如：

sqlite> UPDATE t0
   ...> SET c = 20
   ...> WHERE a = 2
   ...> RETURNING a, c;
2|20

sqlite> DELETE FROM t0
   ...> WHERE a = 1
   ...> RETURNING c;
-2232026377363516485



关于 RETURNING 子句的详细介绍和注意事项，可以参考官方文档。
VACUUM 优化

对于包含超大型 TEXT 或者 BLOB 文本的数据库，运行 VACUUM 清理命令时所需的内存更小。SQLite 不再需要将整个 TEXT 或者 BLOB 文本一次性加载到内存中。

关于 VACUUM 命令的介绍，可以参考官方文档。
通用表表达式优化

新版本支持创建通用表表达式（common table expression）时指定 MATERIALIZED 或者 NOT MATERIALIZED 提示选项。



AS MATERIALIZED 和 AS NOT MATERIALIZED 关键字同样来自 PostgreSQL，它们的作用是提示查询计划器如何实现 CTE。

如果指定了 MATERIALIZED，select-stmt 很可能会产生一个内存或者磁盘临时表，然后将该临时表用于随后的 SQL 语句中。由于 select-stmt 的这种立即执行方式，无法获得子查询展开或者查询条件下推等优化。

如果指定了 NOT MATERIALIZED，select-stmt 以子查询的方式出现在所有引用该 CTE 的子句中。这种方式可以获得子查询展开或者查询条件下推等优化。尽管如此，NOT MATERIALIZED 并不会阻止物化临时表的使用。查询计划器仍然可能基于判断使用临时表实现 CTE。NOT MATERIALIZED 的真正含义更接近于“像任何普通视图或子查询一样处理”。

如果没有指定任何提示选项，SQLite 3.35.0 (2021-03-12) 以及更高版本对于被多次使用的 CTE 默认为 MATERIALIZED，对于只被引用一次的 CTE 默认为 NOT MATERIALIZED。在之前的版本中，所有的 CTE 默认行为和 NOT MATERIALIZED 一致。通过 CTE 被引用的次数来决定 MATERIALIZED 或者 NOT MATERIALIZED 是一种探索行为，如果将来查询计划策略有了改进，这种方式也会跟着改变。无论如何，这些改变只对性能产生影响，最终查询的结果不会改变。

关于通用表表达式的更多介绍，可以参考官方文档。
TEMP 触发器和视图

新版本对 SQLITE_DBCONFIG_ENABLE_TRIGGER 和 SQLITE_DBCONFIG_ENABLE_VIEW 配置选项进行了修改，现在它们只会影响主数据库或者附加数据库中的触发器和视图，不会对 TEMP 模式中的触发器和视图产生影响。TEMP 触发器和视图永远不会被禁用。
查询优化器增强

SQLite 3.35.0 对查询计划器/优化器进行了以下改进：

    进一步增强了 min/max 优化，使得它比之前版本更好地和 IN 运算符一起使用，同时增强了 OP_SeekScan 优化。
    在可能进行转换并提高性能的情况下，尝试将 WHERE 子句中的 EXISTS 运算符转换为 IN 运算符。
    允许 UNION ALL 子查询的展开，即使父查询是一个连接查询。
    即使禁用了 STAT4，在合适的情况下仍然使用索引优化 WHERE 子句中的 IS NOT NULL 表达式。
    如果字段 x 上存在 NOT NULL 约束并且不是出现在外连接查询中，x IS NULL 或者 x IS NOT NULL 形式的表达式可能会转换为简单 FALSE 或者 TRUE。
    如果 UPDATE 语句没有修改任何于外键关联的字段，不再检查外键约束。
    只要 WHERE 条件项完全由常量和所有窗口函数中 PARTITION BY 子句内的表达式构成，允许 WHERE 条件项下推到包含窗口函数的子查询中。

CLI 增强

SQLite 3.35.0 对命令行工具进行了以下增强：

    .stats 支持新的参数 stmt 和 vmstep，分别用于显示语句准备阶段统计和仅显示虚拟机步骤计数。
    增加了 .filectrl data_version 命令，用于显示数据文件页的版本号。
    增强了 .once 和 .output 命令，如果目标参数以 | 开头（表示输出结果重定向到管道），参数不需要使用引号包含。

缺陷修复

新版本还修复了以下缺陷：

    修改了潜在的 NULL 指针间接引用，当系统处理包含关联 WHERE 子句和 HAVING 0 子句的错误 SELECT 语句时可能出现这种问题。（SQLite 3.34.1 补丁版本同步修改了该缺陷。）
    修复了 SQLite 3.33.0 版本引入的 IN 运算符优化可能导致的错误结果。
    修复了 LIKE 运算符以 % 结尾且包含 ESCAPE ‘_’ 子句时的错误结果。

总结

了解了这么多新特性，哪个是你最期待的功能？赶快点击下载最新版使用吧！