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.
大多数 SQL 数据库引擎 （据我们所知，除 SQLite 之外的所有 SQL 数据库引擎）都使用严格的静态类型。使用静态类型，值的类型便由它的容器 – 存储值的特定的列 – 来决定。

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 statements 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.
SQLite 使用更通用的动态类型系统。在 SQLit 中，值的数据类型与值本身相关，而不是与它的容器。SQLite 的动态类型系统与其它数据库引擎的常用静态类型系统是向后兼容的，在这个意义上，工作在静态类型数据库上的 SQL 语句应该以同样的方式工作在 SQLite 中。然而，SQLite 中的动态类型允许它做传统的严格类型的数据库所不能做的事。

1.0 Storage Classes and Datatypes

1.0 存储类型与数据类型

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
存储在 SQLite 数据库中的每个值（或是由数据库引擎所操作的值）都有一个以下的存储类型：

NULL. The value is a NULL value.
NULL. 值是空值。

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
INTEGER. 值是有符号整数，根据值的大小以1，2，3，4，6 或8字节存储。

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
REAL. 值是浮点数，以8字节 IEEE 浮点数存储。

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
TEXT. 值是文本字符串，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE）进行存储。

BLOB. The value is a blob of data, stored exactly as it was input.
BLOB. 值是一个数据块，按它的输入原样存储。

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.
注意，存储类型比数据类型更笼统。以 INTEGER 存储类型为例，它包括6种不同的长度不等的整数类型，这在磁盘上是不同的。但是只要 INTEGER 值从磁盘读取到内存进行处理，它们就被转换为更为一般的数据类型（8字节有符号整型）。因此在一般情况下，“存储类型” 与 “数据类型” 没什么差别，这两个术语可以互换使用。

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.
SQLite 版本3数据库中的任何列，除了整型主键列，都可用于存储任何存储类型的值。

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.
SQL 语句中的任何值，无论它们是嵌入到 SQL 语句中的字面量还是绑定到预编译 SQL 语句中的参数，都有一个隐含的存储类型。在下述情况下，数据库引擎会在执行查询时在数值存储类型（INTEGER 和 REAL）和 TEXT 之间进行转换。