编程

SQLite 数据类型指南

11 2025-05-01 23:32:00

SQLite 是一个流行的轻量级数据库系统，它将数据存储在单个文件中。它的简单性得益于其灵活的动态类型系统，这与 MySQL、PostgreSQL 或 SQL Server 等其他数据库系统中使用的静态、刚性类型截然不同，后者要求你在创建表时指定数据类型。

SQLite 中的数据类型如何工作？

在 SQLite 中，创建表时不需要为列指定数据类型。SQLite 使用动态类型，这意味着它根据内容为每个插入的值分配一个类型。在 SQLite 表中创建列时，你仍然可以指定数据类型，这可以作为一个提示，告诉数据库需要什么类型的数据。但是，SQLite 仍然会在任何列中存储任何类型的数据，而不管声明的类型如何。

SQLite 中的数据类型/存储类

所有列中的 SQLite 值(即使是具有声明类型的列)都可以使用以下五个存储类中的任何一个：

  • TEXT: 任何文本字符串，包括数字、日期和其他类型。
  • INTEGER: 整型值。
  • REAL: 浮点值。
  • BLOB: 二进制大型对象，比如图片或其他二进制数据。
  • NULL: null 值(空值)。

类型亲和性

SQLite 没有使用严格类型，而是使用一个称为“类型亲和性(type affinities)”的概念。这些是 SQLite 用来确定存储在列中的值的类型以及该值使用哪个存储类的规则。存储类是一个更为通用的概念，用于对相似类型进行分组。

尽管存储类和类型亲和性之间存在关系，但亲和性与 SQLite 如何解释插入或更新到列中的数据有关。类型相关性提供了关于用于列中值的存储类的指导，但它并不限制你可以在该列中存储的数据类型。

SQLite 中的类型亲和性是：

  • TEXT: 任何文本字符串，包括数字、日期和其他类型。
  • NUMERIC: 任何数字，包括整数和浮点值。
  • INTEGER: 整型值。
  • REAL: 浮点值。
  • BLOB: 二进制大型对象，比如图片或其他二进制数据。(历史上，曾被称为 NONE)

当创建一个列时，你可以指定一个类型关联，为 SQLite 提供有关你希望存储在该列中的数据类型的提示。SQLite 将使用此信息来确定该列中值的存储类。但是，SQLite 仍然允许你在任何列中存储任何类型的数据，而不管声明的类型如何。

例如，假设你创建了下表：

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

此表中，id 字段预期存储整型值，name 字段预期存储文本值，而 score 字段预期存储浮点型。不过，你仍然可以将文本值插入到 score 字段中，SQLite 将可将其存储为文本值而没有任何问题。

INSERT INTO users (id, name, score) VALUES (1, 'Alice', 'unknown');
SELECT * FROM users WHERE id = 1;

这将返回

1 | Alice | unknown

为列声明的类型关联性指导 SQLite 如何解释和存储该列中的值。如果你声明了一个类型亲和性为 INTEGER 的列，SQLite 将在可能的情况下尝试对该列中的值使用 INTEGER 存储类。另一方面，如果使用 TEXT 关联，SQLite 将对该列中的值使用 TEXT 存储类，即使该值可以存储为整数。

SQLite 的类型亲和性规则

SQLite 基于类型亲和性使用以下规则根据值的类型相关性确定值的存储类：

INTEGER: 如果值可以转换为整数而不丢失信息，则将其存储为整数。如果该值无法转换为整数，SQLite 会将其存储为 real 值。

REAL: 如果值可以转换为 real 值而不丢失信息，则将其存储为 real 值。如果该值无法转换为 real 值，SQLite 会将其存储为文本。

TEXT: 存储为文本值。

NUMERIC: 如果可能 SQLite 将值转换并存储为 integer 或 real 类型。

BLOB: 值存储为 blob。注意：该类型曾经称为 NONE，不过由于该名称可能引起误解将其重命名为 BLOB。

创建列时模式匹配

值得注意的是，尽管 SQLite 只有五个亲和性类型(包括 BLOB，历史上称为 NONE），但它为你提供了相当大的灵活性，这可以使熟悉其他数据库系统的人更熟悉 SQLite。创建列时可以使用每个存储类的别名。这些是不区分大小写的，它们遵循以下顺序：

  • INTEGER: 任何带有 INT 字符串的类型
    • e.g., (e.g. INT, INTEGER, SMALLINT)
  • TEXT: 任何有  CHAR, CLOB, 或 TEXT 字符串的类型
    • e.g., CHARACTER, VARCHAR, NCHAR, etc.
  • BLOB: 任何带有 BLOB 字符串的类型
  • REAL: 任何带有 REAL、FLOA 或 DOUB 字符串的类型
    • e.g., FLOAT, DOUBLE, etc.
  • NUMERIC: 任何不匹配上述类型的数据

SQLite 不支持哪些类型?

SQLite 值得注意的是，它不支持其他数据库系统中常见的某些类型。但是，你仍然可以将这类数据存储在 SQLite 中，并按照预期使用它，但它们将作为上述五个存储类之一存储。

SQLite 不支持的类型包括：

  • BOOLEAN/BIT
  • DATE
  • TIME
  • DATETIME/TIMESTAMP

在 SQLite 中处理日期

尽管 SQLite 缺乏 DATE 类型，你仍然可以在 SQLite 中处理日期。你可以使用 ISO-8601 格式、Unix 时间戳或儒略日数，将日期存储为 text、real 或者 integer 值。SQLite 提供了许多内置日期和时间函数，以帮助你在查询时处理日期。比如，你可以使用 DATE 和 TIME 函数来从文本值中提取日期和时间组件，或者使用 strftime 函数以特定方式格式化日期。

一般而言，你将使用人类可读的 ISO-8601 日期格式(e.g., 2024-09-01 01:48:00)，因为这是最广泛的支持格式却易于在 SQLite 中处理。

以下是在 SQLite 中处理日期的方式：

-- ISO-8601 format, stored as TEXT (YYYY-MM-DD HH:MM:SS)
INSERT INTO events (name, date) VALUES ('Birthday', '2024-09-01');
INSERT INTO events (name, date) VALUES ('Appointment', '2024-09-01 01:48:00');
-- Unix timestamp, stored as INTEGER (Integer seconds since 1970-01-01)
INSERT INTO events (name, date) VALUES ('Meeting', 1723772054);
-- Julian day number, stored as REAL (Days since noon in Greenwich on November 24, 4714 BCE)
INSERT INTO events (name, date) VALUES ('Conference', 2459786.574074);

如果你使用这些格式中的任何一种，SQLite 提供了许多内置的日期和时间函数，以帮助你在查询中处理日期。例如，您可以使用 DATE 和 TIME 函数从文本值中提取日期和时间组件，或使用 strftime 函数以特定方式格式化日期。SQLite 将使用数据的存储类来确定如何解释和存储日期值；存储为 REAL 的值将被视为 Julian 日数，INTEGER 被视为 Unix 时间戳，TEXT 被视为 ISO-8601 格式的字符串。

小结

现在，你应该熟悉 SQLite 动态类型系统中使用的存储类(TEXT、INTEGER、REAL、BLOB 和 NULL)和类型亲和性(TEXT、NUMERIC、INTEGER、REAL 和 BLOB)，以及它们之间的关联。请记住，SQLite 的类型亲和性用于确定列中值的存储类，但它们并不限制你可以在该列中存储的数据类型。你可以在任何列中存储任何类型的数据，而不管声明的类型如何。

 

Sqlite 数据库

最新文章：

上一篇
下一篇