The SQLite data type is a property that specifies the data type of any object. Every column, variable, and expression in SQLite has an associated data type.
You can use these data types while creating tables. SQLite uses a more general dynamic type system. In SQLite, the data type of a value is related to the value itself, not to its container. Each value stored in the SQLite database has one of the following storage classes: Storage class Description NULL The value is a NULL value. INTEGER A value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value. REAL Value is a floating-point value stored as an 8-byte IEEE floating-point number. TEXT The value is a text string stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE). BLOB The value is an blob data that is stored entirely according to its input. SQLite’s storage classes are slightly more common than data types. The INTEGER storage class, for example, contains six different integer data types of different lengths. SQLite supports the concept of affinity types for columns. Any column can still store any type of data, and when the data is inserted, the data in the field will give priority to the kinship type as the way to store the value. The current version of SQLite supports the following five kinship types: Affinity type Description TEXT Before being inserted, numeric data needs to be converted to text format and then inserted into the target field. NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如”30000.0”，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。 INTEGER For fields of kinship type INTEGER, the rule is the same as NUMERIC, the only difference is when the CAST expression is executed. REAL 其规则基本等同于NUMERIC，唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式。 NONE Without any conversion, the data is directly stored as the data type to which the data belongs. The following table lists the various data type names that can be used when creating SQLite3 tables, as well as the 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 No datatype specified NONE REAL DOUBLE DOUBLE PRECISION FLOAT REAL NUMERIC DECIMAL (10dint 5) BOOLEAN DATE DATETIME NUMERIC SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). 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 class Date format TEXT A date in the format “YYYY-MM-DD HH:MM:SS.SSS”. REAL The number of days beginning at noon GMT on November 24, 4714 BC. INTEGER The number of seconds from-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 convert different formats by. 1.6.1. SQLite storage class ¶
1.6.2. SQLite affinity (Affinity) type ¶
1.6.3. SQLite affinity type (Affinity) and type name ¶
1.6.4. Boolean data type ¶
1.6.5. Date and Time data types ¶