数据库基础体系 · 第 114/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQLite 类型系统与 STRICT 表：亲和性、存储类、约束和兼容

SQLite 的类型系统与 Oracle、PostgreSQL 等传统关系数据库有一个根本差异：SQLite 通常不会因为列声明为某种类型，就拒绝存储其他类型的值。

SQLite 的列声明主要用于确定“类型亲和性”（type affinity），而不是像许多数据库那样构成严格的存储类型约束。真正存储到单元格中的，是一个运行时的“存储类”（storage class）。STRICT 表则在此基础上增加了更严格的类型检查，但它仍然不是把 SQLite 变成 Oracle 的 NUMBER、VARCHAR2 和 DATE 模型。

理解这套机制，需要区分四个层次：

1. 声明类型：建表时写在列定义中的字符串，例如 VARCHAR(20)、DECIMAL(10,2)。
2. 类型亲和性：SQLite 根据声明类型推导出的转换倾向，例如 TEXT、NUMERIC、INTEGER。
3. 存储类：实际值在 SQLite 中以 NULL、INTEGER、REAL、TEXT 或 BLOB 中的一种存储。
4. 约束：NOT NULL、UNIQUE、CHECK、PRIMARY KEY、外键以及 STRICT 类型检查。

如果只记住“SQLite 是动态类型”或“STRICT 是强类型”，都会遗漏关键细节。

---

一、声明类型不是存储类型

考虑下面的表：

CREATE TABLE reading (
    id       INTEGER,
    amount   DECIMAL(10, 2),
    name     VARCHAR(20),
    payload  BLOB
);

在许多数据库中：

• DECIMAL(10, 2) 会决定数值的精度和小数位；
• VARCHAR(20) 会限制字符串长度；
• BLOB 会限制为二进制数据。

在 SQLite 中，上述声明首先会被转换为类型亲和性：

声明类型	推导出的亲和性
INTEGER	INTEGER
DECIMAL(10,2)	NUMERIC
VARCHAR(20)	TEXT
BLOB	BLOB

这里的 DECIMAL(10,2) 不会自动提供固定精度和小数位约束，VARCHAR(20) 也不会自动拒绝超过 20 个字符的字符串。

例如：

CREATE TABLE demo (
    amount DECIMAL(10, 2),
    name   VARCHAR(3)
);

INSERT INTO demo(amount, name)
VALUES ('12.3456', 'abcdef');

SELECT
    amount,
    typeof(amount),
    name,
    length(name)
FROM demo;

典型结果是：

12.3456|real|abcdef|6

这里发生了两件事：

1. amount 的 NUMERIC 亲和性尝试把字符串 '12.3456' 转换为数值，结果存储为 REAL；
2. name 的 TEXT 亲和性保留字符串，但 VARCHAR(3) 没有自动执行长度检查。

如果业务需要精度、范围或长度限制，必须显式写约束：

CREATE TABLE payment (
    amount_cents INTEGER NOT NULL
        CHECK (amount_cents >= 0),

    currency TEXT NOT NULL
        CHECK (length(currency) = 3),

    note TEXT
        CHECK (note IS NULL OR length(note) <= 200)
);

这类约束才是数据库实际验证的条件。

---

二、五种存储类：值最终以什么形式存在

SQLite 的五种存储类是：

存储类	含义
NULL	空值
INTEGER	有符号整数
REAL	IEEE 754 浮点数
TEXT	文本，通常使用数据库连接指定的 UTF 编码
BLOB	原始字节序列

可以用 typeof() 观察一个表达式或列中实际值的存储类：

SELECT
    typeof(NULL),
    typeof(1),
    typeof(1.0),
    typeof('1'),
    typeof(x'31');

结果类似：

null|integer|real|text|blob

typeof() 观察的是当前值的存储类，不是列的声明类型，也不是列的亲和性。

例如：

CREATE TABLE values_demo (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO values_demo(n, t, b)
VALUES (1, 1, 1);

SELECT
    typeof(n),
    typeof(t),
    typeof(b)
FROM values_demo;

通常结果为：

integer|text|integer

原因是：

• n 的 NUMERIC 亲和性允许整数保持为 INTEGER；
• t 的 TEXT 亲和性把整数转换为文本；
• b 的 BLOB 亲和性不强制转换，因此保留为 INTEGER。

因此，“列声明为 BLOB”不等于“每一行都一定只能存 BLOB”；在普通 SQLite 表中，BLOB 亲和性主要意味着“不主动做类型转换”。

INTEGER 的大小和 REAL 的边界

SQLite 的 INTEGER 通常以 1 到 8 字节的变长整数形式存储，但其数值范围是有符号 64 位整数：

-9223372036854775808 到 9223372036854775807

超出整数范围的数值可能转为 REAL。REAL 使用 IEEE 754 双精度浮点数，因此不能精确表示所有十进制小数和所有大整数。

这也是金额字段不应直接依赖 REAL 的原因。更可控的表示方式通常是：

amount_cents INTEGER NOT NULL

或者把十进制定点值作为规范化文本保存，但后者需要应用层和约束共同保证格式。

---

三、类型亲和性：SQLite 插入时如何决定是否转换

1. 亲和性的推导规则

SQLite 从声明类型字符串推导亲和性，规则按以下顺序匹配：

1. 声明类型中包含 INT：INTEGER 亲和性；
2. 包含 CHAR、CLOB 或 TEXT：TEXT 亲和性；
3. 声明类型为 BLOB，或者没有声明类型：BLOB 亲和性；
4. 包含 REAL、FLOA 或 DOUB：REAL 亲和性；
5. 其他情况：NUMERIC 亲和性。

关键字匹配优先级很重要。例如：

"FLOATING POINT"

包含 INT，所以推导出的不是 REAL，而是 INTEGER 亲和性。

同样：

"CHARINT"

同时包含 CHAR 和 INT，由于 INT 规则先判断，它得到 INTEGER 亲和性。

这些声明类型在普通表中都可以被 SQLite 接受：

CREATE TABLE affinity_demo (
    a INT,
    b VARCHAR(20),
    c DECIMAL(10,2),
    d BLOB,
    e FLOATING_POINT
);

可以用 PRAGMA table_info(affinity_demo); 查看声明类型，但 table_info 显示的是声明文本，不会直接告诉你亲和性。推导亲和性必须按照上述规则理解。

2. 插入时的转换过程

以 NUMERIC 亲和性的列为例：

CREATE TABLE numeric_demo (
    value NUMERIC
);

INSERT INTO numeric_demo(value) VALUES
    ('42'),
    ('42.5'),
    ('hello'),
    (42),
    (42.5);

SELECT value, typeof(value)
FROM numeric_demo;

典型结果：

42|integer
42.5|real
hello|text
42|integer
42.5|real

逐行分析：

• '42' 是 TEXT，但可以解释为整数，因此转换为 INTEGER；
• '42.5' 是 TEXT，可以解释为实数，因此转换为 REAL；
• 'hello' 无法转换为数值，因此保留为 TEXT；
• 42 本来就是 INTEGER；
• 42.5 本来就是 REAL。

NUMERIC 亲和性不会把所有值强制变成某一个固定存储类，而是尽量选择整数或实数表示。

对于 TEXT 亲和性：

CREATE TABLE text_demo (
    value TEXT
);

INSERT INTO text_demo(value) VALUES (42), (42.5), (x'6162');

SELECT value, typeof(value)
FROM text_demo;

整数和实数通常会转为文本；BLOB 则通常仍保持 BLOB。结果可能类似：

42|text
42.5|text
ab|blob

这里的 ab 是 CLI 对 BLOB 的显示形式，不应把显示形式误认为存储类；typeof(value) 才是判断依据。

3. “无损转换”不是简单的字符串匹配

亲和性转换的核心不是“声明类型必须匹配字符串”，而是 SQLite 尝试在不改变值含义的情况下转换。

例如，在普通 NUMERIC 列中：

INSERT INTO numeric_demo(value) VALUES ('003');

通常会存为整数 3，而不是文本 '003'。这意味着前导零的文本格式丢失了。

如果前导零具有业务意义，例如邮政编码、外部编号或账号，就不应该使用 NUMERIC 亲和性：

CREATE TABLE postal_code (
    code TEXT NOT NULL
);

因为：

INSERT INTO postal_code(code) VALUES ('003');

会保留为 TEXT '003'。

---

四、表达式和比较中的类型转换

类型亲和性不只影响 INSERT，还会影响比较、索引查找以及表达式求值。

需要区分：

• 列具有亲和性；
• 字面量和参数通常没有列亲和性；
• 比较时 SQLite 可能根据操作数的亲和性，把另一侧转换。

例如：

CREATE TABLE compare_demo (
    n NUMERIC
);

INSERT INTO compare_demo(n)
VALUES (8), ('8'), ('08'), ('8.0'), ('x');

SELECT n, typeof(n)
FROM compare_demo;

对于 NUMERIC 列，前四个值通常都会转换为数值，其中 8、'8'、'08' 和 '8.0' 可能都以整数 8 存储；'x' 保留为文本。

因此：

SELECT n, typeof(n)
FROM compare_demo
WHERE n = '8';

通常会匹配所有存储为数值 8 的行，而不只是最初插入字符串 '8' 的行。

如果需要观察表达式本身的类型，可以写：

SELECT
    typeof(n),
    typeof('8'),
    n = '8'
FROM compare_demo;

存储类排序

在没有适用的数值转换或文本转换时，SQLite 的存储类有一个总体排序顺序：

NULL < INTEGER/REAL < TEXT < BLOB

数值之间按数值比较；文本之间通常按排序规则比较；BLOB 按字节序比较。

这意味着混合类型列可能出现令人意外的排序结果：

CREATE TABLE mixed(value);

INSERT INTO mixed(value) VALUES
    (2),
    ('10'),
    (1.5),
    ('abc'),
    (NULL);

SELECT value, typeof(value)
FROM mixed
ORDER BY value;

这个结果不是“把所有值先转成字符串”或“把所有值先转成数字”，而是受到存储类排序、转换规则和排序规则共同影响。

工程上，如果一列参与排序、范围查询、索引查找或唯一性判断，最好让该列的有效值保持同一语义和同一主要存储类。否则即使 SQL 语法正确，结果也可能与应用层直觉不同。

亲和性与索引

索引保存的是键值及其排序信息，而不是一个脱离类型的字符串列表。查询能否有效利用索引，取决于：

• WHERE 条件是否能转化为索引查找；
• 列和参数的类型转换是否一致；
• 表达式是否破坏了可索引形式；
• 排序规则、复合索引列顺序和统计信息。

例如：

CREATE TABLE user_account (
    id INTEGER PRIMARY KEY,
    external_id TEXT NOT NULL
);

CREATE INDEX user_account_external_id_idx
ON user_account(external_id);

如果 external_id 是文本编号，就应始终按文本语义绑定参数：

SELECT id
FROM user_account
WHERE external_id = ?;

不要把同一个业务字段有时按整数绑定、有时按文本绑定，再期待所有比较和索引行为都完全符合应用层的“字符串编号”直觉。可以用：

EXPLAIN QUERY PLAN
SELECT id
FROM user_account
WHERE external_id = '00123';

检查查询计划，但“使用了索引”并不等于“业务语义正确”；计划和结果必须分别验证。

---

五、普通表中的约束：类型不是约束

普通 SQLite 表允许同一列的不同记录具有不同存储类。下面的表在语法和运行时都合法：

CREATE TABLE ordinary (
    quantity INTEGER
);

INSERT INTO ordinary(quantity) VALUES
    (10),
    ('20'),
    ('not-a-number'),
    (NULL);

此时 quantity 可能包含：

10       integer
20       integer
not-a-number text
NULL     null

INTEGER 只是 INTEGER 亲和性，不表示“只能是整数”。

如果需要普通表也拒绝非整数，可以使用 CHECK：

CREATE TABLE quantity_value (
    quantity INTEGER NOT NULL
        CHECK (
            typeof(quantity) = 'integer'
            AND quantity >= 0
        )
);

这条约束分别保证：

1. NOT NULL：值不能为 NULL；
2. typeof(quantity) = 'integer'：运行时存储类必须是整数；
3. quantity >= 0：整数不能为负数。

插入测试：

INSERT INTO quantity_value(quantity) VALUES (10);
-- 成功

INSERT INTO quantity_value(quantity) VALUES ('10');
-- 对 INTEGER 亲和性的普通表而言，通常会先转成 integer 10，因此可能成功

INSERT INTO quantity_value(quantity) VALUES ('10.5');
-- 转换后是 REAL，CHECK 失败

INSERT INTO quantity_value(quantity) VALUES ('abc');
-- 保留为 TEXT，CHECK 失败

这说明 CHECK 检查的是表达式结果，而不是声明类型。它必须根据实际业务语义写清楚。

CHECK 中的 NULL

SQLite 的 CHECK 约束在表达式结果为 0 时失败；结果为非零值或 NULL 时通过。

因此：

CREATE TABLE score1 (
    score INTEGER CHECK (score >= 0)
);

允许 score 为 NULL。如果不允许空值，需要同时写：

CREATE TABLE score2 (
    score INTEGER NOT NULL
        CHECK (score >= 0)
);

这是常见的误解：CHECK (score >= 0) 不等价于“必须是非空且大于等于零”。

UNIQUE 和类型差异

UNIQUE 约束并不把不同存储类自动规范为同一种业务类型。由于 SQLite 的比较和亲和性规则，数值 1、文本 '1'、BLOB x'31' 是否被视为相同，不能脱离列亲和性和比较规则简单判断。

如果唯一键是外部编号，应该把它明确建模为 TEXT，并在写入边界统一格式：

CREATE TABLE customer (
    id INTEGER PRIMARY KEY,
    customer_code TEXT NOT NULL UNIQUE
);

不要把外部编号设计为“有时数字、有时文本”的混合列，再依赖 UNIQUE 猜测业务等价关系。

---

六、STRICT 表：在 SQLite 中增加类型边界

STRICT 表在 SQLite 3.37.0 引入。语法是：

CREATE TABLE strict_reading (
    id INTEGER PRIMARY KEY,
    amount REAL,
    label TEXT,
    raw BLOB
) STRICT;

STRICT 表只允许以下核心类型名称：

• INT
• INTEGER
• REAL
• TEXT
• BLOB
• ANY

不能直接把普通表中常见的 VARCHAR(20)、DECIMAL(10,2) 等声明类型照搬到严格表中：

CREATE TABLE bad_strict (
    amount DECIMAL(10,2)
) STRICT;

这会因类型名称不属于 STRICT 允许的类型集合而失败。

STRICT 插入的基本规则

在 STRICT 表中，SQLite 仍然会尝试进行适当的类型转换；但如果值不能无损转换到声明类型，就会报类型约束错误，而不是像普通表那样保留为任意存储类。

CREATE TABLE strict_quantity (
    quantity INTEGER
) STRICT;

以下行为可以这样理解：

INSERT INTO strict_quantity(quantity) VALUES (10);
-- 成功：本来就是 INTEGER

INSERT INTO strict_quantity(quantity) VALUES ('10');
-- 通常成功：文本可以无损转换为整数 10

INSERT INTO strict_quantity(quantity) VALUES ('10.5');
-- 失败：不能无损转换为 INTEGER

INSERT INTO strict_quantity(quantity) VALUES ('abc');
-- 失败：无法转换为 INTEGER

错误通常属于 SQLITE_CONSTRAINT_DATATYPE，应用程序应把它当作数据契约违反，而不是普通查询无结果。

STRICT 不意味着所有输入都必须在客户端先绑定成目标类型；它允许可验证的、无损的转换。但它会阻止“转换不了就照样存进去”的行为。

STRICT 不提供长度和精度约束

下面的声明在 STRICT 表中仍然不能表达长度和精度：

CREATE TABLE strict_text (
    name TEXT
) STRICT;

它保证的是存储类为 TEXT，而不是长度上限。

应显式添加约束：

CREATE TABLE strict_product (
    name TEXT NOT NULL
        CHECK (length(name) <= 100),

    price_cents INTEGER NOT NULL
        CHECK (price_cents >= 0)
) STRICT;

STRICT 解决的是“类型类别是否正确”；CHECK 解决的是“值是否符合业务条件”。

---

七、STRICT 中的 ANY 与普通表中的 ANY

ANY 是 STRICT 表中一个特殊而重要的类型。

在 STRICT 表中，ANY 表示：

接受任意存储类，并且 SQLite 不对输入值执行亲和性转换。

例如：

CREATE TABLE strict_any (
    value ANY
) STRICT;

INSERT INTO strict_any(value) VALUES
    ('000123'),
    ('3.0e+5'),
    (42),
    (3.14),
    (NULL);

SELECT value, typeof(value)
FROM strict_any;

文本 '000123' 会保持为 TEXT，文本 '3.0e+5' 也会保持为 TEXT。整数、实数和 NULL 则分别保留为对应存储类。

这与普通表中的 ANY 不同。普通表不受 STRICT 的 ANY 规则保护，列的亲和性仍可能导致看似文本的数值字符串被转换：

CREATE TABLE ordinary_any (
    value ANY
);

INSERT INTO ordinary_any(value)
VALUES ('000123'), ('3.0e+5');

SELECT value, typeof(value)
FROM ordinary_any;

由于 ANY 在普通表中会按普通声明类型规则得到 NUMERIC 亲和性，结果通常可能是：

123|integer
300000|integer

因此，若要求“原样保存输入的存储类和文本形式”，应使用：

CREATE TABLE raw_input (
    value ANY
) STRICT;

但 ANY 不是“自动识别并统一类型”的工具。它恰恰意味着列可以混合存储类。读取端必须使用 typeof() 或应用协议明确区分不同情况。

---

八、STRICT、主键、ROWID 与 NULL

SQLite 的 PRIMARY KEY 行为与 Oracle 等数据库不能直接类比。

1. 普通 rowid 表中的 INTEGER PRIMARY KEY

CREATE TABLE parent (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

这里的 id INTEGER PRIMARY KEY 是特殊定义：

• 它对应表的 rowid；
• 插入 NULL 时，SQLite 可以自动分配 rowid；
• 它具有整数键语义。

2. 普通表中的其他 PRIMARY KEY

SQLite 历史兼容行为允许某些普通 rowid 表的主键列出现 NULL，除非：

• 主键是 INTEGER PRIMARY KEY；
• 表使用 WITHOUT ROWID；
• 声明了 NOT NULL；
• 或者使用了 STRICT 表。

因此，若需要明确的非空主键语义，不应只依赖普通表中的：

id TEXT PRIMARY KEY

而应写成：

id TEXT PRIMARY KEY NOT NULL

或者使用 STRICT / WITHOUT ROWID 等明确约束边界。

3. WITHOUT ROWID

WITHOUT ROWID 表使用主键作为实际组织键，不使用隐含 rowid：

CREATE TABLE code_map (
    code TEXT PRIMARY KEY,
    value TEXT NOT NULL
) WITHOUT ROWID;

这适合主键本身就是自然键、且不需要 rowid 行为的场景。但它会改变存储组织、主键要求和部分迁移语义，不应仅作为“让类型更严格”的替代品。

---

九、外键不是自动开启的

SQLite 支持外键，但外键约束通常需要在每个数据库连接上启用：

PRAGMA foreign_keys = ON;

这是连接级设置，不应只在某一个管理工具中临时执行，然后假设所有应用连接都继承了它。

完整示例：

PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE department (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL,
    name TEXT NOT NULL,

    FOREIGN KEY (department_id)
        REFERENCES department(id)
) STRICT;

COMMIT;

验证当前连接是否启用：

PRAGMA foreign_keys;

结果为 1 才表示当前连接启用。

外键检查的是引用完整性，不是通用类型检查。父子键的声明和比较语义仍然需要保持一致。例如，不应让父键按 TEXT 建模、子键却按任意混合值写入。

---

十、完整示例：普通表、STRICT 表和诊断

下面的脚本可以在 SQLite CLI 中执行。它不依赖网络和扩展，适合在单个数据库连接、单个事务边界内验证类型行为。

.mode column
.headers on

DROP TABLE IF EXISTS ordinary_item;
DROP TABLE IF EXISTS strict_item;

CREATE TABLE ordinary_item (
    quantity INTEGER,
    code VARCHAR(5)
);

CREATE TABLE strict_item (
    quantity INTEGER,
    code TEXT
) STRICT;

INSERT INTO ordinary_item(quantity, code)
VALUES
    ('12', '0012'),
    ('12.5', '00012'),
    ('abc', 'abcdef');

SELECT
    quantity,
    typeof(quantity) AS quantity_type,
    code,
    typeof(code) AS code_type,
    length(code) AS code_length
FROM ordinary_item;

普通表中，预期可以看到：

• '12' 因 INTEGER 亲和性转为整数；
• '12.5' 无法无损转为 INTEGER，通常保留为 REAL；
• 'abc' 保留为 TEXT；
• VARCHAR(5) 不限制 'abcdef' 的长度；
• '0012' 在 TEXT 亲和性下保留前导零。

接着执行：

INSERT INTO strict_item(quantity, code)
VALUES ('12', '0012');
-- 成功：quantity 可以无损转换为 INTEGER，code 保持 TEXT

INSERT INTO strict_item(quantity, code)
VALUES ('12.5', '00012');
-- 失败：quantity 不能无损转换为 INTEGER

因为第二条语句失败，可以通过应用程序捕获 SQLITE_CONSTRAINT_DATATYPE。如果这两条语句是在显式事务中执行，应用程序应明确决定是回滚整个事务，还是只处理失败的单条写入；不要把 SQLite 的单条语句失败与整个业务事务的恢复策略混为一谈。

进一步增加业务约束：

DROP TABLE IF EXISTS strict_item;

CREATE TABLE strict_item (
    quantity INTEGER NOT NULL
        CHECK (quantity >= 0),

    code TEXT NOT NULL
        CHECK (length(code) = 5)
) STRICT;

INSERT INTO strict_item(quantity, code)
VALUES (12, '00123');
-- 成功

INSERT INTO strict_item(quantity, code)
VALUES (-1, '00123');
-- CHECK 失败

INSERT INTO strict_item(quantity, code)
VALUES (12, '123');
-- CHECK 失败

这里有三层保障：

1. STRICT：quantity 必须是 INTEGER，code 必须是 TEXT；
2. NOT NULL：两列都不能为 NULL；
3. CHECK：数值范围和字符串长度符合业务要求。

---

十一、迁移普通表到 STRICT 表时会发生什么

把已有普通表改成 STRICT，不是简单地在表名后加一个关键字。SQLite 的 ALTER TABLE 能力有限，实际迁移通常需要新建表、复制数据、删除旧表并重建索引和触发器。

推荐在一个明确的事务中完成：

PRAGMA foreign_keys = OFF;

BEGIN;

CREATE TABLE account_new (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    balance_cents INTEGER NOT NULL
        CHECK (balance_cents >= 0)
) STRICT;

INSERT INTO account_new(id, code, balance_cents)
SELECT id, code, balance_cents
FROM account_old;

-- 只有 INSERT 成功，才继续替换
DROP TABLE account_old;
ALTER TABLE account_new RENAME TO account_old;

COMMIT;

PRAGMA foreign_keys = ON;

但这段迁移有一个重要前提：旧数据必须满足新表的约束。实际迁移前应先诊断：

SELECT rowid, balance_cents, typeof(balance_cents)
FROM account_old
WHERE balance_cents IS NULL
   OR typeof(balance_cents) <> 'integer'
   OR balance_cents < 0;

对于 TEXT 列长度：

SELECT rowid, code, length(code)
FROM account_old
WHERE code IS NULL
   OR length(code) <> 5;

如果旧表中存在：

• 数值字符串和数字混合；
• NULL；
• 超长文本；
• 无法转换的文本；
• 前导零需要保留但原先使用 NUMERIC 亲和性；
• 依赖 rowid 的外部逻辑；

那么迁移必须先定义清洗规则。直接复制可能在中途失败，事务回滚后旧表仍在，但迁移不能算完成。

迁移时的部署边界

如果数据库由多个进程或多个版本的应用共同访问，还需要考虑：

• 新旧应用是否同时运行；
• 新应用是否要求 STRICT 表已经存在；
• 旧应用是否能读取新表；
• 是否有长事务阻塞表替换；
• 索引、触发器、视图和外键是否全部重建；
• 备份是否覆盖迁移前后的数据库文件。

SQLite 的数据库文件是持久化状态，但 PRAGMA foreign_keys 等设置具有连接级作用；不能把文件迁移和连接初始化混为一件事。

---

十二、与 Oracle 类型系统的兼容边界

SQLite 和 Oracle 都支持 SQL，但类型语义不是可直接替换的。

1. VARCHAR(20) 不等于 Oracle 的长度约束

在 Oracle 中，VARCHAR2(20) 是带长度限制的数据类型；在 SQLite 中，VARCHAR(20) 主要产生 TEXT 亲和性。

迁移 Oracle 表时，必须把长度要求转化为 SQLite 约束：

CREATE TABLE user_name (
    name TEXT NOT NULL
        CHECK (length(name) <= 20)
) STRICT;

还需要确认长度语义：

• length(text) 对 TEXT 通常按字符数计算；
• 对 BLOB 则按字节数计算；
• Oracle 的字符语义、字节语义、字符集和补充字符处理可能不同。

不能只把 VARCHAR2(20) 的文字替换成 TEXT，然后认为约束已保留。

2. NUMBER(p,s) 没有直接等价物

SQLite 没有 Oracle NUMBER(10,2) 那样的内建定点精度和小数位约束。

可以根据业务选择：

以最小货币单位存储

CREATE TABLE invoice (
    amount_cents INTEGER NOT NULL
        CHECK (amount_cents >= 0)
) STRICT;

这是最容易验证和跨语言传输的方案。

使用 TEXT 保存规范化十进制

CREATE TABLE decimal_value (
    amount TEXT NOT NULL
        CHECK (amount GLOB '[0-9]*')
) STRICT;

但这个示例还不能完整验证小数位、符号和前导格式；生产约束必须针对允许的十进制语法精确设计，不能把 GLOB '[0-9]*' 误认为完整十进制校验器。

使用 REAL

适合允许浮点误差的测量值，不适合作为财务定点数的直接替代。

3. DATE、TIMESTAMP 和 BOOLEAN

SQLite 没有独立的日期时间存储类。日期时间通常以：

• ISO-8601 TEXT；
• Unix 时间戳 INTEGER；
• Julian day REAL；

三种形式之一保存。SQLite 的日期时间函数可以处理一部分标准格式，但列声明为 DATE 或 TIMESTAMP 并不会像 Oracle 那样自动提供对应类型的全部语义。

同样，SQLite 没有独立 BOOLEAN 存储类。常见约定是：

enabled INTEGER NOT NULL
    CHECK (enabled IN (0, 1))

如果使用 STRICT：

CREATE TABLE feature_flag (
    enabled INTEGER NOT NULL
        CHECK (enabled IN (0, 1))
) STRICT;

STRICT 保证整数类型，CHECK 保证只有 0 和 1；两者作用不同。

4. 空字符串差异

Oracle 中空字符串在许多字符类型语境下会被视为 NULL；SQLite 中：

SELECT
    typeof(''),
    length(''),
    '' IS NULL;

结果是：

text|0|0

也就是说，SQLite 的空字符串是 TEXT，不是 NULL。

从 Oracle 迁移时，下面两种值必须明确区分：

NULL   —— 没有值
''     —— 长度为 0 的文本

这会影响：

• NOT NULL；
• IS NULL；
• UNIQUE；
• 默认值；
• 应用层反序列化；
• 数据迁移脚本。

---

十三、常见误解与失败表现

误解一：INTEGER 列只能存整数

错误：

CREATE TABLE t (value INTEGER);
INSERT INTO t(value) VALUES ('abc');

普通表通常不会因 'abc' 无法转为整数而失败；它可能以 TEXT 存储。

修复方式：

• 使用 STRICT；
• 或添加 CHECK (typeof(value) = 'integer')；
• 如果还需要范围，再增加数值条件。

误解二：VARCHAR(10) 自动限制长度

错误：

CREATE TABLE t (name VARCHAR(10));
INSERT INTO t(name) VALUES ('01234567890');

这通常成功。长度限制必须显式写成：

name TEXT CHECK (length(name) <= 10)

误解三：DECIMAL(10,2) 自动四舍五入

SQLite 不会因为看到这个声明就自动建立 Oracle 式精度、小数位和舍入语义。需要使用整数单位、规范化文本或应用层十进制库，并配合数据库约束。

误解四：STRICT 等同于传统数据库强类型

STRICT 能拒绝无法无损转换的值，但它仍然：

• 只有有限的核心类型名；
• 没有独立日期类型；
• 没有独立布尔类型；
• 不提供 VARCHAR(n) 的长度语义；
• 不提供 DECIMAL(p,s) 的精度语义；
• 不自动保证业务范围、格式和编码规则。

误解五：CHECK (x > 0) 自动拒绝 NULL

不会。CHECK 表达式得到 NULL 时通过；需要 NOT NULL。

误解六：typeof() 是列类型

SELECT typeof(column_name) FROM table_name;

得到的是某一行某个值的运行时存储类，不是列声明。列声明应通过 schema 信息查看，业务类型还需要结合约束和应用协议判断。

---

十四、如何诊断混合类型数据

面对已有数据库，先观察实际值，而不是只看建表 SQL：

SELECT
    typeof(value) AS storage_class,
    count(*) AS row_count
FROM some_table
GROUP BY typeof(value)
ORDER BY storage_class;

查找非预期类型：

SELECT rowid, value, typeof(value)
FROM some_table
WHERE typeof(value) NOT IN ('integer', 'null');

检查可能被数值亲和性改变的编号：

SELECT rowid, code, typeof(code)
FROM some_table
WHERE code LIKE '0%';

检查一个表达式在绑定参数和列亲和性下的行为：

SELECT
    typeof(?),
    typeof(CAST(? AS TEXT)),
    typeof(CAST(? AS INTEGER));

参数需要重复绑定或由 API 正确设置。这里要注意：不同驱动对 int、float、字符串和字节数组的绑定方式不同，绑定类型可能改变比较和存储结果。调试时应同时记录：

• SQL 文本；
• 参数值；
• 参数绑定类型；
• typeof() 观察结果；
• EXPLAIN QUERY PLAN 输出；
• 当前连接的 PRAGMA 设置。

---

十五、选择普通表、STRICT 和 ANY

可以把三种设计理解为不同的数据契约：

普通表

适合：

• 快速原型；
• 外部数据暂存；
• 明确需要 SQLite 动态类型行为的场景。

代价是：类型错误可能在写入时不暴露，问题延迟到查询、排序或业务计算阶段。

STRICT 表

适合：

• 业务核心表；
• 需要尽早发现数据类型错误的应用；
• 希望 schema 对写入边界提供更强保证的场景。

但仍应配合 NOT NULL、CHECK、UNIQUE、外键和明确的日期、金额、布尔编码。

STRICT + ANY

适合：

• 需要保留原始输入类型的通用载荷；
• 兼容多种输入格式；
• 由应用层另行解释值的字段。

代价是失去单一类型保证。ANY 应该是有意识的边界设计，而不是因为无法决定类型就把所有列都设成 ANY。

---

SQLite 的类型系统并非“没有类型”，而是把类型约束拆成了不同层次：声明类型决定亲和性，亲和性影响转换，存储类描述实际值，约束决定哪些值可以被接受。普通表允许动态存储类，STRICT 表则把类型错误更早地变成约束错误；但长度、精度、日期、布尔和业务格式仍然需要显式建模。

因此，设计 SQLite schema 时，不能只问“这一列叫什么类型”，还要逐项回答：

• 输入值允许哪些存储类？
• 是否允许 SQLite 自动转换？
• 转换后是否会丢失格式信息？
• NULL 和空字符串是否有区别？
• 长度、范围、精度和格式由什么机制验证？
• 查询参数会以什么类型绑定？
• 迁移或多版本部署时，旧数据和旧客户端是否满足新契约？

只有把这些问题落实为 STRICT、CHECK、NOT NULL、外键、索引和应用绑定协议，SQLite 的灵活类型系统才会从隐患变成可控的设计能力。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：Oracle Data Pump 与迁移：导出导入、字符集、校验和停机窗口
• 下一篇：SQLite JSON、FTS5 与扩展：虚拟表、Tokenizer 和安全加载
• 延伸：SQLite 查询与索引：类型亲和性、查询计划、FTS5 和性能边界

官方资料

• SQLite Documentation
• SQLite Architecture

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。