SQLite data type

SQLite Storage Classes

Every value stored in a SQLite database has one of the following storage classes:

Storage Class	Description
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 size 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 entirely based on its input.

#SQLite's storage classes are slightly more general than data types. The INTEGER storage class, for example, contains 6 different integer data types of varying lengths.

SQLite Affinity Types

SQLite supports the type affinity concept on columns. Any column can still store any type of data, but the column's preferred storage class is its affinity. In a SQLite3 database, each table's columns are assigned one of the following types of affinity:

Affinity	Description
TEXT	This column uses the storage class NULL, TEXT or BLOB to store all data.
NUMERIC	This column can contain values ​​using all five storage classes.
INTEGER	Same as column with NUMERIC affinity, with exception in CAST expression.
REAL	Similar to columns with NUMERIC affinity, except that it forces integer values ​​to be converted to floating point representation.
NONE	Columns with affinity NONE will not prioritize which storage class to use, nor will they try to force data from one storage class to another. kind.

SQLite Affinity and type names

The following table lists the various data type names that can be used when creating SQLite3 tables, and also shows the corresponding application Affinity:

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

Data Type	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

Date and Time data types

SQLite does not have a separate storage class for storing dates and/or times, but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Storage classDate formatThe format is "YYYY-MM -DD ​​HH:MM:SS.SSS" date. The number of days starting at noon GMT on November 24, 4714 BC. The number of seconds since 1970-01-01 00:00:00 UTC. You can store dates and times in any of the above formats, and you can use the built-in date and time functions to freely convert between different formats.

	TEXT
	REAL
	INTEGER