SQLite - 数据类型

SQLite - 数据类型

SQLite 与大多数其他 SQL 数据库的一个关键区别在于其动态类型系统。在 SQLite 中，数据类型是值本身的属性，而不是存储该值的列的属性。这提供了极大的灵活性，但理解存储类 (Storage Classes) 和类型亲和性 (Type Affinity) 的基本概念至关重要。

存储类 (Storage Classes)

存储在 SQLite 数据库中的每个值都分配有以下五种存储类之一：

序号	存储类及描述
1	NULL 该值为一个 NULL 值。
2	INTEGER 一个有符号整数（1、2、3、4、6 或 8 字节），取决于其大小。
3	REAL 一个浮点值，存储为 8 字节的 IEEE 浮点数。
4	TEXT 一个文本字符串，使用数据库编码（例如 UTF-8）存储。
5	BLOB (二进制大对象) 一个数据块，完全按照输入时的样子存储。用于文件、图片等。

类型亲和性 (Type Affinity)

尽管任何列在技术上都可以存储任何类型的值，但列会被分配一个“类型亲和性”。亲和性是关于列首选存储类的建议。SQLite 会在合理的情况下尝试将值强制转换为列的亲和类型。

亲和性	描述	包含以下类型名称时触发
INTEGER	倾向于将数据存储为整数。看起来像数字但带有小数点的数值可能会存储为 REAL。	INT
TEXT	倾向于将数据存储为文本。	CHAR、CLOB、TEXT
BLOB	倾向于将数据存储为 BLOB。不尝试进行任何转换。	BLOB（或未指定类型）
REAL	倾向于将数据存储为浮点数。	REAL、FLOA、DOUB
NUMERIC	一种灵活的亲和性，可以存储任何格式的值。如果可能，它会尝试将数据存储为 INTEGER 或 REAL，否则退回到 TEXT。	NUMERIC、DECIMAL、BOOL、DATE

现代数据类型与用法

布尔值

SQLite 没有独立的布尔存储类。它将布尔值存储为 INTEGER 0（假）和 1（真）。现代版本的 SQLite 在 SQL 语句中识别 TRUE 和 FALSE 关键字，使查询更具可读性。

CREATE TABLE tasks (id INTEGER PRIMARY KEY, description TEXT, is_complete INTEGER);

-- 以下两个语句是等效的
INSERT INTO tasks (description, is_complete) VALUES ('Write tutorial', 1);
INSERT INTO tasks (description, is_complete) VALUES ('Publish tutorial', TRUE);

日期和时间值

如日期和时间章节所述，SQLite 通过将日期存储为 TEXT、REAL 或 INTEGER 并使用内置函数来处理日期。声明一个 DATE 或 DATETIME 类型的列会使其具有 NUMERIC 亲和性，但 TEXT 通常是存储 ISO 8601 字符串最实用的选择。

使用 JSON

SQLite 对 JSON 数据有出色的支持，这些数据存储在具有 TEXT 亲和性的列中。一组丰富的 json_* 函数允许您直接在 SQL 中查询和操作 JSON 文档。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes TEXT -- 将 JSON 数据存储为文本
);

INSERT INTO products (name, attributes) VALUES (
    'Laptop',
    '{"brand": "AwesomeCorp", "ram_gb": 16, "storage": ["512gb_ssd", "1tb_hdd"]}'
);

-- 使用 json_extract() 从 JSON 对象中提取值
SELECT name, json_extract(attributes, '$.brand') AS brand
FROM products
WHERE json_extract(attributes, '$.ram_gb') >= 16;