加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

SQLite3的数据类型

发布时间：2020-12-13 00:06:36 所属栏目：百科 来源：网络整理
导读：SQLite3的数据类型 大多数SQL数据库引擎（据我所知，所有除SQLite外的SQL数据库引擎）使用静态固定类型。使用静态类型，数据的类型由它的容器——即存储数据的列决定。 SQLite使用一个更通用的动态类型系统。在SQLite中，数据的类型与数据本身绑定，而不是与

Example Typenames From The
CREATE TABLE Statement
or CAST Expression

Resulting Affinity

Rule Used To Determine Affinity

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。

2.3 Column Affinity Behavior Example

下面的SQL示例说明SQLite如何使用列类型关联，在表中插入数据时进行类型转换。

CREATE TABLE t1(

t TEXT,-- text affinity by rule 2

nuNUMERIC,-- 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

);

-- 数据存储为 TEXT,INTEGER,REAL,TEXT.

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

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

text|integer|integer|real|text

-- 数据存储为 TEXT,REAL.

DELETE FROM t1;

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

SELECT typeof(t),typeof(no) FROM t1;

text|integer|integer|real|real

-- 数据存储为 TEXT,INTEGER.

DELETE FROM t1;

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

SELECT typeof(t),typeof(no) FROM t1;

text|integer|integer|real|integer

-- BLOB数据总是存储BLOB存储类型，而忽略列类型关联

DELETE FROM t1;

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

SELECT typeof(t),typeof(no) FROM t1;

blob|blob|blob|blob|blob

-- NULL数据不受列类型关联的影响

DELETE FROM t1;

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

SELECT typeof(t),typeof(no) FROM t1;

null|null|null|null|null

3.0Comparison Expressions

SQLite 版本 3 包含通常的SQL比较运算符集合，包括“=”， “==”， “<”，“<=”， “>”， “>=”， “!=”， “<>”，“IN”， “NOT IN”， “BETWEEN”， “IS” 和 “ISNOT”。

3.1 Sort Order

比较结果依赖于操作数的存储类型，参见如下规则：
  • 存储类型为NULL的数据小于任何其它类型的数据（包括另一个存储类型为NULL的数据）。
  • INTEGER或REAL存储类型的数据小于任何TEXT或BLOB类型的数据。如果一个INTEGER或REAL数据与另一个INTEGER或REAL数据进行比较，使用数值比较运算符。
  • TEXT存储类型数据小于BLOB存储类型数据。两个TEXT类型的数据进行比较，使用内置的collating sequence（wayz注：collating sequence就是一组内置的函数，后面会有介绍）。
  • 两个BLOB存储类型数据进行比较时，使用memcmp ()函数进行比较。

3.2 Affinity Of ComparisonOperands

SQLite在进行比较前可能会在INTEGER，REAL和TEXT间转化数据。是否需要转化取决于操作数的类型关联。操作数的类型关联由以下规则决定：
  • 简单引用一个列数据的表达式具有与列相同的类型关联。注意，如果X和Y.Z是列名，那么+X和+Y.Z用来表示希望用规则决定类型关联的表达式（wayz注：即使用NONE类型关联）。
  • 具有“CAST (exp AS type)”形式的表达式，其类型关联与使用type声明的列相同。
  • 否则，表达式具有NONE类型关联。

3.3 Type Conversions Prior ToComparison

“应用类型关联”指在无损且可逆的情况下把操作数转化为一个特定的存储类型。按如下顺序指定的规则，在比较前，对比较运算符的操作数应用类型关联：
  • 如果一个操作数具有INTEGER、REAL或NUMERIC类型关联，另一个操作数具有TEXT或NONE类型关联，则对TEXT或NONE类型关联的操作数应用NUMERIC类型关联。
  • 如果一个操作数具有TEXT类型关联，另一个操作数具有NONE类型关联，则对NONE类型关联的操作数应用TEXT类型关联。
  • 否则，不对比较运算符的两个操作数应用类型关联。

表达式“a BETWEEN b AND c”被当做两个独立的比较“a >= b AND a<= c”，即使a在两个比较中应用了不同的类型关联。表达式“x IN (SELECT y ...)”中的类型转换，被当做“x=y”处理。表达式“a IN (x,y,z,...)”等价于“a = +x OR a = +y ORa = +z OR ...”。换句话说，IN的运算符右边的数据（上例中的数据“x”，“y”和“z”）被认为没有类型关联，即使它们是列数据或CAST表达式。

3.4 Comparison Example

CREATE TABLE t1(

a TEXT,-- text affinity

b NUMERIC,-- numeric affinity

c BLOB,-- no affinity

d -- no affinity

);

-- Values will be stored as TEXT,TEXT,andINTEGER respectively

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

SELECT typeof(a),typeof(b),typeof(c),typeof(d) FROMt1;

text|integer|text|integer

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

-- right-hand side of the comparisons are converted totext before

-- the comparison occurs.

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

0|1|1

-- Text affinity is applied to the right-hand operandsbut since

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

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

0|1|1

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

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

-- the application of affinity is a no-op; no conversionsoccur. 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 theTEXT 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 withthe 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

All of the result in the example are the same if the comparisons arecommuted - if expressions of the form "a<40" are rewritten as"40>a".

4.0 Operators

所有的算术运算符（+，-，*，/，%，<<，>>，& 和 |）在计算前都会把两个操作数转化为NUMERIC存储类型。即使是有损或不可逆，这种转化也会被执行。如果算术运算符有一个NULL操作数，则运算结果为NULL。如果算术运算符的操作数不能合理的转化为数值，且不是NULL，则操作数被转化为0或0.0。

5.0 Sorting,Grouping and Compound SELECTs

当查询结果使用ORDER BY子句排序时，NULL数据排在最前面，接下来是按数值大小排序的INTERGER和REAL数据，然后是按collatingsequence顺序排序的TEXT数据，最后使用memcmp()排序的BLOB数据。在排序前不进行存储类的转化。

当使用GROUP BY子句对数据进行分组时，除了数值相等的INTEGER和REAL数据外，不同存储类型的数据被认为是不同的。在GROUP BY子句中不应用类型关联。

与SELECT配合使用的UNION，INTERSECT和EXCEPT运算符暗含对数据的比较。在这些比较中也不应用类型关联。

6.0 Collating Sequences

SQLite使用排序序列（collating sequence）或称为排序函数（collating function）比较字符串的大小。SQLite有3个内置的排序函数：BINARY，NOCASE和RTRIM。
  • BINARY：使用memcmp ()比较字符，忽略字符编码。
  • NOCASE：除了在比较前，将26个ASCII英文大写字母转换为小写外，与BINARY相同。注意，这里只对ASCII编码的大写字母进行转换。
  • RTRIM：除了忽略尾部空格外，与BINARY相同。

使用sqlite3_create_collation()接口，应用可以注册自己的排序函数。

6.1 Assigning Collating Sequences from SQL

表中每列都有一个与之关联的排序函数。如果没有明确指定，排序函数默认为BINARY。列定义中的COLLATE子句用于指定列的排序函数。

按如下顺序指定的规则，决定在二进制比较运算符（=，<，>，<=，>=，!=，IS，和IS NOT）使用哪个排序函数：
  1. 如果操作数使用COLLATE子句明确指定了排序函数，则使用明确指定的排序函数。如果两个操作数都指定了排序函数，使用左操作数的排序函数。
  2. 如果某个操作数是列名，则使用列关联的排序函数。如果两个操作数都是列名，则使用左操作列的排序函数。如果列名前有一个或多个一元操作符“+”，则认为是放弃使用此列的排序函数。
  3. 否则，使用BINARY排序函数。

如果比较运算符的操作数的子表达式中使用了COLLATE运算符，则认为此操作数明确指定了排序函数（规则1）。因此，如果比较表达式中包含了COLLATE操作符，则字符串比较时将使用此操作符指定的排序函数，而不管比较表达式中是否有列名。如果一个比较表达式中包含2个或以上COLLATE运算符子表达式，则使用最左端的COLLATE指定的排序函数，而忽略比较表达式的嵌套方式和括号。

表达式“x BETWEEN y and z”逻辑上等价于有2个比较的表达式“x >= y AND x <= z”，并且这2个比较使用各自的排序函数，就仿佛是2个独立的比较。表达式“x IN (SELECT y ...)”决定排序函数时与表达式“x = y”处理方式相同。表达式“x IN (y,...)”使用x的排序函数。

ORDER BY术语作为SELECT语句的一部分，可以使用COLLATE运算符指定一个排序函数。此时，在排序时将使用指定的排序函数。否则，如果由ORDERBY子句排序的表达式是一个列，则使用列的排序函数；如果排序表达式不是一个列，且没有COLATE子句，则使用BINARY排序函数。

6.2 Collation Sequence Examples

下面的例子确定排序函数。排序函数用于文本比较。各种SQL语句中都可能用到文本比较。注意，在数据类型是NUMERIC、BLOB或NULL时，将不使用文本比较。

CREATE TABLE t1(

x INTEGERPRIMARY KEY,

a,/* collating sequence BINARY*/

b COLLATE BINARY,/* collating sequence BINARY */

c COLLATERTRIM,/* collating sequence RTRIM */

d COLLATENOCASE /* collating sequence NOCASE */

);

/* x a bc d */

INSERT INTO t1 VALUES(1,'abc','abc ','abc');

INSERT INTO t1 VALUES(2,'ABC');

INSERT INTO t1 VALUES(3,'Abc');

INSERT INTO t1 VALUES(4,'ABC','abc');

/* Text comparison a=b is performed using the BINARYcollating sequence. */

SELECT x FROM t1 WHERE a = b ORDER BY x;

--result 1 2 3

/* Text comparison a=b is performed using the RTRIMcollating sequence. */

SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;

--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collatingsequence. */

SELECT x FROM t1 WHERE d = a ORDER BY x;

--result 1 2 3 4

/* Text comparison a=d is performed using the BINARYcollating sequence. */

SELECT x FROM t1 WHERE a = d ORDER BY x;

--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIMcollating sequence. */

SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;

--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIMcollating sequence. */

SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;

--result 1 2 3

/* Grouping is performed using the NOCASE collatingsequence (Values

** 'abc',and 'Abc' are placed in the same group).*/

SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;

--result 4

/* Grouping is performed using the BINARY collatingsequence. 'abc' and

** 'ABC' and 'Abc' form different groups */

SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;

--result 1 1 2

/* Sorting or column c is performed using the RTRIMcollating sequence. */

SELECT x FROM t1 ORDER BY c,x;

--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARYcollating sequence. */

SELECT x FROM t1 ORDER BY (c||''),x;

--result 4 2 3 1

/* Sorting of column c is performed using the NOCASEcollating sequence. */

SELECT x FROM t1 ORDER BY c COLLATE NOCASE,x;

--result 2 4 3 1

（编辑：李大同）

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

SQLite3的数据类型

大多数SQL数据库引擎（据我所知，所有除SQLite外的SQL数据库引擎）使用静态固定类型。使用静态类型，数据的类型由它的容器——即存储数据的列决定。

SQLite使用一个更通用的动态类型系统。在SQLite中，数据的类型与数据本身绑定，而不是与存储数据的列绑定。其它数据库引擎使用的静态类型系统更为普遍。在其它静态类型系统中工作的SQL语句在SQLite中以同样的方式工作，在这点上，SQLite的动态类型系统向后兼容静态类型系统。

1.0 Storage Classes andDatatypes

在SQLite数据库中存储（或由数据库引擎操作）的数据都有一个存储类（storage class）。如下是SQLite的所有存储类：
  • NULL：数据为空值。
  • INTEGER：数据为带符号整数，按数值大小存储为1、2、3、4、6或8个字节。
  • REAL：数据为浮点数，存储为8字节的IEEE浮点数。
  • TEXT：数据为文本字符串。按数据库编码（UTF-8、UTF-16BE、UTF-16LE）存储。
  • BLOB：数据为无格式二进制数据，按输入的格式存储。

注意，存储类比数据类型（datatype）更通用（general）。例如，INTEGER存储类就包含6中不同长度的数据类型。这在存储在磁盘上是不同的。一旦INTEGER数据从磁盘读到内存，它们就转换为更为通用的数据类型（8字节带符号整数）。因此大多数情况下，不区分存储类和数据类型，这两个术语可以互换使用[。

只要不是INTEGER类型的主键列（INTEGER PRIMARY KEY），任何其它的SQLite版本3的数据列，可以存储任何存储类型的数据。

SQL语句中的数值，不管是内嵌在SQL语句文本中的字面量（literals）还是绑定到预编译SQL语句中的参变量，都有一个默认的存储类。如下描述的情形中，数据库引擎会在数值存储类（INTEGER和REAL）和TEXT存储类之间转换数值。

1.1 Boolean Datatype

SQLite没有一个单独的布尔存储类。布尔值存储为整数0（false）和1（true）。

1.2 Date and Time Datatype

SQLite没有用来存储日期和时间值的存储类。替代的，SQLite的内置日期时间函数可以用来把日期时间值存储为TEXT，REAL或INTEGER数值。
  • TEXT：ISO8601字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
  • REAL：Julian日数（the number of days since noon in Greenwich on November 24,4714 B.C. according to the proleptic Gregorian calendar）。
  • INTEGER：Unix时间，自1970-01-01 00:00:00 UTC以来的秒数。

使用内置日期时间函数，应用可以选择以上任意一种格式存储日期时间值，并在以上格式间自由转换。

2.0 Type Affinity

为了使SQLite最大化的与其它数据库兼容，SQLite支持列的“类型关联（type affinity）”。列的类型关联指数据存储在该列的推荐类型。重要的思想是，类型是推荐的，而不是必须的。任何列仍然可以存储任何类型的数据。类型关联只不过是给一些列机会优先选择某些存储类。列优先选择的存储类被称为列的“关联（affinity）”。（wayz注，关联和类型关联是一回事，可以认为关联是类型关联的简称）。

SQLite 3数据库中的每列都被赋予下面的一个类型关联：

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • NONE

具有TEXT类型关联的列使用存储类NULL，TEXT或BLOB存储数据。如果数值数据插入到TEXT类型关联的列，则数据在存储前首先转换成文本格式。

具有NUMERIC类型关联的列使用所有五种存储类型存储数据。当文本数据插入到NUMERIC类型关联的列时，在无损（lossless）且可逆（reversible）的情况下，文本数据的存储类型转化为INTEGER或REAL（选择存储类时INTEGER优先于REAL）。对于TEXT和REAL存储类型的转化，如果能保留前15个有效数字，则认为转换是无损且可逆的。如果TEXT不能无损的转化为INTEGER或REAL，则使用TEXT存储类型。对于NULL或BLOB数据，不进行转化。

一个含有小数点或指数符号的字符串可能看起来像是一个浮点数，但是只要数值能表示为一个整数，NUMERIC类型关联就会把它转化为整数。因此，字符串‘3.0e+5’在存入到NUMERIC类型关联的列时，会被存储为整数300000，而不是浮点数300000.0。

具有INTEGER类型关联的列处理方式和NUMERIC类型关联的列类似。区别仅仅在于CAST表达式（CAST expression）。

除了把所有的整数类型数据强制转化为浮点数，具有REAL类型关联的列处理方式和NUMERIC类型关联的列类似。（作为内部优化，无小数部分的小浮点数，存储在REAL类型关联的列时，为了节省空间，作为整数写入磁盘，在读出时自动转化为浮点数。这一优化在SQL层完全不可见，只有在检查数据库文件的原始比特时可见。）

具有NONE类型关联的列不会选择或转化数据的存储类。

2.1 Determination Of ColumnAffinity

类型关联由列声明的数据类型决定，按如下的规则顺序决定：
  1. 如果声明类型包含字符串“INT”，则列的类型关联为INTEGER。
  2. 如果声明类型包含字符串“CHAR”、“CLOB”或“TEXT”，则类型关联为TEXT。注意，类型VARCHAR因包含字符串“CHAR”，因此列被赋予TEXT类型关联。
  3. 如果声明类型包含字符串“BLOB”或未指定列类型，则类型关联为NONE。
  4. 如果声明类型包含字符串“REAL”、“FLOA”或“DOUB”，则类型关联为REAL。
  5. 否则，类型关联为NUMURIC。

注意决定类型关联的规则的顺序是重要的。如果一个列的声明类型为“CHARINT”，则同时满足规则1和规则2，由于规则1在先，所以列的类型关联为INTEGER。

2.2 Affinity Name Examples

下表展现了传统SQL中使用的许多普通数据类型如何根据上节描述的5条规则决定类型关联。这个表只展现SQLite结束数据类型名的一个子集。注意类型名后括号括起来的数值参数（例如：“VARCHAR(255)”）将被SQLite忽略——（只要不超过宏SQLITE_MAX_LENGTH大小的限制（wayz注：这是一个非常大的数，由编译宏SQLITE_MAX_LENGTH指定））SQLite不对字符串，BLOB或数值数据的长度施加限制。
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同