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

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数据库引擎通常会尝试自动将值转换为适当的数据类型。考虑一下：

CREATE TABLE t1(a INT, b VARCHAR(10)); 
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数据库巡检脚本，值得收藏

（编辑：温州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!