SQLite tutorial (7): Detailed explanation of data types

1. Storage type and data type:

SQLite divides the storage of data values ​​into the following storage types:
 

Since SQLite uses dynamic data types, while other traditional relational databases use static data types, that is, the data types that fields can store are determined when the table is declared, there are still big differences in data storage between them. In SQLite, there are certain differences in storage classification and data types. For example, the INTEGER storage category can contain 6 Integer data types of different lengths. However, once these INTEGER data are read into memory, SQLite will treat them all as occupies 8 bytes of unsigned integers. Therefore, for SQLite, even if the field type is explicit in the table declaration, we can still store other types of data in that field. However, it should be noted that although SQLite provides us with this convenience, once we take into account the portability of the database platform, we should ensure the consistency of data types storage and declarations as much as possible in actual development. Unless you have extremely good reasons and no longer consider the migration of database platforms, you can indeed use this feature provided by SQLite in this case.

1. Boolean data type:

SQLite does not provide a special boolean storage type, instead the storage integer 1 means true and 0 means false.

2. Date and time data types:

Like Boolean types, SQLite also does not provide a special date and time storage type, but represents the type in different formats of TEXT, REAL and INTEGER types, such as:
 

2. Type of kinship:

In order to maximize data type compatibility between SQLite and other database engines, SQLite proposed the concept of "Type Affinity". We can understand "type affinity" in this way. After the table field is declared, SQLite will select a relative type for it based on the type declared by the field. When data is inserted, the data in the field will preferentially use the relative type as the storage method of the value, unless the relative type does not match or the current data cannot be converted to the relative type, SQLite will consider other types that are more suitable for the value to store the value. The current version of SQLite supports the following five types of relativity:

Relationship Type	describe
TEXT	Before numerical data is inserted, it needs to be converted to text format first, and then inserted into the target field.
NUMERIC	When text data is inserted into a field with affinity of NUMERIC, if the conversion operation does not cause data information to be lost 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 in TEXT mode. For new data of NULL or BLOB type, SQLite will not do any conversions and store the data directly in NULL or BLOB. It should be noted that for constant text in floating point format, such as "30000.0", if the value can be converted to INTEGER without losing numerical information, SQLite will convert it to INTEGER storage method.
INTEGER	For fields with affinity type INTEGER, the rules are equivalent to NUMERIC, the only difference is when executing a CAST expression.
REAL	The rules are basically equivalent to NUMERIC. The only difference is that text data such as "30000.0" will not be converted into INTEGER storage method.
NONE	No conversion is done, and it is stored directly in the data type to which the data belongs.

1. Rules for determining the affinity of a field:

The affinity of a field is determined based on the type defined by the field when declaring. For specific rules, please refer to the following list. It should be noted that the following list is the order of the list, that is, if a certain field type meets both affinities at the same time, the rules that are ahead will have an effect first.
1). If the type string contains "INT", then the relative type of the field is INTEGER.
2). If the type string contains "CHAR", "CLOB" or "TEXT", then the relative type of this field is TEXT, such as VARCHAR.
3). If the type string contains "BLOB", then the relative type of this field is NONE.
4). If the type string contains "REAL", "FLOA" or "DOUB", then the relative type of this field is REAL.
5). In other cases, the relative type of the field is NUMERIC.

2. Specific examples:

Declaration type	Relationship Type	Application rules
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

Note: In SQLite, the length information 255 of type VARCHAR(255) has no practical significance and is only to ensure the declaration consistency with other databases.

3. Comparison expressions:

The comparison expressions supported in SQLite3 are: "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS" and "IS NOT".
The comparison results of data mainly depend on the storage method of operands, and the rules are:
1). The value of the storage method NULL is smaller than the value of other storage types.
2). The numerical values ​​of INTEGER and REAL are less than those of TEXT or BLOB type. If they are both INTEGER or REAL, they are compared based on the numerical rules.
3). The storage method is TEXT value is less than the BLOB type value. If both are TEXT, it is compared based on text rules (ASCII value).
4). If two BLOB types are compared, the result is the result of the C runtime function memcmp().

4. Operator:

All mathematical operators (+, -, *, /, %, <<, >>, &, and |) will convert the operands to NUMERIC storage type before execution, even if data information may be lost during the conversion process. Furthermore, if one of the operands is NULL, then their results are also NULL. In mathematical operators, if one of the operands does not look like a numeric type, then they result in 0 or 0.0.