小巧、快速、可靠。
三者选其二。
SQLite 中的数据类型
► 目录

1. SQLite 中的数据类型

大多数 SQL 数据库引擎（据我们所知，除了 SQLite 之外的所有 SQL 数据库引擎）都使用静态、严格的类型系统。在静态类型系统中，值的类型由其容器决定 - 即存储值的特定列。

SQLite 使用更通用的动态类型系统。在 SQLite 中，值的类型与其自身关联，而不是与其容器关联。SQLite 的动态类型系统与其他数据库引擎更常见的静态类型系统向后兼容，因为在静态类型数据库上工作的 SQL 语句在 SQLite 中的工作方式相同。然而，SQLite 中的动态类型允许它做一些在传统严格类型数据库中不可能做到的事情。灵活的类型系统是 SQLite 的一个特性，而不是一个错误。

更新：从版本 3.37.0（2021-11-27）开始，SQLite 提供了STRICT 表，它执行严格的类型强制，适合那些喜欢这种方式的开发人员。

2. 存储类和数据类型

存储在 SQLite 数据库中（或由数据库引擎操作）的每个值都具有以下存储类之一

存储类比数据类型更通用。例如，INTEGER 存储类包含 7 种不同长度的整数数据类型。这在磁盘上有所不同。 但是，一旦 INTEGER 值从磁盘读取到内存中进行处理，它们就会转换为最通用的数据类型（8 字节有符号整数）。因此，在大多数情况下，“存储类”与“数据类型”没有区别，这两个术语可以互换使用。

SQLite 版本 3 数据库中的任何列（除了INTEGER PRIMARY KEY 列）都可以用来存储任何存储类别的值。

SQL 语句中的所有值，无论是嵌入在 SQL 语句文本中的字面量还是参数 绑定到预编译的 SQL 语句，都具有隐式存储类。在下面描述的情况下，数据库引擎可能会在查询执行期间将值在数字存储类（INTEGER 和 REAL）和 TEXT 之间转换。

2.1. 布尔数据类型

SQLite 没有单独的布尔存储类。相反，布尔值存储为整数 0（false）和 1（true）。

从版本 3.23.0（2018-04-02）开始，SQLite 识别关键字“TRUE”和“FALSE”，但这些关键字实际上只是整数字面量 1 和 0 的另一种写法。

2.2. 日期和时间数据类型

SQLite 没有专门用于存储日期和/或时间的存储类。相反，SQLite 的内置日期和时间函数能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值

应用程序可以选择以任何这些格式存储日期和时间，并使用内置的日期和时间函数在格式之间自由转换。

3. 类型亲和性

使用严格类型的 SQL 数据库引擎通常会尝试自动将值转换为适当的数据类型。考虑以下情况

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

严格类型数据库会将字符串“123”转换为整数 123，并将整数 456 转换为字符串“456”，然后执行插入操作。

为了最大限度地提高 SQLite 与其他数据库引擎之间的兼容性，以及为了使上面的示例在 SQLite 上的工作方式与其他 SQL 数据库引擎相同，SQLite 支持列上的“类型亲和性”的概念。列的类型亲和性是存储在该列中的数据的推荐类型。这里重要的是，类型是推荐的，而不是必需的。任何列仍然可以存储任何类型的數據。只是有些列，在有选择的情况下，会更倾向于使用一种存储类而不是另一种。列的首选存储类被称为其“亲和性”。

SQLite 3 数据库中的每一列都分配了以下类型亲和性之一

（历史说明：“BLOB”类型亲和性曾经被称为“NONE”。但这个术语很容易与“无亲和性”混淆，因此它被重命名了。）

具有 TEXT 亲和性的列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。如果将数值数据插入具有 TEXT 亲和性的列，它将在存储之前转换为文本形式。

具有 NUMERIC 亲和性的列可以包含使用所有五种存储类的值。当将文本数据插入 NUMERIC 列时，如果文本是格式正确的整数或实数字面量，则文本的存储类将转换为 INTEGER 或 REAL（按优先级顺序）。如果 TEXT 值是格式正确的整数字面量，但太大而无法放入 64 位有符号整数，则它将转换为 REAL。对于 TEXT 和 REAL 存储类之间的转换，只保留数字的前 15 位有效小数位。如果 TEXT 值不是格式正确的整数或实数字面量，则该值将存储为 TEXT。对于本段的目的，十六进制整数字面量不被视为格式正确，并且存储为 TEXT。（这样做是为了与版本 3.8.6 2014-08-15 之前的 SQLite 版本保持历史兼容性，在该版本中十六进制整数字面量首次被引入 SQLite。）如果可以准确地表示为整数的浮点数插入到具有 NUMERIC 亲和性的列中，则该值将转换为整数。不会尝试转换 NULL 或 BLOB 值。

字符串可能看起来像带有小数点和/或指数表示法的浮点数字面量，但只要该值可以表示为整数，NUMERIC 亲和性就会将其转换为整数。因此，字符串“3.0e+5”存储在具有 NUMERIC 亲和性的列中，作为整数 300000，而不是作为浮点数 300000.0。

使用 INTEGER 亲和性的列的行为与使用 NUMERIC 亲和性的列相同。INTEGER 和 NUMERIC 亲和性之间的区别仅在CAST 表达式中显而易见：表达式“CAST(4.0 AS INT)”返回整数 4，而“CAST(4.0 AS NUMERIC)”将值保留为浮点数 4.0。

具有 REAL 亲和性的列的行为类似于具有 NUMERIC 亲和性的列，只是它将整数强制转换为浮点数表示。（作为内部优化，没有小数部分的小浮点数存储在具有 REAL 亲和性的列中，将被写入磁盘为整数以占用更少的空间，并且当值被读出时自动转换回浮点数。这种优化在 SQL 级别是完全不可见的，只能通过检查数据库文件的原始位来检测。）

具有亲和性 BLOB 的列不会偏好一种存储类而不是另一种，并且不会尝试将数据从一种存储类强制转换为另一种存储类。

3.1. 列亲和性的确定

对于未声明为STRICT 的表，列的亲和性由列的声明类型决定，根据以下规则按显示的顺序进行

  1. 如果声明的类型包含字符串“INT”，则分配 INTEGER 亲和性。

  2. 如果列的声明类型包含任何字符串“CHAR”、“CLOB”或“TEXT”，则该列具有 TEXT 亲和性。请注意，类型 VARCHAR 包含字符串“CHAR”，因此分配了 TEXT 亲和性。

  3. 如果列的声明类型包含字符串“BLOB”，或者没有指定类型，则该列具有亲和性 BLOB。

  4. 如果列的声明类型包含任何字符串“REAL”、“FLOA”或“DOUB”，则该列具有 REAL 亲和性。

  5. 否则，亲和性为 NUMERIC。

请注意，确定列亲和性的规则顺序很重要。声明类型为“CHARINT”的列将同时匹配规则 1 和 2，但第一条规则优先，因此列亲和性将为 INTEGER。

3.1.1. 亲和性名称示例

下表显示了来自更传统的 SQL 实现的许多常见数据类型名称是如何根据上一节中的五条规则转换为亲和性的。此表仅显示 SQLite 将接受的数据类型名称的一小部分子集。请注意，在类型名称后面的括号中的数字参数（例如，“VARCHAR(255)”）被 SQLite 忽略 - SQLite 不会对字符串、BLOB 或数字值的长度施加任何长度限制（除了全局的SQLITE_MAX_LENGTH 限制）。

来自
CREATE TABLE 语句
或 CAST 表达式的示例类型名称		产生的亲和性用于确定亲和性的规则
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8		INTEGER1
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB		TEXT2
BLOB
未指定数据类型 		BLOB3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT		REAL4
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME		NUMERIC5

请注意，声明类型为“FLOATING POINT”将赋予 INTEGER 亲和性，而不是 REAL 亲和性，因为“POINT”的末尾是“INT”。而声明类型为“STRING”的亲和性为 NUMERIC，而不是 TEXT。

3.2. 表达式的亲和性

每个表列都具有类型亲和性（BLOB、TEXT、INTEGER、REAL 或 NUMERIC 之一），但表达式不一定具有亲和性。

表达式的亲和性由以下规则决定

3.3. 视图和子查询的列亲和性

VIEW 或 FROM 子句子查询的“列”实际上是实现 VIEW 或子查询的 SELECT 语句的结果集中的表达式。因此，VIEW 或子查询的列的亲和性由上述表达式亲和性规则决定。考虑一个例子

CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

v1.x 列的亲和性将与 t1.b (TEXT) 的亲和性相同，因为 v1.x 直接映射到 t1.b。但是 v1.y 和 v1.z 列都没有亲和性，因为这些列映射到表达式 a+c 和 42，而表达式始终没有亲和性。

3.3.1. 复合视图的列亲和性

当实现 VIEW 或 FROM 子句子查询的 SELECT 语句是 复合 SELECT 时，VIEW 或子查询的每一列的亲和性将是构成复合 SELECT 的单个 SELECT 语句之一的相应结果列的亲和性。但是，哪一个 SELECT 语句将用于确定亲和性是不确定的。不同的组成 SELECT 语句可能会在查询评估的不同时间用于确定亲和性。选择可能在 SQLite 的不同版本中有所不同。在 SQLite 的同一个版本中，选择可能会在一次查询和下一次查询之间发生变化。选择在同一查询中的不同时间点可能不同。因此，您永远无法确定在复合 SELECT 的列上将使用哪种亲和性，这些列在组成子查询中具有不同的亲和性。

最佳实践是在关心结果数据类型的情况下避免在复合 SELECT 中混合亲和性。在复合 SELECT 中混合亲和性会导致令人惊讶且不直观的結果。例如，请参阅 论坛帖子 02d7be94d7.

3.4. 列亲和性行为示例

以下 SQL 演示了 SQLite 如何使用列亲和性在将值插入表时进行类型转换。

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob

-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null

4. 比较表达式

SQLite 版本 3 具有通常的 SQL 比较运算符集，包括 "=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS" 和 "IS NOT"。

4.1. 排序顺序

比较的结果取决于操作数的存储类别，根据以下规则

4.2. 比较之前的类型转换

SQLite 可能会尝试在执行比较之前在存储类别 INTEGER、REAL 和/或 TEXT 之间转换值。在比较之前是否尝试进行任何转换取决于操作数的类型亲和性。

要“应用亲和性”意味着仅当转换不会丢失基本信息时，才将操作数转换为特定存储类别。数值始终可以转换为 TEXT。如果文本内容是格式良好的整数或实数文字，但不是十六进制整数文字，则可以将 TEXT 值转换为数值。通过将二进制 BLOB 内容简单地解释为当前数据库编码中的文本字符串，将 BLOB 值转换为 TEXT 值。

根据以下规则，在进行比较之前，将亲和性应用于比较运算符的操作数，按显示的顺序排列

表达式 "a BETWEEN b AND c" 被视为两个独立的二元比较 "a >= b AND a <= c"，即使这意味着在每次比较中对 'a' 应用不同的亲和性。形式为 "x IN (SELECT y ...)" 的比较中的数据类型转换的处理方式就好像比较实际上是 "x=y" 一样。表达式 "a IN (x, y, z, ...)" 等效于 "a = +x OR a = +y OR a = +z OR ..."。换句话说，IN 运算符右侧的值（本例中的 "x"、"y" 和 "z" 值）被认为没有亲和性，即使它们碰巧是列值或 CAST 表达式。

4.3. 比较示例

CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40,   c < 60,   c < 600 FROM t1;
0|0|0

-- No affinity conversions occur.  Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40,   d < 60,   d < 600 FROM t1;
0|0|1

-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

如果比较被交换，则示例中的所有结果都相同 - 如果形式为 "a<40" 的表达式被重写为 "40>a"。

5. 运算符

数学运算符 (+, -, *, /, %, <<, >>, & 和 |) 将两个操作数解释为数字。STRING 或 BLOB 操作数会自动转换为 REAL 或 INTEGER 值。如果 STRING 或 BLOB 看起来像一个实数（如果它有小数点或指数），或者如果值超出可以表示为 64 位有符号整数的范围，则它会转换为 REAL。否则，操作数会转换为 INTEGER。数学操作数的隐式类型转换与 CAST 到 NUMERIC 略有不同，因为看起来像实数但没有小数部分的字符串和 BLOB 值会保留为 REAL，而不是像 CAST 到 NUMERIC 那样转换为 INTEGER。即使是损失性且不可逆的，也会从 STRING 或 BLOB 转换为 REAL 或 INTEGER。一些数学运算符 (%, <<, >>, & 和 |) 需要 INTEGER 操作数。对于这些运算符，REAL 操作数会转换为 INTEGER，与 CAST 到 INTEGER 的方式相同。<<, >>, & 和 | 运算符始终返回 INTEGER（或 NULL）结果，但 % 运算符返回 INTEGER 或 REAL（或 NULL），具体取决于其操作数的类型。数学运算符上的 NULL 操作数会产生 NULL 结果。数学运算符上的操作数，它在任何方面都不像数字，并且不是 NULL，会被转换为 0 或 0.0。除以零的结果为 NULL。

6. 排序、分组和复合 SELECT

当查询结果按 ORDER BY 子句排序时，存储类别为 NULL 的值排在最前面，然后是按数值顺序排列的 INTEGER 和 REAL 值，然后是按排序顺序排列的 TEXT 值，最后是按 memcmp() 顺序排列的 BLOB 值。在排序之前不会发生任何存储类别转换。

当使用 GROUP BY 子句对值进行分组时，具有不同存储类别的值被认为是不同的，除了 INTEGER 和 REAL 值，如果它们在数值上相等，则被认为是相等的。由于 GROUP by 子句，不会将任何亲和性应用于任何值。

复合 SELECT 运算符 UNION、INTERSECT 和 EXCEPT 在值之间执行隐式比较。不会将亲和性应用于与 UNION、INTERSECT 或 EXCEPT 相关的隐式比较的比较操作数 - 值按原样进行比较。

7. 排序顺序

当 SQLite 比较两个字符串时，它使用排序顺序或排序函数（两个术语指的是同一个东西）来确定哪个字符串更大，或者两个字符串是否相等。SQLite 有三个内置的排序函数：BINARY、NOCASE 和 RTRIM。

应用程序可以使用 sqlite3_create_collation() 接口注册额外的排序函数。

排序函数只在比较字符串值时才起作用。数值始终按数值进行比较，而 BLOB 始终使用 memcmp() 按字节进行比较。

7.1. 从 SQL 中分配排序顺序

每个表的每一列都有一个关联的排序函数。如果未明确定义排序函数，则排序函数默认为 BINARY。列定义 的 COLLATE 子句用于为列定义替代排序函数。

确定对二元比较运算符 (=, <, >, <=, >=, !=, IS 和 IS NOT) 使用哪个排序函数的规则如下

  1. 如果任一操作数使用后缀 COLLATE 运算符 显式分配排序函数，则使用显式排序函数进行比较，并优先使用左侧操作数的排序函数。

  2. 如果任一操作数是列，则使用该列的排序函数，优先使用左侧操作数。为了便于理解前一句，由一个或多个一元 "+" 运算符和/或 CAST 运算符前缀的列名仍然被视为列名。

  3. 否则，使用 BINARY 排序函数进行比较。

如果比较操作数的任何子表达式使用后缀 COLLATE 运算符，则比较操作数被认为具有显式排序函数分配（上面规则 1）。因此，如果在比较表达式中使用了任何地方的 COLLATE 运算符，则无论该表达式中可能包含哪些表格列，该运算符定义的排序函数都用于字符串比较。如果在比较中出现两个或多个 COLLATE 运算符 子表达式，则使用最左侧的显式排序函数，无论 COLLATE 运算符嵌套在表达式中的深度如何，以及表达式如何使用括号。

表达式 "x BETWEEN y and z" 在逻辑上等同于两个比较 "x >= y AND x <= z"，并且在排序函数方面的工作方式就像两个单独的比较一样。表达式 "x IN (SELECT y ...)" 在确定排序顺序时，与表达式 "x = y" 的处理方式相同。表达式形式 "x IN (y, z, ...)" 使用的排序顺序是 x 的排序顺序。如果在 IN 运算符上需要显式排序顺序，则应将其应用于左侧操作数，例如："x COLLATE nocase IN (y,z, ...)”。

作为 SELECT 语句一部分的 ORDER BY 子句中的项可以使用 COLLATE 运算符 指定排序顺序，在这种情况下，将使用指定的排序函数进行排序。否则，如果由 ORDER BY 子句排序的表达式是列，则使用该列的排序顺序来确定排序顺序。如果表达式不是列并且没有 COLLATE 子句，则使用 BINARY 排序顺序。

7.2. 排序顺序示例

以下示例标识了用于确定各种 SQL 语句可能执行的文本比较结果的排序顺序。请注意，在数字、blob 或 NULL 值的情况下，可能不需要进行文本比较，也不使用任何排序顺序。

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1

此页面最后修改时间为 2022-04-27 09:17:51 UTC