SQLite tutorial (7): Data Type Details, sqlite Data Type

SQLite tutorial (7): Data Type Details, sqlite Data Type

I. Storage types and data types:

SQLite divides data values into the following Storage types:
 Copy codeThe Code is as follows:
NULL: The value is NULL.
INTEGER: unsigned INTEGER value.
REAL: floating point value.
TEXT: TEXT string that stores the encoding method used for UTF-8, UTF-16BE, UTF-16LE.
BLOB: stores Blob data, which is exactly the same as input data.

Since SQLite uses dynamic data types, other traditional relational databases use static data types, that is, the data types that can be stored by fields are determined when the table is declared, therefore, there is a big difference between them in terms of data storage. In SQLite, storage classes and data types are also different. For example, an INTEGER storage class can contain six Integer Data Types of different lengths, however, once the INTEGER data is read into the memory, SQLite will regard it as occupying 8 bytes of unsigned INTEGER. Therefore, for SQLite, even if the field type is specified in the Table Declaration, we can still store other types of data in this field. However, we need to note that, although SQLite provides us with this convenience, once we consider the portability of the database platform, in actual development, we should try our best to ensure consistency of data type storage and declarations. Unless you have a very good reason and no longer consider porting the database platform, you can 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 indicates true, and 0 indicates false.

2. Date and Time data types:

Like the boolean type, SQLite does not provide a special date and time storage type, but it is represented in different formats of the TEXT, REAL, and INTEGER types, such:
 Copy codeThe Code is as follows:
TEXT: "YYYY-MM-DD HH: MM: SS. SSS"
REAL: stored in Julian Date Format
INTEGER: the number of seconds that the data value flows from 00:00:00 to the current time.
 

Ii. Type affinity:

To maximize the compatibility between SQLite and other database engines, SQLite proposes the concept of "Type Affinity. We can understand "type affinity" in this way. After a table field is declared, SQLite selects a kinship type based on the type declared for this field. When data is inserted, the data of this field will take the kinship type as the storage method of this value first, unless the kinship type does not match or the current data cannot be converted to this kinship type, in this way, SQLite will consider other types that are more suitable for storing this value. Currently, SQLite supports the following five kinship types:

Kinship type	Description
TEXT	Before the numeric data is inserted, it must be converted to the text format before being inserted into the target field.
NUMERIC	When text data is inserted into a field with the affinity of NUMERIC, if the conversion operation does not cause data loss or completely reversible, SQLite converts the TEXT data to INTEGER or REAL data. If the conversion fails, SQLite will still store the data in TEXT format. For new data of the NULL or BLOB type, SQLite will not perform any conversion and store the data directly in the NULL or BLOB mode. It should be noted that for constant text in floating-point format, such as "30000.0", if the value can be converted to an INTEGER without losing the value information, then SQLite converts it to an INTEGER storage method.
INTEGER	For fields with an INTEGER kinship, the rule is equivalent to NUMERIC. The only difference is that when the CAST expression is executed.
REAL	The rule is basically equivalent to NUMERIC. The only difference is that text data such as "30000.0" is not converted into INTEGER storage.
NONE	Without any conversion, the data type to which the data belongs is stored directly.

1. rules for determining field affinity:

The affinity of a field is determined by the type defined when the field is declared. For specific rules, refer to the following list. Note that the order of the following list is that if a field type meets both affinity, the rule that comes first takes effect.
1). If the type string contains "INT", the kinship type of this field is INTEGER.
2) If the type string contains "CHAR", "CLOB", or "TEXT", the kinship type of this field is TEXT, such as VARCHAR.
3) if the type string contains "BLOB", the kinship type of this field is NONE.
4). If the type string contains "REAL", "FLOA", or "DOUB", the kinship type of this field is REAL.
5) in other cases, the kinship type of the field is NUMERIC.

2. Example:

Declaration type	Kinship type	Application Rules
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER (20) VARCHAR (255) Varying character (0, 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 of Type VARCHAR (255) 255 does not have any practical significance, just to ensure the Declaration consistency with other databases.

3. Comparison expression:

The supported comparison expressions in SQLite3 are: "=", "=", "<", "<=", ">", "> = ","! = "," <> "," IN "," not in "," BETWEEN "," IS "and" is not ".
The comparison result mainly depends on the Storage Method of the operands. The rule is as follows:
1) The value of the storage method NULL is smaller than the value of other storage types.
2) the storage method is that the values of INTEGER and REAL are smaller than the values of the TEXT or BLOB type. If they are both INTEGER or REAL, the comparison is based on the numerical rules.
3). The storage method is that the value of TEXT is smaller than the value of BLOB type. If it is the same as TEXT, it is compared based on TEXT rules (ASCII value.
4) if two BLOB-type values are compared, the result is the result of the memcmp () function in the C runtime.

Iv. operators:

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