sqlite3中的数据类型

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

1.0 Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

• NULL. The value is a NULL value.

• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

• BLOB. The value is a blob of data, stored exactly as it was input.

Note that a storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

2.0 Type Affinity

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

Each column in an SQLite 3 database is assigned one of the following type affinities:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)

A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

2.1 Determination Of Column Affinity

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.

2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.

4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

5. Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.

2.2 Affinity Name Examples

The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

Example Typenames From The
CREATE TABLE Statement
or CAST ExpressionResulting AffinityRule Used To Determine AffinityINT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8INTEGER1CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOBTEXT2BLOB
no datatype specifiedNONE3REAL
DOUBLE
DOUBLE PRECISION
FLOATREAL4NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIMENUMERIC5

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

2.3 Column Affinity Behavior Example

The following SQL demonstrates how SQLite uses column affinity to do type conversions when values are inserted into a table.

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);-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.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-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.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-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.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-- BLOBs are always stored as BLOBs regardless of column affinity.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 are also unaffected by affinityDELETE 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 Comparison Expressions

SQLite version 3 has the usual set of SQL comparison operators including "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .

3.1 Sort Order

The results of a comparison depend on the storage classes of the operands, according to the following rules:

• A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

• An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

• A TEXT value is less than a BLOB value. When two TEXT values are compared an appropriate collating sequence is used to determine the result.

• When two BLOB values are compared, the result is determined using memcmp().

3.2 Affinity Of Comparison Operands

SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the affinity of the operands. Operand affinity is determined by the following rules:

• An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity.

• An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

• Otherwise, an expression has NONE affinity.

3.3 Type Conversions Prior To Comparison

To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion is lossless and reversible. Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:

• If one operand has INTEGER, REAL or NUMERIC affinity and the other operand as TEXT or NONE affinity then NUMERIC affinity is applied to other operand.

• If one operand has TEXT affinity and the other has NONE affinity, then TEXT affinity is applied to the other operand.

• Otherwise, no affinity is applied and both operands are compared as is.

The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons. Datatype conversions in comparisons of the form "x IN (SELECT y ...)" are handled is if the comparison were really "x=y". The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...". In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions.

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, INTEGER, TEXT, and INTEGER respectivelyINSERT 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

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

4.0 Operators

All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

5.0 Sorting, Grouping and Compound SELECTs

When query results are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values in collating sequence order, and finally BLOB values in memcmp() order. No storage class conversions occur before the sort.

When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP by clause.

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. No affinity is applied to comparison operands for the implicit comparisons associated with UNION, INTERSECT, or EXCEPT - the values are compared as is.

6.0 Collating Sequences

When SQLite compares two strings, it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.

• BINARY - Compares string data using memcmp(), regardless of text encoding.
• NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.
• RTRIM - The same as binary, except that trailing space characters are ignored.

An application can register additional collating functions using the sqlite3_create_collation() interface.

6.1 Assigning Collating Sequences from SQL

Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column.

The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows and in the order shown:

1. If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.

2. If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators is still considered a column name.

3. Otherwise, the BINARY collating function is used for comparison.

An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfixCOLLATE operator. Thus, if a COLLATE operator is used anywhere in a comparision expression, the collating function defined by that operator is used for string comparison regardless of what table columns might be a part of that expression. If two or more COLLATE operator subexpressions appear anywhere in a comparison, the left most explicit collating function is used regardless of how deeply the COLLATE operators are nested in the expression and regardless of how the expression is parenthesized.

The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons. The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collating sequence. The collating sequence used for expressions of the form "x IN (y, z, ...)" is the collating sequence of x.

Terms of the ORDER BY clause that is part of a SELECT statement may be assigned a collating sequence using the COLLATE operator, in which case the specified collating function is used for sorting. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order. If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used.

6.2 Collation Sequence Examples

The examples below identify the collating sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collating sequence used, in the case of numeric, blob or NULL values.

CREATE TABLE t1(    x INTEGER PRIMARY KEY,    a,                 /* collating sequence BINARY */    b COLLATE BINARY,  /* collating sequence BINARY */    c COLLATE RTRIM,   /* collating sequence RTRIM  */    d COLLATE NOCASE   /* collating sequence NOCASE */);                   /* x   a     b     c       d */INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc'); /* Text comparison a=b is performed using the BINARY collating sequence. */SELECT x FROM t1 WHERE a = b ORDER BY x;--result 1 2 3/* Text comparison a=b is performed using the RTRIM collating 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 collating sequence. */SELECT x FROM t1 WHERE d = a ORDER BY x;--result 1 2 3 4/* Text comparison a=d is performed using the BINARY collating sequence. */SELECT x FROM t1 WHERE a = d ORDER BY x;--result 1 4/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;--result 1 2 3/* Text comparison c='abc' is performed using the RTRIM collating sequence. */SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;--result 1 2 3/* Grouping is performed using the NOCASE collating sequence (Values** 'abc', '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 collating sequence.  '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 RTRIM collating sequence. */SELECT x FROM t1 ORDER BY c, x;--result 4 1 2 3/* Sorting of (c||'') is performed using the BINARY collating sequence. */SELECT x FROM t1 ORDER BY (c||''), x;--result 4 2 3 1/* Sorting of column c is performed using the NOCASE collating sequence. */SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;--result 2 4 3 1

SQLite使用动态类型系统，在SQLite中，值的数据类型和值本身，而不是和它的容器，关联在一起的。SQLite的动态类型系统和其他数据库引擎的静态类型系统是兼容的，这样在静态类型的数据库上执行的SQL语句也可以在SQLite中执行。
1.0 存储类和数据类型
每个存储在SQLite数据库中（或被数据库引擎操纵的）的值都有下列存储类的一个：
NULL。空值。
INTEGER。有符号整数，存储在1、2、3、4、6或8个字节中。
REAL。浮点数，存储为8字节的IEEE浮点数。
TEXT。文本串，使用数据库编码（UTF-8, UTF-16BE或UTF-16LE）存储。
BLOB。大块数据。
注意存储类（storage class）比数据类型更一般。INTEGER存储类，例如，包含6种长度不同的整数数据类型。这在磁盘中是有区别的。不过一旦INTEGER值从磁盘读到内容中进行处理的时候，这些值会转化为更普通的数据类型（8位有符号整数）。因此在大部分情况下，存储类和数据类型是不易分辨的，这两个术语可以交换使用。
在SQLite 3 数据库中，任何列，除了INTEGER PRIMARY KEY列，都可以存储任何存储类的值。
SQL语句中的所有值，不管是SQL语句中嵌入的字面值，还是预编译的SQL语句中的参数，都有一个隐式的存储类。在下面描述的条件下，在查询执行阶段，数据库引擎可能会在数字存储类（INTEGER和REAL）和TEXT存储类之间转换。
1.1 Boolean数据类型
SQLite没有单独的Boolean存储类，相反，Booean值以整数0（false）和1（true）存储。
1.2 日期和时间数据类型
SQLite没有为存储日期和/或时间设置专门的存储类，相反，内置的日期和时间函数能够把日期和时间作为TEXT，REAL或INTEGER值存储：
TEXT：作为ISO8601字符串（"YYYY-MM-DD HH:MM:SS.SSS"）。
REAL：作为Julian天数，……
INTEGER：作为Unix Time，即自1970-01-01 00:00:00 UTC以下的秒数。
2.0 类型相像（type affinity）
为了最大化SQLite和其他数据库引擎之间的兼容性，SQLite支持列的”类型相像“的概念。这里重要的思想是，类型是推荐的，不是必需的。任何列仍然能够存储任何类型的数据。只是某些列，能够选择优先使用某种存储类。这个对列的优先存储类称作它的”相像“。
SQLite 3 数据库中的每个列都赋予下面类型相像中的一个：
TEXT
NUMERIC
INTEGER
REAL
NONE
带有TEXT相像的列会使用存储类NULL、TEXT或BLOB来存储所有的数据。如果数据数据被插入到带有TEXT相像的列中，它会在插入前转换为文本格式。
带有NUMERIC相像的列可以使用所有5个存储类来包含值。当文本数据被插入到一个NUMERIC列，文本的存储类会被转换成INTEGER或REAL（为了优先），如果这个转换是无损的和可逆的话。如果TEXT到INTEGER或REAL的转换是不可能的，那么值会使用TEXT存储类存储。不会试图转换NULL或BLOB值。
……
2.1 列相像的确定
列相像是由列声明的类型确定的，规则是按照下面的顺序：
1. 如果声明的类型包含字符串”INT“那么它被赋予INTEGER相像。
2. 如果列声明的类型包含任何字符串”CHAR“，”CLOB“，或”TEXT“，那么此列拥有TEXT相像。注意类型VARCHAR包含”CHAR“，因此也会赋予TEXT相像。
3. 如果列声明的类型包含”BLOB“或没有指定类型，那么此列拥有NONE相像。
4. 如果列声明的类型包含任何”REAL“，”FLOA“，或”DOUB“，那么此列拥有REAL相像。
5. 其他情况，相像是NUMERIC。
注意规则的顺序是重要的。声明类型为“CHARINT”的列同时匹配规则1和规则2，但第一个规则会优先采用，因此此列的相像是INTEGER。
2.2 相像示例
示例相像结果规则INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8INETGER1CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOBTEXT2BLOB 未指定数据类型NONE3REAL DOUBLE DOUBLE PRECISION FLOATREAL4NUMERIC DECIMAL(10, 5) BOOLEAN DATE DATETIMENUMERIC5
注意“FLOATING POINT”类型会指定INTEGER相像，而不是REAL相像，因为”INT“在末尾。”STRING“类型会拥有NUMERIC相像，而不是TEXT。