Data types in SQLite 3

SQLite uses the dynamic type system in SQLite, where the data type of the value and the value itself, rather than its container, are associated. SQLite's dynamic type system is compatible with the static type system of other database engines, so that SQL statements executed on statically typed databases can also be executed in SQLite.

1.0 storage classes and data types

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

• Null. Null value.
• INTEGER. Signed integers, stored in 1, 2, 3, 4, 6, or 8 bytes.
• REAL. Floating-point number, which is stored as a 8-byte IEEE floating-point number.
• TEXT. A text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).
• Blob. Chunk data.

Note that the storage class (storage Class) is more general than the data type. An integer storage class, for example, contains 6 different lengths of integer data types. This is different on the disk. However, once the integer value is processed from disk to content, these values are converted to more general data types (8-bit signed integers). Therefore, in most cases, storage classes and data types are hard to distinguish, and these two terms can be exchanged for use.

In the SQLite 3 database, any column, except the integer PRIMARY key column, can store the value of any storage class.

All the values in the SQL statement, whether they are embedded in the SQL statement or parameters in a precompiled SQL statement, have an implicit storage class. Under the conditions described below, during the query execution phase, the database engine may convert between the numeric storage class (integer and Real) and the text storage class.

1.1 Boolean data type

SQLite does not have a separate Boolean storage class, instead, the Booean value is stored in integers 0 (false) and 1 (true).

1.2 Date and Time data types

SQLite does not have a dedicated storage class for storing dates and/or times, instead, built-in date and time functions can store dates and times as text,real or integer values:

• TEXT: As a ISO8601 string ("Yyyy-mm-dd HH:MM:SS. SSS ").
• REAL: As Julian days, ...
• INTEGER: The number of seconds that are the Unix time, which is from 1970-01-01 00:00:00 UTC.

2.0 Type Similarity (type affinity)

To maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type similarity" for columns. The important idea here is that the type is recommended, not required. Any column can still store any type of data. Just some columns, you can choose to prioritize using some kind of storage class. The priority storage class for this column is called its "similarity".

Each column in the SQLite 3 database is given one of the following types:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

Columns with a text-like type use the storage class null, text, or blob to store all the data. If the data data is inserted into a column that resembles text, it is converted to text format before inserting.

Columns that resemble numeric can use all 5 storage classes to contain values. When text data is inserted into a numeric column, the stored class of the text is converted to integer or real (in order of precedence) if the conversion is lossless and reversible. If the text-to-integer or real conversion is not possible, then the value is stored using the text storage class. Does not attempt to convert null or BLOB values.

......

2.1 Rows of similar determination

Similar columns are determined by the types declared by the columns, and the rules are in the following order:

1. If the declared type contains the string "INT" then it is given an integer resemblance.

2. If the type of the column declaration contains any string "CHAR", "CLOB", or "text", then this column has the text similarity. Note that the type varchar contains "CHAR", so it is also given a similar text.

3. If a column declares a type that contains "BLOB" or does not have a specified type, then this column has none similar.

4. If the type of the column declaration contains any "real", "Floa", or "Doub", then this column has a real resemblance.

5. In other cases, the resemblance is numeric.

It is important to note the order of the rules. A column with the claim type "charint" matches both Rule 1 and Rule 2, but the first rule takes precedence, so the column is similar to integer.

2.2 Similar examples

Example	Similar results	Rules
Int INTEGER TINYINT SMALLINT Mediumint BIGINT UNSIGNED BIG INT INT2 INT8	Inetger	1
CHARACTER (20) VARCHAR (255) VARYING CHARACTER (255) NCHAR (55) NATIVE CHARACTER (70) NVARCHAR (100) TEXT Clob	TEXT	2
Blob No data type specified	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL (10, 5) BOOLEAN DATE Datetime	NUMERIC	5

Note that the "floating point" type specifies an integer similarity, rather than real, because "INT" is at the end. The "STRING" type will have numeric resemblance, not text.

Data types in SQLite 3