Data Types in the third edition of SQLite

ArticleDirectory

• 1. Storage category
• 3. Comparison expression
• 4. Operators
• 5. Classification, sorting, and hybrid selection
• 6. Other affinity Modes
• 7. User-Defined proofreading Sequence

1. Storage category

The second version stores the values of all columns in the ASCII text format. The third edition stores data as integers and real numbers, and BLOB data.

Each value stored in an SQLite database has an attribute that belongs to one of the classes listed below (controlled by the database engine)

• Null. The value is null.

• Integer. The value is identified as an integer, which can be stored as 1, 2, 3, 4, 5, 6, or 8 in sequence based on the value size.

• Real Number. All values are floating values and are stored as 8-byte IEEE floating mark numbers.

• TextThe value is a text string stored using database encoding (TUTF-8, UTF-16BE or UTF-16-LE ).

• BlobThe value is BLOB data, and the input is stored without changing the format.

Like sqlite2.0, in MySQL 3.0, all columns in the database can store any type of data except integer primary key. this rule is also described in the "strict similarity mode" below.

Enter all SQLite values. Whether embedded in SQL statements or pre-compiled values bound to SQL statements, these values are stored as a class before SQL statements are executed. as described below, the database engine checks during execution and converts values between the digital storage class (integer and real number) and text class.

The storage category is initially classified as follows:

• For example, the text with double quotation marks or single quotation marks in the SQL statement is defined as text. If the text does not contain quotation marks and has no decimal point or index, it is defined as an integer, if the text is not enclosed in quotation marks but has a decimal point or index, it is defined as a real number. If the value is null, it is defined as a null value. BLOB data is identified by the symbol 'abc.

• Values supplied using the input value uses sqlite3_bind _ * APIs to be classified as a storage level, which is basically the same as the original class. (For example, sqlite3_bind_blob () is bound to a blob value ).

The classification of values is the result of SQL hierarchical operations, which is determined by the farthest operation expression. user-defined functions may return any class value. it is basically impossible to determine the storage class of expressions during compilation.

2. affinity between columns

In sqlite3.0, the reason why the value type is defined is only related to the value itself is that it has no relationship with the column and the variable. (This is sometimes called a weak type .) all other database engines we use are restricted by the static type system. The class of all values is determined by the attribute of the column to which the database engine belongs, and the value is irrelevant.

To maximize compatibility between SQLite databases and other databases, SQLite supports column "type affinity ". the column affinity is recommended for the data stored in this column. we should pay attention to suggestions rather than forced. theoretically, any column can still store any type of data. only for certain columns. If the recommended type is provided, the database will store the data according to the recommended type. the data type that is preferentially used is called "affinity type ".

In sqlite3.0, each column in the database is defined as one of the following affinity types:

• Text
• Number
• Integer
• None

A column with type affinity stores all data according to the non-type, text, or blob. if the numeric data is inserted into a column with the text type affinity, the number is converted to text before being stored.

A column with a numeric type affinity may use all five storage types to store values. when text data is inserted into a numeric column, the database will try to convert the text to an integer or a real number before it is stored. if the conversion is successful, the value will be stored based on the type of live real number of the certificate. if the conversion fails, the values can only be stored by text type instead of being converted to non-type or blob type for storage.

A column with integer affinity is the same as a column with numeric affinity in terms of conversion, but there are also some differences, such as the real value without floating volume (the value of text value conversion) when a column with integer affinity is inserted, it is converted to an integer and stored as an integer.

A column with no type affinity does not select which type to use first. It does not force data conversion before data is input.

Affinity of 2.1 Columns

The affinity type of a column is determined by the declared type of the column. The following rules are observed:

1. If the data type includes the string "int", it is defined as an integer affinity.

2. If the data types in the column include any of the following strings: "char", "clob", or "text", the column has the text affinity. note that the varchar type includes the string "char", so it also has the text type affinity.

3. If the data type of a column includes the string "blob" or if the data type is embodied, the column has no type affinity.

4. Otherwise, the data type is compatible.

If the table is generated using the if "create table as select..." statement, no specific data type exists for all columns, and no type affinity exists.

Example of the affinity of the 2.2 Column

Create Table T1 (
T text,
Nu numeric,
I integer,
No blob
);

-- Storage classes for the following row:
-- Text, real, integer, text
Insert into T1 values ('2014. 0', '2014. 0', '2014. 0', '2014. 0 ');

-- Storage classes for the following row:
-- Text, real, integer, real
Insert into T1 values (500.0, 500.0, 500.0, 500.0 );

3. Comparison expression

Like sqlite2.0, one feature of version 3.0 is the binary comparison operator '=', '<', '<=', '> =' and '! = ', An operation 'in' can be used to test a fixed membership. The Triple comparison operator 'betenen '.

The comparison result depends on the storage type of the two values to be compared. Follow the following rules:

• A value with an empty storage type is considered to be smaller than any value (including another value with an empty storage type ).

• An integer or real value is smaller than any text value or blob value. When an integer or real number is compared with another integer or real number, it is compared according to the actual value.

• A text value is smaller than the Blob value. When comparing two text values, use the memcmp () function in the C language class library to compare them. However, sometimes this is not the case, for example, in the "user-defined sorting order" described below.

• When two blob texts are compared, the result depends on the memcmp () function.

Before starting the comparison, SQLite tries to convert values between numbers (integers and real numbers) and texts. The following is an example of how to compare binary values. The expression used in the duplicate below can represent an SQL scalar expression or text, but not a column value.

• When a column value is compared to the expression result, the column affinity is applied to the expression result before the comparison starts.

• When two column values are compared, if one column has an integer or number affinity, but the other column does not, the numeric affinity applies to any value with a text storage type extracted from a non-numeric column. p>

• When comparing the results of two expressions, the result is directly compared without any conversion. If a string is compared with a number, the number is always smaller than the string.

In SQLite, the expression "a between B and C" is equal to the expression "a> = B and A <= C". When comparing expressions, A can have any affinity.

Expression "A in (select B ....) "The comparison follows the three rules mentioned above. It is a binary comparison. (for example, in a similar style "A = B "). for example, if 'B' is a column value and 'A' is an expression, before comparison, the 'B' affinity is converted to the 'A' affinity.

SQLite treats the expressions "A in (x, y, z)" and "A = Z or a = Y or a = z" as equal.

3.1 comparison examples

Create Table T1 (
A text,
B numeric,
C blob
);

-- Storage classes for the following row:
-- Text, real, text
Insert into T1 values ('20170101', '20160301', '20160301 ');

-- 60 and 40 are converted to '60' and '40' and values are compared as text.
Select a <60, a <40 from T1;
1 | 0

-- Comparisons are numeric. No conversions are required.
Select B <60, B <600 from T1;
0 | 1

-- Both 60 and 600 (Storage Class numeric) are less than '000000'
-- (Storage Class text ).
Select C <60, C <600 from T1;
0 | 0

4. Operators

All mathematical operators (all operators, rather than the chain operator "|") have a numerical affinity first, if one or both of them cannot be converted to numbers, the operation result will be null.

For Concatenation Operators, all operators will first have text affinity. If any of the operators cannot be converted to text (because it is null or blob), the concatenation operator will be null.

5. Classification, sorting, and hybrid selection

When a value is selected using the order clause, the null value is first selected, then the integer and real number are selected in order, and then the text value is selected in the memcmp () Order, finally, blob values are selected in the memcmp () Order. values of no storage type are converted before selection.

When a group by clause is used to group values, values of Different Storage types are considered different, but there are also exceptions. For example, an integer is equal to an actual number, so they are equal. after the group by clause is used for comparison, the value does not have any affinity.

The Union, intersect, and intersect operators use an absolute comparison between values. The same affinity is applied to all values, these values will be stored in a separate column with a mixed select result group. the given affinity is the affinity of this column, which is returned by most of the remaining mixed selects, these hybrid selects have column values at that position (instead of other types of table types ). if a given hybrid Select column does not have the number of selects, the value of this column does not have any affinity before comparison.

6. Other affinity Modes

The above section describes the operations performed by the database engine in the normal affinity mode. SQLite describes the other two affinity modes, as shown below:

• Strict affinityMode. In this mode, if the data storage type needs to be converted between values, the database engine will send an error report and the current statement will run again.

• No affinityMode. In this mode, the data storage type of values is not converted. Values of Different Storage types cannot be compared, but can be compared between integers and real numbers.

7. User-Defined proofreading Sequence

By default, when SQLite compares two text values, it is set by the system to use memcmp () for comparison regardless of the string encoding. the third edition of SQLite allows you to provide any function to replace memcmp (), that is, the User-Defined comparison sequence.

In addition to the binary comparison sequence predefined by the system, it is compared using the memcmp () function. SQLite also contains two additional built-in comparison sequence functions, nocase and reverse:

• Binary-Use memcmp () to compare string data, regardless of text encoding.
• Reverse-Compare binary text in reverse order.
• Nocase-It is the same as binary, but before comparison, the 26-digit upper-case letter disk will be converted into the corresponding lower-case letter disk.

7.1 allocation comparison order

Each column in each table has a predefined comparison type. If a comparison type is not required by binary, the comparison clause is embodied in the column definition to define the column.

When we use SQLite to compare two text values, the comparison sequence determines the comparison result according to the following rules. The third and fifth sections of the document describe the situation in which such a comparison occurs.

For binary comparison operators (=, <, >,< = and> =), if each operand is a column, the default comparison type of this column is determined by the comparison sequence used. if both operands are columns, the comparison type of the operands on the left determines the comparison sequence to be used. if neither of the two operands is a column, binary comparison is used.

The expressions "x between Y and Z" and "x> = y and x <= z" are the same. expression "X in (select y ...) "And expression" x = y "use the same method to operate, this is to determine the order of comparison to be used. if X is a column or binary, "X in (Y, Z ...) "The comparison sequence used by the expression in the form is the default comparison type of X.

Order by clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. in this case the explicit collation sequence is always used. otherwise, if the expression sorted by an order by clause is a column, then the default collation type of the column is used to determine sort order. if the expression is not a column, then the binary collation sequence is used.

7.2 comparison sequence example

The following example describes the examples below identify the collation sequences that wocould be used to determine the results of text comparisons that may be placed med by various SQL statements. note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or null values.

Create Table T1 (
A, -- default collation Type Binary
B collate binary, -- default collation Type Binary
C collate reverse, -- default collation type reverse
D collate nocase -- default collation type nocase
);

-- Text comparison is saved med using the binary collation sequence.
Select (A = B) from T1;

-- Text comparison is saved med using the nocase collation sequence.
Select (D = A) from T1;

-- Text comparison is saved med using the binary collation sequence.
Select (A = d) from T1;

-- Text comparison is saved med using the reverse collation sequence.
Select ('abc' = C) from T1;

-- Text comparison is saved med using the reverse collation sequence.
Select (C = 'abc') from T1;

-- Grouping is saved med using the nocase collation sequence (I. e. Values
-- 'Abc' and 'abc' are placed in the same group ).
Select count (*) group by D from T1;

-- Grouping is saved med using the binary collation sequence.
Select count (*) group by (d | '') from T1;

-- Sorting is saved med using the reverse collation sequence.
Select * from T1 order by C;

-- Sorting is performed med using the binary collation sequence.
Select * from T1 order by (c | '');

-- Sorting is saved med using the nocase collation sequence.
Select * from T1 order by C collate nocase;