Shulou(Shulou.com)05/31 Report--

What are the data types in SQLite? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1.0 Storage types and data types

Each value stored in the SQLite database (or manipulated by the database engine) has one of the following storage types:

NULL. The value is null.

INTEGER. Values are signed integers that are stored in 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value.

REAL. The value is a floating-point number, stored as an 8-byte IEEE floating-point number.

TEXT. Values are text strings that are stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE).

BLOB. A value is a block of data that is stored as it is entered.

Note that storage types are more general than data types. Take the INTEGER storage type as an example, which includes six different integer types of varying lengths, which are different on disk. But as long as INTEGER values are read from disk to memory for processing, they are converted to more general data types (8-byte signed integers). So in general, there is no difference between "storage type" and "data type", and the two terms can be used interchangeably.

Any column in the SQLite version 3 database, except the integer primary key column, can be used to store values of any storage type.

Any value in a SQL statement, whether it is a literal amount embedded in a SQL statement or a parameter bound to a precompiled SQL statement, has an implicit storage type. In the following cases, the database engine converts between numeric storage types (INTEGER and REAL) and TEXT when executing a query.

1.1 Boolean type

SQLite does not have a separate Boolean storage type, but instead stores Boolean values as integers 0 (false) and 1 (true).

1.2 date and time Typ

SQLite does not have another storage type to store dates and times. SQLite's built-in date and time functions can store dates and times as TEXT, REAL, or INTEGER values:

TEXT ISO8601 string ("YYYY-MM-DD HH:MM:SS.SSS").

The number of REAL Julian days (Julian Day Numbers), according to the former Gregorian calendar, since noon on November 24, 4714 BC, Greenwich time.

INTEGER Unix time, the number of seconds since-01 00:00:00 UTC.

Applications can choose 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 type 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 is the recommended type in which data is stored in that column. The important idea here is that types are recommended, not necessary. Any column can still store any type of data. This just allows some columns to selectively give priority to a certain storage type. The preferred storage type for a column is called its "affinity".

The columns in each SQLite 3 database fall into one of the following types of affinity:

TEXT

NUMERIC

INTEGER

REAL

NONE

A column with TEXT affinity uses the storage type NULL, TEXT, or BLOB to store all data. If the numeric data is inserted into a column with TEXT affinity, the data is converted to text form before being stored.

Columns with numeric affinity may contain values that use all five storage classes. When text data is inserted 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 conversion between text and real number types, SQLite considers the conversion to be lossless and reversible if the first 15 important decimal digits are retained. If the text cannot be converted to an integer or real number losslessly, the value is stored as a text type. Do not attempt to convert NULL or blob values.

A string may look like floating-point text with a decimal point and / or an index, but as long as the value can be represented as an integer, numerical affinity converts it to an integer. Therefore, the string '3.0 eroom5' is stored in a numeric affinity column in the form of an integer 300000 rather than a floating point value of 30000.0.

A column that uses integer affinity behaves in the same way as a column with numerical affinity. It's just that in CAST expressions, the difference between them is obvious.

In addition to forcing the conversion of integer values to floating-point representations, a column with real affinity behaves in the same way as columns with numerical affinity (as an internal optimization, in order to take up less space, small floating-point values that are not fractional and stored on real-affinity columns are written to disk in integer 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 raw bits of the database file.

A column with NONE affinity cannot be converted from one storage type to another, nor do you try to force it to be converted.

2.1 column affinity test

The affinity of a column is determined by its declaration type, as shown in the following order:

1. If the declaration type contains the string "INT", it is specified as integer affinity

two。 If the declaration type of a column contains any "CHAR", "CLOB", or "TEXT" strings, the column has text affinity. Note: the VARCHAR type contains "CHAR" and is specified as text affinity

3. If the declared type of the column contains "BLOB" or does not specify a type, then the column has NONE affinity

4. If the declaration type of a column contains any "REAL", "FLOA" or "DOUB" strings, the column has real-number affinity

5. Otherwise, it will have numerical affinity.

Note: it is important to determine the order of column affinity rules. A column with the "CHARINT" declaration type will match rules 1 and 2, but rule 1 takes precedence over all the columns with integer affinity.

2.2 examples of affinity names

The following table shows how many common data type names from the more traditional SQL implementation have been converted to various affinity types through the five rules described in the previous section. This table shows only a small number of data type names that are acceptable to SQLite. Note: following the type name, numeric parameters in parentheses (such as VARCHAR (255)) will be ignored by SQLite-SQLite imposes no restrictions on the length of strings, BLOBs, or numeric values (except for large global SQLITE_MAX_LENGTH restrictions).

Note: because of the "INT" at the end of "POINT", a "FLOATING POINT" declaration type is given integer affinity instead of real number affinity. And the "STRING" declaration type has numeric affinity, not text affinity.

2.3 list examples of affinity sex

The following SQL demonstrates how SQLite uses column affinity to achieve type conversion when values are 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,-- real affinity by rule 4 no BLOB-- no affinity by rule 3)

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT. (values are stored in text, integer, integer, real, and text, respectively) INSERT INTO T1 VALUES ('500.0,' 500.0, '500.0,' 500.0, '500.0,' 500.0); SELECT typeof (t), typeof (nu), typeof (I), typeof (r), typeof (no) FROM t1bomtext | 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 t1bomtext | integer | integer | 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 t1bomtext | integer | integer | real | integer-BLOBs are always stored as BLOBs regardless of column affinity. DELETE FROM T1 insert INTO T1 VALUES; SELECT typeof (t), typeof (nu), typeof (I), typeof (r), typeof (no) insert INTO T1 VALUES | blob | blob-NULLs are also unaffected by affinityDELETE FROM t1boot insert INTO T1 VALUES (NULL,NULL,NULL,NULL,NULL); SELECT typeof (t), typeof (nu), typeof (I), typeof (r), typeof (no) FROM T1

3.0 Comparative expression

Like the standard SQL, SQLite 3 supports the following comparison operators: "=", "=", "!", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT".

3.1 collation

The result of the comparison is related to the storage type of the operands and is based on the following rules:

The NULL value is less than any other value (including another NULL)

INTEGER or REAL is less than TEXT, blob; if two INTEGER (or REAL) are compared, follow the actual value.

TEXT is less than BLOB. If the two TEXT are compared, the result will be determined by the appropriate sorting order.

If the two BLOD are compared, the result is consistent with that of memcmp ().

3.2 similarity when comparing operands

Before comparing values, SQLite attempts to convert values between the storage classes INTEGER, REAL, and / or TEXT. Whether or not to try to convert before comparison depends entirely on the similarity of operands. The rules for determining Operand similarity are as follows:

It's just that an expression that references a value in a column has exactly the same similarity as the referenced column. Note that if X and Y.Z represent the name of the column, then + X and + Y.Z can be thought of as expressions to determine their similarity.

The expression represented by "CAST (expr AS type)" has exactly the same similarity as a column of type defined as "type".

The expressions in other cases have NONE similarity.

3.3 Type conversion before comparison

"apply affinity" means to convert an Operand to a particular storage type if and only if the conversion involved is lossless and reversible. The rules for applying similarity to the operands of a comparison operator before making a comparison are as follows:

If one Operand has INTEGER, REAL, or NUMERIC similarity and the other Operand has TEXT or NONE similarity, then apply NUMERIC similarity to that other Operand.

If one of the operands has TEXT similarity and the other has NONE similarity, then apply TEXT similarity to that other Operand.

In other cases, no similarity is applied, and the two operands are compared as they are.

Think of the expression "a BETWEEN b AND c" as two separate binary comparison operations "a > = b AND a = y AND x"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.