SQLite Data Types
A more general dynamic type data type system is used in SQLite to represent the type of data that can be stored and processed and to specify the type of operations that can be performed. These data types are divided into various categories, where the value of a data type is not associated with its container but is associated with the value itself.
SQLite Storage Classes:
One of the five storage classes that SQLite supports is associated with each stored value in a SQLite database. These storage classes are listed below:
| STORAGE CLASS | USES |
|
NULL |
Value is a null value. |
|
INTEGER |
Value is a signed integer. Depending on the magnitude of the value, it can be stored in 1, 2, 3, 4, 6, or 8 bytes. |
|
REAL |
Value is a floating point value. Stored as an 8-byte IEEE floating point number. |
|
text |
Value is a text string. Stored using the database encoding (utf-8, utf-16be or utf-16le) |
|
BLOB |
Value is a blob of data. Stored exactly as it was input. |
SQLite Affinity Types:
The preferred storage class for a column is called its affinity. There are five types of affinity supported by SQLite3 database.
| AFFINITY | USES |
|
TEXT |
Stores all data using storage classes NULL, TEXT or BLOB. |
|
NUMERIC |
Contain values using all five storage classes. |
|
INTEGER |
Same as numeric affinity with an exception in a cast expression. |
|
REAL |
Same as numeric affinity but forces integer values into floating point representation. |
|
NONE |
No preference for one storage class and no persuasion of data from one storage class to other. |
SQLite Affinity and Type Names:
Various names for data types can also be used while creating SQLite tables. These names are grouped together and than listed as per their Affinity. Thus there are five different groups of SQLite Affinity and Type Names which are listed below.
| DATA TYPE | AFFINITY |
|
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIGINT 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:
In SQLite, Boolean values are stored as integers 0 and 1, since there is no separate storage class for Boolean values in SQLite. Here, 0 represents FALSE and 1 represents TRUE.
Date and Time Data Type:
There is no separate class in SQLite databases to store dates and times and is thus stored as TEXT, REAL or INTEGER values.
|
STORAGE CLASS |
FORMAT |
|
TEXT |
Format like “yyyy-mm-dd hh:mm:ss.sss”. |
|
REAL |
Number of days since noon in Greenwich on November 24, 4714 B.C. |
|
INTEGER |
Number of seconds since 1970-01-01 00:00:00 utc. |