Red Creation Technology Blog: SQLite 数据类型

January 30, 2023

By: Kevin'

SQLite 数据类型

SQLite 的动态类型系统
数据类型的意义何在?
类型亲和性与强制转换
为什么 SQLite 要使用动态类型系统?
需要更严格的类型约束? 试试严格模式
严格模式下的数据类型和约束
总结

SQLite 在数据类型上显得格外特立独行.

先来看一个创建表的 SQL 语句:

CREATE TABLE contacts (
    name TEXT,
    age INTEGER
);

如果有使用其他关系型数据库的经验, 看到这个语句, 很可能会有一些"理所当然"的假设. 但这些假设很可能是错误的.

思考一下, 如果执行下面这条 INSERT 语句会发生什么?

INSERT INTO contacts (name, age) VALUES ('Alice', '48');

这条语句能成功执行吗? SQLite 新手会认为, 这条语句肯定会报错! 因为试图将一个字符串插入到 INTEGER 类型的 age 列中, 这在其他数据库中是绝对不允许的. 对吗?

打开 SQLite 命令行工具, 并创建一个新的数据库文件 mydb.db:

sqlite3 mydb.db

依次执行创建表和插入数据的语句:

CREATE TABLE contacts (
    name TEXT,
    age INTEGER
);

INSERT INTO contacts (name, age) VALUES ('Alice', '48');

没有报错! 难道插入成功了? 验证起见, 查询 contacts 表:

SELECT * FROM contacts;

输出结果如下:

Alice|48

结果显示, 插入操作竟然成功了! 这到底是怎么回事? 为什么没有报错?

SQLite 的动态类型系统

原因在于, SQLite 默认使用了一种更为灵活的动态类型系统. 这与几乎所有其他流行的关系型数据库所采用的严格或静态类型系统截然不同.

对于 SQLite 这种灵活的动态类型系统, 我猜很多人可能不太喜欢这种行为, 可能会觉得:

容易导致数据错误(bugs).
可能会产生脏数据.
仅仅是因为以前没见过这种操作, 感觉不符合" 常理" .

如果真的不喜欢刚刚看到的情况, 也先别急着放弃 SQLite. 因为接下来, 会介绍如何让 SQLite 的行为更接近熟悉的那些传统数据库.

数据类型的意义何在?

既然 SQLite 似乎并没有严格执行数据类型约束, 那么在创建表时指定数据类型还有意义吗? 为什么不直接像下面这样, 完全省略数据类型呢?

CREATE TABLE contacts (
    name,
    age
);

这条语句在 SQLite 中是有效的, 尝试一下.

删除已有的 contacts 表:

DROP TABLE contacts;

执行上面这条不带数据类型的 CREATE TABLE 语句:

CREATE TABLE contacts (
    name,
    age
);

同样没有报错! 看来是成功了. 为了深入验证, 插入一条记录并查询:

INSERT INTO contacts (name, age) VALUES ('Bob', 25);

SELECT * FROM contacts;

输出结果:

Bob|25

这进一步证实了插入操作成功. 自然会引出这么个问题: 如果数据类型是可选的, 即使使用它们, 效果也和预期的不一样, 那么 SQLite 中数据类型的意义到底是什么呢?

类型亲和性与强制转换

SQLite 中的数据类型确实有其作用, 只是它们的工作方式可能与预期不同. 为了演示数据类型在 SQLite 中的作用, 再次删除 contacts 表, 并重新创建它, 这次仍然指定数据类型:

DROP TABLE contacts;

CREATE TABLE contacts (
    name TEXT,
    age INTEGER
);

插入一条新的记录, 这次插入的 age 仍然是一个字符串, 但字符串的内容是一个有效的数字:

INSERT INTO contacts (name, age) VALUES ('Charlie', '48');

SELECT * FROM contacts;

输出结果:

Charlie|48

看到了插入的记录, 但是, 这里的 48 到底是什么数据类型呢? 是字符串? 还是整数? 从表面上看, 无法判断. 使用 SQLite 的 typeof() 函数来查看值的实际数据类型:

SELECT typeof(age) FROM contacts WHERE name = 'Charlie';

输出结果:

integer

真相大白! SQLite 将字符串 '48' 强制转换成了数字 48! 这是因为, 将 age 列指定为 INTEGER 类型, 为该列创建了所谓的 类型亲和性 (type affinity).

类型亲和性 的作用是什么呢? 简单来说, 它告诉 SQLite 尽可能地将值强制转换为列的数据类型. 由于字符串 '48' 可以无损地转换为整数 48, SQLite 自动完成了类型转换.

顺便提一下, 类型亲和性和强制转换的概念也存在于其他流行的关系型数据库中. 不同之处在于, 其他数据库会尝试将值强制转换为列的数据类型, 如果转换成功, 插入或更新操作就会成功; 但如果转换失败, 则整个 SQL 语句会执行失败并报错. 而 SQLite 则有所不同, 即使强制转换失败, 插入操作也可能会成功, 只是数据类型可能不是期望的那样.

再看一个例子, 如果尝试插入一个无法无损转换为整数的值, 会发生什么呢?

INSERT INTO contacts (name, age) VALUES ('David', '48.5');

SELECT name, age, typeof(age) FROM contacts WHERE name = 'David';

输出结果:

David|48.5|real

结果显示, 48.5 并没有被强制转换为整数, 而是被插入为 real (实数)类型. 因为如果强制转换为整数, 会丢失小数部分, 造成数据损失. 因此, SQLite 以一种无损的方式插入了该列, 最终 age 列的值成为了 real 类型.

为什么 SQLite 要使用动态类型系统?

SQLite 为什么要使用这种灵活的动态类型系统呢?

根据 SQLite 作者的说法, 他们更倾向于 SQLite 的灵活动态类型系统, 因为它能够实现一些在其他数据库中无法实现的功能. 考虑以下 SQL 语句:

CREATE TABLE store (
    key TEXT,
    value
);

看出这个 store 表的用途了吗? 用 SQLite 创建了一个简单的 键值存储 (key-value store)! 如果想使用其他流行的数据库创建键值存储, 通常会比 SQLite 的方案复杂得多, 因为可能需要为要存储的每种数据类型都添加单独的列.

可见, SQLite 的灵活动态数据类型确实提供了一些其他数据库所不具备的优点.

需要更严格的类型约束? 试试严格模式

尽管 SQLite 的数据类型有其优势, 但我相信仍然有人不喜欢这种"过于灵活"的行为. 但不妨保持开放的心态. SQLite 已经被广泛应用, 并且运行良好. 据估计, 有超过一百万个不同的应用程序使用 SQLite, 并且存在超过一万亿个 SQLite 实例. SQLite 很可能也能很好地满足各类应用需求.

如果仍然觉得 SQLite 的动态数据类型"不可接受", 那么 SQLite 提供了一个相对较新的功能, 可以使其行为更接近其他流行的数据库引擎.

这就是 SQLite 的严格数据类型模式 (strict data typing mode), 这个功能是在 2021 年 11 月添加的.

严格模式, 顾名思义, 会让 SQLite 的行为更像其他流行的关系型数据库. 需要明确的是, SQLite 的严格模式 并不完全等同于 其他关系型数据库的行为.

那么, 如何使用严格模式呢? 很简单, 只需在 CREATE TABLE 语句的末尾, ) 之前, 添加 STRICT 关键字即可, 如下所示:

CREATE TABLE contacts (
    name TEXT,
    age INTEGER
) STRICT;

通过一个例子来演示. 首先, 删除现有的 contacts 表:

DROP TABLE contacts;

然后, 创建一个新的 contacts 表, 启用严格模式:

CREATE TABLE contacts (
    name TEXT,
    age INTEGER
) STRICT;

接下来, 尝试插入之前那个"有争议" 的记录:

INSERT INTO contacts (name, age) VALUES ('Eve', '48');

这次会发生什么? 会报错吗? 还是会成功插入?

Error: cannot store text value in integer column age

看到了错误! "无法在整数列 age 中存储文本值". 这种错误是不是很熟悉? 这正是在其他流行的关系型数据库中会看到的错误!

严格模式下的数据类型和约束

为 contacts 表添加一个新的列 phone, 用来存储电话号码. 首先, 删除 contacts 表, 并重新创建, 这次加入 phone 列:

DROP TABLE contacts;

CREATE TABLE contacts (
    name TEXT,
    age INTEGER,
    phone TEXT
) STRICT;

现在思考一下, phone 列应该使用什么数据类型呢? 可以使用 TEXT. 但如果想限制电话号码的字符长度, 比如最多 15 个字符, 该怎么办呢? 在大多数数据库中, 可能会使用类似 VARCHAR(15) 的类型. 看看在 SQLite 严格模式下, VARCHAR(15) 是否有效:

CREATE TABLE contacts (
    name TEXT,
    age INTEGER,
    phone VARCHAR(15)
) STRICT;

运行结果显示, 报错了! " 未知的列类型: VARCHAR(15)". 这说明, 在严格模式下, 能使用的数据类型是有限制的.

那么, 在严格模式下, 哪些数据类型是有效的呢? 只有以下几种:

INT
INTEGER
REAL
TEXT
BLOB
ANY

这意味着, 不能像在其他数据库中那样, 使用 CHAR(15) 或 VARCHAR(15) 等变体来限制字符串的长度. 只能使用 TEXT 类型.

但是, 如果限制字段长度很重要, 可以使用 CHECK 约束 (check constraint) 来实现. 例如, 可以这样重新创建 contacts 表, 并添加一个 CHECK 约束来限制 phone 列的长度:

DROP TABLE contacts;

CREATE TABLE contacts (
    name TEXT,
    age INTEGER,
    phone TEXT,
    CHECK (length(phone) < 16)
) STRICT;

现在, 如果插入一个格式正确的电话号码, 操作会成功:

INSERT INTO contacts (name, age, phone) VALUES ('Frank', 30, '123-456-7890');

但是, 如果插入一个电话号码过长的联系人, 就会报错:

INSERT INTO contacts (name, age, phone) VALUES ('Grace', 28, '123-456-7890-123');

Error: CHECK constraint failed: contacts

总结

总而言之, SQLite 的数据类型与其他流行的关系型数据库有很大不同. 对于大多数用例来说, 这种灵活的动态类型系统运行良好. 毕竟, SQLite 是世界上使用最广泛的数据库, 绝大多数基于 SQLite 的应用程序都使用了其灵活的动态数据类型, 并且运行良好.

但请记住, 如果应用场景需要更严格的类型约束, 仍然可以在 SQLite 中使用 严格模式.

Tags: sqlite sql

« 使用SQLiteFTS5扩展实现全文检索 homebrew的原理以及使用 »

SQLite 数据类型

SQLite 的动态类型系统

数据类型的意义何在?

类型亲和性与强制转换

为什么 SQLite 要使用动态类型系统?

需要更严格的类型约束? 试试严格模式

严格模式下的数据类型和约束

总结

加入我们

文档&Doc

Recent Posts

Tags