个人中心
退出
SQLite 3.32 新特性分析
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
支持数据库的粗略分析
增加虚拟表 Bytecode() 和 Tables_Used()
增加 VFS 校验和垫片
增加 SQL 函数 iif()
INSERT 和 UPDATE 语句在 CHECK 约束之前执行字段亲和性转换
为 VFS 垫片增加新的接口
默认主机参数限制从 999 增加到 32766
增加 UINT 排序序列
命令行工具增强
LIKE 运算符支持 ESCAPE 转义字符
大家好！我是只谈技术不剪发的 Tony 老师。今天给大家分享一个关于 SQLite 的最新消息。🍺
SQLite 于 2020 年 5 月 22 日发布了 3.32.0 版本，带来了以下新的功能和增强：
支持数据库的粗略分析
通过使用 PRAGMA analysis_limit 命令，可以实现针对大型数据库的近似分析（approximate ANALYZE ）。
默认情况下，ANALYZE 命令需要针对每个索引进行完全扫描。对于大型数据库而言，这种操作需要占用大量时间。因此，从 SQLite 3.32.0 开始，引入了PRAGMA analysis_limit命令，可以用于限制 ANALYZE 执行扫描的数量，使得分析操作能够更快执行。
该指令的设置方式如下：
PRAGMA analysis_limit=1000;
对于以上命令，ANALYZE 以正常方式对索引进行完全扫描；当它扫描的行数到达 1000（或者其他指令设置的限制）时，ANALYZE 将会停止扫描。具体来说，如果索引最左列的值在这个过程中至少发生过一次变化，分析操作将会立即停止；但是如果最左列的值一直没有改变，ANALYZE 将会跳跃到一个同最左列不的索引值节点并且再次扫描 1000 行。
推荐将分析限制的数值设置为 100 到 1000。如果将其设置为 0，可以禁用分析限制，也就是 ANALYZE 需要完全扫描每个索引。默认值为 0，可以支持向下兼容。
增加虚拟表 Bytecode() 和 Tables_Used()
增加了两个虚拟表 bytecode 和 tables_used，提供预编译语句的相关信息。
bytecode 和 tables_used 都是表函数，只有一个必选的参数，可以是 SQL 语句的文本字符串或者已有预编译语句的指针。bytecode 函数为预编译语句中的每个字节码操作返回一行数据；tables_used 函数为预编译语句访问过的持久性 btree 对象（表或者索引）返回一行数据。
需要使用编译时选项 -DSQLITE_ENABLE_BYTECODE_VTAB 编译 SQLite 才能使用 bytecode 和 tables_used 表。CLI 默认使用了该选项，可以用于测试该功能。
增加 VFS 校验和垫片
VFS 校验和插件是一个新增的 VFS 垫片（VFS shim），用于在 SQLite 数据库每个数据页的最后增加一个 8 字节的校验和。
每次写入数据页时增加校验和，每次读取时进行验证。增加校验和是为了检测大容量存储设备中的随机位翻转引起的数据库损坏。
增加 SQL 函数 iif()
SQLite 增加了函数 iif(X,Y,Z) ，当 X 为真时返回 Y，否则返回 Z。例如：
sqlite> select iif(true, 1, 0), iif(null, 0, 1);
1|1
函数 iif(X,Y,Z) 逻辑上等价于CASE WHEN X THEN Y ELSE Z END。
INSERT 和 UPDATE 语句在 CHECK 约束之前执行字段亲和性转换
从 SQLite 3.32.0 开始，INSERT 和 UPDATE 语句总是在 CHECK 约束之前执行字段亲和性转换。这是一个 bug 修复，在之前的版本中：
CREATE TABLE t1(x INTEGER CHECK(typeof(x)==‘text’));
INSERT INTO t1 VALUES(‘123’);
PRAGMA integrity_check;
INSERT 语句可以成功执行，但是 PRAGMA 检查失败。在修复之后，INSERT 执行时就会返回错误：
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 修复可能会导致遗留数据库中不规范的 CHECK 约束出错。例如上面的约束，因为它要求的数据类型和字段声明的类型不一致。但是对于下面的语句，修复之前无法执行 INSERT 语句，但是新版本解决了这个问题：
CREATE TABLE t2(x INT CHECK(typeof(x)==‘integer’));
INSERT INTO t2(x) VALUES(‘123’);
为 VFS 垫片增加新的接口
为了更好地支持 VFS 垫片实现，增加了 sqlite3_create_filename()、sqlite3_free_filename() 和 sqlite3_database_file_object() 三个接口。
默认主机参数限制从 999 增加到 32766
单个 SQL 语句可以使用的默认主机参数上限（SQLITE_MAX_VARIABLE_NUMBER）的值从 999 增加到了 32766。
该限制可以在运行时通过 sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) 接口进行修改。
增加 UINT 排序序列
增加了一个可加载的 SQLite 扩展：UINT 排序序列，实现了按照数字顺序对包含无符号整数的文本字符串进行排序（自然排序）。
UINT 排序序列对于文本字符的排序与默认的 BINARY 排序序列类似，只是对于其中的数字字符按照数字大小进行排序：
数字字符中的前导零不会影响比较的结果，“x00123y”和“x123y”顺序相同。
只处理字符串中的无符号整数。加号和减号将会被看作文本字符，小数点和指数表示法将也被看作文本字符。
字符串中的整数可以是任意长度，而不仅限于 64 位整数。
例如，以下是一些字符串在不同排序序列中的排序结果：
命令行工具增强
新版本对命令行工具 CLI 增加了以下功能：
.import 导入命令增加了以下选项：--csv（导入 CSV）、--ascii（导入 ASCII）、--skip（跳过前 N 行）。使用.help .import命令可以查看相关帮助。
.dump 命令支持多个 LIKE 模式参数，匹配任意模式的表都会被导出。
调试版本中增加了 .oom 命令。
.excel、.output 以及 .once 命令增加了--bom选项，用于导入带 BOM 的 UTF8 文件。
.filectrl 命令增加了--schema选项，用于指定操作的数据库（SCHEMA）。
自动加载上文中的 UINT 排序序列。
LIKE 运算符支持 ESCAPE 转义字符
LIKE 运算符的 ESCAPE 子句会覆盖通配符。ESCAPE 转义字符由单个字符构成，转义字符加上 %、_ 或者另一个转义字符分别表示它们自身，而不是通配符。
分类导航