YouTip

# SQLite Data Types SQLite data types are attributes used to specify the data type of any object. Every column, variable, and expression in SQLite has an associated data type. You can use these data types when creating a table. SQLite uses a more general dynamic type system. In SQLite, the data type of a value is associated with the value itself, not with its container. ## SQLite Storage Classes Every value stored in an 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 magnitude 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 exactly as it was input. | SQLite storage classes are slightly more general than data types. The INTEGER storage class, for example, includes six different integer data types of different lengths. ## SQLite Affinity Types SQLite supports the concept of column affinity. Any column can still store any type of data, but when data is inserted, the field's data will preferentially use the affinity type as its storage method. The current version of SQLite supports the following five affinity types: | Affinity Type | Description | | --- | --- | | TEXT | Numeric data is converted to text format before being inserted into the target field. | | NUMERIC | When text data is inserted into a field with NUMERIC affinity, if the conversion operation does not cause data loss and is completely reversible, SQLite will convert the text data to INTEGER or REAL type data. If the conversion fails, SQLite will still store the data as TEXT. For new data of NULL or BLOB type, SQLite will not perform any conversion and will store the data directly as NULL or BLOB. It should be noted that for floating-point format constant text, such as "30000.0", if the value can be converted to INTEGER without losing numerical information, SQLite will convert it to INTEGER storage. | | INTEGER | For fields with INTEGER affinity, the rules are the same as NUMERIC, with the only difference being when executing CAST expressions. | | REAL | The rules are basically the same as NUMERIC, with the only difference being that text data like "30000.0" will not be converted to INTEGER storage. | | NONE | No conversion is performed; the data is stored directly in its original data type. | ## SQLite Affinity and Type Names The following table lists the various data type names that can be used when creating SQLite3 tables, along with their corresponding affinity types: | Data Type | Affinity Type | | --- | --- | | * 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 * Unspecified Type | BLOB | | * REAL * DOUBLE * DOUBLE PRECISION * FLOAT | REAL | | * NUMERIC * DECIMAL(10,5) * BOOLEAN * DATE * DATETIME | NUMERIC | ## Boolean Data Type SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). ## Date and Time Data Type 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. | Storage Class | Date Format | | --- | --- | | TEXT | A date in the format "YYYY-MM-DD HH:MM:SS.SSS". | | REAL | The number of days since noon in Greenwich on November 24, 4714 BC. | | INTEGER | 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 can use the built-in date and time functions to freely convert between different formats.