数据库原理与选型 / 系统课时

存储类别与类型亲和性

存储类别与类型亲和性

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 客户端执行。

sql
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;

预期结果

text
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 类型问题的第一步。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

存储类别与类型亲和性

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通