Sqlite 简明教程
SQLite - Data Type
SQLite 数据类型是一种属性，用于指定任何对象的类型。SQLite 中的每一列、变量和表达式都具有相关的数据类型。
SQLite data type is an attribute that specifies the type of data of any object. Each column, variable and expression has related data type in SQLite.
在创建表时会使用这些数据类型。SQLite 使用一个更通用的动态类型系统。在 SQLite 中，一个值的数据类型与其自身关联，而不仅仅与其容器相关联。
You would use these data types while creating your tables. 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.
SQLite Storage Classes
存储在 SQLite 数据库中的每个值都具有以下存储类之一 −
Each value stored in an SQLite database has one of the following storage classes −
Sr.No. |
Storage Class & Description |
1 |
NULL The value is a NULL value. |
2 |
INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
3 |
REAL The value is a floating point value, stored as an 8-byte IEEE floating point number. |
4 |
TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) |
5 |
BLOB The value is a blob of data, stored exactly as it was input. |
SQLite 存储类比数据类型要略为更通用。例如，INTEGER 存储类包括6种不同长度的不同整数数据类型。
SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths.
SQLite Affinity Type
SQLite 支持{@s1}在列上的概念。任何列仍然可以存储任何类型的数据，但是列的首选存储类被称作其{@s2}。SQLite3 数据库中的每一张表都被分配以下类型关联中的一个 −
SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities −
Sr.No. |
Affinity & Description |
1 |
TEXT This column stores all data using storage classes NULL, TEXT or BLOB. |
2 |
NUMERIC This column may contain values using all five storage classes. |
3 |
INTEGER Behaves the same as a column with NUMERIC affinity, with an exception in a CAST expression. |
4 |
REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. |
5 |
NONE 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. |
SQLite Affinity and Type Names
下表列出了在使用相应的应用关联创建 SQLite3 时可用于创建 SQLite3 数据库的各种数据类型名称。
Following table lists down various data type names which can be used while creating SQLite3 tables with the corresponding applied affinity.
Data Type |
Affinity |
INTINTEGERTINYINTSMALLINTMEDIUMINTBIGINTUNSIGNED BIG INTINT2INT8 |
INTEGER |
CHARACTER(20)VARCHAR(255)VARYING CHARACTER(255)NCHAR(55)NATIVE CHARACTER(70)NVARCHAR(100)TEXTCLOB |
TEXT |
BLOBno datatype specified |
NONE |
REALDOUBLEDOUBLE PRECISIONFLOAT |
REAL |
NUMERICDECIMAL(10,5)BOOLEANDATEDATETIME |
NUMERIC |
Boolean Datatype
SQLite 没有单独的布尔存储类。相反，布尔值被存储为整数 0（假）和 1（真）。
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
Date and Time Datatype
SQLite 没有用于存储日期和/或时间的单独存储类，但 SQLite 能够将日期和时间作为 TEXT、REAL 或 INTEGER 值进行存储。
SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values.
Sr.No. |
Storage Class & Date Formate |
1 |
TEXT A date in a format like "YYYY-MM-DD HH:MM:SS.SSS" |
2 |
REAL The number of days since noon in Greenwich on November 24, 4714 B.C. |
3 |
INTEGER The number of seconds since 1970-01-01 00:00:00 UTC |
你可以选择使用其中任何格式来存储日期和时间，并使用内置日期和时间函数在格式之间自由转换。
You can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.