SQLite版本3中的数据类型
► ⽬录
1. SQLite中的数据类型
⼤多数SQL数据库引擎（据我们所知，除SQLite以外的所有SQL数据库引擎）都使⽤静态的刚性类型。对于静态类型，值的数据类型由其容器（存储值的特定列）确定。
SQLite使⽤更通⽤的动态类型系统。在SQLite中，值的数据类型与值本⾝（⽽不是其容器）相关联。SQLite的动态类型系统与其他数据库引擎更常见的静态类型系统向后兼容，因为在静态类型的数据库上运⾏的SQL语句应在SQLite中以相同的⽅式运⾏。但是，SQLite中的动态类型允许它执⾏传统的严格类型数据库中⽆法做到的事情。
2.存储类和数据类型
存储在SQLite数据库中（或由数据库引擎操纵）的每个值均具有以下存储类之⼀：
NULL。该值为NULL值。
整数。该值是⼀个有符号整数，根据值的⼤⼩存储在1、2、3、4、6或8个字节中。
真实的。该值是⼀个浮点值，存储为8字节IEEE浮点数。
⽂字。该值是⼀个⽂本字符串，使⽤数据库编码（UTF-8，UTF-16BE或UTF-16LE）存储。预言之神
BLOB。该值是数据的⼀滴，完全按输⼊存储。
存储类⽐数据类型更通⽤。例如，INTEGER存储类包括6种不同长度的不同整数数据类型。这在磁盘上有所作为。但是，⼀旦从磁盘上读取INTEGER值并将其读⼊内存进⾏处理，它们便会转换为最通⽤的数据类型（8字节有符号整数）。因此，在⼤多数情况
下，“存储类”与“数据类型”是⽆法区分的，并且这两个术语可以互换使⽤。
除了列外，SQLite版本3数据库中的任何列均可⽤于存储任何存储类的值。
SQL语句中的所有值，⽆论它们是嵌⼊在SQL语句⽂本中的⽂字还是绑定到 都具有隐式存储类。在下⾯描述的情况下，数据库引擎可以在查询执⾏期间在数字存储类（INTEGER和REAL）和TEXT之间转换值。
2.1。布尔数据类型
SQLite没有单独的布尔存储类。⽽是将布尔值存储为整数0（假）和1（真）。
2.2。⽇期和时间数据类型
SQLite没有为存储⽇期和/或时间预留存储类。相反，SQLite 内置的可以将⽇期和时间存储为TEXT，REAL或INTEGER值：
⽂本为ISO8601字符串（“ YYYY-MM-DD HH：MM：SS.SSS”）。
REAL为儒略⽇的数字，因为中午在格林威治11⽉24⽇的天数，4714根据proleptic公历BC。
INTEGER as Unix Time，⾃1970-01-01 00:00:00 UTC以来的秒数。
应⽤程序可以选择以任何⼀种格式存储⽇期和时间，并使⽤内置的在格式之间⾃由转换 。
3.类型亲和⼒
使⽤刚性类型的SQL数据库引擎通常会尝试⾃动将值转换为适当的数据类型。考虑⼀下：
创建表t1（a INT，b VARCHAR（10））;
插⼊t1（a，b）值（'123'，456）;
排骨炖土豆
刚性类型的数据库将在执⾏插⼊操作之前将字符串“ 123”转换为整数123，将整数456转换为字符串“ 456”。
为了最⼤程度地提⾼SQLite与其他数据库引擎之间的兼容性，并使上⾯的⽰例在SQLite上像在其他SQL数据库引擎上⼀样⼯
作，SQLite⽀持列上的“类型相似性”概念。列的类型相似性是该列中存储的数据的推荐类型。这⾥的重要思想是建议使⽤类型，⽽不是必需类型。任何列仍可以存储任何类型的数据。只是，某些列（如果有选择）将倾向于使⽤⼀种存储类⽽不是另⼀种。列的⾸选存储类称为其“关联性”。
向SQLite 3数据库中的每⼀列分配以下类型关联之⼀：
⽂本
数字
整数
真实
BLOB
（历史记录：“ BLOB”类型的相似性以前被称为“ NONE”。但是该术语很容易与“⽆相似性”相混淆，因此将其重命名。）
具有TEXT关联性的列使⽤存储类NULL，TEXT或BLOB存储所有数据。如果将数字数据插⼊到具有TEXT关联性的列中，则在存储之前将其转换为⽂本形式。
肛拭子采样
具有NUMERIC关联性的列可能包含使⽤所有五个存储类的值。将⽂本数据插⼊NUMERIC列时，如果⽂本分别是格式良好的整数或实数⽂字，则⽂本的存储类将转换为INTEGER或REAL（按优先顺序）。如果TEXT值是格式正确的整数⽂字，太⼤⽽⽆法容纳64位有符号整数，则将其转换为REAL。对于TEXT和REAL存储类之间的转换，仅保留该数字的前15个有效⼗进制数字。如果TEXT值不是格式正确的整数或实数⽂字，则该值将存储为TEXT。就本段⽽⾔，⼗六进制整数⽂字不被认为格式正确，⽽是存储为TEXT。（这样做是为了与SQLite之前的版本保持历史兼容 2014-08-15，其中⼗六进制整数⽂字⾸次引⼊SQLite。）不尝试转换NULL或BLOB值。
字符串可能看起来像是带⼩数点和/或指数表⽰法的浮点⽂字，但是只要该值可以表⽰为整数，NUMERIC关联就可以将其转换为整数。因此，字符串“ 3.0e + 5”存储在具有NUMERIC关联性的整数300000⽽不是浮点值300000.0的列中。
使⽤INTEGER关联的列的⾏为与具有NUMERIC关联的列的⾏为相同。INTEGER和NUMERIC亲和⼒
之间的区别仅在才明显。
具有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将接受的数据类型名称的⼀⼩部分。请注意，在括号中的数值参数是以下类型的名称（如：“VARCHAR（255）”）通过的SQLite忽略-SQLite不强加任何长度限制（除了⼤型全球其他对字符串的长度上限）的BLOB或数值。
结果亲和⼒⽤于确定亲和⼒的规则
CREATE TABLE语句
或CAST表达式中的 ⽰例类型名
整数1个
INT
整数
TINYINT
SMALLINT
MEDIUMINT
BIGINT⽆ 符号
BIG
INT2
INT8
⽂本2
电脑如何超频
字符（20）
VARCHAR（255）
可变字符（255）
NCHAR（55）
原始字符（70）
NVARCHAR（100）
⽂本
CLOB
BLOB3
BLOB
未指定数据类型
真实4
REAL
DOUBLE
DOUBLE PRECISION
FLOAT
数字5
NUMERIC
DECIMAL（10,5）
BOOLEAN
DATE
DATETIME
注意，由于“ POINT”末尾的“ INT”，声明的类型“ FLOATING POINT”将赋予INTEGER亲和⼒，⽽不是REAL亲和⼒。并且声明的“ STRING”类型具有NUMERIC（⽽不是TEXT）的相似性。
3.2。表达的亲和⼒
每个表列都有⼀个类型关联（BLOB，TEXT，INTEGER，REAL或NUMERIC中的⼀个），但表达式不⼀定具有关联。
表达亲和⼒由以下规则确定：
如果操作数是列表，则IN或NOT IN运算符的右侧操作数没有亲和⼒；如果操作数是SELECT，则它与结果集表达式的亲和⼒具有相同的亲和⼒。
如果表达式是对实际表的某个列（⽽不是或⼦查询）的简单引⽤，则该表达式具有与表列相同的相似性。
列名周围的括号将被忽略。因此，如果X和YZ是列名，则（X）和（YZ）也被视为列名，并且具有相应列的相似性。
应⽤于列名称的所有运算符，包括⽆操作符⼀元“ +”运算符，都会将列名称转换为始终没有关联的表达式。因此，
即使X和YZ是列名，表达式+ X和+ YZ也不是列名并且没有亲和⼒。
形式为“ CAST（expr AS type）”的表达式的亲和⼒与声明类型为“ type ” 的列相同。
COLLATE运算符与其左侧操作数具有相同的相似性。
九月吉他谱否则，表达式没有亲和⼒。
3.3。视图和⼦查询的列相似性
或FROM⼦句的“列” 实际上是实现VIEW或⼦查询的语句的结果集中的表达式。因此，对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，并且表达式始终没有亲和⼒。
如果实现或FROM⼦句的语句是则VIEW或⼦查询的每个假定列的亲和⼒将是组成该复合的单个SELE
CT语句之⼀的相应结果列的亲和⼒。 。但是，不确定使⽤哪个SELECT语句来确定亲和⼒。在查询评估期间，可以使⽤不同的组成SELECT语句来确定不同时间的相似性。最佳实践是避免在化合物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。排序
根据以下规则，⽐较结果取决于操作数的存储类：
具有存储类NULL的值被认为⼩于任何其他值（包括具有存储类NULL的另⼀个值）。
INTEGER或REAL值⼩于任何TEXT或BLOB值。将⼀个INTEGER或REAL与另⼀个INTEGER或REAL进⾏⽐较时，将执⾏数值⽐较。
TEXT值⼩于BLOB值。⽐较两个TEXT值时，将使⽤适当的整理顺序来确定结果。
⽐较两个BLOB值时，使⽤memcmp（）确定结果。
4.2。⽐较之前的类型转换
在执⾏⽐较之前，SQLite可能会尝试在存储类INTEGER，REAL和/或TEXT之间转换值。在进⾏⽐较之前是否尝试进⾏任何转换取决于操作数的类型相似性。
“应⽤相似性”是指，并且仅当该转换不会丢失基本信息时，才将操作数转换为特定的存储类。数字值始终可以转换为TEXT。如果⽂本内容是格式正确的整数或实数⽂字，⽽不是⼗六进制整数⽂字，则TEXT值可以转换为数值。通过简单地将⼆进制BLOB上下⽂解释为当前数据库编码中的⽂本字符串，可以将BLOB值转换为TEXT值。
在⽐较之前，将按照以下规则按照显⽰的顺序将亲和⼒应⽤于⽐较运算符的操作数：
如果⼀个操作数具有INTEGER，REAL或NUMERIC关联，⽽另⼀个操作数具有TEXT或BLOB或⽆关联，则将NUMERIC关联应⽤于其他操作数。
如果⼀个操作数具有TEXT相似性，⽽另⼀个操作数没有TEXT亲和性，则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。⽐较例
