`

sqlite3中的数据类型

 
阅读更多

(转)http://www.cnblogs.com/kfqcome/archive/2011/06/27/2137000.html

 

大多数的数据库引擎(到现在据我们所知的除了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 比较举例

CREATE TABLE t1(

    a TEXT,      -- text affinity

    b NUMERIC,   -- numeric affinity

    c BLOB,      -- no affinity

    d            -- no affinity

);

 

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

SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;

text|integer|text|integer

 

-- Because column "a" has text affinity, numeric values on the

-- right-hand +side of the comparisons are converted to text before

-- the comparison occurs.

SELECT a < 40,   a < 60,   a < 600 FROM t1;

0|1|1

 

-- Text affinity is applied to the right-hand operands but since

-- they are already TEXT this is a no-op; no conversions occur.

SELECT a < '40', a < '60', a < '600' FROM t1;

0|1|1

 

-- Column "b" has numeric affinity and so numeric affinity is applied

-- to the operands on the right.  Since the operands are already numeric,

-- the application of affinity is a no-op; no conversions occur.  All

-- values are compared numerically.

SELECT b < 40,   b < 60,   b < 600 FROM t1;

0|0|1

 

-- Numeric affinity is applied to operands on the right, converting them

-- from text to integers.  Then a numeric comparison occurs.

SELECT b < '40', b < '60', b < '600' FROM t1;

0|0|1

 

-- No affinity conversions occur.  Right-hand side values all have

-- storage class INTEGER which are always less than the TEXT values

-- on the left.

SELECT c < 40,   c < 60,   c < 600 FROM t1;

0|0|0

 

-- No affinity conversions occur.  Values are compared as TEXT.

SELECT c < '40', c < '60', c < '600' FROM t1;

0|1|1

 

-- No affinity conversions occur.  Right-hand side values all have

-- storage class INTEGER which compare numerically with the INTEGER

-- values on the left.

SELECT d < 40,   d < 60,   d < 600 FROM t1;

0|0|1

 

-- No affinity conversions occur.  INTEGER values on the left are

-- always less than TEXT values on the right.

SELECT d < '40', d < '60', d < '600' FROM t1;

1|1|1

 

从这里可以看出,假如可以使用3.1中的规则进行比较的话,就不需要进行类型转换,否则的话就要进行类型转换

 

4.0 操作符

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

分享到:
评论

相关推荐

    SQLite3中的数据类型

    在SQLite3中,数据类型的设计与许多其他数据库系统有所不同,它具有灵活性且易于理解。下面将详细介绍SQLite3中的数据类型及其特点。 1. NULL类型 SQLite3允许字段值为NULL,这意味着没有任何值。NULL并不是一种...

    DatatypesInSQLiteVersion3中文版SQLite3的数据类型.rar

    本资料"DatatypesInSQLiteVersion3中文版SQLite3的数据类型.pdf"将详细介绍SQLite3中的数据类型,帮助你深入理解如何在SQLite3数据库中存储和管理各种数据。 SQLite3的数据类型不同于传统的关系型数据库,如MySQL或...

    Sqlite3支持的数据类型

    下面将详细介绍SQLite3支持的数据类型及其特性。 1. NULL(空值) SQLite中的NULL类型代表一个未知或未定义的值。它不同于其他类型的零值,如数字0或空字符串。NULL值在比较时与其他任何值都不相等,包括另一个NULL...

    Sqlite3中文路径解决

    1. **编码转换**:SQLite3使用UTF-8编码存储数据,而Windows系统则通常使用GBK或者Unicode(UTF-16)编码处理文件路径。因此,我们需要在读取或写入路径时进行编码转换。可以使用C++标准库中的`std::wstring_convert...

    将sqlite3中数据导入到mysql中的实战教程

    本教程将详细介绍如何将sqlite3数据库中的数据导入到mysql数据库中,这对于处理并发访问能力不足或需要更强大功能的数据库场景非常有用。以下是具体的步骤和注意事项。 ### 前言 sqlite3是一个轻量级的数据库系统,...

    sqlite3文件(sqlite3.dll,sqlite3.h,sqlite3.lib)

    它不需要单独的服务器进程,可以直接在应用程序中运行,提供事务处理、SQL查询支持以及多种数据类型。标题提到的"sqlite3文件"主要包括三个关键组成部分:`sqlite3.dll`、`sqlite3.h`和`sqlite3.lib`,它们在开发和...

    sqlite3数据库查看工具

    SQLite3是一种轻量级、开源的嵌入式SQL数据库引擎,广泛应用于移动设备、桌面应用以及服务器环境,尤其适合处理小到中等规模的数据存储需求。它的设计目标是实现零配置、事务支持、完整的SQL语法以及自包含性,使得...

    sqlite3 for delphi 解决中文乱码问题

    Delphi提供了与SQLite3交互的接口,允许开发者在应用程序中方便地存储和检索数据。然而,由于编码不匹配或者处理方式不当,可能会导致中文字符在存储或读取时出现乱码。 中文乱码的主要原因在于字符编码格式的不...

    Sqlite3重编译版本,支持Sqlite3_key和Sqlite3_rekey方法.rar

    通过`Sqlite3_key`和`Sqlite3_rekey`函数,开发人员能够更好地管理和保护存储在SQLite3数据库中的数据,确保数据在传输和存储过程中的安全性。在使用此版本之前,应仔细阅读相关文档,并确保正确地集成和使用这些...

    Sqlite3 安装文件

    3. **安全性**:SQLite3 提供事务处理功能,确保数据的一致性和完整性。如果在事务执行过程中发生错误,可以回滚到事务开始前的状态。 4. **跨平台**:SQLite3 可以运行在多种操作系统上,包括 Windows、Linux、...

    Linux-sqlite3

    4. **事务处理**:SQLite3支持事务处理,确保数据的一致性和完整性。BEGIN、COMMIT和ROLLBACK语句用于控制事务的开始、提交和回滚。 5. **索引**:为了加快查询速度,SQLite3允许创建索引。可以使用CREATE INDEX...

    sqlite3.h、sqlite3.lib、sqlite3.dll

    在本主题中,我们关注的是SQLite的核心组件——`sqlite3.h`、`sqlite3.lib`和`sqlite3.dll`,这些是开发过程中用于与SQLite交互的关键文件。 1. **sqlite3.h**: 这是一个头文件,包含了SQLite的所有公共API(应用...

    sqlite3使用简介数据类型和头文件动态库

    二、SQLite3中的数据类型 SQLite3支持以下数据类型: 1. NULL:空值,表示未赋值。 2. INTEGER:整型,可以存储8、16、32或64位有符号整数,取决于所需空间。 3. REAL:浮点型,用于存储双精度浮点数。 4. TEXT:...

    SQLite3C++

    在本主题中,我们关注的是SQLite3与C++的结合,即如何在C++程序中使用SQLite3进行数据存储和检索。 1. **SQLite3的特性** - **轻量级**: SQLite3数据库文件是一个普通的文件,可以在文件系统中直接创建、读取和...

    sqlite3.lib sqlite3.h sqlite3.dll sqlite3.exe

    4. **高性能**:尽管轻量级,SQLite3在处理大量数据时表现出良好的性能。 5. **嵌入式**:适合于嵌入式设备和应用程序,无需额外的服务器软件。 综上所述,这个压缩包提供了SQLite3开发和运行所需的基本组件,无论...

    Delphi使用ASqlite3 连接SQLite上例子

    本教程将详细讲解如何在Delphi环境中使用ASqlite3控件连接并操作SQLite数据库。ASqlite3是一款专门为Delphi开发者设计的SQLite数据库接口组件,它使得在Delphi程序中与SQLite数据库交互变得简单。 首先,我们需要...

    sqlite数据库 大数据量处理demo

    总的来说,理解和掌握这些SQLite大数据处理技术,可以帮助开发者构建更高效、更稳定的数据库应用,特别是在资源有限的环境中。通过实际的代码示例和注释,新手可以快速上手,逐步提升数据库管理技能。

    sqlite3 驱动 for odbc

    1. 数据库连接:通过在ODBC数据源管理器中配置SQLite3数据源,用户可以创建到SQLite3数据库的连接。这通常涉及提供数据库文件的路径和必要的连接参数。 2. SQL查询:使用ODBC API,开发者可以编写SQL语句,执行查询...

    详解SQLite中的数据类型

    在SQLite中,数据类型的设计与大多数传统SQL数据库引擎不同,它采用了一种更为灵活的动态类型系统。这种设计使得SQLite在处理不同类型的数据时具有更大的灵活性和兼容性。 首先,SQLite支持五种基本的存储类型: 1...

    sqlite3-用于SVN下的数据指令操作

    SQLite3是一种轻量级、自包含的数据库引擎,它无需单独的服务器进程,可以直接在客户端进行数据存储和处理。在SVN(Subversion)版本控制系统中,SQLite3常被用作仓库元数据的存储库,这使得SVN可以对文件历史、版本...

Global site tag (gtag.js) - Google Analytics