SQLite 3.33.0 發(fā)布，新特性了解一下？

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



在這里插入圖片描述

文章目錄

        支持 UPDATE FROM 語(yǔ)句
        數(shù)據(jù)庫(kù)文件最大支持 281 TB
        PRAGMA integrity_check 語(yǔ)句增強(qiáng)
        decimal 插件
        ieee754 插件增強(qiáng)
        命令行工具增強(qiáng)
        查詢計(jì)劃改進(jìn)
        WAL 索引文件恢復(fù)
        總結(jié)

今天給大家?guī)?lái)一個(gè)關(guān)于 SQLite 嵌入式數(shù)據(jù)庫(kù)的最新消息，SQLite 開(kāi)發(fā)團(tuán)隊(duì)于 2020 年 8 月 14 日發(fā)布了 SQLite Release 3.33.0 版本。本文就來(lái)給大家分析一下這個(gè)版本中的一些新特性。
支持 UPDATE FROM 語(yǔ)句

UPDATE FROM 語(yǔ)句是針對(duì) SQL 標(biāo)準(zhǔn)的一個(gè)擴(kuò)展，允許使用其他表中的數(shù)據(jù)更新目標(biāo)表。通過(guò) UPDATE FROM 語(yǔ)句可以將目標(biāo)表和數(shù)據(jù)庫(kù)中的其他表進(jìn)行連接，確定需要更新的數(shù)據(jù)行以及更新后的字段值。

SQLite 中的 UPDATE FROM 語(yǔ)句使用 PostgreSQL語(yǔ)法。假設(shè)有一個(gè)銷售應(yīng)用程序，SALES 表存儲(chǔ)了產(chǎn)品每天的累積銷售。在每天銷售結(jié)束后，需要根據(jù)每天的銷售情況更新 INVENTORY 庫(kù)存表；為此，可以基于每天的累積銷量更新 INVENTORY 表中的庫(kù)存數(shù)量。例如：

UPDATE inventory
   SET quantity = quantity - daily.amt
  FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
 WHERE inventory.itemId = daily.itemId;

FROM 子句中的子查詢按照不同的 itemId 計(jì)算銷售的數(shù)量，然后將它和 inventory 表進(jìn)行連接并更新 inventory 表中的庫(kù)存數(shù)量。

更新的目標(biāo)表不在 FROM 子句中，除非是執(zhí)行自連接查詢。此時(shí)，F(xiàn)ROM 子句中的表必須使用別名和目標(biāo)表進(jìn)行區(qū)分。

如果連接操作導(dǎo)致目標(biāo)表中的同一數(shù)據(jù)行返回多行結(jié)果，只更新其中的隨機(jī)一行結(jié)果。例如：

UPDATE department d
   SET d.manager = e.emp_id
  FROM (SELECT dept_id, emp_id FROM employee) AS e
 WHERE d.dept_id = e.dept_id;


一個(gè)部門可能存在多名員工，子查詢 e 中的每個(gè)部門編號(hào)對(duì)應(yīng)多個(gè) emp_id，最終部門的 manager 可能是該部門中的任意員工。
數(shù)據(jù)庫(kù)文件最大支持 281 TB

每個(gè)數(shù)據(jù)庫(kù)由一個(gè)或多個(gè)數(shù)據(jù)頁(yè)組成，一個(gè)數(shù)據(jù)庫(kù)內(nèi)的所有數(shù)據(jù)頁(yè)大小都相同，但是不同的數(shù)據(jù)庫(kù)可以使用不同的數(shù)據(jù)頁(yè)大小，范圍從 512 到 65536 字節(jié)。

從 SQLite 3.33.0 開(kāi)始，一個(gè)數(shù)據(jù)庫(kù)文件最多使用 4294967294 個(gè)數(shù)據(jù)頁(yè)（由 max_page_count 編譯指令決定），意味著最大支持約 2.8e+14 字節(jié)（281 TB）。

由于開(kāi)發(fā)人員沒(méi)有能夠達(dá)到該限制的硬件，因此沒(méi)有對(duì)這個(gè)上限進(jìn)行測(cè)試。但是，測(cè)試驗(yàn)證了 SQLite 在數(shù)據(jù)庫(kù)達(dá)到底層文件系統(tǒng)的最大文件大小（通常比該限制小得多）和磁盤空間耗盡而導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法增長(zhǎng)時(shí)仍然能夠正確且穩(wěn)定地運(yùn)行。
PRAGMA integrity_check 語(yǔ)句增強(qiáng)

PRAGMA integrity_check 語(yǔ)句現(xiàn)在可以選擇性地針對(duì)單個(gè)表及其索引進(jìn)行完整性檢查，而不僅僅是針對(duì)整個(gè)數(shù)據(jù)庫(kù)文件。

PRAGMA schema.integrity_check;
PRAGMA schema.integrity_check(N)
PRAGMA schema.integrity_check(TABLENAME)

默認(rèn)情況下針對(duì)整個(gè)數(shù)據(jù)庫(kù)文件進(jìn)行檢查。如果指定了 TABLENAME 參數(shù)，則只會(huì)針對(duì)該表和相關(guān)索引進(jìn)行檢查，也就是“部分完整性檢查”。

由于只檢查了數(shù)據(jù)庫(kù)的部分內(nèi)容，可能無(wú)法檢測(cè)出某些錯(cuò)誤，例如文件中存在的未使用部分，或者兩個(gè)或多個(gè)表重復(fù)使用文件中的相同部分。只有當(dāng) TABLENAME 參數(shù)為 sqlite_schema 或者它的別名時(shí)，部分完整性檢查才會(huì)驗(yàn)證 空閑列表（freelist）。
decimal 插件

新版本增加了 decimal 插件，支持任意精度的十進(jìn)制算術(shù)運(yùn)算。由于采用文本格式存儲(chǔ)數(shù)字，因此可以保留任意精度，不需要進(jìn)行近似運(yùn)算。該插件包含了三個(gè)數(shù)學(xué)函數(shù)：

decimal_add(A,B)
decimal_sub(A,B)
decimal_mul(A,B)

它們分別用于計(jì)算參數(shù)相加、相減和相乘，返回一個(gè)字符串格式的結(jié)果。例如：

sqlite> select decimal_add(1, 0.23456789);
1.23456789
sqlite> select decimal_sub(1, 0.23456789);
0.76543211
sqlite> select decimal_mul(2, 0.23456789);
0.46913578


目前還沒(méi)有提供除法運(yùn)算函數(shù)。

函數(shù) decimal_cmp(A,B) 可以用于比較兩個(gè)數(shù)字值的大小，如果 A 小于、等于或者大于 B，分別返回一個(gè)負(fù)數(shù)、零或者正數(shù)。例如：

sqlite> select decimal_cmp(1, 2);
-1
sqlite> select decimal_cmp(2, 2);
0
sqlite> select decimal_cmp(3, 2);
1


函數(shù) decimal_sum(X) 是一個(gè)類似于 sum() 的聚合函數(shù)，支持任意精度數(shù)字的計(jì)算。例如：

sqlite> WITH RECURSIVE generate_series(v) AS (
   ...>   SELECT 1
   ...>   UNION ALL
   ...>   SELECT v+1.0/3 FROM generate_series
   ...>   WHERE v+1.0/3<=10
   ...> )
   ...> SELECT decimal_sum(v)
   ...> FROM generate_series;
153.99999999999997


decimal 插件提供了“十進(jìn)制”的排序規(guī)則，也就是按照數(shù)字順序比較包含數(shù)字的文本字符串。
ieee754 插件增強(qiáng)

增強(qiáng)了 ieee754 插件，用于支持 IEEE 754 浮點(diǎn)數(shù)的 64 位二進(jìn)制（binary64）格式和科學(xué)計(jì)數(shù)法（ M × 2 E M×2^E M×2E ）之間的轉(zhuǎn)換：

F = M × 2 E F = M × 2^E F=M×2E

函數(shù) ieee754(F) 接受一個(gè)浮點(diǎn)數(shù)的輸入?yún)?shù)，返回一個(gè)如下所示的字符串：

'ieee754(M,E)'


其中，M 和 E 分別代表了浮點(diǎn)數(shù)的尾數(shù)和指數(shù)部分。例如：

sqlite> SELECT ieee754(47.49) AS x;
ieee754(6683623321994527,-47)


與此相反，函數(shù) ieee754(M, E) 將一個(gè)使用尾數(shù)和指數(shù)表示的浮點(diǎn)數(shù)轉(zhuǎn)換為十進(jìn)制格式。例如：

sqlite> SELECT ieee754(6683623321994527,-47) as x;
47.49


函數(shù) ieee754(F) 雖然方便閱讀，但是不方便在表達(dá)式中使用。因此，增加了 ieee754_mantissa() 和 ieee754_exponent() 兩個(gè)函數(shù)，分別用于返回浮點(diǎn)數(shù)的尾數(shù)和指數(shù)部分。例如：

sqlite> SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;
6683623321994527|-47


函數(shù) ieee754_to_blob(F) 用于將浮點(diǎn)數(shù)轉(zhuǎn)為為一個(gè) 8 字節(jié) BLOB 數(shù)據(jù)，代表了該數(shù)字的 big-endian 模式的 binary64 編碼。函數(shù) ieee754_from_blob(B) 執(zhí)行相反的轉(zhuǎn)換。例如：

sqlite> SELECT quote(ieee754_to_blob(4.94065645841247e-324)) AS binary64;
X'0000000000000001'

sqlite> SELECT ieee754_from_blob(x'0000000000000001') AS F;
4.94065645841247e-324

命令行工具增強(qiáng)

SQLite 命令行工具（CLI）增加了 4 種輸出模式：box、json、markdown 以及 table。例如：

sqlite> .mode box
sqlite> select 1 as val, 'sqlite' as str;
┌─────┬────────┐
│ val │  str   │
├─────┼────────┤
│ 1   │ sqlite │
└─────┴────────┘

sqlite> .mode json
sqlite> select 1 as val, 'sqlite' as str;
[{"val":1,"str":"sqlite"}]

sqlite> .mode markdown
sqlite> select 1 as val, 'sqlite' as str;
| val |  str   |
|-----|--------|
| 1   | sqlite |

sqlite> .mode table
sqlite> select 1 as val, 'sqlite' as str;
+-----+--------+
| val |  str   |
+-----+--------+
| 1   | sqlite |
+-----+--------+

   

column（以及 box、markdown、table）輸出模式自動(dòng)將字段的顯示長(zhǎng)度擴(kuò)展為輸出數(shù)據(jù)行的最大長(zhǎng)度，并且自動(dòng)打開(kāi) .headers 設(shè)置。

quote 輸出模式支持 .separator"。例如：

sqlite> .mode quote
sqlite> select 1 as val, 'sqlite' as str;
'val','str'
1,'sqlite'

sqlite> .separator ":"
sqlite> select 1 as val, 'sqlite' as str;
'val':'str'
1:'sqlite'


CLI 默認(rèn)支持 decimal 插件和 ieee754 插件。
查詢計(jì)劃改進(jìn)

查詢計(jì)劃對(duì)于使用了 INDEXED BY 選項(xiàng)的 SQL 語(yǔ)句會(huì)顯示 full-index-scan 信息，例如：

sqlite> create table t(id int, c1 int);
sqlite> create index idx_t_id on t(id);
sqlite> EXPLAIN QUERY PLAN select * from t indexed by idx_t_id;
QUERY PLAN
`--SCAN TABLE t USING INDEX idx_t_id

如果在之前的版本中執(zhí)行以上命令，將會(huì)返回錯(cuò)誤信息：

sqlite> EXPLAIN QUERY PLAN select * from t indexed by idx_t_id;
Error: no query solution


SQLite 可以更好地檢測(cè)丟失的、不完整的和/或不可靠的 sqlite_stat1 統(tǒng)計(jì)數(shù)據(jù)，并且在存在錯(cuò)誤信息能夠生成良好的查詢計(jì)劃。

如果 t(x,y) 上存在索引，能夠提高類似以下查詢語(yǔ)句的性能：

SELECT min(x) FROM t WHERE y IN (?,?,?);


WAL 索引文件恢復(fù)

在預(yù)寫(xiě)式日志（WAL）模式下，如果寫(xiě)入器崩潰導(dǎo)致 shm 文件處于不一致?tīng)顟B(tài)，隨后的事務(wù)可以恢復(fù)該 shm 文件，即使存在活動(dòng)的讀取事務(wù)。

在此之前，這種情況下恢復(fù) shm 文件將會(huì)導(dǎo)致 SQLITE_PROTOCOL 錯(cuò)誤。
總結(jié)

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