SQLite 3.35.0 發(fā)布，帶來多個(gè)實(shí)用的新特性

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

    支持內(nèi)置的 SQL 數(shù)學(xué)函數(shù)
    支持 ALTER TABLE DROP COLUMN 語句
    擴(kuò)展 UPSERT 的功能
    DML 語句支持 RETURNING 子句
    VACUUM 優(yōu)化
    通用表表達(dá)式優(yōu)化
    TEMP 觸發(fā)器和視圖
    查詢優(yōu)化器增強(qiáng)
    CLI 增強(qiáng)
    缺陷修復(fù)
    總結(jié)

最流行的嵌入式數(shù)據(jù)庫 SQLite 開發(fā)團(tuán)隊(duì)于 2021 年 3 月 12 日發(fā)布了 SQLite 3.35.0 版本。該版本增加了多個(gè) SQL 語句以及查詢優(yōu)化器相關(guān)的一些新特性，本文給大家逐一進(jìn)行分析。

支持內(nèi)置的 SQL 數(shù)學(xué)函數(shù)

SQLite 3.35.0 在核心源代碼中增加內(nèi)置的 SQL 數(shù)學(xué)函數(shù)支持，我們只需要在編譯時(shí)使用-DSQLITE_ENABLE_MATH_FUNCTIONS選項(xiàng)，而且默認(rèn)的編譯配置中已經(jīng)啟用。也就是說，我們不再需要通過編譯 extension-functions.c 文件獲得這些數(shù)學(xué)函數(shù)。

目前支持的數(shù)學(xué)函數(shù)包括：

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)

這些函數(shù)的參數(shù)可以是整數(shù)、浮點(diǎn)數(shù)或者可以轉(zhuǎn)換為整數(shù)或?qū)崝?shù)的字符串或二進(jìn)制串。如果任何參數(shù)為 NULL，或者無法轉(zhuǎn)換為數(shù)字的字符串或二進(jìn)制串，函數(shù)將會(huì)返回 NULL。另外，如果存在數(shù)值域錯(cuò)誤，例如計(jì)算負(fù)數(shù)的平方根或者計(jì)算大于 1.0 或小于 -1.0 數(shù)值的反余弦值，函數(shù)也會(huì)返回 NULL。

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

另外，這些函數(shù)的返回值通常是近似結(jié)果。例如，函數(shù) pi() function 的返回結(jié)果為 3.141592653589793115997963468544185161590576171875，比實(shí)際在大概小了 1.22465e-16 ，但這是 IEEE754 雙精度浮點(diǎn)數(shù)中最接近 Pi 的數(shù)值。

關(guān)于這些數(shù)學(xué)函數(shù)的具體介紹可以參考官方文檔。
支持 ALTER TABLE DROP COLUMN 語句

SQLite 新版本增加了 ALTER TABLE DROP COLUMN 語句，可以用于刪除表中的已有字段。該命令將會(huì)刪除表中指定名稱的字段，并且重新寫入表的內(nèi)容以清除該字段的數(shù)據(jù)。例如：

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

該命令只能刪除沒有被其他對(duì)象引用的字段，同時(shí)也不能刪除 PRIMARY KEY 字段或者 UNIQUE 約束字段。以下情況可能會(huì)導(dǎo)致該命令執(zhí)行失?。?/p> 

被刪除字段是 PRIMARY KEY 或者主鍵的一部分。
被刪除字段用于 UNIQUE 約束
被刪除字段存在索引。
被刪除字段出現(xiàn)在某個(gè)部分索引的 WHERE 子句中。
被刪除字段出現(xiàn)在表級(jí) CHECK 約束或者其他字段的 CHECK 約束中。
被刪除字段被外鍵約束引用。
被刪除字段被用于生成列的表達(dá)式中。
被刪除字段被用于觸發(fā)器或者視圖中。

關(guān)于 ALTER TABLE DROP COLUMN 語句的實(shí)現(xiàn)原理，可以參考官方文檔。
擴(kuò)展 UPSERT 的功能

新版本的 SQLite 在以下兩個(gè)方面對(duì) UPSERT 語句進(jìn)行了增強(qiáng)：

允許多個(gè) ON CONFLICT 子句，它們按照順序進(jìn)行判斷；
最后的 ON CONFLICT 子句可以忽略沖突的目標(biāo)，仍然使用 DO UPDATE 命令進(jìn)行更新操作。

例如：

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

關(guān)于 UPSERT 功能的詳細(xì)介紹，可以參考官方文檔。
DML 語句支持 RETURNING 子句

SQLite 新版本為 INSERT、UPDATE 以及 DELETE 語句提供了 RETURNING 子句，可以返回插入、更新后或者刪除的數(shù)據(jù)。這個(gè)擴(kuò)展功能來自 PostgreSQL。例如：

CREATE TABLE t0(
a INTEGER PRIMARY KEY,
b DATE DEFAULT CURRENT_TIMESTAMP,
c INTEGER
);
sqlite> INSERT INTO t0?
…> 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 為所有字段生成了默認(rèn)的數(shù)值。RETURNING 子句可以將這些值返回給應(yīng)用程序，避免了應(yīng)用再次查詢數(shù)據(jù)庫的操作。

對(duì)于 INSERT 和 UPDATE 語句，RETURNING 子句返回的是修改之后的數(shù)據(jù)。對(duì)于 DELETE 語句，RETURNING 子句返回的是刪除之前的數(shù)據(jù)。例如：

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

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

關(guān)于 RETURNING 子句的詳細(xì)介紹和注意事項(xiàng)，可以參考官方文檔。
VACUUM 優(yōu)化

對(duì)于包含超大型 TEXT 或者 BLOB 文本的數(shù)據(jù)庫，運(yùn)行 VACUUM 清理命令時(shí)所需的內(nèi)存更小。SQLite 不再需要將整個(gè) TEXT 或者 BLOB 文本一次性加載到內(nèi)存中。

關(guān)于 VACUUM 命令的介紹，可以參考官方文檔。
通用表表達(dá)式優(yōu)化

新版本支持創(chuàng)建通用表表達(dá)式（common table expression）時(shí)指定 MATERIALIZED 或者 NOT MATERIALIZED 提示選項(xiàng)。

在這里插入圖片描述

AS MATERIALIZED 和 AS NOT MATERIALIZED 關(guān)鍵字同樣來自 PostgreSQL，它們的作用是提示查詢計(jì)劃器如何實(shí)現(xiàn) CTE。

如果指定了 MATERIALIZED，select-stmt 很可能會(huì)產(chǎn)生一個(gè)內(nèi)存或者磁盤臨時(shí)表，然后將該臨時(shí)表用于隨后的 SQL 語句中。由于 select-stmt 的這種立即執(zhí)行方式，無法獲得子查詢展開或者查詢條件下推等優(yōu)化。

如果指定了 NOT MATERIALIZED，select-stmt 以子查詢的方式出現(xiàn)在所有引用該 CTE 的子句中。這種方式可以獲得子查詢展開或者查詢條件下推等優(yōu)化。盡管如此，NOT MATERIALIZED 并不會(huì)阻止物化臨時(shí)表的使用。查詢計(jì)劃器仍然可能基于判斷使用臨時(shí)表實(shí)現(xiàn) CTE。NOT MATERIALIZED 的真正含義更接近于“像任何普通視圖或子查詢一樣處理”。

如果沒有指定任何提示選項(xiàng)，SQLite 3.35.0 (2021-03-12) 以及更高版本對(duì)于被多次使用的 CTE 默認(rèn)為 MATERIALIZED，對(duì)于只被引用一次的 CTE 默認(rèn)為 NOT MATERIALIZED。在之前的版本中，所有的 CTE 默認(rèn)行為和 NOT MATERIALIZED 一致。通過 CTE 被引用的次數(shù)來決定 MATERIALIZED 或者 NOT MATERIALIZED 是一種探索行為，如果將來查詢計(jì)劃策略有了改進(jìn)，這種方式也會(huì)跟著改變。無論如何，這些改變只對(duì)性能產(chǎn)生影響，最終查詢的結(jié)果不會(huì)改變。

關(guān)于通用表表達(dá)式的更多介紹，可以參考官方文檔。
TEMP 觸發(fā)器和視圖

新版本對(duì) SQLITE_DBCONFIG_ENABLE_TRIGGER 和 SQLITE_DBCONFIG_ENABLE_VIEW 配置選項(xiàng)進(jìn)行了修改，現(xiàn)在它們只會(huì)影響主數(shù)據(jù)庫或者附加數(shù)據(jù)庫中的觸發(fā)器和視圖，不會(huì)對(duì) TEMP 模式中的觸發(fā)器和視圖產(chǎn)生影響。TEMP 觸發(fā)器和視圖永遠(yuǎn)不會(huì)被禁用。
查詢優(yōu)化器增強(qiáng)

SQLite 3.35.0 對(duì)查詢計(jì)劃器/優(yōu)化器進(jìn)行了以下改進(jìn)：

進(jìn)一步增強(qiáng)了 min/max 優(yōu)化，使得它比之前版本更好地和 IN 運(yùn)算符一起使用，同時(shí)增強(qiáng)了 OP_SeekScan 優(yōu)化。
在可能進(jìn)行轉(zhuǎn)換并提高性能的情況下，嘗試將 WHERE 子句中的 EXISTS 運(yùn)算符轉(zhuǎn)換為 IN 運(yùn)算符。
允許 UNION ALL 子查詢的展開，即使父查詢是一個(gè)連接查詢。
即使禁用了 STAT4，在合適的情況下仍然使用索引優(yōu)化 WHERE 子句中的 IS NOT NULL 表達(dá)式。
如果字段 x 上存在 NOT NULL 約束并且不是出現(xiàn)在外連接查詢中，x IS NULL 或者 x IS NOT NULL 形式的表達(dá)式可能會(huì)轉(zhuǎn)換為簡單 FALSE 或者 TRUE。
如果 UPDATE 語句沒有修改任何于外鍵關(guān)聯(lián)的字段，不再檢查外鍵約束。
只要 WHERE 條件項(xiàng)完全由常量和所有窗口函數(shù)中 PARTITION BY 子句內(nèi)的表達(dá)式構(gòu)成，允許 WHERE 條件項(xiàng)下推到包含窗口函數(shù)的子查詢中。

CLI 增強(qiáng)

SQLite 3.35.0 對(duì)命令行工具進(jìn)行了以下增強(qiáng)：

.stats 支持新的參數(shù) stmt 和 vmstep，分別用于顯示語句準(zhǔn)備階段統(tǒng)計(jì)和僅顯示虛擬機(jī)步驟計(jì)數(shù)。
增加了 .filectrl data_version 命令，用于顯示數(shù)據(jù)文件頁的版本號(hào)。
增強(qiáng)了 .once 和 .output 命令，如果目標(biāo)參數(shù)以 | 開頭（表示輸出結(jié)果重定向到管道），參數(shù)不需要使用引號(hào)包含。

缺陷修復(fù)

新版本還修復(fù)了以下缺陷：

修改了潛在的 NULL 指針間接引用，當(dāng)系統(tǒng)處理包含關(guān)聯(lián) WHERE 子句和 HAVING 0 子句的錯(cuò)誤 SELECT 語句時(shí)可能出現(xiàn)這種問題。（SQLite 3.34.1 補(bǔ)丁版本同步修改了該缺陷。）
修復(fù)了 SQLite 3.33.0 版本引入的 IN 運(yùn)算符優(yōu)化可能導(dǎo)致的錯(cuò)誤結(jié)果。
修復(fù)了 LIKE 運(yùn)算符以 % 結(jié)尾且包含 ESCAPE ‘_’ 子句時(shí)的錯(cuò)誤結(jié)果。