SQLite Tutorial (vii): detailed data type _postgresql

First, storage types and data types:

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

Copy Code code as follows:

Null: Indicates that the value is a null value.
Integer: unsigned integer value.
Real: Floating-point value.
Text: A literal string that the encoding used for storage is UTF-8, Utf-16be, Utf-16le.
Blob: Stores blob data that is exactly the same type of data as the input data.

Because SQLite uses dynamic data types, and other traditional relational databases use static data types, where the data types that a field can store are determined when the table is declared, there is a significant difference in data storage between them. In SQLite, there are also differences in storage classification and data types, such as the integer storage category can contain 6 different lengths of the integer data type, however, once the integer data is read into memory, SQLite will consider all of them to occupy a 8-byte unsigned integer. So for SQLite, even if the field type is explicitly defined in the table declaration, we can still store other types of data in that field. However, it is particularly useful to note that, although SQLite provides us with this convenience, once the portability of the database platform is taken into account, we should try to ensure the storage and declaration consistency of the data types as much as possible in actual development. Unless you have a very good reason, and you no longer consider the problem of porting the database platform, you can actually use this feature provided by SQLite in such a case.

1. Boolean Data type:

SQLite does not provide a dedicated Boolean storage type, instead storing integer 1 indicates that true,0 is false.

2. Date and time data type:

As with a Boolean type, SQLite does not provide a dedicated date-time storage type, but rather a different format for text, real, and integer types, such as:

Copy Code code as follows:

TEXT: "Yyyy-mm-dd HH:MM:SS. SSS "
Real: Storing in Julian date format
INTEGER: Saves the data value in Unix time form, that is, the number of seconds that flow through from 1970-01-01 00:00:00 to the current time.

Second, the type of affinity:

To maximize data type compatibility between SQLite and other database engines, SQLite proposes the concept of "type affinity Affinity". We can understand "type affinity" so that after a table field is declared, SQLite chooses a relative type for it based on the type of the fields declaration, and when the data is inserted, the data for that field takes precedence over the affinity type as the storage for that value, Unless the affinity type does not match or the current data cannot be converted to the affinity type, then SQLite considers other types that are more appropriate for the value to store the value. The current version of SQLite supports the following five types of affinity:

Affinity type	Describe
TEXT	Numeric data needs to be converted to text format before being inserted into the target field before being inserted.
NUMERIC	When text data is inserted into a numeric field, if the conversion does not result in loss of data information and is completely reversible, then SQLite converts the text data to an integer or real type of data, and if the conversion fails, SQLite will still store the data in text format. For new data of a null or BLOB type, SQLite will not make any conversions and store the data directly as a null or BLOB. It is also necessary to note 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 the integer storage mode.
INTEGER	For fields whose affinity type is integer, their rules are equivalent to numeric, and the only difference is when the cast expression is executed.
Real	Its rules are essentially equivalent to numeric, and the only difference is that text data such as "30000.0" is not converted to an integer storage method.
NONE	Without making any conversions, it is stored directly to the data type to which the data belongs.

1. Rules for determining field affinity:

The affinity of a field is determined by the type defined at the time the field is declared, and the specific rules can refer to the following list. Note that in the order of the following list, if a field type conforms to both affinity, the preceding rule will have a 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 the field is TEXT, such as varchar.
3). If the type string contains a "BLOB", then the relative type of the field is none.
4. If the type string contains "real", "Floa" or "Doub", then the field's affinity type is real.
5). In other cases, the field's affinity type is numeric.

2. Specific examples:

declaring type	Affinity type	Apply Rule
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 real meaning, just to guarantee the declaration consistency with other databases.

Third, the comparison of expressions:

The comparison expressions supported in SQLite3 are: "= ', ' = = ', ' < ', ' <= ', ' > ', ' >= ', '!= ', ' <> ', ' in ', ' not in ', ' BETWEEN ', ' is ' and ' is Not ".
The result of the data comparison depends primarily on how the operands are stored, and the rules are:
1. The value stored as NULL is less than that of other storage types.
2. Values that are stored in integer and Real are less than the value of the text or blob type, and are compared based on numeric rules if the same is integer or real.
3. The value stored as text is less than the BLOB type and, if the same text, is based on the text rule (ASCII value) for comparison.
4. If a numeric value of two blob types is compared, the result is the result of the C Run-time function memcmp ().

Four, Operator:

All mathematical operators (+,-, *,/,%, <<, >>,;, and |) The operand is converted to the numeric storage type prior to execution, even though the data information may be lost during the conversion process. Also, if one of the operands is null, then their result is null. In mathematical operators, if one of the operands does not look like a numeric type, they result in 0 or 0.0.