数据库原理与选型 / 系统课时
存储类别与类型亲和性
存储类别与类型亲和性
SQLite 的“类型”分成两个层面:值本身有存储类别,列只有类型亲和性。理解这一区别后,你就能解释同一列为什么可能同时出现整数和文本,以及 SQLite 何时会自动做类型转换。
学习目标
- 识别
NULL、INTEGER、REAL、TEXT、BLOB五种存储类别 - 根据列声明判断
INTEGER、REAL、TEXT、BLOB、NUMERIC五类亲和性 - 用
typeof()验证插入值经过亲和性转换后的实际存储类型
先区分值的存储类别与列的亲和性
SQLite 的类型信息主要跟着“值”走,而不是由列把值永久锁死。每个值最终属于五种存储类别之一:NULL 表示空值,INTEGER 表示有符号整数,REAL 表示浮点数,TEXT 表示字符串,BLOB 表示原始字节。
列声明中的类型名称决定的是类型亲和性。亲和性是一种“优先尝试的转换规则”,不是绝对约束。例如 INTEGER 亲和性的列可以保存文本,只是当文本能够按数值解析时,SQLite 会优先转换成数值后再保存。
列声明如何得到类型亲和性
SQLite 根据声明类型中的关键字按固定顺序匹配,因此推荐使用常见、清晰的类型名称,避免故意制造含糊的声明。
| 声明类型包含的关键字 | 得到的亲和性 | 常见声明 |
|---|---|---|
INT |
INTEGER | INTEGER、BIGINT |
CHAR、CLOB、TEXT |
TEXT | VARCHAR(100)、TEXT |
BLOB 或未声明类型 |
BLOB | BLOB |
REAL、FLOA、DOUB |
REAL | REAL、DOUBLE |
| 以上都不匹配 | NUMERIC | NUMERIC、DECIMAL、BOOLEAN、DATE |
匹配顺序很重要。某个自定义类型名同时含有多个关键字时,前面的规则会先命中。工程中不要依赖这种技巧;直接使用 INTEGER、REAL、TEXT、BLOB、NUMERIC 等容易读懂的声明更安全。
用 typeof() 观察真实存储类型
下面的 SQL 同时验证五种存储类别和五类亲和性的转换行为,可直接在 SQLite 客户端执行。
DROP TABLE IF EXISTS affinity_demo;
CREATE TABLE affinity_demo (
i INTEGER,
r REAL,
t TEXT,
b BLOB,
n NUMERIC
);
INSERT INTO affinity_demo VALUES ('42', '42', '42', '42', '42');
SELECT
typeof(NULL) AS null_type,
typeof(1) AS integer_type,
typeof(1.5) AS real_type,
typeof('abc') AS text_type,
typeof(x'4142') AS blob_type;
SELECT
typeof(i) AS i_type,
typeof(r) AS r_type,
typeof(t) AS t_type,
typeof(b) AS b_type,
typeof(n) AS n_type
FROM affinity_demo;预期结果
null|integer|real|text|blob
integer|real|text|text|integer第二行最关键:输入明明都是文本字面量 '42',但 INTEGER 与 NUMERIC 亲和性把它转换为整数,REAL 转为浮点数,TEXT 保持文本,BLOB 亲和性不主动转换,因此仍是文本。不同客户端可能显示列标题,但 typeof() 的五个结果应一致。
转换发生在哪些边界
亲和性只在转换可行时生效。NULL 和 BLOB 通常不因列亲和性而改变;无法解析为数值的文本插入 INTEGER、REAL 或 NUMERIC 列时,也会继续以 TEXT 保存。
这意味着普通 SQLite 表不能仅靠声明 INTEGER 就阻止文本 abc 进入该列。若业务需要更严格的类型约束,可以使用 STRICT 表,或者配合 CHECK、应用层校验等方式明确拒绝不符合要求的数据。
设计表结构时怎么选类型
金额、计数、时间戳等需要数值比较的字段,应选择能体现数值语义的类型;名称、状态码等按文本处理;二进制内容使用 BLOB。不要把“能存进去”当作“类型设计正确”,因为排序、比较、索引和表达式计算都会受到实际存储类别影响。
遇到类型行为不符合预期时,先用 typeof(列名) 查看真实存储类别,再检查列的声明类型和输入值。把“值的类型”和“列的转换偏好”分开看,是排查 SQLite 类型问题的第一步。