個人中心
退出
SQLite 3.32 新特性分析
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
支持?jǐn)?shù)據(jù)庫的粗略分析
增加虛擬表 Bytecode() 和 Tables_Used()
增加 VFS 校驗和墊片
增加 SQL 函數(shù) iif()
INSERT 和 UPDATE 語句在 CHECK 約束之前執(zhí)行字段親和性轉(zhuǎn)換
為 VFS 墊片增加新的接口
默認(rèn)主機參數(shù)限制從 999 增加到 32766
增加 UINT 排序序列
命令行工具增強
LIKE 運算符支持 ESCAPE 轉(zhuǎn)義字符
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家分享一個關(guān)于 SQLite 的最新消息。??
SQLite 于 2020 年 5 月 22 日發(fā)布了 3.32.0 版本，帶來了以下新的功能和增強：
支持?jǐn)?shù)據(jù)庫的粗略分析
通過使用 PRAGMA analysis_limit 命令，可以實現(xiàn)針對大型數(shù)據(jù)庫的近似分析（approximate ANALYZE ）。
默認(rèn)情況下，ANALYZE 命令需要針對每個索引進行完全掃描。對于大型數(shù)據(jù)庫而言，這種操作需要占用大量時間。因此，從 SQLite 3.32.0 開始，引入了PRAGMA analysis_limit命令，可以用于限制 ANALYZE 執(zhí)行掃描的數(shù)量，使得分析操作能夠更快執(zhí)行。
該指令的設(shè)置方式如下：
PRAGMA analysis_limit=1000;
對于以上命令，ANALYZE 以正常方式對索引進行完全掃描；當(dāng)它掃描的行數(shù)到達 1000（或者其他指令設(shè)置的限制）時，ANALYZE 將會停止掃描。具體來說，如果索引最左列的值在這個過程中至少發(fā)生過一次變化，分析操作將會立即停止；但是如果最左列的值一直沒有改變，ANALYZE 將會跳躍到一個同最左列不的索引值節(jié)點并且再次掃描 1000 行。
推薦將分析限制的數(shù)值設(shè)置為 100 到 1000。如果將其設(shè)置為 0，可以禁用分析限制，也就是 ANALYZE 需要完全掃描每個索引。默認(rèn)值為 0，可以支持向下兼容。
增加虛擬表 Bytecode() 和 Tables_Used()
增加了兩個虛擬表 bytecode 和 tables_used，提供預(yù)編譯語句的相關(guān)信息。
bytecode 和 tables_used 都是表函數(shù)，只有一個必選的參數(shù)，可以是 SQL 語句的文本字符串或者已有預(yù)編譯語句的指針。bytecode 函數(shù)為預(yù)編譯語句中的每個字節(jié)碼操作返回一行數(shù)據(jù)；tables_used 函數(shù)為預(yù)編譯語句訪問過的持久性 btree 對象（表或者索引）返回一行數(shù)據(jù)。
需要使用編譯時選項 -DSQLITE_ENABLE_BYTECODE_VTAB 編譯 SQLite 才能使用 bytecode 和 tables_used 表。CLI 默認(rèn)使用了該選項，可以用于測試該功能。
增加 VFS 校驗和墊片
VFS 校驗和插件是一個新增的 VFS 墊片（VFS shim），用于在 SQLite 數(shù)據(jù)庫每個數(shù)據(jù)頁的最后增加一個 8 字節(jié)的校驗和。
每次寫入數(shù)據(jù)頁時增加校驗和，每次讀取時進行驗證。增加校驗和是為了檢測大容量存儲設(shè)備中的隨機位翻轉(zhuǎn)引起的數(shù)據(jù)庫損壞。
增加 SQL 函數(shù) iif()
SQLite 增加了函數(shù) iif(X,Y,Z) ，當(dāng) X 為真時返回 Y，否則返回 Z。例如：
sqlite> select iif(true, 1, 0), iif(null, 0, 1);
1|1
函數(shù) iif(X,Y,Z) 邏輯上等價于CASE WHEN X THEN Y ELSE Z END。
INSERT 和 UPDATE 語句在 CHECK 約束之前執(zhí)行字段親和性轉(zhuǎn)換
從 SQLite 3.32.0 開始，INSERT 和 UPDATE 語句總是在 CHECK 約束之前執(zhí)行字段親和性轉(zhuǎn)換。這是一個 bug 修復(fù)，在之前的版本中：
CREATE TABLE t1(x INTEGER CHECK(typeof(x)==‘text’));
INSERT INTO t1 VALUES(‘123’);
PRAGMA integrity_check;
INSERT 語句可以成功執(zhí)行，但是 PRAGMA 檢查失敗。在修復(fù)之后，INSERT 執(zhí)行時就會返回錯誤：
sqlite> CREATE TABLE t1(x INTEGER CHECK(typeof(x)==‘text’));
sqlite> INSERT INTO t1 VALUES(‘123’);
Error: CHECK constraint failed: t1
sqlite> PRAGMA integrity_check;
ok
這個 bug 修復(fù)可能會導(dǎo)致遺留數(shù)據(jù)庫中不規(guī)范的 CHECK 約束出錯。例如上面的約束，因為它要求的數(shù)據(jù)類型和字段聲明的類型不一致。但是對于下面的語句，修復(fù)之前無法執(zhí)行 INSERT 語句，但是新版本解決了這個問題：
CREATE TABLE t2(x INT CHECK(typeof(x)==‘integer’));
INSERT INTO t2(x) VALUES(‘123’);
為 VFS 墊片增加新的接口
為了更好地支持 VFS 墊片實現(xiàn)，增加了 sqlite3_create_filename()、sqlite3_free_filename() 和 sqlite3_database_file_object() 三個接口。
默認(rèn)主機參數(shù)限制從 999 增加到 32766
單個 SQL 語句可以使用的默認(rèn)主機參數(shù)上限（SQLITE_MAX_VARIABLE_NUMBER）的值從 999 增加到了 32766。
該限制可以在運行時通過 sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) 接口進行修改。
增加 UINT 排序序列
增加了一個可加載的 SQLite 擴展：UINT 排序序列，實現(xiàn)了按照數(shù)字順序?qū)Π瑹o符號整數(shù)的文本字符串進行排序（自然排序）。
UINT 排序序列對于文本字符的排序與默認(rèn)的 BINARY 排序序列類似，只是對于其中的數(shù)字字符按照數(shù)字大小進行排序：
數(shù)字字符中的前導(dǎo)零不會影響比較的結(jié)果，“x00123y”和“x123y”順序相同。
只處理字符串中的無符號整數(shù)。加號和減號將會被看作文本字符，小數(shù)點和指數(shù)表示法將也被看作文本字符。
字符串中的整數(shù)可以是任意長度，而不僅限于 64 位整數(shù)。
例如，以下是一些字符串在不同排序序列中的排序結(jié)果：
命令行工具增強
新版本對命令行工具 CLI 增加了以下功能：
.import 導(dǎo)入命令增加了以下選項：--csv（導(dǎo)入 CSV）、--ascii（導(dǎo)入 ASCII）、--skip（跳過前 N 行）。使用.help .import命令可以查看相關(guān)幫助。
.dump 命令支持多個 LIKE 模式參數(shù)，匹配任意模式的表都會被導(dǎo)出。
調(diào)試版本中增加了 .oom 命令。
.excel、.output 以及 .once 命令增加了--bom選項，用于導(dǎo)入帶 BOM 的 UTF8 文件。
.filectrl 命令增加了--schema選項，用于指定操作的數(shù)據(jù)庫（SCHEMA）。
自動加載上文中的 UINT 排序序列。
LIKE 運算符支持 ESCAPE 轉(zhuǎn)義字符
LIKE 運算符的 ESCAPE 子句會覆蓋通配符。ESCAPE 轉(zhuǎn)義字符由單個字符構(gòu)成，轉(zhuǎn)義字符加上 %、_ 或者另一個轉(zhuǎn)義字符分別表示它們自身，而不是通配符。
分類導(dǎo)航