SQLite版本3中的数据类型--688IT编程网

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。列亲和⾏为⽰例

decimal是整数数据类型以下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'）;