3. SQLite 数据类型体系：动态类型、存储类与亲和性规则

核心观点：SQLite 采用动态类型系统，字段不强制绑定固定数据类型，而是通过存储类（Storage Class）和类型亲和性（Type Affinity）实现灵活性与传统 SQL 数据类型之间的平衡。理解这套机制是正确使用 SQLite 的前提。

3.1 动态类型与静态类型的本质差异

对比维度静态类型数据库（MySQL/PostgreSQL）SQLite 动态类型
列定义严格绑定类型（如 INT、VARCHAR(100)）仅声明类型建议（如 INTEGER、TEXT）
存储值必须符合列类型，否则报错或隐式转换每个值独立记录自身的存储类，与列声明无关
灵活性高一致性，低灵活性高灵活性，但需注意隐式转换风险
典型应用企业级 OLTP、强约束场景嵌入式、原型、配置存储、边缘设备

在 MySQL 中，如果创建 CREATE TABLE t (c INT)，则列 c 只能存储整数值；若插入字符串 'abc'，会导致转换错误或插入 0。SQLite 则允许任何列插入任何类型的值，即使在 INTEGER 列中写入文本 'hello' 也不会报错——该值会以 TEXT 存储类保存。

3.2 五大存储类

SQLite 每个值在物理存储上属于以下五种之一，称为存储类：

  • NULL —— 空值，不占数据空间。
  • INTEGER —— 有符号整数，根据值大小使用 1~8 字节可变长度存储。
  • REAL —— 浮点数，固定 8 字节 IEEE 754 双精度。
  • TEXT —— 字符串，使用数据库编码（UTF‑8/UTF‑16LE/UTF‑16BE）存储。
  • BLOB —— 二进制大对象，原样存储，不进行编码转换。

注意：SQLite 没有独立的 BOOLEAN 或 DATETIME 存储类。布尔值用 INTEGER 0/1 表示，日期时间用 TEXT（ISO‑8601）或 REAL（儒略日）或 INTEGER（unix 时间戳）表示。

3.3 类型亲和性规则

当建表语句包含传统类型名（如 INT、CHAR、DOUBLE）时，SQLite 会根据亲和性规则将其映射为五种存储类之一，从而决定插入值时的自动转换倾向。规则优先级如下：

  1. INTEGER AFFINITY —— 声明包含 INT 关键字（如 INT、BIGINT、INTEGER）
  2. TEXT AFFINITY —— 包含 CHAR、CLOB、TEXT 关键字（如 VARCHAR(255)、CHAR(10)）
  3. REAL AFFINITY —— 包含 REAL、FLOAT、DOUBLE 关键字
  4. NUMERIC AFFINITY —— 包含 NUMERIC、DECIMAL、BOOLEAN、DATE、DATETIME 等无明确 INT/CHAR/FLOAT 关键字的类型
  5. BLOB AFFINITY —— 不带任何类型名或使用 BLOB 关键字

实际插入值时，SQLite 会按以下顺序尝试转换（以 INTEGER 亲和性列为例）：

  • 若值为 TEXT 且可以无损解析为整数（如 '123'），则转换为 INTEGER 存储。
  • 若值为 REAL 且小数部分为零（如 12.0），则转换为 INTEGER。
  • 否则保持原存储类。

常见误区：认为 CREATE TABLE t (a INTEGER) 后，插入 'abc' 会报错。实际上 SQLite 不会报错，该值会以 TEXT 存储类保存，且不会自动转换为 0——与 MySQL 不同。

SQLite 存储类与亲和性映射 NULL INTEGER REAL TEXT BLOB → 任何 NULL → 整数、布尔 → 浮点数 → 字符串、JSON → 二进制、图片 列声明类型 亲和性规则判定 INTEGER AFFINITY TEXT AFFINITY REAL AFFINITY NUMERIC AFFINITY BLOB AFFINITY 说明：存储类决定值如何存入文件，亲和性决定插入时是否自动转换。两者共同构成 SQLite 数据类型体系。

3.4 常见误区

  • 误区一：SQLite 没有数据类型。
    事实：SQLite 有严格的存储类，只是类型绑定在值上而非列上。
  • 误区二：INTEGER 列可以安全存放任意大整数。
    事实：INTEGER 存储类支持最大 8 字节有符号整数（-9.22e18 ~ 9.22e18），超范围将转为 REAL 或截断。
  • 误区三：TEXT 列插入数字会损失精度。
    事实：若声明为 TEXT 列，插入数字会作为 TEXT 存储，不会丢失精度，但比较时按字符串规则（词典序）进行，需要小心。
  • 误区四：BLOB 和 TEXT 在存储上完全一样。
    事实：BLOB 不进行字符编码转换，TEXT 会根据数据库编码进行转换，且排序方式不同。

3.5 应用边界与最佳实践

场景建议理由
主键自增列使用 INTEGER PRIMARY KEY自动获得 ROWID 优化，列类型为 INTEGER 亲和性
存储 JSON 文档使用 TEXT 列SQLite 3.9+ 有内置 JSON 函数，TEXT 存储保留原始格式
存储二进制小文件（< 10KB）使用 BLOB 列避免文件系统碎片，但注意备份性能
需要严格类型约束在应用层校验，或启用 STRICT 表（SQLite 3.37+）STRICT 模式强制值类型必须匹配列声明

3.6 练习题

  1. （选择）SQLite 中，INSERT INTO t (col) VALUES (3.14) 且 col 列声明为 INTEGER，实际存储为哪个存储类？
    A. INTEGER B. REAL C. TEXT D. NUMERIC
  2. （判断）若列声明为 VARCHAR(50)，插入整数 42 后可以用索引加速 WHERE col = 42 查询吗？说明理由。
  3. （填空）SQLite 五大存储类分别是 ________、________、________、________、________。
  4. （简答）设计一个标签表，要求标签名称（最长 200 字符）、创建时间（精确到秒）、关联图片（PNG 文件，平均 50KB）。应该如何选择列类型？给出建表语句片段（可使用 SQLite 方言）。

实用清单：亲和性规则速查

-- 整数亲和性
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
-- 文本亲和性
CHARACTER, VARCHAR, VARYING CHARACTER, NCHAR, NATIVE CHARACTER, NVARCHAR, TEXT, CLOB
-- 实数亲和性
REAL, DOUBLE, DOUBLE PRECISION, FLOAT
-- 数值亲和性
NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME, TIMESTAMP
-- 二进制亲和性
BLOB 或不声明任何类型名
← 上一章 📖 返回目录 下一章 →