|
|
51CTO旗下网站
|
|
移动端

SQLite3的数据类型-动态数据类型

大多数SQL数据库引擎(除了SQLite之外的每个SQL数据库引擎,据我们所知)都使用静态,严格的类型。使用静态类型时,列的数据类型由其容器(存储值的特定列)确定。

作者:源理君来源:技术原理君|2019-08-12 11:40

1. SQLite中的数据类型

大多数SQL数据库引擎(除了SQLite之外的每个SQL数据库引擎,据我们所知)都使用静态,严格的类型。使用静态类型时,列的数据类型由其容器(存储值的特定列)确定。

SQLite使用更通用的动态类型系统。在SQLite中,值的数据类型与值本身相关联,而不是与其容器相关联。SQLite的动态类型系统向后兼容其他数据库引擎的更常见的静态类型系统,因为在静态类型数据库上工作的SQL语句应该在SQLite中以相同的方式运行。但是,SQLite中的动态类型允许它执行传统的刚性类型数据库中无法实现的操作。

2. 存储类和数据类型

存储在SQLite数据库中(或由数据库引擎操纵)的每个值都是以下存储类之一:

  • NULL。该值为NULL值。
  • INTEGER。该值是有符号整数,存储为1,2,3,4,6或8个字节,具体取决于值的大小。
  • REAL。该值是浮点值,存储为8字节IEEE浮点数。
  • TEXT。该值是一个文本字符串,使用数据库编码(UTF-8,UTF-16BE或UTF-16LE)存储。
  • BLOB。该值是一个数据块,可以二进制的形式存储任何数据。

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

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

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

(1) 布尔数据类型

SQLite没有单独的布尔存储类。相反,布尔值存储为整数0(假)和1(真)。

(2) 日期和时间数据类型

SQLite没有为存储日期和/或时间而预留的存储类。相反,SQLite 的内置日期和时间函数能够将日期和时间存储为TEXT,REAL或INTEGER值:

  • TEXT为ISO8601字符串(“YYYY-MM-DD HH:MM:SS.SSS”)。
  • REAL如朱利安日数,根据公历4714年11月24日格林威治中午以来的天数。
  • INTEGER as Unix Time,自1970-01-01 00:00:00 UTC以来的秒数。

应用程序可以选择以任何这些格式存储日期和时间,并使用内置的日期和时间函数在格式之间自由转换 。

3. Type Affinity

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

  1. CREATE TABLE t1(a INT, b VARCHAR(10)); 
  2. INSERT INTO t1(a,b) VALUES('123',456); 

在执行插入之前,刚性类型的数据库将字符串'123'转换为整数123,将整数456转换为字符串'456'。

为了最大化SQLite和其他数据库引擎之间的兼容性,上面的示例将像在其他SQL数据库引擎上一样对SQLite起作用,SQLite支持列上的“类型亲和性”概念。列的类型亲缘关系是存储在该列中的数据的推荐类型。这里的重要思想是建议使用类型,而不是必需的。任何列仍然可以存储任何类型的数据。根据选择,某些列更倾向于使用一个存储类而不是另一个存储类。列的首选存储类称为“亲和性”。

SQLite 3数据库中的每一列都分配了以下类型之一:

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

(历史记录:“BLOB”类型的亲和力曾被称为“NULL”。但该术语容易与“无亲和力”混淆,因此它被重命名。)

具有TEXT亲缘关系的列使用存储类NULL,TEXT或BLOB存储所有数据。如果将数值数据插入到具有TEXT亲和力的列中,则在存储之前将其转换为文本形式。

具有NUMERIC亲缘关系的列可能包含使用所有五个存储类的值。当文本数据插入NUMERIC列时,如果此类转换是无损且可逆的,则文本的存储类将转换为INTEGER或REAL(按优先顺序)。对于TEXT和REAL存储类之间的转换,如果保留数字的前15个有效十进制数字,SQLite认为转换是无损且可逆的。如果无法将TEXT无损转换为INTEGER或REAL,则使用TEXT存储类存储该值。不尝试转换NULL或BLOB值。

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

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

具有REAL亲和性的列的行为类似于具有NUMERIC亲和力的列,除了它将整数值强制为浮点表示形式。(作为内部优化,没有小数组件并存储在具有REAL关联性的列中的小浮点值将作为整数写入磁盘,以便占用更少的空间,并在读取值时自动转换回浮点。优化在SQL级别完全不可见,只能通过检查数据库文件的原始位来检测。)

具有关联性BLOB的列不优选一个存储类而不是另一个存储类,并且不会尝试将数据从一个存储类强制转换为另一个存储类。

【编辑推荐】

  1. 后端程序员必备:MySQL数据库相关流程图/原理图
  2. 8月数据库排行:Oracle 节节攀升,PG 和 MongoDB 分数再现下跌
  3. 面试官:在使用MySQL数据库时,遇到重复数据怎么处理?
  4. MySQL、SQL Server、Oracle对比,你必须了解的三大数据库区别
  5. 分享一份实用的MySQL数据库巡检脚本,值得收藏
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

92人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

361人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

739人订阅学习

读 书 +更多

非常网管——网络应用

在网络应用越来越复杂的今天,传统的网络应用已经不能满足企业和用户的需要,这就对网络管理员、信息管理部门提出了更高的要求。本书介绍了...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微