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

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








文章目錄

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

大家好，我是只談技術不剪發(fā)的 Tony 老師。

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

如果覺得文章有用，歡迎評論??、點贊??、推薦??
支持內(nèi)置的 SQL 數(shù)學函數(shù)

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

目前支持的數(shù)學函數(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ù)、浮點數(shù)或者可以轉換為整數(shù)或實數(shù)的字符串或二進制串。如果任何參數(shù)為 NULL，或者無法轉換為數(shù)字的字符串或二進制串，函數(shù)將會返回 NULL。另外，如果存在數(shù)值域錯誤，例如計算負數(shù)的平方根或者計算大于 1.0 或小于 -1.0 數(shù)值的反余弦值，函數(shù)也會返回 NULL。

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



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

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

SQLite 新版本增加了 ALTER TABLE DROP COLUMN 語句，可以用于刪除表中的已有字段。該命令將會刪除表中指定名稱的字段，并且重新寫入表的內(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



該命令只能刪除沒有被其他對象引用的字段，同時也不能刪除 PRIMARY KEY 字段或者 UNIQUE 約束字段。以下情況可能會導致該命令執(zhí)行失敗：

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

關于 ALTER TABLE DROP COLUMN 語句的實現(xiàn)原理，可以參考官方文檔。
擴展 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 子句，可以返回插入、更新后或者刪除的數(shù)據(jù)。這個擴展功能來自 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 為所有字段生成了默認的數(shù)值。RETURNING 子句可以將這些值返回給應用程序，避免了應用再次查詢數(shù)據(jù)庫的操作。

對于 INSERT 和 UPDATE 語句，RETURNING 子句返回的是修改之后的數(shù)據(jù)。對于 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



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

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

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

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



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

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

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

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

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

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

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

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

CLI 增強

SQLite 3.35.0 對命令行工具進行了以下增強：

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

缺陷修復

新版本還修復了以下缺陷：

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

總結

了解了這么多新特性，哪個是你最期待的功能？趕快點擊下載最新版使用吧！