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

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



在這里插入圖片描述

文章目錄

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

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

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

SQLite 中的 UPDATE FROM 語句使用 PostgreSQL語法。假設(shè)有一個銷售應(yīng)用程序，SALES 表存儲了產(chǎn)品每天的累積銷售。在每天銷售結(jié)束后，需要根據(jù)每天的銷售情況更新 INVENTORY 庫存表；為此，可以基于每天的累積銷量更新 INVENTORY 表中的庫存數(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 計算銷售的數(shù)量，然后將它和 inventory 表進(jìn)行連接并更新 inventory 表中的庫存數(shù)量。

更新的目標(biāo)表不在 FROM 子句中，除非是執(zhí)行自連接查詢。此時，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;


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

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

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

由于開發(fā)人員沒有能夠達(dá)到該限制的硬件，因此沒有對這個上限進(jìn)行測試。但是，測試驗證了 SQLite 在數(shù)據(jù)庫達(dá)到底層文件系統(tǒng)的最大文件大?。ㄍǔ１仍撓拗菩〉枚啵┖痛疟P空間耗盡而導(dǎo)致數(shù)據(jù)庫無法增長時仍然能夠正確且穩(wěn)定地運(yùn)行。
PRAGMA integrity_check 語句增強(qiáng)

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

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

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

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

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

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

它們分別用于計算參數(shù)相加、相減和相乘，返回一個字符串格式的結(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


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

函數(shù) decimal_cmp(A,B) 可以用于比較兩個數(shù)字值的大小，如果 A 小于、等于或者大于 B，分別返回一個負(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) 是一個類似于 sum() 的聚合函數(shù)，支持任意精度數(shù)字的計算。例如：

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é)計數(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) 接受一個浮點(diǎn)數(shù)的輸入?yún)?shù)，返回一個如下所示的字符串：

'ieee754(M,E)'


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

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


與此相反，函數(shù) ieee754(M, E) 將一個使用尾數(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() 兩個函數(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)為為一個 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）輸出模式自動將字段的顯示長度擴(kuò)展為輸出數(shù)據(jù)行的最大長度，并且自動打開 .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ìn)

查詢計劃對于使用了 INDEXED BY 選項的 SQL 語句會顯示 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í)行以上命令，將會返回錯誤信息：

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


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

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

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


WAL 索引文件恢復(fù)

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

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

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