SQLite 数据类型: 从入门到精通 – wiki基地

SQLite 数据类型：从入门到精通

SQLite 是一种轻量级嵌入式数据库，以其零配置、跨平台和易于使用而闻名。理解 SQLite 的数据类型对于有效地设计和使用数据库至关重要。虽然 SQLite 宣称是“无类型”数据库，但这并不意味着它没有数据类型。它采用的是一种动态类型系统，这意味着值的类型与值本身关联，而不是与列的声明关联。本文将深入探讨 SQLite 的数据类型系统，涵盖其核心概念、类型亲和性、存储类别、最佳实践以及一些高级用法。

一、SQLite 的“无类型”哲学

SQLite 的“无类型”特性指的是你可以将任何类型的数据存储在任何列中，即使该列被声明为特定类型。数据库引擎不会强制执行类型约束。例如，你可以在声明为 INTEGER 的列中存储文本字符串，反之亦然。

然而，这种灵活性并不意味着类型无关紧要。SQLite 内部仍然会根据值的类型进行优化和处理。理解这种“类型亲和性”机制对于编写高效的 SQL 查询至关重要。

二、类型亲和性（Type Affinity）

类型亲和性是 SQLite 用于将值与特定存储类别关联的机制。当一个值插入到列中时，SQLite 会根据值的格式将其分配给一个亲和类型。即使列声明了类型，SQLite 仍然会优先考虑值的实际类型。

SQLite 定义了五种类型亲和性：

• INTEGER: 整数值。
• REAL: 浮点值。
• TEXT: 文本字符串。
• BLOB: 二进制数据。
• NUMERIC: 尝试将值转换为 INTEGER 或 REAL。如果转换失败，则将其视为 TEXT。

理解类型亲和性对于查询优化至关重要。例如，如果一个列的亲和性是 INTEGER，那么在该列上进行数值比较会比在 TEXT 列上进行相同的比较效率更高。

三、存储类别（Storage Classes）

存储类别定义了值在磁盘上的实际存储方式。SQLite 使用更细粒度的存储类别来优化存储空间和性能。一个值的存储类别由其类型亲和性和声明类型共同决定。

以下是一些常见的存储类别：

• INTEGER: 以 1, 2, 3, 4, 6 或 8 字节的整数形式存储。
• REAL: 以 8 字节的浮点数形式存储。
• TEXT: 以数据库编码（通常是 UTF-8）存储。
• BLOB: 以其原始字节序列存储。

四、声明类型（Declared Types）

虽然 SQLite 不强制执行声明类型，但它们仍然具有以下作用：

• 文档: 声明类型可以提高数据库模式的可读性，并帮助理解数据的预期类型。
• 类型亲和性: 声明类型会影响类型亲和性的确定。例如，声明为 INT 的列具有 INTEGER 亲和性，即使存储了文本值。
• 一些内置函数: 某些内置函数（例如 typeof()）会考虑声明类型。

五、常用声明类型及其对应的亲和性

以下是一些常用的声明类型及其对应的亲和性：

• INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8：INTEGER
• REAL, DOUBLE, DOUBLE PRECISION, FLOAT：REAL
• CHARACTER(n), VARCHAR(n), VARYING CHARACTER(n), NCHAR(n), NATIVE CHARACTER(n), NVARCHAR(n), TEXT, CLOB：TEXT
• BLOB, NO CASE, CASE SENSITIVE：BLOB
• NUMERIC, DECIMAL(p,s), BOOLEAN, DATE, DATETIME：NUMERIC

六、日期和时间

SQLite 没有专门的日期和时间类型。日期和时间值通常存储为 TEXT、REAL 或 INTEGER。

• TEXT: 以 ISO8601 字符串格式存储，例如 “YYYY-MM-DD HH:MM:SS.SSS”。
• REAL: 以儒略日数存储。
• INTEGER: 以 Unix 时间戳（自 1970 年 1 月 1 日以来的秒数）存储。

可以使用内置的日期和时间函数来处理这些值，例如 date(), time(), datetime(), strftime() 等。

七、布尔值

SQLite 没有专门的布尔类型。布尔值通常存储为 INTEGER，其中 0 表示 false，1 表示 true。

八、最佳实践

• 选择合适的存储类别: 根据数据的实际类型和查询需求选择合适的声明类型，以优化存储空间和查询性能。
• 使用类型亲和性: 了解类型亲和性机制，避免不必要的类型转换，提高查询效率。
• 一致性: 在同一列中保持数据类型的一致性，避免混用不同类型的数据。
• 使用内置函数: 利用 SQLite 的内置函数来处理日期、时间和其他特殊数据类型。

九、高级用法：自定义函数

SQLite 允许用户创建自定义函数来扩展其功能。这对于处理特定数据类型或实现复杂的逻辑非常有用。例如，你可以创建一个自定义函数来解析 JSON 数据或执行地理空间计算。

十、总结

SQLite 的动态类型系统提供了很大的灵活性，但也需要开发者理解其背后的机制。掌握类型亲和性、存储类别和声明类型的关系对于有效地设计和使用 SQLite 数据库至关重要。 通过遵循最佳实践和利用 SQLite 的强大功能，你可以构建高效且可靠的应用程序。

示例：

“`sql
CREATE TABLE example (
id INTEGER PRIMARY KEY,
name TEXT,
value NUMERIC,
data BLOB
);

INSERT INTO example (name, value, data) VALUES (‘Test’, 123.45, x’48656c6c6f20576f726c64′); — 插入二进制数据

SELECT typeof(value) FROM example WHERE id = 1; — 输出 ‘real’，因为 123.45 被存储为 REAL

SELECT CAST(value AS TEXT) FROM example WHERE id = 1; — 将 value 转换为 TEXT 类型
“`

这个例子展示了如何创建表、插入数据以及使用 typeof() 和 CAST() 函数。 通过理解这些概念，你可以更好地利用 SQLite 的灵活性，并构建高效的数据库应用程序。