SQLite 数据类型支持

概述

SQLite采用动态类型系统，具有类型亲和性特性，这与传统的关系型数据库有很大不同。理解SQLite的数据类型系统对于设计高效、可靠的数据库架构至关重要。本文档将详细介绍SQLite的核心数据类型、类型亲和性、扩展数据类型、版本差异以及生产环境最佳实践。

核心数据类型

SQLite支持五种基本数据类型，所有其他数据类型都会根据类型亲和性规则转换为这五种核心类型之一：

数据类型	描述	存储方式	示例
NULL	空值，表示缺少值	不占用存储空间	`NULL`
INTEGER	整数类型	可变长度（1-8字节），根据值大小自动调整	`123`, `-456`, `0`, `9223372036854775807`
REAL	浮点数类型	8字节IEEE浮点数	`3.14`, `-0.5`, `1.0e10`, `2.71828`
TEXT	文本字符串	UTF-8、UTF-16BE或UTF-16LE编码	`'hello'`, `'SQLite'`, `'中文'`, `'こんにちは'`
BLOB	二进制大对象	原始字节数据，不进行任何转换	图片、音频、视频文件、压缩数据

类型亲和性

SQLite的类型亲和性是指当数据插入表中时，SQLite会根据列的声明类型将数据转换为最适合的存储类型。这种设计提供了灵活性，同时保持了良好的性能。

亲和类型规则

SQLite根据列的声明类型确定其亲和类型：

声明类型特征	亲和类型	示例声明类型
包含INT关键字	INTEGER	INT, INTEGER, BIGINT, TINYINT, SMALLINT, MEDIUMINT, UNSIGNED INT
包含TEXT、CHAR或CLOB	TEXT	TEXT, VARCHAR, CLOB, CHAR(10), VARCHAR(255), NTEXT
包含BLOB或无类型声明	BLOB	BLOB, 无类型声明
包含REAL、FLOAT或DOUBLE	REAL	REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC(10,2)
包含NUMERIC	NUMERIC	NUMERIC, DECIMAL, BOOLEAN

类型转换规则

当数据插入列时，SQLite会按照以下规则进行类型转换：

INTEGER亲和性：
- 尝试转换为INTEGER，如果成功则以INTEGER存储
- 转换失败则尝试转换为REAL，如果成功则以REAL存储
- 仍失败则以TEXT存储
TEXT亲和性：
- 尝试转换为TEXT，如果成功则以TEXT存储
- 转换失败则以BLOB存储
REAL亲和性：
- 尝试转换为REAL，如果成功则以REAL存储
- 转换失败则尝试转换为TEXT，如果成功则以TEXT存储
- 仍失败则以BLOB存储
BLOB亲和性：
- 保持原始类型，不进行任何转换
NUMERIC亲和性：
- 对于整数数值，以INTEGER存储
- 对于浮点数数值，以REAL存储
- 对于文本数值，根据值自动选择INTEGER或REAL
- 其他情况以TEXT或BLOB存储

扩展数据类型

日期和时间类型

SQLite没有专门的日期和时间类型，而是使用TEXT、REAL或INTEGER类型存储日期时间数据：

存储类型	格式	优点	缺点	示例
TEXT	ISO8601格式	人类可读，排序正确	占用空间较大	`'2023-12-31T23:59:59Z'`
REAL	儒略日	计算高效	可读性差	`2459949.5`
INTEGER	Unix时间戳	计算高效，占用空间小	可读性差，受限于32位/64位	`1672531199`

日期时间函数

SQLite提供了丰富的日期时间函数，支持各种日期时间操作：

sql

-- 获取当前日期和时间
SELECT datetime('now'); -- 当前UTC时间
SELECT datetime('now', 'localtime'); -- 当前本地时间

-- 格式化日期
SELECT strftime('%Y-%m-%d', '2023-12-31T23:59:59'); -- 2023-12-31
SELECT strftime('%H:%M:%S', '2023-12-31T23:59:59'); -- 23:59:59

-- 日期计算
SELECT date('now', '+1 day'); -- 明天
SELECT datetime('now', '-1 month', '+3 days'); -- 上个月加3天
SELECT julianday('2023-12-31') - julianday('2023-01-01'); -- 计算天数差

-- 时间戳转换
SELECT datetime(1672531199, 'unixepoch'); -- Unix时间戳转ISO格式
SELECT strftime('%s', '2023-12-31T23:59:59'); -- ISO格式转Unix时间戳

JSON类型

从SQLite 3.9.0版本开始支持JSON数据类型，使用TEXT存储JSON数据：

sql

-- 创建包含JSON列的表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    profile JSON
);

-- 插入JSON数据
INSERT INTO users (profile) VALUES ('{"name": "Alice", "age": 30, "email": "alice@example.com"}');

-- 查询JSON数据
SELECT json_extract(profile, '$.name') AS name FROM users;
SELECT profile->'$.age' AS age FROM users; -- SQLite 3.38.0+ 简化语法
SELECT profile->>'$.email' AS email FROM users; -- SQLite 3.38.0+ 获取文本值

-- 修改JSON数据
UPDATE users SET profile = json_set(profile, '$.age', 31) WHERE id = 1;
UPDATE users SET profile = json_insert(profile, '$.phone', '1234567890') WHERE id = 1;

-- 聚合JSON数据
SELECT json_group_array(json_object('id', id, 'name', json_extract(profile, '$.name'))) AS users FROM users;

JSONB支持

从SQLite 3.33.0版本开始支持JSONB，提供更高效的JSON存储和查询：

sql

-- 创建包含JSONB列的表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    details JSONB
);

-- 插入JSONB数据
INSERT INTO products (details) VALUES (jsonb('{"name": "Product A", "price": 100, "tags": ["electronics", "gadget"]}'));

-- JSONB查询（语法与JSON相同）
SELECT json_extract(details, '$.name') AS name FROM products;
SELECT details->'$.price' AS price FROM products;

-- JSONB优化特点：
-- 1. 解析一次，多次使用
-- 2. 更高效的索引支持
-- 3. 减少存储空间（重复键只存储一次）
-- 4. 更快的查询性能

数值类型扩展

SQLite支持多种数值类型声明，这些类型最终会根据类型亲和性转换为核心类型：

声明类型	亲和类型	存储类型	适用场景
TINYINT	INTEGER	INTEGER	小整数（-128到127）
SMALLINT	INTEGER	INTEGER	中等整数（-32768到32767）
MEDIUMINT	INTEGER	INTEGER	较大整数（-8388608到8388607）
BIGINT	INTEGER	INTEGER	大整数（-9223372036854775808到9223372036854775807）
DECIMAL(p,s)	REAL	REAL	小数，如货币金额
NUMERIC	NUMERIC	INTEGER/REAL	根据值自动选择最适合的类型
BOOLEAN	INTEGER	INTEGER	布尔值（0表示false，1表示true）

字符串类型扩展

声明类型	亲和类型	存储类型	适用场景
VARCHAR(n)	TEXT	TEXT	可变长度字符串，如用户名、邮箱
CHAR(n)	TEXT	TEXT	固定长度字符串，如身份证号、手机号
CLOB	TEXT	TEXT	大文本数据，如文章内容、日志
NTEXT	TEXT	TEXT	Unicode文本，支持多种语言
NVARCHAR(n)	TEXT	TEXT	可变长度Unicode文本

版本差异

SQLite在不同版本中对数据类型的支持有所增强，特别是在JSON处理方面：

版本	数据类型相关新特性	生产影响
3.0.0	UTF-8/UTF-16支持	增强了TEXT类型的国际化支持，可存储多语言文本
3.9.0	JSON支持，新增json_extract等函数	引入了基本的JSON处理能力，可直接存储和查询JSON数据
3.18.0	增强JSON函数，新增json_set、json_insert等	支持修改JSON数据，提高了JSON处理的灵活性
3.25.0	增强的JSON聚合函数	支持JSON数据的聚合操作，适合生成复杂JSON结果
3.33.0	JSONB支持，新增jsonb()函数	提供更高效的JSON存储和查询，适合大量JSON数据场景
3.38.0	JSON路径操作符 -> 和 ->>	简化了JSON查询语法，提高了开发效率
3.40.0	增强的JSON函数，如json_group_object	支持更复杂的JSON聚合操作

兼容性最佳实践

JSON功能：如果需要使用JSON功能，建议使用SQLite 3.9.0或更高版本
JSONB功能：对于大量JSON数据或频繁JSON查询，建议使用SQLite 3.33.0或更高版本
JSON路径操作符：如果需要简化的JSON查询语法，建议使用SQLite 3.38.0或更高版本
版本检测：在应用中添加SQLite版本检测，根据版本动态调整功能

生产环境最佳实践

数据类型选择

根据实际数据特征选择类型：

sql

-- 推荐：根据数据特征选择合适类型
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增整数主键
    username TEXT NOT NULL UNIQUE, -- 用户名（文本）
    email TEXT NOT NULL UNIQUE, -- 邮箱（文本）
    password_hash TEXT NOT NULL, -- 密码哈希（文本）
    age INTEGER CHECK (age >= 0 AND age <= 150), -- 年龄（整数，带范围检查）
    balance REAL CHECK (balance >= 0), -- 余额（浮点数，非负）
    created_at TEXT DEFAULT (datetime('now')), -- 创建时间（ISO格式文本）
    updated_at TEXT DEFAULT (datetime('now')), -- 更新时间（ISO格式文本）
    profile JSONB, -- 用户资料（JSONB，适合频繁查询）
    avatar BLOB -- 头像（二进制数据）
);

使用明确的类型声明：
- 明确的类型声明提高代码可读性和维护性
- 帮助其他开发者理解数据结构
- 便于ORM框架生成正确的映射
合理使用约束：
- 使用NOT NULL约束防止空值
- 使用UNIQUE约束确保数据唯一性
- 使用CHECK约束限制数据范围
- 使用DEFAULT约束提供默认值

JSON数据最佳实践

选择合适的JSON存储方式：
- 对于少量JSON数据或不频繁查询的JSON字段，使用普通JSON类型
- 对于大量JSON数据或频繁查询的JSON字段，使用JSONB类型（SQLite 3.33.0+）

将频繁查询的JSON字段提取为单独的列：

sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_data JSONB,
    -- 提取频繁查询的字段作为生成列
    customer_id INTEGER GENERATED ALWAYS AS (json_extract(order_data, '$.customer.id')) VIRTUAL,
    order_total REAL GENERATED ALWAYS AS (json_extract(order_data, '$.total')) VIRTUAL,
    order_date TEXT GENERATED ALWAYS AS (json_extract(order_data, '$.date')) VIRTUAL
);

-- 为生成列创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_total ON orders(order_total);

避免过度使用JSON：
- 不要将所有数据都存储在JSON中，只存储半结构化或动态数据
- 对于结构化数据，使用传统的关系型设计
- 考虑JSON与关系型设计的混合使用

日期时间处理最佳实践

统一日期时间格式：
- 推荐使用ISO8601格式（YYYY-MM-DDTHH:MM:SSZ）
- 确保所有日期时间数据使用相同的时区（建议使用UTC）
- 使用SQLite的日期时间函数处理日期计算

使用触发器自动更新时间戳：

sql

CREATE TRIGGER update_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

为日期时间列创建索引：

sql

CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_date ON orders(order_date);

性能优化建议

优先使用INTEGER作为主键：
- 整数主键比文本主键更高效
- 自增整数主键（AUTOINCREMENT）便于数据管理
- INTEGER主键的索引更小、查询更快
选择合适的存储类型：
- 对于整数，使用INTEGER而不是REAL
- 对于固定长度文本，使用CHAR(n)而不是VARCHAR(n)
- 对于二进制数据，使用BLOB而不是TEXT
优化索引设计：
- 为频繁查询的列创建索引
- 考虑复合索引（多个列组合）
- 避免在索引列上使用函数
- 定期重建索引（使用VACUUM命令）
处理大数据：
- 对于大文本数据，考虑压缩后存储为BLOB
- 对于大型BLOB数据，考虑外部存储（只在数据库中存储文件路径）
- 使用分页查询处理大量数据

常见问题（FAQ）

Q: SQLite支持BOOLEAN类型吗？

A: SQLite没有专门的BOOLEAN类型，通常使用INTEGER存储布尔值，0表示false，1表示true：

sql

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT 0 -- 使用INTEGER存储布尔值
);

INSERT INTO tasks (title, completed) VALUES ('完成文档', 1); -- true
INSERT INTO tasks (title, completed) VALUES ('编写测试', 0); -- false

-- 查询时可以直接使用布尔表达式
SELECT * FROM tasks WHERE completed = true;
SELECT * FROM tasks WHERE completed = false;

Q: SQLite支持数组类型吗？

A: SQLite没有专门的数组类型，可以使用以下两种方式存储数组数据：

使用JSON数组：适合小型数组或不频繁查询的数组

sql

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    hobbies JSON
);

INSERT INTO users (hobbies) VALUES ('["reading", "sports", "music"]');

-- 查询包含特定爱好的用户
SELECT * FROM users WHERE json_contains(hobbies, '"reading"');

-- 查询爱好数量大于2的用户
SELECT * FROM users WHERE json_array_length(hobbies) > 2;

使用关联表：适合大型数组或频繁查询的数组

sql

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE user_hobbies (
    user_id INTEGER,
    hobby TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, hobby) -- 复合主键，防止重复
);

-- 查询包含特定爱好的用户
SELECT u.* FROM users u
JOIN user_hobbies uh ON u.id = uh.user_id
WHERE uh.hobby = 'reading';

-- 查询爱好数量大于2的用户
SELECT u.* FROM users u
JOIN (
    SELECT user_id FROM user_hobbies GROUP BY user_id HAVING COUNT(*) > 2
) uh ON u.id = uh.user_id;

Q: 如何处理大文本或二进制数据？

A: 根据数据类型选择合适的存储方式：

sql

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT, -- 大文本数据
    attachment BLOB, -- 二进制文件
    file_path TEXT, -- 外部存储路径（可选，用于超大文件）
    created_at TEXT DEFAULT (datetime('now'))
);

-- 插入大文本数据
INSERT INTO documents (title, content) VALUES ('Large Document', '... 1MB of text ...');

-- 插入二进制数据（通过应用程序绑定参数）
-- INSERT INTO documents (title, attachment) VALUES (?, ?);

-- 对于超大文件（如超过100MB），建议使用外部存储：
-- 1. 将文件存储在文件系统中
-- 2. 在数据库中只存储文件路径和元数据
-- 3. 使用事务确保数据一致性

Q: 为什么SQLite允许在INTEGER列中插入字符串？

A: 这是SQLite动态类型系统和类型亲和性的特性。当向INTEGER列插入字符串时：

SQLite会尝试将字符串转换为整数
如果转换成功，则以INTEGER类型存储
如果转换失败，则以TEXT类型存储
这种设计提供了灵活性，但也需要开发者注意数据一致性

sql

CREATE TABLE test (
    id INTEGER
);

INSERT INTO test (id) VALUES ('123'); -- 转换成功，存储为INTEGER
INSERT INTO test (id) VALUES ('abc'); -- 转换失败，存储为TEXT
INSERT INTO test (id) VALUES (456); -- 直接存储为INTEGER

-- 查询结果
SELECT id, typeof(id) FROM test;
-- 结果：
-- 123 | integer
-- abc | text
-- 456 | integer

Q: 如何确保数据类型的一致性？

A: 可以使用以下方法确保数据类型一致性：

使用CHECK约束：

sql

CREATE TABLE users (
    id INTEGER CHECK (typeof(id) = 'integer'),
    name TEXT CHECK (typeof(name) = 'text'),
    age INTEGER CHECK (typeof(age) = 'integer' AND age >= 0 AND age <= 150),
    email TEXT CHECK (typeof(email) = 'text' AND email LIKE '%@%')
);

使用触发器：

sql

CREATE TRIGGER ensure_valid_email
BEFORE INSERT ON users
FOR EACH ROW
WHEN typeof(NEW.email) != 'text' OR NEW.email NOT LIKE '%@%'
BEGIN
    SELECT RAISE(ABORT, 'Invalid email format');
END;

CREATE TRIGGER ensure_integer_age
BEFORE UPDATE ON users
FOR EACH ROW
WHEN typeof(NEW.age) != 'integer'
BEGIN
    SELECT RAISE(ABORT, 'Age must be an integer');
END;

在应用层进行数据验证：
- 使用ORM框架的类型验证
- 在业务逻辑层添加数据类型检查
- 使用表单验证库确保输入数据类型正确

Q: SQLite支持ENUM类型吗？

A: SQLite没有专门的ENUM类型，可以使用以下方法模拟ENUM：

使用CHECK约束：

sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')) DEFAULT 'pending',
    total REAL
);

使用关联表和外键：

sql

-- 创建枚举值表
CREATE TABLE order_statuses (
    status TEXT PRIMARY KEY
);

-- 插入枚举值
INSERT INTO order_statuses (status) VALUES ('pending'), ('processing'), ('shipped'), ('delivered'), ('cancelled');

-- 使用外键引用枚举值
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT REFERENCES order_statuses(status) DEFAULT 'pending',
    total REAL
);

Q: 如何处理SQLite与其他数据库的数据类型差异？

A: 处理跨数据库类型差异的最佳实践：

使用ORM框架：大多数ORM框架会自动处理不同数据库之间的类型映射
使用抽象层：创建数据访问抽象层，封装数据库特定的类型处理
避免使用数据库特定功能：只使用SQL标准功能，提高跨数据库兼容性
使用条件编译或运行时检测：根据数据库类型动态调整SQL语句
编写数据库迁移脚本：为不同数据库编写专门的迁移脚本

Q: 如何优化SQLite中的日期时间查询？

A: 优化日期时间查询的建议：

使用ISO8601格式存储日期时间：确保正确的排序和比较
为日期时间列创建索引：提高查询速度
使用SQLite内置的日期时间函数：避免在查询中使用自定义函数

范围查询优化：

sql

-- 推荐：使用索引列进行范围查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 不推荐：在索引列上使用函数
SELECT * FROM orders WHERE strftime('%Y', order_date) = '2023';

使用生成列优化复杂日期查询：

sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_date TEXT,
    -- 生成年份列用于快速查询
    order_year INTEGER GENERATED ALWAYS AS (CAST(strftime('%Y', order_date) AS INTEGER)) VIRTUAL
);

-- 创建索引
CREATE INDEX idx_orders_year ON orders(order_year);

-- 快速查询2023年的订单
SELECT * FROM orders WHERE order_year = 2023;

性能考虑

数据类型对性能的影响

存储大小：
- 选择合适的数据类型可以减少存储大小，提高I/O性能
- INTEGER通常比TEXT和BLOB占用更少的存储空间
- JSONB比普通JSON占用更少的存储空间（特别是重复键较多的情况）
索引效率：
- 整数索引比文本索引更高效
- 较短的文本索引比长文本索引更高效
- JSONB索引比普通JSON索引更高效
查询速度：
- 类型匹配的查询比需要类型转换的查询更快
- 例如：WHERE id = 123 比 WHERE id = '123' 更快
- JSONB查询比普通JSON查询更快
内存使用：
- 较小的数据类型占用较少的内存
- 对于大型数据集，选择合适的数据类型可以显著减少内存使用

优化建议

分析查询性能：使用EXPLAIN QUERY PLAN分析查询执行计划
优化数据类型：根据实际数据特征选择合适的数据类型
合理设计索引：为频繁查询的列创建索引
使用事务：批量操作时使用事务提高性能
定期维护：使用VACUUM命令优化数据库文件
监控性能：定期监控数据库性能指标，如查询响应时间、I/O使用率等

总结

SQLite的数据类型系统具有独特的灵活性和强大的功能，理解其工作原理对于设计高效、可靠的数据库架构至关重要。通过选择合适的数据类型、合理使用类型亲和性、遵循最佳实践，可以充分发挥SQLite的优势，同时确保数据库的性能和可靠性。

在实际应用中，需要根据具体场景选择合适的数据类型：

对于结构化数据，使用传统的关系型设计
对于半结构化数据，考虑使用JSON或JSONB类型
对于二进制数据，使用BLOB类型或外部存储
对于日期时间数据，使用ISO8601格式并创建索引

同时，需要考虑不同SQLite版本之间的差异，确保应用程序的兼容性。通过合理的设计和优化，可以构建出高性能、可扩展的SQLite数据库应用。

SQLite 数据类型支持 ​

概述 ​

核心数据类型 ​

类型亲和性 ​

亲和类型规则 ​

类型转换规则 ​

扩展数据类型 ​

日期和时间类型 ​

日期时间函数 ​

JSON类型 ​

JSONB支持 ​

数值类型扩展 ​

字符串类型扩展 ​

版本差异 ​

兼容性最佳实践 ​

生产环境最佳实践 ​

数据类型选择 ​

JSON数据最佳实践 ​

日期时间处理最佳实践 ​

性能优化建议 ​

常见问题（FAQ） ​

Q: SQLite支持BOOLEAN类型吗？ ​

Q: SQLite支持数组类型吗？ ​

Q: 如何处理大文本或二进制数据？ ​

Q: 为什么SQLite允许在INTEGER列中插入字符串？ ​

Q: 如何确保数据类型的一致性？ ​

Q: SQLite支持ENUM类型吗？ ​

Q: 如何处理SQLite与其他数据库的数据类型差异？ ​

Q: 如何优化SQLite中的日期时间查询？ ​

性能考虑 ​

数据类型对性能的影响 ​

优化建议 ​

总结 ​