SQLite数据类型详解

[TOC]

1 存储类

SQLite将数据值的存储划分为五大类,每个存储在 SQLite 数据库中的值都具有以下存储类之一:

存储类 描述
NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。
TEXT 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。
BLOB 值是一个 blob 数据,完全根据它的输入存储, 该类型数据和输入数据完全相同。
  • 由于SQLite采用的是动态数据类型,而其他传统的关系型数据库使用的是静态数据类型,即字段可以存储的数据类型是在表声明时即以确定的,因此它们之间在数据存储方面还是存在着很大的差异。
  • 在SQLite中,存储分类和数据类型也有一定的差别,如INTEGER存储类别可以包含6种不同长度的Integer数据类型,然而这些INTEGER数据一旦被读入到内存后,SQLite会将其全部视为占用8个字节无符号整型。因此对于SQLite而言,即使在表声明中明确了字段类型,我们仍然可以在该字段中存储其它类型的数据。
  • 然而需要特别说明的是,尽管SQLite为我们提供了这种方便,但是一旦考虑到数据库平台的可移植性问题,我们在实际的开发中还是应该尽可能的保证数据类型的存储和声明的一致性。除非你有极为充分的理由,同时又不再考虑数据库平台的移植问题,在此种情况下确实可以使用SQLite提供的此种特征。

2 亲缘类型

为了最大化SQLite和其它数据库引擎之间的数据类型兼容性,SQLite提出了”类型亲缘性(Type Affinity)”的概念。

五种亲缘类型

SQLite目前的版本支持以下五种亲缘类型,

亲和类型 描述
TEXT 数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中。
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明的是,对于浮点格式的常量文本,如”30000.0”,如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER 对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。
REAL 其规则基本等同于NUMERIC,唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式。
NONE 不做任何的转换,直接以该数据所属的数据类型进行存储。

亲缘类型的应用规则

字段的亲缘性是根据该字段在声明时被定义的类型来决定的,具体的规则可以参照以下列表。需要注意的是以下列表的顺序,即如果某一字段类型同时符合两种亲缘性,那么排在前面的规则将先产生作用。   

  1. 如果类型字符串中包含”INT”,那么该字段的亲缘类型是INTEGER
  2. 如果类型字符串中包含”CHAR”、”CLOB”或”TEXT”,那么该字段的亲缘类型是TEXT,如VARCHAR。
  3. 如果类型字符串中包含”BLOB”,那么该字段的亲缘类型是NONE。
  4. 如果类型字符串中包含”REAL”、”FLOA”或”DOUB”,那么该字段的亲缘类型是REAL。
  5. 其余情况下,字段的亲缘类型为NUMERIC。

Sqlite 使用的类型汇总

声明类型 亲缘类型 应用规则
INT, INTEGER
TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT
INT2 INT8
INTEGER 1
CHARACTER(20),
VARCHAR(255), VARYING CHARACTER(255)
NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100)
TEXT, CLOB
TEXT 2
BLOB NONE 3
REAL, DOUBLE, DOUBLE PRECISION, FLOAT REAL 4
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME NUMERIC 5

3 Boolean 数据类型

 SQLite并没有提供专门的布尔存储类型,取而代之的是存储整型1表示true,0表示false。

4 Date 与 Time 数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类,但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

存储类 日期格式
TEXT 格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
REAL 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER 从 1970-01-01 00:00:00 UTC 算起的秒数。

5 类型比较和类型计算

比较表达式

算术操作

References

Table of Contents