SQLite 数据类型：深入解析存储类

Q: SQLite 的存储类有哪些？

SQLite 的存储类包括 NULL 、 INTEGER 、 REAL 、 TEXT 与 BLOB 。

Q: 一列能否存储多种数据类型？

是的，除非将表声明为 STRICT ，任何列都可以存放不同类型的值。

Q: SQLite 如何处理 BOOLEAN 值？

SQLite 没有单独的 BOOLEAN 类型。布尔值通常以 INTEGER 存储，即 0 （FALSE）或 1 （TRUE），使用 NUMERIC 亲和性。

Q: 如何检查一个值的存储方式？

使用 typeof() 函数检查值的实际存储类。

SQLite 使用基于存储类和类型亲和性的灵活类型系统。与强制严格类型的传统关系型数据库不同，SQLite 允许更动态的类型处理。理解这一模型对于确保数据一致性并避免应用逻辑中的隐性错误至关重要。

在本文中，我将探讨 SQLite 的类型系统，并演示如何在 SQLite 中使用常见数据类型。若您是 SQL 新手，建议先学习我们的Introduction to SQL 课程，夯实基础。此外，我还推荐可下载的SQL Basics Cheat Sheet，它汇总了最常用的 SQL 函数，作为参考非常实用。

SQLite 类型系统的核心概念

要顺畅使用 SQLite，理解其内部如何存储和解释数据很重要。下面我将解释 SQLite 类型系统如何帮助构建可靠且高效的应用。

存储类详解

SQLite 使用五种存储类在内部表示值：

NULL 表示缺失或未定义的值。
INTEGER 表示带符号整数，根据数值大小使用 1 到 8 字节存储。
REAL 表示浮点数，按 8 字节 IEEE-754 值存储。
TEXT 存储文本字符串，采用 UTF-8、UTF-16BE 或 UTF-16LE 编码。
BLOB 按输入原样存储二进制数据，不做任何转换。

SQLite 数据类型。图片来源：OpenAI

这些存储类与表模式中声明的数据类型不同。某列可能声明为 VARCHAR(50) 或 BOOLEAN，但实际存储时会根据内容使用五种存储类之一。这种分离让 SQLite 具备动态且宽容的特性，但也要求开发者在应用代码中更加注意数据校验与类型一致性。

类型亲和性与列行为

在 SQLite 中，类型亲和性是依据列的声明类型为其分配的推荐类型。SQLite 通过以下规则从声明类型的文本确定亲和性。

声明为 VARCHAR、CHAR 或 TEXT 的列获得 TEXT 亲和性。
像 INT 与 INTEGER 这类类型获得 INTEGER 亲和性。
如 DECIMAL、NUMERIC 或 BOOLEAN 的声明获得 NUMERIC 亲和性。
像 REAL、FLOAT 或 DOUBLE 这类类型获得 REAL 亲和性。
若未声明可识别类型，则亲和性默认为 BLOB。

例如，声明为 VARCHAR(100) 的列将具有 TEXT 亲和性，这意味着 SQLite 在存储时会尝试将值转换为文本。亲和性指导 SQLite 如何强制转换与存储数据，但并不限制在该列中存储其他类型的值。

显式类型绑定及其影响

SQLite 采用显式类型绑定（manifest typing），即数据类型与单个值相关，而非与列容器绑定。这使得一个列中可以共存多种类型的值。例如，声明为 INTEGER 的列也可以存放 TEXT 或 BLOB 值。

显式类型绑定允许开发者在不更改模式的情况下插入多样化的数据类型。但其代价是可能导致数据不一致、排序不可预测，并且更依赖应用逻辑来强制类型规则。

我建议您学习我们的 Database Design 课程，以了解数据库管理，包括以不同数据类型组织数据及管理数据库。

在 SQLite 中使用常见数据类型

下面我们看看如何在 SQLite 中处理常见数据类型，如文本、数字、布尔值、日期以及二进制数据。

文本、数字与布尔值

SQLite 常见的数据声明包括 TEXT、INTEGER、REAL、NUMERIC 与 BOOLEAN，但其存储基于类型亲和性而非严格类型。以下是在真实场景中各数据类型的使用示例。

TEXT 存储字符串，适合姓名、邮箱、URL 及通用文本数据。
INTEGER 适合计数、ID 与代码等整数，具有效率高、排序快的优势。
REAL 存储浮点数，用于度量、货币或非整数值。
NUMERIC 亲和性涵盖声明为 DECIMAL、BOOLEAN 等的值。SQLite 会根据实际值尝试将其存为 INTEGER 或 REAL。
BOOLEAN 不是原生存储类，而是通过在后台将 0（FALSE）或 1（TRUE）作为 INTEGER 存储来模拟。

我建议使用 INTEGER 来表示布尔值，并在应用侧显式施加约束；对于需要灵活数值解析的场景，NUMERIC 也是合适的选择。

日期、时间与时序策略

SQLite 没有原生的 DATE 或 DATETIME 类型，但支持以下格式来存储时间数据：

TEXT：将日期/时间存为 ISO8601 字符串，如 YYYY-MM-DD HH:MM:SS。此格式便于阅读并兼容诸多工具，但比较时可能较慢。
REAL：将日期存为儒略日（浮点数），便于日期运算与范围查询，但可读性较差。
INTEGER：存为 Unix 时间戳（自 1970-01-01 起的秒数），存储高效，数值比较速度快，但不便于直接阅读。

处理时间数据时，您可以使用这些函数：date()、time()、datetime()、julianday() 与 strftime() 来解析、格式化和在时间格式之间转换。

二进制与数值边界情况

SQLite 也通过 BLOB 存储类支持二进制数据。BLOB 适合存储图像、文件或任何非文本数据。处理 BLOB 数据时，应始终使用参数化查询或绑定来插入 BLOB。您也可以将大型二进制数据存于外部，并通过路径或哈希进行引用。

虽然 SQLite 可存储最大 8 字节的整数（-2^63 至 2^63-1），但超出该范围的值可能会被悄然转换为 REAL 类型，从而丢失精度。SQLite 不严格强制小数精度，因此在需要精确度的金融数据场景下，请务必使用外部库或应用逻辑来保证精度。

使用 STRICT 表强制结构

与传统的灵活类型系统不同，SQLite 在 3.37.0 版本中引入了 STRICT 表。STRICT 表提供更严格的类型模式，要求列值与声明类型精确匹配。当您将表声明为 STRICT 时，插入的每个值都必须与列的声明类型一致，否则将报错。

STRICT 模式提升了数据一致性与校验能力，有助于在开发早期捕获与类型相关的错误，使 SQLite 的行为更可预测，更接近传统关系型数据库。

作为开发者在使用 STRICT 前需注意，它仅在 SQLite 3.37.0 及以上版本可用。此外，同一张表内不能混用 STRICT 与显式类型绑定。

性能与设计考量

SQLite 的灵活类型系统会带来性能层面的影响。SQLite 对 INTEGER 与 REAL 值使用变长编码，也就是较小的数字占用更少字节。这在大规模数据集中有助于降低数据库体积。

类型亲和性也会影响值的存储与比较。在同一列中存在类型不一致的情况，会降低索引效率，因为 SQLite 在查找时可能需要额外进行类型转换，从而拖慢查询。

请考虑以下策略来设计高效的模式与查询。

为列定义清晰且一致的亲和性，尽量减少类型强制转换带来的开销。
当需要类型转换时，在查询中考虑显式 CAST，以确保行为一致。
对数值型 ID 与计数器使用 INTEGER 亲和性，对浮点数据使用 REAL，对字符串使用 TEXT。
避免在同一列中混用数据类型，以防出现排序与比较的异常。
规范化模式，避免冗余的类型转换或数据重复。
使用 EXPLAIN QUERY PLAN 分析性能并优化查询。

SQLite 数据类型示例与模式范式

以下表结构示例展示了对 SQLite 数据类型、类型亲和性与约束的合理使用。

带布尔模拟与约束的 Users 表

在下面的示例中：

is_active 使用 INTEGER 加 CHECK 约束来模拟布尔值。
TEXT 数据类型确保字符串值的一致性，而 INTEGER 适用于类似布尔的逻辑。

 -- Create users table with auto-incrementing rowid and enforce uniqueness
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    username TEXT NOT NULL UNIQUE, 
    email TEXT NOT NULL, 
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))  -- Simulates boolean
);技术支持

使用时间数据与数值精度的 Orders 表

在下面的示例中，total 使用 NUMERIC 存放类货币值，并在应用层处理精度；order_date 以 ISO-8601 格式作为 TEXT 存储，保证可读性并与日期函数兼容。

-- Create orders table with row identifier and foreign key in practice
-- ISO date format "YYYY-MM-DD"
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,  
    customer_id INTEGER NOT NULL, 
    total NUMERIC NOT NULL, 
    order_date TEXT NOT NULL CHECK (length(order_date) = 10)  );技术支持

带二进制与类型检查的 Files 表

在下面的示例中，content 使用 BLOB 存储原始二进制数据。uploaded_at 使用 INTEGER 存储时间，以获得空间与性能效率。

-- Create files table with raw binary data and Unix timestamp (epoch seconds)
CREATE TABLE files (
    file_id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    content BLOB NOT NULL,  
    uploaded_at INTEGER NOT NULL 
);技术支持

您可以通过以下查询在运行时验证类型：

SELECT typeof(uploaded_at), typeof(content) FROM files;技术支持

我建议您学习我们的 Introduction to Relational Databases in SQL 课程，进一步了解数据结构以及如何在数据库中创建表。

将 SQLite 与其他数据库比较

与 MySQL 或 PostgreSQL 等传统关系型数据库相比，SQLite 的类型系统更为灵活。下表对它们进行了比较。

特性	SQLite	MySQL	PostgreSQL
类型模型	动态（显式类型绑定、类型亲和性）	严格（存在一些例如静默强制转换的怪癖）	严格（强类型强制执行）
声明类型的强制性	不强制（除非使用 `STRICT` 模式）	大多强制，但允许一定灵活性	完全强制
布尔处理	模拟为 `0`/`1`、`TEXT`、`NUMERIC`	拥有 `BOOLEAN` 类型（`TRUE`、`FALSE`）	原生 `BOOLEAN` 类型
日期/时间类型	手动存为 `TEXT`、`REAL` 或 `INTEGER`	原生日期/时间类型	原生且丰富的时序类型
索引敏感性	对同列混合类型较敏感	由于类型化而可靠	由于类型化而可靠
模式灵活性	非常高（默认限制较少）	中等	结构化且健壮
迁移到 SQLite	可能需要放宽严格类型强制	总体顺畅，仅需少量调整	可能需要简化或牺牲精度
从 SQLite 迁移	需要数据清理与类型规范化	需要符合更严格的类型要求	需要数据符合要求并使用更严格的模式

结论

SQLite 拥有灵活的类型系统，只要用得恰当就能发挥多种优势。通过理解存储类、类型亲和性与显式类型绑定，开发者可以设计在适应性与可靠性之间取得平衡的模式。诸如 STRICT 表、一致的类型使用与规范化等特性，有助于在需要时强制结构。

作为下一步，我建议学习我们的 Exploratory Data Analysis in SQL 与 Data Manipulation in SQL 课程，学习如何用高级 SQL 查询分析不同数据类型。

作者

Allan Ouko

数据科学技术写作者，具备数据分析、商业智能和数据科学的一线实践经验。我撰写以行业为导向的实用内容，涵盖 SQL、Python、Power BI、Databricks 和数据工程，并以真实的分析工作为基础。我的写作兼顾技术深度与业务影响，帮助专业人士将数据转化为自信的决策。

FAQs

SQLite 的存储类有哪些？

SQLite 的存储类包括 NULL、INTEGER、REAL、TEXT 与 BLOB。

一列能否存储多种数据类型？

是的，除非将表声明为 STRICT，任何列都可以存放不同类型的值。

什么是 SQLite 的类型亲和性？

类型亲和性是基于声明类型为列推荐的一种类型。SQLite 会尝试将插入的值转换为该列的亲和类型，但不会严格强制。

SQLite 如何处理 BOOLEAN 值？

SQLite 没有单独的 BOOLEAN 类型。布尔值通常以 INTEGER 存储，即 0（FALSE）或 1（TRUE），使用 NUMERIC 亲和性。

如何检查一个值的存储方式？

使用 typeof() 函数检查值的实际存储类。

主题

SQLite 数据工程

主题

技术