`
wxlgzxx_1988
  • 浏览: 69112 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

sqlite3中的数据类型-[转载#笔记]

阅读更多

大多数的数据库引擎(到现在据我们所知的除了sqlite的每个sql数据库引擎)都使用静态的、刚性的类型,使用静态类型,数据的类型就由它的容器决定,这个容器是这个指被存放的特定列。

Sqlite使用一个更一般的动态类型系统,sqlite中,值的数据类型跟值本身相关,而不是与它的容器相关Sqlite的动态类型系统和其他数据库的更为一般的静态类型系统相兼容,但同时,sqlite中的动态类型允许它能做到一些传统刚性类型数据库所不可能做到的事。

 

1  存储类和数据类型

每个存放在sqlite数据库中(或者由这个数据库引擎操作)的值都有下面中的一个存储类

l  NULL,值是NULL

l  INTEGER,值是有符号整形,根据值的大小以1,2,3,4,68字节存放

l  REAL,值是浮点型值,以8字节IEEE浮点数存放

l  TEXT,值是文本字符串,使用数据库编码(UTF-8UTF-16BE或者UTF-16LE)存放

l  BLOB,只是一个数据块,完全按照输入存放(即没有准换)

从上可以看出存储类比数据类型更一般化。比如INTEGER存储类,包括6中不同长度的不同整形数据类型,这在磁盘上造成了差异。但是只要INTEGER值被从磁盘读出进入到内存进行处理,它们被转换成最一般的数据类型(8-字节有符号整形)。

Sqlite v3数据库中的任何列,除了整形主键列,可以用于存储任何一个存储列的值。sql语句中的中所有值,不管它们是嵌入在sql文本中或者是作为参数绑定到一个预编译的sql语句,它们的存储类型都是未定的。在下面描述的情况中,数据库引擎会在查询执行过程中在数值(numeric)存储类型(INTEGERREAL)和TEXT之间转换值。

1.1布尔类型

Sqlite没有单独的布尔存储类型,它使用INTEGER作为存储类型,0false1true

 

1.2 DateTime Datatype

Sqlite没有另外为存储日期和时间设定一个存储类集,内置的sqlite日期和时间函数能够将日期和时间以TEXTREALINTEGER形式存放

l  TEXT 作为IS08601字符串("YYYY-MM-DD HH:MM:SS.SSS"

l  REAL 从格林威治时间1124日,4174 B.C中午以来的天数

l  INTEGER  1970-01-01 00:00:00 UTC以来的秒数

程序可以任意选择这几个存储类型去存储日期和时间,并且能够使用内置的日期和时间函数在这些格式间自由转换

 

2.0 类型近似

 

为了使sqlite和其他数据库间的兼容性最大化,sqlite支持列上“类型近似”的观点,列的类型近似指的是存储在列上数据的推荐类型。这里必须记住一点,这个类型是被推荐,而不是必须的。任何列仍然能存储任意类型的数据。只是一些列,给予选择的话,将会相比于其他的一些类型优选选择一些存储类型,这个列优先选择的存储类型被称为它的“近似”。

每个sqlite3数据库中的列都被赋予下面类型近似中的一种:

l  TEXT

l  NUMERIC

l  INTEGER

l  REAL

l  NONE

具有TEXT近似的列可以用NULLTEXT或者BLOB类型存储数据。如果数值数据被插入到具有TEXT近似的列,在被存储前被转换为文本形式

一个有NUMERIC近似的列可以使用1中的所有5中存储类来存储数据。当文本数据被存放到NUMERIC近似的列中,这个文本的存储类被转换到INTEGERREAL(根据优先级顺序),如果这个转换是无损的话。对于TEXTREAL存储类间的转换,如果数据的前15位的被保留的话sqlite就认为这个转换是无损的、可反转的。如果TEXTINTEGERREAL的转换不可避免的会造成损失,那么数据将使用TEXT存储类存储。不会企图去转换NULLBLOB值。

一个字符串可能看起来像浮点数据,有小数点或指数符号,但是只要这个数据可以使用整形存放,NUMERIC近似就会将它转换到整形。比如,字符串 '3.0e+5'存放到一个具有NUMERIC近似的列中,被存为300000,而不是浮点型值300000.0

具有INTEGER近似的列和具有NUMERIC近似的列表现相同。它们之间的差别仅处于转换描述上。

具有REAL近似的列和具有NUMERIC近似的列一样,除了它将整形数据转换成浮点型形式

具有NONE近似的列不会优先选择一个存储列,也不会强制将数据从一个存储类转换到另外一个类。

2.1 列近似的决定因素

列的近似由这个列的声明类型所决定,根据下面的顺序的规则:

<1> 如果声明类型包含”INT”字符串,那么这个列被赋予INTEGER近似

<2> 如果这个列的声明类型包含”CHAR””CLOB”,或者”TEXT”中的任意一个,那么这个列就有了TEXT近似。注意类型VARCHAR包含了”CHAR”字符串,那么也就被赋予了TEXT近似

<3> 如果列的声明类型中包含了字符串”BLOB”或者没有为其声明类型,这个列被赋予NONE近似

<4> 其他的情况,列被赋予NUMERIC近似

上面规则额顺序对于决定列的近似很重要。一个列的声明类型为”CHARINT”的话同时会匹配规则<1><2>,但是第一个规则占有优先级所以这个列的近似将是INTEGER

 

2.2 近似名称例子

     下面这个表显示了多少来自更传统的SQL操作的普通数据类型名称,使用上一节中的5个规则,被转换到近似类型。这个表只显示了sqlite能够接受的数据类名称的一个子集。注意到跟随类型名的圆括号内的数值参数(如:”VARCHAR(255)”)被sqlite忽略—sqlite不在字符串、BLOBS或者数值的长度上强加任何长度限制(除了一个全局的SQLITE_MAX_LENGTH限制)。

来自create table语句或者强转语句的范例类型名

产生的近似

用于决定近似的规则

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
no datatype specified

NONE

3

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

REAL

4

NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME

NUMERIC

5

注意到声明类型为”FLOATING POINT”将被赋予INTEGER近似,而不是REAL近似,因为在”POINT”中的”INT”。声明类型为”STRING”的将被赋予NUMERIC,而不是TEXT(因为上述表中定义的类型中不存在STRING这一类型,它被归于到规则<4>中,属于其他情况)

(从上面可以看出,sqlite3只是从声明类型字符串中去查找它知道的声明类型,比如”XINT”将被赋予INTEGER近似因为这个字符串里面有”INT”,所以这里并不需要一个单独的正确的声明类型,而是只要声明类型字符串里面包含了sqlite所知道的声明类型即可)

2.3 列近似操作例子

CREATE TABLE t1(

    t  TEXT,     -- text affinity by rule 2

    nu NUMERIC,  -- numeric affinity by rule 5

    i  INTEGER,  -- integer affinity by rule 1

    r  REAL,     -- real affinity by rule 4

    no BLOB      -- no affinity by rule 3

); //这里根据声明类型确定了列的类型近似

INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果:text|integer|integer|real|text

DELETE FROM t1;

INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果:text|integer|integer|real|real

DELETE FROM t1;

INSERT INTO t1 VALUES(500, 500, 500, 500, 500);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果:text|integer|integer|real|integer

(这里的第四个值,对应的列是REAL近似的,传输的值整形的,但是根据REAL近似的规则它会将它转换为real型数据)

// 数据块(BLOB)不管是什么列近似都一直存为BLOB类型

DELETE FROM t1;

INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果:blob|blob|blob|blob|blob

// NULLs也不受列近似影响

DELETE FROM t1;

INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

//结果:null|null|null|null|null

3.0 比较表达式

              Sqlite v3有一系列有用的比较操作符,包括 "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", "IS NOT"

3.1 排序

比较操作的结果基于操作数的存储类型,根据下面的规则:

l  存储类型为NULL的值被认为小于其他任何的值(包括另一个存储类型为NULL的值)

l  一个INTEGERREAL值小于任何TEXTBLOB值。当一个INTEGERREAL值与另外一个INTEGERREAL值比较的话,就执行数值比较

l  TEXT值小于BLOB值。当两个TEXT值比较的时候,就根据序列的比较来决定结果

l  当两个BLOB值比较的时候,使用memcmp来决定结果

 

3.2 比较操作数的近似(Affinity

              Sqlite可能在执行一个比较之前会在INTEGERREALTEXT之间转换比较值。是否在比较操作之前发生转换基于操作数的近似(类型)。操作数近似(类型)由下面的规则决定:

l  对一个列的简单引用的表达式与这个列有相同的affinity,注意如果XY.Z是列名,那么+X+Y.Z均被认为是用于决定affinity的表达式

l  一个”CAST(expr as type)”形式的表达式与用声明类型为”type”的列有相同的affinity

l  其他的情况,一个表达式为NONE affinity

3.3 在比较前的类型转换

     只有在转换是无损、可逆转的时候“应用近似”才意味着将操作数转换到一个特定的存储类。近似在比较之前被应用到比较的操作数,遵循下面的规则(根据先后顺序):

l  如果一个操作数有INTEGERREALNUMERIC近似,另一个操作数有TEXTNONE近似,那么NUMERIC近似被应用到另一个操作数

l  如果一个操作数有TEXT近似,另一个有NONE近似,那么TEXT近似被应用到另一个操作数

l  其他的情况,不应用近似,两个操作数按本来的样子比较

表达式"a BETWEEN b AND c"表示两个单独的二值比较 a >= b AND a <= c”,即使在两个比较中不同的近似被应用到’a’

3.4 操作符

所有的数学操作符(+, -, *, /, %, <<, >>, &, |),在被执行前,都会将两个操作数都转换为数值存储类型(INTEGERREAL)。即使这个转换是有损和不可逆的,转换仍然会执行。一个数学操作符上的NULL操作数将产生NULL结果。一个数学操作符上的操作数,如果以任何方式看都不像数字,并且又不为空的话,将被转换为00.0

 

分享到:
评论

相关推荐

    sqlite-tools-win-x64-3440200.zip

    SQLite是一款轻量级的、开源的关系型数据库管理系统,它无需单独的服务器进程,可以直接嵌入到各种应用软件中。SQLite的工具集对于开发者来说是非常重要的,尤其是对于那些需要在Windows x64环境下处理SQLite数据库...

    sqlite-devel-3.7.17-8.el7.x86_64.rpm

    sqlite-devel-3.7.17-8.el7.x86_64.rpm

    PyPI 官网下载 | sqlite3-to-mysql-1.4.5.tar.gz

    在实际使用过程中,`sqlite3-to-mysql`库提供了许多自定义选项,如指定要迁移的特定表、处理数据类型转换、处理主键和外键关系等。这些功能使得用户可以根据具体需求对迁移过程进行精细控制,避免数据丢失或错误。 ...

    sqlite-shell-win32-x86: sqlite3.exe

    --sqllite3 sqlite-shell-win32-x86: sqlite3.exe --svn执行clean up命令时报错“Previous operation has not finished; run 'cleanup' if it was interrupted”。 解决此问题所需文件 2. 为了方便命令行执行,将...

    sqlite-tools-win-x64-3460000.zip

    SQLite3.exe 是 SQLite 数据库引擎的一个命令行接口工具,它允许用户通过文本命令与 SQLite 数据库进行交互。SQLite 是一个开源、轻量级、自包含的 SQL 数据库引擎,广泛应用于嵌入式系统和移动应用中,因为它无需...

    wvp-GB28181-pro 适配支持 SQlite3 数据库 支持wvp-GB28181-pro 2.6.9

    wvp-GB28181-pro 适配支持 SQlite3 数据库 支持wvp-GB28181-pro 2.6.9

    sqlite3.exe sqlite3.exe sqlite-dll-win64-x64-3230100

    标题中的 "sqlite3.exe sqlite3.exe sqlite-dll-win64-x64-3230100" 指的是这个压缩包包含了两个 `sqlite3.exe` 文件以及一个特定版本(3230100)的 `sqlite-dll-win64-x64`,即64位 Windows 平台的 SQLite3 动态...

    SQLiteStudio-3.4.4-windows-x64-installer.zip

    SQLite在Windows平台上的使用,除了SQLiteStudio,还可以通过命令行工具sqlite3.exe进行操作,但对于非开发者或不熟悉命令行的用户,SQLiteStudio提供了更友好的图形化界面,使得数据库管理更加便捷。 总之,SQLite...

    sqlite-shell-linux-x86-3080900.zip

    例如,可以使用`echo "SQL Command" | sqlite3 db_file`将SQL命令从命令行输入,或者`sqlite3 db_file 执行存储在文件中的SQL脚本。 SQLite3.8.9版本相对于更早的版本,可能包含了一些性能优化和新的特性和函数,...

    sqlite3.7z

    sqlite3.exe Ver 3.33版本 2020-8-14出版,还是蛮新的版本。 E:\sqlite3&gt;sqlite3 --version #显示sqlite3的版本 3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0alt2 E...

    SQLite-1.0.66.0-setup

    SQLite是一款开源、轻量级的嵌入式数据库引擎,它不需要独立的服务进程,可以直接在应用程序中使用。"SQLite-1.0.66.0-setup" 标题表明这是一个关于SQLite数据库的安装程序,版本号为1.0.66.0。这个压缩包可能包含了...

    sqlite .net 源码包 sqlite-netFx-source-1.0.86.0

    通过分析这个源码包,开发者可以了解到如何在.NET项目中集成SQLite数据库,包括数据访问层的设计、事务处理、错误处理、查询构建等。同时,源码包还支持多种.NET版本和平台,使得开发者可以根据项目需求选择合适的...

    sqlite-shell-linux-x86-3080500.zip

    1. **独立性**:SQLite3是完全自包含的,不需要单独的服务器进程,可以直接在应用程序中使用。 2. **移植性**:它支持多种操作系统,包括Linux、Windows、Mac OS X等,且对硬件要求低。 3. **SQL标准兼容**:SQLite3...

    SQLite3-net2-4.6-DLL

    3. sqlite-netFx451-static-binary-x64-2013-1.0.109.0.zip、sqlite-netFx45-static-binary-x64-2012-1.0.109.0.zip、sqlite-netFx451-static-binary-Win32-2013-1.0.109.0.zip 和 sqlite-netFx45-static-binary-Win...

    sqlite-tools-linux-x86-3350400.zip

    SQLite是一款开源、轻量级的嵌入式关系数据库管理系统,广泛应用于移动设备、桌面系统以及服务器环境,尤其在需要快速实现数据存储的应用中备受青睐。SQLite的工具集是其核心功能的一部分,它提供了多种用于与SQLite...

    sqlite-shell-win32-x86-3080200.zip

    1. **安装与运行**:解压"sqlite-shell-win32-x86-3080200.zip"到任意目录,然后找到sqlite3.exe,双击运行或在命令行中输入其路径启动。 2. **连接数据库**:在命令行中,输入`.open 数据库文件名`,例如`.open ...

    sqlite-tools-win32-x86-3290000

    用户可以在该目录下运行sqlite3.exe来执行SQL命令,如创建表、插入数据、查询数据等。此外,这个目录还可能包含其他与SQLite相关的工具和实用程序。请注意,具体的文件名和版本号可能会因SQLite的更新而有所变化。

    sqlite-autoconf-3070800-arm.tar.gz_arm sqlite_arm sqlite-autocon

    解压后,通常会包含`sqlite3`可执行文件、动态库(`libsqlite3.so`)和头文件,这些文件可供应用程序链接和调用SQLite API,实现数据存储和查询功能。 标签中的“arm_sqlite”和“sqlite_for_arm”强调了这是面向ARM...

    sqlite3-win32-x86-307

    SQLite3支持多种数据类型,包括NULL、INTEGER、REAL、TEXT和BLOB,以及自定义类型。它的SQL语法基本兼容标准SQL,同时提供了一些特有功能,如事务处理、视图、触发器等。由于其轻量级和无服务器特性,SQLite3常用于...

Global site tag (gtag.js) - Google Analytics