SQLite 为什么说没有固定数据类型？类型亲和性怎么用？

SQLite 的数据类型最容易让从 MySQL、PostgreSQL 转过来的人困惑：它声明了列类型，但真正决定存储方式的往往是值本身。SQLite 把值分成五种存储类：NULL、INTEGER、REAL、TEXT、BLOB。列声明会产生“类型亲和性”，它会尝试转换数据，但默认不会像强类型数据库那样严格拒绝所有不匹配的值。

存储类和类型亲和性不是一回事

存储类描述值怎么落盘，类型亲和性描述列倾向于把值转成什么。比如声明 INTEGER 的列更倾向于存整数，声明 TEXT 的列更倾向于存字符串，但你仍可能插入看似不符合直觉的值。

sql
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  price NUMERIC,
  note TEXT,
  raw BLOB
);

INSERT INTO demo(price, note) VALUES ('12.30', 123);
SELECT typeof(price), typeof(note) FROM demo;

NUMERIC 可能把 '12.30' 转成整数或浮点，TEXT 可能把数字转成文本。这个灵活性让 SQLite 很适合本地缓存和格式不完全稳定的数据，但也要求应用层更自律。

STRICT 表让类型更可控

如果你希望 SQLite 更像传统强类型数据库，可以使用 STRICT 表。它会让类型检查更严格，减少脏数据进入表的机会。

sql
CREATE TABLE user_profile (
  id INTEGER PRIMARY KEY,
  age INTEGER NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL
) STRICT;

STRICT 的取舍是清晰换灵活。新项目里，如果表结构稳定、数据要长期维护，建议优先考虑；如果是临时导入、弱结构日志或兼容旧数据，就要评估迁移成本。

日期、布尔值和金额怎么存

SQLite 没有独立的 DATE、BOOLEAN、DECIMAL 存储类，通常用约定解决。日期可以存 ISO-8601 文本、Unix 时间戳整数或 Julian day 浮点数；布尔值常用 0/1；金额最好用整数分、厘，避免浮点误差。

sql
CREATE TABLE invoice (
  id INTEGER PRIMARY KEY,
  paid INTEGER NOT NULL CHECK (paid IN (0, 1)),
  amount_cent INTEGER NOT NULL CHECK (amount_cent >= 0),
  created_at TEXT NOT NULL
);

这里的 CHECK 很关键。SQLite 的灵活类型系统不代表可以放弃约束，越是长期运行的本地数据库，越需要把边界写进 schema。

追问

SQLite 声明 VARCHAR(20) 会限制长度吗？

默认不会像很多人期待的那样自动限制到 20 个字符。SQLite 会识别它的 TEXT 亲和性，但括号里的长度更多是兼容 SQL 写法。要限制长度，应显式加 CHECK (length(name) <= 20)。踩坑点是从 MySQL 迁移表结构后，以为长度限制还在，结果线上写入了超长文本。

INTEGER PRIMARY KEY 有什么特殊之处？

在 SQLite 里，INTEGER PRIMARY KEY 通常是 rowid 的别名，插入和查询都很高效。它和普通 INT PRIMARY KEY 不完全一样，类型名字必须准确匹配 INTEGER 才有这个特殊语义。AUTOINCREMENT 也不是必需品，它会避免复用旧 rowid，但带来额外开销。取舍是多数场景用 INTEGER PRIMARY KEY 就够了，只有强依赖永不复用 ID 时才考虑 AUTOINCREMENT。

金额为什么不建议用 REAL？

REAL 是二进制浮点数，很多十进制小数无法精确表示。用于展示统计问题不大，用于账务累计就可能出现几分钱误差。更稳的做法是用 INTEGER 存最小货币单位，比如分或厘，展示时再格式化。边界是科学计算或传感器数据可以用 REAL，但业务金额最好别赌浮点精度。

动态类型会不会让数据质量失控？

会，如果没有约束和应用层校验。SQLite 的灵活性适合快速迭代，但长期项目需要 NOT NULL、UNIQUE、CHECK、外键和 STRICT 表一起兜底。外键还要确认开启：PRAGMA foreign_keys = ON;。经验上，越靠近核心业务的表越应该严格，越靠近缓存和临时数据的表可以灵活一点。