SQLite 数据类型
SQLite 数据类型详解（2025 最新版）
SQLite 采用 动态类型系统（Dynamic Typing），与 MySQL/PostgreSQL 的静态类型不同 —— 列的类型由存储的值决定，而非声明时指定。
核心原则：“类型跟值走，不是跟列走”
一、五大存储类（Storage Classes）
| 存储类 | 说明 | 示例 |
|---|---|---|
NULL | 空值 | NULL |
INTEGER | 整数（-2⁶³ 到 2⁶³-1） | 123, -456 |
REAL | 浮点数（IEEE 754 双精度） | 3.14, -0.001 |
TEXT | 文本字符串（UTF-8/UTF-16） | 'hello', '张三' |
BLOB | 二进制数据（原样存储） | X'53514C697465' |
注意：SQLite 没有布尔型、日期型，用
INTEGER存0/1或时间戳，用TEXT存'2025-01-01'
二、声明类型 vs 实际存储（类型亲和性）
| 声明类型 | 类型亲和性 | 实际存储 |
|---|---|---|
INT, INTEGER, TINYINT, BIGINT | INTEGER | 整数 |
CHAR, VARCHAR, TEXT, CLOB | TEXT | 文本 |
BLOB, BYTEA | BLOB | 二进制 |
REAL, FLOAT, DOUBLE | REAL | 浮点 |
NUMERIC, DECIMAL, BOOLEAN, DATE | NUMERIC | 尝试存数字，否则 TEXT |
推荐写法：使用标准类型，避免混淆
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL,
photo BLOB,
created_at TEXT -- 或 INTEGER (Unix 时间戳)三、INTEGER 详解
- 自动适应大小：1、2、3、4、6、8 字节
- 范围：
-9,223,372,036,854,775,808到9,223,372,036,854,775,807 INTEGER PRIMARY KEY→ 自动成为ROWID别名AUTOINCREMENT：保证递增（有性能开销）
CREATE TABLE t(
id INTEGER PRIMARY KEY AUTOINCREMENT,
score INTEGER
);
INSERT INTO t(score) VALUES (100), ('abc'); -- 允许！但不推荐四、TEXT 详解
- 存储 UTF-8 编码字符串
- 最大长度：约 10 亿字符（受磁盘限制）
- 支持
COLLATE NOCASE实现大小写不敏感
name TEXT COLLATE NOCASE, -- 'Alice' = 'alice'
email TEXT UNIQUE,
bio TEXT DEFAULT ''五、REAL 详解
- 8 字节 IEEE 754 双精度浮点
- 精度 ≈ 15 位十进制
- 不适合精确金额（用
INTEGER存分）
-- 错误：浮点精度问题
price REAL -- 0.1 + 0.2 ≠ 0.3
-- 正确：用分存金额
price_cents INTEGER -- 1999 表示 19.99六、BLOB 详解
- 存储任意二进制数据（如图片、文件）
- 用
X'...'字面量表示 - Python 中用
bytes或buffer
CREATE TABLE files(
id INTEGER PRIMARY KEY,
name TEXT,
data BLOB
);
-- 插入二进制
INSERT INTO files VALUES (1, 'photo.jpg', X'FFD8FFE000104A46...');七、特殊类型处理
| 类型 | 推荐做法 | 示例 |
|---|---|---|
| 布尔值 | INTEGER 存 0/1 | is_active INTEGER DEFAULT 1 |
| 日期时间 | TEXT（ISO8601）或 INTEGER（Unix 时间戳） | created_at TEXT DEFAULT CURRENT_TIMESTAMP |
| JSON | TEXT + json1 扩展函数 | config TEXT CHECK(json_valid(config)) |
-- 日期推荐写法
created_at TEXT DEFAULT (datetime('now')),
updated_at INTEGER DEFAULT (strftime('%s','now')) -- Unix 时间戳八、类型转换函数
| 函数 | 说明 | 示例 |
|---|---|---|
CAST(expr AS TYPE) | 强制转换 | CAST('123' AS INTEGER) |
typeof(expr) | 查看存储类 | SELECT typeof('123'); -- 'text' |
hex(), quote() | 调试 BLOB | SELECT hex(data) FROM files; |
SELECT typeof(123), typeof('123'), typeof(12.3);
-- integer text real九、类型亲和性规则（重要！）
当插入值时，SQLite 按以下顺序尝试存储：
- 匹配声明的类型亲和性
- 若不匹配，按值类型存储
- 若仍不匹配，尝试转换为数字
CREATE TABLE demo(
a INTEGER,
b TEXT,
c NUMERIC
);
INSERT INTO demo VALUES
('123', 456, '2025-01-01'), -- a:text→INTEGER, b:INTEGER→TEXT, c:text→NUMERIC
(789, 'abc', 3.14); -- 正常十、推荐类型声明规范
| 用途 | 推荐类型 | 理由 |
|---|---|---|
| 主键 | INTEGER PRIMARY KEY | 自动 ROWID |
| 外键 | INTEGER REFERENCES t(id) | 一致性 |
| 文本 | TEXT | 标准 |
| 数字 | INTEGER / REAL | 明确 |
| 金额 | INTEGER（分） | 避免浮点误差 |
| 日期 | TEXT（ISO8601） | 可读性 |
| JSON | TEXT + CHECK(json_valid()) | 验证 |
| 图片/文件 | BLOB | 原样存储 |
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL, -- 1999 = ¥19.99
description TEXT,
image BLOB,
created_at TEXT DEFAULT (datetime('now')),
tags TEXT CHECK(json_valid(tags))
);十一、常见错误与避坑
| 错误 | 原因 | 正确做法 |
|---|---|---|
0.1 + 0.2 = 0.30000000000000004 | 浮点精度 | 用 INTEGER 存分 |
'00123' 被存为 123 | 声明 INTEGER | 需存字符串用 TEXT |
DATETIME 无效 | 无此类型 | 用 TEXT 或 INTEGER |
BLOB 显示乱码 | 文本查看器 | 用 hex() 查看 |
十二、类型速查表（贴桌边）
INTEGER → 整数、布尔(0/1)、时间戳
TEXT → 字符串、JSON、日期(ISO8601)
REAL → 浮点（不用于金额）
BLOB → 图片、文件、字节
NULL → 空值十三、实战练习
-- 创建测试表
CREATE TABLE test_types(
id INTEGER PRIMARY KEY,
flag INTEGER DEFAULT 1, -- 布尔
name TEXT,
score REAL,
data BLOB,
created TEXT DEFAULT (datetime('now')),
config TEXT CHECK(json_valid(config))
);
-- 插入混合数据
INSERT INTO test_types (name, score, config) VALUES
('测试', 95.5, '{"level": "A"}');
-- 查询类型
SELECT typeof(flag), typeof(name), typeof(created) FROM test_types;
-- integer text text学习建议
- 永远用
INTEGER,TEXT,REAL,BLOB声明 - 金额用
INTEGER存分 - 日期用
TEXT+DEFAULT (datetime('now')) - 用
typeof()调试类型问题 - 开启
PRAGMA strict;（3.37+）强制类型检查
PRAGMA strict = ON; -- 实验性，未来默认下一步学习？ 回复：
日期时间→ 完整时间处理JSON 支持→json1扩展金额处理→ 精确计算方案BLOB 操作→ 图片存储实战
现在试试：
SELECT sqlite_version(), typeof(3.14), typeof('2025-01-01');看看输出什么！