Most SQL database engines (as far as we know, all SQL database engines except SQLite) use strict static types. With a static type, the type of the value is determined by its container-the specific column in which the value is stored.
SQLite uses a more general dynamic type System. In Sqlit, the data type of a value is associated with the value itself, not with its container. SQLite dynamic type systems are backward compatible with the common static type systems of other database engines, in the sense that SQL statements working on statically typed databases should work in the same way in SQLite. However, the dynamic type in SQLite allows it to do things that traditional, strictly typed databases cannot do.
1.0 storage types and data types
Each value stored in the SQLite database (or the value that is manipulated by the database engine) has one of the following storage types:
Note that the storage type is more general than the data type. As an example of an integer storage type, it consists of 6 different integer types of varying lengths, which are different on disk. However, as long as the INTEGER value is read from disk to memory for processing, they are converted to a more general data type (8-byte signed integer). So in general, there is no difference between a "storage type" and a "data type", and the two terms can be used interchangeably.
Any column in the SQLite version 3 database, in addition to an integer primary key column, can be used to store values for any storage type.
Any values in the SQL statement, whether they are literal amounts embedded in an SQL statement or parameters that are bound to a precompiled SQL statement, have an implied storage type. The database engine converts between the numeric storage type (INTEGER and real) and TEXT when the query is executed in the following cases.
1.1 Boolean types
SQLite does not have a separate Boolean storage type, but instead stores the Boolean values as integers 0 (false) and 1 (true).
1.2 Date and time types
SQLite there is no additional storage type to store the date and time. SQLite's built-in date and time functions can save dates and times as TEXT, real, or INTEGER values:
Applications can select any of these formats to store dates and times, and use built-in date and time functions to freely convert between these formats.
2.0 Types of Affinity
To maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" for columns. The type affinity of a column refers to the recommended type of data that is stored in the column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. This simply allows some columns to selectively use some type of storage. The preferred storage type for a column is called its affinity.
The columns in each SQLite 3 database are attributed to one of the following types of affinity:
A column with TEXT affinity stores all data using a storage type NULL, TEXT, or BLOB. If the numeric data is inserted into a column with text affinity, the data is converted to text before it is stored.
A column of numeric affinity may contain values that use all five storage classes. When you insert text data into a numeric column, the storage type of the text is converted to an integer or real number (sorted by priority) if the conversion is lossless or reversible. For the conversion between text and real type, if the first 15 important decimal digits are retained, SQLite considers the conversion to be lossless and reversible. If the text cannot be converted to an integer or real number without prejudice, that value is stored as a literal type. Do not attempt to convert null or BLOB values.
A string may look like a floating-point literal with a decimal and/or exponential character, but as long as the value can be represented by an integer, numeric affinity converts it to an integral type. Therefore, the string ' 3.0e+5 ' is stored in the column of a numeric affinity in the form of an integer 300000 instead of a floating-point value of 30000.0.
A column that uses integral affinity is consistent with a column with numerical affinity. Only in the cast expression, the difference between them is obvious.
In addition to forcing an integer value to be converted to a floating-point representation, a column with a real-affinity is consistent with a column with numerical affinity (as an internal optimization, in order to occupy less space, small floating-point values that are not fractional and stored on the real affinity column are written to disk in integral form and automatically converted back to floating-point values when read out. At the SQL level, this optimization is completely invisible and can only be detected by checking the original bits of the database file.
A column with the none affinity cannot be converted from one storage type to another, and do not attempt to force it to be converted.
2.1 Column Affinity Determination
The affinity of a column is determined by its declaring type, as shown in the following order:
1. If the declaring type contains the string "INT", it is specified as integral affinity;
2. If the declaring type of the column contains any "CHAR", "CLOB" or "text" string, then the column has text affinity. Note: The varchar type contains "CHAR" and is specified as text affinity;
3. If the declaring type of the column contains "BLOB" or does not specify a type, the column has none affinity;
4. If the declaring type of the column contains any "real", "Floa" or "Doub" strings, the column has a true number affinity;
5. Otherwise, it will be of numerical affinity.
Note: It is important to determine the order of the column affinity rules. A column with a "charint" declaration type will match rules 1 and 2, but Rule 1 takes precedence over all of the columns with integer affinity.
2.2 Examples of affinity names
The following table shows how many common data type names are implemented from more traditional SQL implementations, and the five rules described in the previous section are converted to various affinity types. This table shows only a small number of data type names acceptable to SQLite. Note: After the type name, numeric arguments within parentheses (such as: VARCHAR (255)) are SQLite ignored-SQLite does not impose any restrictions on the length of strings, blobs, or numeric values (except for large global sqlite_max_length limits).
Note: Because at "INT" at the end of "point", a "floating point" declaration type is given an integral affinity rather than a real-sex affinity. and the "STRING" declaration type has numeric affinity, not text affinity.
Example of 2.3 rows of affinity sex
The following SQL demonstrates how SQLite uses column affinity to implement type conversions when a value is inserted into a table:
CREATE TABLE T1 ( t text, --text affinity by rule 2 nu NUMERIC,--NUMERIC affinity by rule 5 i INTEGER,- -Integer affinity by rule 1 r real, --the real affinity by rule 4 no BLOB --no affinity by rule 3 );
--Values stored as text, Integer, Integer, Real, text. (value is stored in text, Integer, Integer, Actual, and textual form) INSERT into T1 Values (' 500.0 ', ' 500.0 ', '
500.0 ', ' 500.0 ', ' 500.0 ');
SELECT typeof (T), typeof (Nu), typeof (i), typeof (R), typeof (No) from T1;
Text|integer|integer|real|text-Values stored as text, Integer, Integer, real, real.
DELETE from T1;
INSERT into T1 VALUES (500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof (T), typeof (Nu), typeof (i), typeof (R), typeof (No) from T1;
Text|integer|integer|real|real-Values stored as TEXT, Integer, Integer, Real, Integer.
DELETE from T1;
INSERT into T1 VALUES (500, 500, 500, 500, 500);
SELECT typeof (T), typeof (Nu), typeof (i), typeof (R), typeof (No) from T1; Text|integer|integer|real|integer--BLOBs are always stored as BLOBs regardless of column affinity.
DELETE from T1;
INSERT into T1 VALUES (x ' 0500 ', X ' 0500 ', X ' 0500 ', X ' 0500 ', X ' 0500 ');
SELECT typeof (T), typeof (Nu), typeof (i), typeof (R), typeof (No) from T1; Blob|blob|blob|blob|blob--NULLs are also unaffected BY affinity DELETE from T1;
INSERT into T1 VALUES (null,null,null,null,null);
SELECT typeof (T), typeof (Nu), typeof (i), typeof (R), typeof (No) from T1;
Null|null|null|null|null
3.0 Comparison Expressions
As with standard SQL, SQLite 3 supports the following comparison operators: "=", "= =", "<", "<=", ">", ">=", "!=", "<>", "in", "No In", "BETWEEN", "is" , and "is not".
3.1 Sorting Rules
The results of the comparison are related to the storage type of the operand, and are based on the following rules:
Similarity of 3.2 operands for comparison
Before a value is compared, SQLite attempts to convert the value between the storage class integer, real, and/or text. It is entirely dependent on the similarity of the operands to try to do the conversion before comparing. The rules for determining the similarity of operands are as follows:
3.3 Type conversions prior to comparison
Application similarity ("apply affinity") means converting an operand to a specific storage type when and only if the transformation involved is lossless and reversible. The rules for applying similarities to the operands of a comparison operator before comparison are shown in order:
The expression "a BETWEEN B and C" is considered as two separate two-dollar comparison operations "a >= B and a <= C", even though this may result in a different similarity between a and two comparisons. Datatype conversions in comparisons of the form on "x in (SELECT y ...)" In this form of comparison, the conversion of data types is handled exactly as "x=y". The expression "A in (x, Y, Z, ...)" is equivalent to "a = +x or a = +y or a = +z or ...". In other words, the value on the right side of the in operator (in this case, "X", "Y", and "Z") is considered to be without similarity, even if they happen to be the values of a column or a cast expression.
3.4 Comparison Examples
CREATE TABLE T1 (a text,--text affinity b NUMERIC,--NUMERIC affinity C BLOB,--no affinity D--No
Affinity);
--values would be stored as TEXT, Integer, TEXT, and integer respectively inserts into T1 Values (' 500 ', ' 500 ', ' 500 ', 500);
SELECT typeof (A), typeof (b), typeof (C), typeof (d) from T1; Text|integer|text|integer--Because column "a" has text affinity, numeric values on the--right-hand side of the Compa
Risons are converted to text before-the comparison occurs.
SELECT a <, a <, a < from T1; 0|1|1--text affinity is applied to the right-hand operands but since--they are already Text this is a no-op;
No conversions occur.
SELECT a < ', a < ', a < ' from T1; 0|1|1--Column "B" has numeric affinity and so numeric affinity are applied--to the operands on the right. Since The operands are already numeric, the application of affinity is a no-op; No conversions occur. All--values are compared numerically.
SELECT B <, b <, b < from T1; 0|0|1-Numeric affinity is applied to operands on the right, converting them--from text to integers.
Then A numeric comparison occurs.
SELECT B < ', b < ', b < ' from T1; 0|0|1--No affinity conversions occur.
Right-hand side values all have--storage class INTEGER which are always less than the TEXT values--on the left.
SELECT C <, C <, C < from T1; 0|0|0--No affinity conversions occur.
Values are compared as TEXT.
SELECT C < ', C < ', C < ' from T1; 0|1|1--No affinity conversions occur.
Right-hand side values all have--storage class integer which compare and the integer-values on the left.
SELECT D < A, d <, d < from T1; 0|0|1--No affinity conversions occur.
INTEGER values on the left are--always less than TEXT values to the right.
SELECT D < ', D < ', D < ' from T1;
1|1|1
If the comparison in the example is replaced-for example, "A<40" is written "40>a"-all the results will remain the same.
4.0 operator
All mathematical operators (+,-, *,/,%, <<, >>,;, and |) Two operands are put into the NUMERIC storage class before they are expanded. Even if the process is damaging and irreversible. A null operand produces a null result on the mathematical operator. The operands on the counting operator are not treated as numbers, and NULL is not converted to 0 or 0.0.
5.0 sorting, grouping and combining queries
When the query results are sorted using an ORDER BY clause, the null null value of the storage type is ranked first, followed by the integer and the real data that is scattered in the numeric order, followed by the value of the text in the order of the check sequence, and finally the blob of the memcmp (). No storage type conversions occur before sorting.
When grouped by using the GROUP BY clause, different types of values are considered to be different data, except for the integer and real values that are considered identical if they are of equal value. No affinity applies to any value of the result of a GROUP BY clause.
A combined query uses UNION, INTERSECT, and EXCEPT to perform an implicit comparison between data. No affinity applies to the operands of an implicit comparison with union, INTERSECT, or except-data comparisons are like this.
6.0 Finishing Sequence
When SQLite compares two strings, it uses a collation sequence or a collation function (a two-object table) to determine which string value is higher when two strings are the same. SQLite has three built-in finishing functions: BINARY, Nocase, and RTRIM.
Applications can register additional collation functions through the sqlite3_create_collation () interface.
6.1 Setting the order in SQL
Each column in each table has a related sort function. If you do not explicitly define a sort function, the default is to use binary as the sort function. The COLLATE clause in the column definition defines an optional sort function for the column.
For two-dollar comparison operators (=, <=, >=,!=, is, and are not), the rules that determine which sort function to use are listed in order:
The operands in the comparison operation, if the suffix collate operator is used in any of its subexpression, can be considered to have an explicit sort function (rule 1 above). Furthermore, if the collate operator is used anywhere in the comparison expression, the sort function defined by the operator is used for comparison of strings, regardless of which column in the table appears in the expression. If two or more collate operations subcode expressions appear anywhere in the comparison, regardless of how deep they are embedded in the expression, and regardless of how the expressions are used, the explicit sort function that appears on the left is used.
The expression "x BETWEEN Y and z" logically is exactly equivalent to the two comparison operations of "x >= y and x <= z", and when using the sort function they are treated like two separate comparison operations. The expression "x in" (SELECT y ...) is determined when the order is sorted. The process is exactly the same as the expression "x = Y", in the form of "x in (Y, Z, ...)" Expressions that are arranged in exactly the same order as X.
As part of the SELECT statement, the sort conditions in the ORDER BY clause can also be ordered by using the COLLATE operator, which is sorted by the Set sort function if the sort is set. Otherwise, if the ORDER BY clause uses a sort expression that is a column, the column is sorted in order to determine the order. If the sort expression is not a column and there is no COLLATE clause, the binary order is used.
6.2 Collating Sequence Example
The following example identifies the collation sequence and determines the text comparison result of the SQL statement. Note that if you are a numeric, binary, or null value in a text comparison, the collation sequence may not be used.
CREATE TABLE T1 (x INTEGER PRIMARY KEY, A,/* collating sequence BINARY/b COLLATE BINARY,/* collating s Equence BINARY/C COLLATE RTRIM,/* collating sequence (RTRIM/d COLLATE nocase/* Collating sequence * * *)
;
* * X a b c d */INSERT into T1 VALUES (1, ' abc ', ' abc ', ' abc ', ' abc ');
INSERT into T1 VALUES (2, ' abc ', ' abc ', ' abc ', ' abc ');
INSERT into T1 VALUES (3, ' abc ', ' abc ', ' abc ', ' abc ');
INSERT into T1 VALUES (4, ' abc ', ' abc ', ' abc ', ' abc '); /* a=b text comparisons are performed using the BINARY (binary) collation sequence.
* * SELECT x from t1 WHERE a = b order by x; --Results 1 2 3/* a=b text comparisons are performed using the RTRIM collation sequence.
* * SELECT x from t1 WHERE a = b COLLATE RTRIM order by X; --Results 1 2 3 4/* d=a text comparisons are performed using the Nocase collation sequence.
* * SELECT x from t1 WHERE d = an order by x; --Results 1 2 3 4/* A=d text comparisons are performed using the BINARY (binary) collation sequence.
* * SELECT x from t1 WHERE a = d order by x; --Results 1 4/* ' abc ' =c text comparisons are performed using RTRIM (binary) collating sequences.
* * SELECT x from t1 WHERE ' abc ' = c order by x; --Results 1 2 3/* c= ' abc ' text comparison is performed using RTRIM collating sequence。
* * SELECT x from t1 WHERE c = ' abc ' ORDER BY X; --Results 1 2 3/* Groups are shown to use the Nocase collation sequence (value ' abc ', ' ABC ' and ' ABC ' * * are divided into the same group).
* * SELECT COUNT (*) from T1 GROUP by D: 1; --The result 4/* grouping is performed using the BINARY collation sequence (value ' abc ', ' ABC ' and ' ABC ' * * are divided into different groups). */SELECT COUNT (*) from T1 GROUP by (d | |
"") Order by 1; --The result 1 1 2/* column C sort performance is performed using RTRIM finishing sequence. * * Sorting or column C is suspected to be sorting.
SELECT x from T1 order by C, X; --Results 4 1 2 3/* (c| | ") The sort performance is performed using the BINARY collation sequence.
*/SELECT X from T1 (c| | "), x; --The result 4 2 3 1/* column C sort performance is performed using nocase finishing sequence.
* * SELECT X from T1 ORDER by C COLLATE nocase, X;
--Results 2 4 3 1
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
