Latest web development tutorials

SQLite data types

SQLite is a data type used to specify properties of any object's data type. SQLite each column, the data type of each variable and expression are related.

You can use these data types at the same time create the table. SQLite uses a more general dynamic type system. In SQLite, the value of the data types and values ​​themselves are related, but not with its associated container.

SQLite storage class

Each value is stored in a SQLite database storage class you have one of the following:

存储类描述
NULL值是一个 NULL 值。
INTEGER值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。
TEXT值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。
BLOB值是一个 blob 数据,完全根据它的输入存储。

SQLite storage class is slightly more common than data type. INTEGER storage class, for example, includes 6 different integer datatypes of different lengths.

SQLite Affinity Type

SQLite supports the concept of thetype of affinitycolumns. Any column can still store any type of data, but the preferred storage class column is itsaffinity.In SQLite3 database column of each table is assigned to one of the following types of affinity:

Affinity描述
TEXT该列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。
NUMERIC该列可以包含使用所有五个存储类的值。
INTEGER与带有 NUMERIC affinity 的列相同,在 CAST 表达式中带有异常。
REAL与带有 NUMERIC affinity 的列相似,不同的是,它会强制把整数值转换为浮点表示。
NONE带有 affinity NONE 的列,不会优先使用哪个存储类,也不会尝试把数据从一个存储类强制转换为另一个存储类。

SQLite Affinity name and type

The following table lists the various data type name when creating SQLite3 table can be used, but also shows the corresponding application Affinity:

数据类型Affinity
  • INT

  • INTEGER

  • TINYINT

  • SMALLINT

  • MEDIUMINT

  • BIGINT

  • UNSIGNED BIG INT

  • INT2

  • INT8

INTEGER
  • CHARACTER(20)

  • VARCHAR(255)

  • VARYING CHARACTER(255)

  • NCHAR(55)

  • NATIVE CHARACTER(70)

  • NVARCHAR(100)

  • TEXT

  • CLOB

TEXT
  • BLOB

  • no datatype specified

NONE
  • REAL

  • DOUBLE

  • DOUBLE PRECISION

  • FLOAT

REAL
  • NUMERIC

  • DECIMAL(10,5)

  • BOOLEAN

  • DATE

  • DATETIME

NUMERIC

Boolean data type

SQLite does not separate Boolean storage class. On the contrary, the Boolean value is stored as an integer of 0 (false) and 1 (true).

Date and Time Data Types

SQLite does not have a separate storage for the date and / or time of storage classes, SQLite but the date and time can be stored as TEXT, REAL or INTEGER value.

存储类日期格式
TEXT格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。
REAL从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER从 1970-01-01 00:00:00 UTC 算起的秒数。

You can order any of the above format to store the date and time, and you can use the built-in date and time functions to free convert different formats.