Data types in the Sqlite3

Data types in the Sqlite3

Most database engines (as far as we know of each SQL database engine except SQLite) use static, rigid types, and the type of data is determined by its container, which is the specific column that is stored.

SQLite uses a more general dynamic type System in SQLite, where the data type of the value is related to the value itself, not to its container. SQLite's dynamic type system is compatible with the more general static type system of other databases, but at the same time, the dynamic type in SQLite allows it to do what is impossible with traditional rigid type databases.

1. Storage classes and data types

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

L NULL, value is NULL

L INTEGER, the value is signed, and is stored in 1,2,3,4,6 or 8 bytes depending on the size of the value

L REAL, value is a floating-point value, stored in 8-byte IEEE floating-point numbers

L text, value is a text string, stored using database encoding (UTF-8,UTF-16BE or Utf-16le)

L BLOB, just a block of data that is stored exactly as entered (that is, no change is allowed)

From the above you can see that the storage analogy data type is more generalized. For example, the integer storage class, which includes different types of shaping data of different lengths in 6, makes a difference on disk. But as long as the integer values are read from disk into memory for processing, they are converted to the most general data type (8-byte signed shaping).

Any column in the Sqlite V3 database can be used to store the value of any one storage column in addition to the reshape primary key column. All of the values in the SQL statement, whether they are embedded in SQL text or bound to a precompiled SQL statement as parameters, are undefined. In the scenario described below, the database engine converts values between the numeric (numeric) storage type (integer and real) and text during query execution.

1.1 Boolean type

SQLite does not have a separate Boolean storage type, it uses integer as the storage type, and 0 is false,1 true

1.2 Date and Time Datatype

SQLite does not also set a storage class set for storing dates and times, and the built-in SQLite date and time function can store dates and times in text,real or integers

L TEXT as IS08601 string ("Yyyy-mm-dd HH:MM:SS. SSS ")

L REAL from GMT November 24, 4174 B.C days since noon

L INTEGER number of seconds since 1970-01-01 00:00:00 UTC

The program can arbitrarily select these storage types to store the date and time, and can use the built-in date and time functions to freely convert between these formats

2.0 Type approximation

To maximize compatibility between SQLite and other databases, SQLite supports the idea of "type approximation" on the column, which refers to the recommended type of data stored on a column. One thing to keep in mind here is that this type is recommended, not required. Any column can still store any type of data. Just some columns, given the choice of words, would be preferable to some of the other types of storage types, and this column-first choice of storage type is called its "approximation".

The columns in each SQLITE3 database are given one of the following types of approximations:

L TEXT

L NUMERIC

L INTEGER

L REAL

L NONE

Columns with text approximation can store data with Null,text or BLOB types. If the value data is inserted into a column with a text approximation, it is converted to textual form before being stored

A column with numeric approximation can use all 5 storage classes in 1 to store the data. When the text data is stored in a numeric approximation column, the stored class of the text is converted to integer or real (in order of precedence) if the conversion is lossless. For conversions between the text and real storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 bits of the data are retained. If the text-to-integer or real conversion inevitably causes a loss, then the data will be stored using the text storage class. Does not attempt to convert null or BLOB values.

A string may look like floating-point data with a decimal point or exponential symbol, but as long as this data can be stored using shaping, the numeric approximation converts it to shaping. For example, the string ' 3.0e+5 ' is stored in a column with a numeric approximation and is stored as 300000 instead of a floating-point value of 300000.0.

Columns with an integer approximation behave the same as columns with numeric approximations. The difference between them is only in the conversion description.

A column with a real approximation is like a column with a numeric approximation, except that it transforms the shaping data into a floating-point form.

A column with a none approximation does not take precedence over a storage column, nor does it force the conversion of data from one storage class to another.

Determinant of 2.1-column approximation

The approximate column is determined by the declaration type of the column, according to the following sequence of rules:

<1> if the declaration type contains an "INT" string, then the column is given an integer approximation

<2> if the declaration type of this column contains "CHAR", "CLOB", or any of the "text", then the column has a text approximation. Note that the type varchar contains a "CHAR" string, so it is also given a text approximation

<3> if a column's declaration type contains a string "BLOB" or no type is declared for it, the column is given a none approximation

<4> other cases, the column is given a numeric approximation

The order of the above rule amounts is important for determining the approximation of the column. The declaration type of a column is "Charint" and matches the rules <1> and <2>, but the first rule takes precedence so the approximation of this column will be an integer.

2.2 Approximate name examples

The following table shows how many common data type names from more traditional SQL operations are converted to approximate types using the 5 rules in the previous section. This table shows only a subset of the data class names that SQLite can accept. Note that numeric parameters within parentheses following the type name (e.g. "VARCHAR (255)") are ignored by SQLite-sqlite not imposing any length restrictions on the length of strings, blobs, or numbers (except for a global sqlite_max_length limit).

from the CREATE table example type name of a statement or a strongly-forwarded statement	The resulting approximation	Used to determine the approximate 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 No datatype specified	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL (10,5) BOOLEAN DATE Datetime	NUMERIC	5

Notice that the claim type "floating point" is given an integer approximation, not a real approximation, because "INT" in "point". A declaration of type "string" will be given numeric, not text (because the type defined in the above table does not exist in type string, it is attributed to Rule <4>, which belongs to other cases).

(as can be seen from the above, sqlite3 just from the declaration type string to find the type of declaration it knows, such as "Xint" will be given an integer approximation because the string contains "INT", so there is no need for a separate correct declaration type, But only if the declaration type string contains the type of claim that SQLite knows.

2.3-Column Approximation operation example

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

); This determines the type approximation of the column based on the type of claim

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;

Results: Text|integer|integer|real|text

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;

Results: Text|integer|integer|real|real

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;

Results: Text|integer|integer|real|integer

(The fourth value here, the corresponding column is real approximation, the transferred value is shaped, but according to the real approximation of the rules it will convert it to real data)

Data blocks (BLOBs) are stored as BLOB types regardless of the column approximation

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;

Results: Blob|blob|blob|blob|blob

Nulls also not affected by column approximation

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;

Results: Null|null|null|null|null

3.0 Comparison Expressions

Sqlite V3 has a number of useful comparison operators, including "=", "= =", "<", "<=", ">", ">=", "! =", "<>", "in", "not", "between", "is", and "Is not"

3.1 Sort

The result of the comparison operation is based on the storage type of the operand, according to the following rules:

L storage type null value is considered to be less than any other value (including another value that stores type null)

L An integer or real value is less than any text or blob value. Performs a numeric comparison when an integer or real value is compared to another integer or real value

L text value is less than BLOB value. When two text values are compared, the results are determined based on the comparison of the sequence

L use MEMCMP to determine results when comparing two BLOB values

3.2 Comparison of approximations of operands (Affinity)

SQLite may convert comparison values between Integer,real or text before performing a comparison. Whether the conversion occurs before the comparison operation is based on the approximation (type) of the operand. Operand approximation (type) is determined by the following rules:

The expression for a simple reference to a column has the same affinity as the column, and note that if X and y.z are column names, then +x and +y.z are considered to be the expressions used to determine affinity

L An expression of "CAST (expr as type)" has the same affinity as a column with the declaration type "type"

l other cases, one expression is none affinity

3.3 Type conversions prior to comparison

Applying approximation only when the conversion is lossless and reversible means converting the operand to a specific storage class. Approximately before comparison is applied to the operands of the comparison, followed by the following rules (according to the Order of precedence):

L If one operand has a integer,real or numeric approximation and the other operand has a text or none approximation, then the numeric approximation is applied to the other operand

L If one operand has a text approximation and the other has a none approximation, then the text approximation is applied to the other operand

l Other cases, do not apply approximation, two operands are compared in the original way

The expression "A between B and C" represents two separate two value comparisons "a >= B and a <= C", even if different approximations are applied to ' a ' in both comparisons.

3.4 Comparison Examples

CREATE TABLE T1 (

A text,--text affinity

b NUMERIC,--NUMERIC Affinity

C BLOB,--no affinity

D--no affinity

);

INSERT 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 comparisons is converted to text before

--the comparison occurs.

SELECT a < B, a <, a < from T1;

0|1|1

--Text affinity is applied to the right-hand operands but since

--they is already TEXT the 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 is applied

--The operands on the right. Since The operands is already numeric,

-The application of affinity is a no-op;  No conversions occur. All

--values are compared numerically.

SELECT B < A, b <, b < from T1;

0|0|1

--Numeric affinity is applied to operands in 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 has

--storage class INTEGER which is always less than the TEXT values

-On the left.

SELECT C < b, 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 has

--storage class integer which compare numerically with the integer

--values on the left.

SELECT D <, D <, d < from T1;

0|0|1

--No affinity conversions occur. INTEGER values on the left is

than TEXT values on the right.

SELECT D < ' + ', D < ' + ', D < ' + ' from T1;

1|1|1

As can be seen here, if you can use the rules in 3.1 to compare, you do not need to do type conversion, otherwise it is necessary to type conversion

4.0 operator

All mathematical operators (+,-, *,/,%, <<, >>, &, |), before being executed, will convert all two operands to a numeric storage type (integer and real). Even if the conversion is lossy and irreversible, the conversion will still be performed. A null operand on a mathematical operator produces a null result. An operand on a mathematical operator that, if viewed in any way, is not like a number and is not empty, it will be converted to 0 or 0.0.