SQLite版本3中的数据类型

最新推荐文章于 2023-08-26 02:52:34 发布

文产人 leoking

最新推荐文章于 2023-08-26 02:52:34 发布

阅读量2.1k

点赞数

分类专栏： python 文章标签： python 数据库 sqlite3 sql

原文链接：https://www.sqlite.org/datatype3.html

版权

python 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

► 目录

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字节有符号整数）。因此，在大多数情况下，“存储类”与“数据类型”是无法区分的，并且这两个术语可以互换使用。

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

SQL语句中的所有值，无论它们是嵌入在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之前的版本保持历史兼容版本3.8.6 2014-08-15，其中十六进制整数文字首次引入SQLite。）不尝试转换NULL或BLOB值。

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

使用INTEGER关联的列的行为与具有NUMERIC关联的列的行为相同。INTEGER和NUMERIC亲和力之间的区别仅在CAST表达中才明显。

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

具有亲和力BLOB的列不喜欢一个存储类别而不是另一个存储类别，也没有尝试将数据从一个存储类别强制转换为另一个存储类别。

3.1。色谱柱亲和力的测定

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

如果声明的类型包含字符串“ INT”，那么将为其分配INTEGER关联。
如果列的声明类型包含任何字符串“ CHAR”，“ CLOB”或“ TEXT”，则该列具有TEXT关联性。请注意，类型VARCHAR包含字符串“ CHAR”，因此被分配为TEXT关联。
如果列的声明类型包含字符串“ BLOB”，或者未指定类型，则该列具有关联BLOB。
如果列的声明类型包含任何字符串“ REAL”，“ FLOA”或“ DOUB”，则该列具有REAL亲和力。
否则，关联性为NUMERIC。

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

3.1.1。相似性名称示例

下表显示了通过上一节的五个规则将更传统的SQL实现中的多少个通用数据类型名称转换为关联性。该表仅显示SQLite将接受的数据类型名称的一小部分。请注意，在括号中的数值参数是以下类型的名称（如：“VARCHAR（255）”）通过的SQLite忽略- SQLite不强加任何长度限制（除了大型全球其他SQLITE_MAX_LENGTH对字符串的长度上限）的BLOB或数值。

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

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中的一个），但表达式不一定具有关联。

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

如果操作数是列表，则IN或NOT IN运算符的右侧操作数没有亲和力；如果操作数是SELECT，则它与结果集表达式的亲和力具有相同的亲和力。
如果表达式是对实际表的某个列（而不是VIEW或子查询）的简单引用，则该表达式具有与表列相同的相似性。
- 列名周围的括号将被忽略。因此，如果X和YZ是列名，则（X）和（YZ）也被视为列名，并且具有相应列的相似性。
- 应用于列名称的所有运算符，包括无操作符一元“ +”运算符，都会将列名称转换为始终没有关联的表达式。因此，即使X和YZ是列名，表达式+ X和+ YZ也不是列名并且没有亲和力。
形式为“ CAST（expr AS type）”的表达式的亲和力与声明类型为“ type ” 的列相同。
COLLATE运算符与其左侧操作数具有相同的相似性。
否则，表达式没有亲和力。

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语句来确定不同时间的相似性。最佳实践是避免在化合物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。比较例

创建表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中选择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。

BINARY-使用memcmp（）比较字符串数据，而不考虑文本编码。
NOCASE-与二进制文件类似，不同之处在于它使用 sqlite3_strnicmp（）进行比较。因此，在执行比较之前，将ASCII的26个大写字母折叠为它们的小写字母。请注意，只有ASCII字符是大小写折叠的。由于所需表的大小，SQLite不会尝试进行完整的UTF大小写折叠。另请注意，出于比较目的，字符串中的所有U + 0000字符均被视为字符串终止符。
RTRIM-与二进制文件相同，只是忽略了尾随空格字符。

应用程序可以使用sqlite3_create_collation（）接口注册其他整理功能。

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

7.1。从SQL分配整理序列

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

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

如果两个操作数都使用后缀COLLATE运算符进行了显式整理功能分配，则该显式整理功能将用于比较，并优先于左操作数的整理功能。
如果任一操作数是一列，则使用该列的整理功能，其优先级高于左操作数。出于上一句子的目的，在一个或多个一元“ +”运算符和/或CAST运算符之后的列名称仍被视为列名称。
否则，将使用BINARY整理函数进行比较。

如果比较操作数的任何子表达式使用后缀COLLATE运算符，则认为该比较操作数具有显式整理函数分配（上述规则1）。因此，如果在比较表达式的任何地方使用COLLATE运算符，则整理函数

文产人 leoking

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
SQLite版本3中的数据类型

SQLite版本3中的数据类型►目录1.SQLite中的数据类型大多数SQL数据库引擎（据我们所知，除SQLite以外的所有SQL数据库引擎）都使用静态的刚性类型。对于静态类型，值的数据类型由其容器（存储值的特定列）确定。SQLite使用更通用的动态类型系统。在SQLite中，值的数据类型与值本身（而不是其容器）相关联。SQLite的动态类型系统与其他数据库引擎更常见的静态类型系统向后兼容，因为在静态类型的数据库上运行的SQL语句应在SQLite中以相同的方式运行。但是，SQLite...
复制链接

扫一扫