Small. Fast. Reliable.
Choose any three.
SQLite版本3中的数据类型
► 目录

1. SQLite中的数据类型

大多数SQL数据库引擎（据我们所知，除SQLite以外的所有SQL数据库引擎）都使用静态的刚性类型。对于静态类型，值的数据类型由其容器（存储值的特定列）确定。

SQLite使用更通用的动态类型系统。在SQLite中，值的数据类型与值本身（而不是其容器）相关联。SQLite的动态类型系统与其他数据库引擎的更常见的静态类型系统向后兼容，因为在静态类型的数据库上运行的SQL语句应在SQLite中以相同的方式运行。但是，SQLite中的动态类型允许它执行传统的严格类型数据库中无法做到的事情。

2.存储类和数据类型

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

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

除了INTEGER PRIMARY KEY列外，SQLite版本3数据库中的任何列均可用于存储任何存储类的值。

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

2.1。布尔数据类型

SQLite没有单独的布尔存储类。取而代之的是，布尔值存储为整数0（假）和1（真）。

2.2。日期和时间数据类型

SQLite没有预留用于存储日期和/或时间的存储类。相反，SQLite内置的日期和时间功能可以将日期和时间存储为TEXT，REAL或INTEGER值：

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

3.类型亲和力

使用刚性类型的SQL数据库引擎通常会尝试自动将值转换为适当的数据类型。考虑一下： 

创建表t1（a INT，b VARCHAR（10））;
插入t1（a，b）值（'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。（这样做是为了与SQLite之前的版本保持历史兼容版本3.8.6 2014-08-15，其中十六进制整数文字首次引入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。色谱柱亲和力的测定

列的亲和力由列的声明类型确定，并按照所示顺序遵循以下规则：

  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将接受的数据类型名称的一小部分。请注意，SQLite会忽略类型名称后面的括号中的数字参数（例如：“ VARCHAR（255）”）-SQLite不会对字符串，BLOB或字符串的长度施加任何长度限制（大的全局SQLITE_MAX_LENGTH限制除外）。数值。


CREATE TABLE语句
或CAST表达式中的 示例类型名		结果亲和力 用于确定亲和力的规则
INT
整数
TINYINT
SMALLINT
MEDIUMINT
BIGINT无 符号
BIG
INT2
INT8 		整数 1个
字符（20）
VARCHAR（255）
可变字符（255）
NCHAR（55）
原始字符（70）
NVARCHAR（100）
文本
CLOB 		文本 2个
BLOB
未指定数据类型 		BLOB 3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT 		真实的 4
NUMERIC
DECIMAL（10,5）
BOOLEAN
DATE
DATETIME 		数字 5

注意，由于“ POINT”末尾的“ INT”，声明的类型“ FLOATING POINT”将赋予INTEGER亲和力，而不是REAL亲和力。并且声明的“ STRING”类型具有NUMERIC（而不是TEXT）的相似性。

3.2。表达的亲和力

每个表列都有一个类型关联（BLOB，TEXT，INTEGER，REAL或NUMERIC中的一个），但表达式不一定具有关联。

表达亲和力由以下规则确定：

3.3。视图和子查询的列亲和力

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

创建表t1（a INT，b TEXT，c REAL）;
创建视图v1（x，y，z）作为SELECT b，a + c，42从t1那里b！= 11;

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

当实现VIEW或FROM子句的SELECT语句是复合SELECT时那么VIEW或子查询的每个假定列的亲和力将是组成该化合物的单个SELECT语句之一的相应结果列的亲和力。但是，不确定使用哪个SELECT语句来确定亲和力。在查询评估期间，可以使用不同的组成SELECT语句来确定不同时间的相似性。不同版本的SQLite的选择可能会有所不同。在相同版本的SQLite中，一个查询和下一个查询之间的选择可能会改变。同一查询中的不同时间选择可能有所不同。因此，您永远无法确定在组成子查询中具有不同亲和力的复合SELECT列将使用何种亲和力。

3.4。列亲和行为示例

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

创建表t1（
    t TEXT，-规则2的文本亲和力
    nu NUMERIC，-规则5的数字相似性
    i INTEGER，-规则1的整数相似性
    r REAL，-根据规则4的真实亲和力
    无BLOB-按规则3无亲和力
）;

-将值存储为TEXT，INTEGER，INTEGER，REAL，TEXT。
插入t1值（'500.0'，'500.0'，'500.0'，'500.0'，'500.0'）;
从t1中选择typeof（t），typeof（nu），typeof（i），typeof（r），typeof（no）;
文字|整数|整数|实数|文字

-将值存储为TEXT，INTEGER，INTEGER，REAL，REAL。
从t1删除；
插入t1值（500.0，500.0，500.0，500.0，500.0）;
从t1中选择typeof（t），typeof（nu），typeof（i），typeof（r），typeof（no）;
文字|整数|整数|实数|实数

-将值存储为TEXT，INTEGER，INTEGER，REAL，INTEGER。
从t1删除；
插入t1值（500，500，500，500，500）;
从t1中选择typeof（t），typeof（nu），typeof（i），typeof（r），typeof（no）;
文字|整数|整数|实数|整数

-BLOB始终存储为BLOB，而与列亲和力无关。
从t1删除；
插入t1值（x'0500'，x'0500'，x'0500'，x'0500'，x'0500'）;
从t1中选择typeof（t），typeof（nu），typeof（i），typeof（r），typeof（no）;
blob | blob | blob | blob | blob

-NULL也不受关联性的影响
从t1删除；
插入t1值（NULL，NULL，NULL，NULL，NULL）;
从t1中选择typeof（t），typeof（nu），typeof（i），typeof（r），typeof（no）;
null | null | null | null | null

4.比较表达式

SQLite版本3具有一组常用的SQL比较运算符，包括“ =“，” ==“，” <“，” <=“，”>“，”> =“，”！=“，”“，” IN“ ，“禁止进入”，“不在”，“是”和“不是”，。

4.1。排序

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

4.2。比较之前的类型转换

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

“应用相似性”是指，并且仅当该转换不会丢失基本信息时，才将操作数转换为特定的存储类。数字值始终可以转换为TEXT。如果文本内容是格式正确的整数或实数文字，而不是十六进制整数文字，则TEXT值可以转换为数值。通过简单地将二进制BLOB上下文解释为当前数据库编码中的文本字符串，可以将BLOB值转换为TEXT值。

在比较之前，将按照以下规则按照显示的顺序将亲和力应用于比较运算符的操作数：

表达式“ a在b AND c之间”被视为两个单独的二进制比较“ a> = b AND a <= c”，即使这意味着在每个比较中对“ a”应用了不同的亲和力。如果比较确实是“ x = y”，则处理形式为“ x IN（SELECT y ...）”的比较中的数据类型转换。表达式“ a IN（x，y，z，...）”等效于“ a = + x OR a = + y OR a = + z OR ...”。换句话说，IN运算符右边的值（在此示例中为“ x”，“ y”和“ z”值）被认为没有亲和力，即使它们碰巧是列值或CAST表达式。

4.3。比较例

创建表t1（
    文本-​​文本相似性
    b NUMERIC，-数值相似性
    c BLOB-没有亲和力
    d-无亲和力
）;

-值将分别存储为TEXT，INTEGER，TEXT和INTEGER
插入t1值（'500'，'500'，'500'，500）;
从t1中选择typeof（a），typeof（b），typeof（c），typeof（d）;
文字|整数|文字|整数

-由于列“ a”具有文本相似性，因此
-比较的右侧在转换为文本之前
-进行比较。
从t1选择a <40，a <60，a <600;
0 | 1 | 1

-文本相似性适用于右侧操作数，但由于
-他们已经是TEXT，这是一项禁止操作；没有转换发生。
从t1中选择<'40'，<'60'，<'600';
0 | 1 | 1

-列“ b”具有数字亲和力，因此应用了数字亲和力
-位于右侧的操作数。由于操作数已经是数字，
-亲和力的应用是禁止操作的; 没有转换发生。全部
-将数值进行数值比较。
从t1中选择b <40，b <60，b <600;
0 | 0 | 1

-数值亲和力适用于右侧的操作数，将其转换
-从文本到整数。然后进行数字比较。
从t1中选择b <'40'，b <'60'，b <'600';
0 | 0 | 1

-不发生亲和力转换。右侧值都有
-存储类INTEGER总是小于TEXT值
-  在左边。
从t1选择SELECT c <40，c <60，c <600;
0 | 0 | 0

-不发生亲和力转换。将值比较为TEXT。
从t1中选择c <'40'，c <'60'，c <'600';
0 | 1 | 1

-不发生亲和力转换。右侧值都有
-与INTEGER进行数字比较的存储类INTEGER
-左侧的值。
从t1中选择d <40，d <60，d <600;
0 | 0 | 1

-不发生亲和力转换。左侧的INTEGER值是
-始终小于右侧的TEXT值。
从t1中选择d <'40'，d <'60'，d <'600';
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到有损和不可逆的转换也要执行。一些数学运算符（％，<<，>>和＆|）期望使用INTEGER操作数。对于那些运算符，将REAL操作数转换为INTEGER的方式与CAST to INTEGER的方式相同。<<，>>，＆和| 运算符始终返回INTEGER（或NULL）结果，但是％运算符根据其操作数的类型返回INTEGER或REAL（或NULL）。数学运算符上的NULL操作数将产生NULL结果。数学运算符上的操作数将不会以任何形式出现且不是NULL的值将转换为0或0.0。除以零的结果为NULL。

6.排序，分组和复合选择

当查询结果按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_collat​​ion（）接口注册其他整理功能。

整理函数仅在比较字符串值时才重要。始终对数值进行数字比较，并且始终使用memcmp（）逐字节比较BLOB。

7.1。从SQL分配整理序列

每个表的每一列都有一个关联的整理功能。如果未显式定义整理函数，则整理函数默认为BINARY。列定义的COLLATE子句用于定义列的替代整理功能。

确定用于二进制比较运算符（=，<，>，<=，> =，！=，IS和IS NOT）的整理函数的规则如下：

  1. 如果两个操作数都使用后缀COLLATE运算符进行了显式整理功能分配，则该显式整理功能将用于比较，并优先于左操作数的整理功能。

  2. 如果任何一个操作数是一列，则使用该列的整理功能优先于左操作数。出于上一句子的目的，在一个或多个一元“ +”运算符和/或CAST运算符之后的列名称仍被视为列名称。

  3. 否则，将使用BINARY整理功能进行比较。

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

表达式“ x介于y和z之间”在逻辑上等效于两个比较“ x> = y AND x <= z”，并且对于整理函数而言就像是两个单独的比较一样。为了确定整理顺序，以与表达式“ x = y”相同的方式处理表达式“ x IN（SELECT y ...）”。用于形式为“ x IN（y，z，...）”的表达式的整理序列是x的整理序列。如果IN运算符上需要显式的整理序列，则应将其应用于左操作数，如下所示：“ x COLLATE nocase IN（y，z，...）”。

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

7.2。整理序列示例

下面的示例标识整理序列，这些整理序列将用于确定各种SQL语句可能执行的文本比较结果。请注意，在使用数字，blob或NULL值的情况下，可能不需要文本比较，也不需要使用排序规则序列。 

创建表t1（
    x整数主键，
    a，/ *整理序列BINARY * /
    b COLLATE BINARY，/ *整理序列BINARY * /
    c COLLATE RTRIM，/ *整理序列RTRIM * /
    d COLLATE NOCASE / *整理序列NOCASE * /
）;
                   / * xabcd * /
插入t1值（1，'abc'，'abc'，'abc'，'abc'）;
插入t1值（2，'abc'，'abc'，'abc'，'ABC'）;
插入t1值（3，'abc'，'abc'，'abc'，'Abc'）;
插入t1值（4，'abc'，'abc'，'ABC'，'abc'）;
 
/ *使用BINARY整理序列执行文本比较a = b。* /
从t1处选择x，其中a = b按x排序；
-结果1 2 3

/ *使用RTRIM整理序列执行文本比较a = b。* /
从t1选择x，在其中a = b通过x收集RTRIM ORDER；
-结果1 2 3 4

/ *使用NOCASE整理序列执行文本比较d = a。* /
在t1处d =在x处排序时选择SELECT x;
-结果1 2 3 4

/ *使用BINARY整理序列执行文本比较a = d。* /
在t = 1的情况下从t1选择x；
-结果1 4

/ *使用RTRIM整理序列执行文本比较'abc'= c。* /
在t1的``abc''= c中选择x;
-结果1 2 3

/ *使用RTRIM整理序列执行文本比较c ='abc'。* /
从t1处选择x，其中c ='abc'按x排序；
-结果1 2 3

/ *使用NOCASE整理序列（值
**“ abc”，“ ABC”和“ Abc”位于同一组中）。* /
从t1 GROUP BY d ORDER BY 1中选择count（*）；
-结果4

/ *使用BINARY整理序列进行分组。'abc'和
**'ABC'和'Abc'组成不同的组* /
SELECT count（*）FROM t1 GROUP BY（d ||''）ORDER BY 1;
-结果1 1 2

/ *使用RTRIM整理序列执行排序或列c。* /
从t1中选择x，然后按c，x;
-结果4 1 2 3

/ *使用BINARY整理序列对（c ||''）进行排序。* /
SELECT x FROM t1 ORDER BY（c ||''），x;
-结果4 2 3 1

/ *使用NOCASE整理序列对c列进行排序。* /
通过c COLLATE NOCASE从x中选择t1中的x；
-结果2 4 3 1