Analysis on the combination of storage and data types in SQLite3

SQLite3 is a small database that runs on a mobile phone and a set-top box... so it cannot be as standard as musql or sqlserver and has many data types. I thought it was

SQLite3 is a small database that runs on a mobile phone and a set-top box... so it cannot be as standard as musql or sqlserver and has many data types. I thought it was

SQLite3 is a small database that runs on a mobile phone and a set-top box .... it cannot be as standardized as musql and sqlserver, and there are many data types. I thought it defined many data types before, but it is actually not just five storage classes, so many data types are mapped according to a set of rigorous rules !! What else does char and varchar actually do not exist? I will explain it in detail in the following documents. I believe you will learn more after reading it. In fact, it is mainly a translation document ....

Official sqlite Website:

Sqlite3 Data Type

Most database engines (all SQL database engines except sqlite as we know now) use static and rigid types, the data type is determined by its container. This container is the specific column to be stored.
Sqlite uses a more general dynamic type system. In sqlite, the value data type is related to the value itself, rather than its container. The dynamic type system of Sqlite is more compatible with the static type system of other databases, but at the same time, the dynamic type in sqlite allows it to do something that is impossible for traditional rigid type databases.

// This is a bit abstract. The simple meaning is that the stored values and column types are separated. There can be many column types, and there are five storage types, this is why sqlite is so small but supports so many data types. Just like ing, sqlite has its own data type to storage type ing.

1. Storage type and Data Type

1.0 storage type

Each value stored in an SQLite database (or an operating database engine) has the following storage classes (actually exist in the Database ):

From this we can see that the storage analogy data type is more general. For example, the INTEGER storage class contains 6 different INTEGER Data Types of different lengths, which makes a difference on the disk. However, as long as INTEGER values are read from the disk to the memory for processing, they are converted to the most common data type (8-byte signed INTEGER ).

Any column in The Sqlite v3 database, except the primary key column, can be used to store any storage type value.

· All values in SQL statements, whether embedded in SQL text or bound as parameters to a pre-compiled SQL statement, are not fixed in their storage types. As described below, the database engine converts values between the 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, 0 is false, and 1 is true.

1.2 Date and Time Datatype

Sqlite does not set a storage class set for the storage date and time. The built-in sqlite date and time functions can store the date and time in the form of TEXT, REAL or INTEGER.
L). TEXT as the IS08601 string ("YYYY-MM-DD HH: MM: SS. SSS ")
2). REAL's number of days since, November 24 B .C, January 1, 4174, Greenwich Mean Time
3) number of seconds since INTEGER 00:00:00 UTC
The program can choose 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 (sqlite data set)

To maximize compatibility between sqlite and other databases, sqlite supports column type approximation. column type Approximation refers to the recommendation type stored in column data. Remember that this type is recommended, not necessary. Any column can still store any type of data. Only some columns are selected, and some storage types are preferred compared to other types. The storage type of this column is called its "Approximation ".
Each column in The sqlite3 database is assigned one of the following types:
1). TEXT
2). NUMERIC
3). INTEGER
4). REAL
5). BLOB (BLOB was called NONE in the past, but this word is more likely to confuse "no approximation ")

Columns with TEXT similarity can store data of the NULL, TEXT, or BLOB type. If the value data is inserted into a column similar to TEXT, it is converted to TEXT format before being stored.
A column with the NUMERIC similarity can use all the 5 storage classes to store data. When text data is stored in a column similar to NUMERIC, the storage class of the text is converted to INTEGER or REAL (in order of priority), if the conversion is lossless. For the conversion between the TEXT and REAL storage classes, if the first 15 digits of the data are retained, sqlite considers the conversion to be lossless and reversible. If the conversion from TEXT to INTEGER or REAL inevitably results in losses, the data will be stored in the TEXT storage class. It does not attempt to convert NULL or BLOB values.

A string may look like a floating point data with a decimal point or an exponential sign, but as long as the data can be stored using an integer, NUMERIC will convert it to an integer. For example, the string '3. 0e + 5' is stored in a column with a NUMERIC approximation, and is saved as 300000 instead of a floating point value of 300000.0.
Columns with INTEGER similarity share the same performance as columns with NUMERIC similarity. The difference between them is only in the conversion description.
A column with a REAL approximation is the same as a column with a NUMERIC approximation, except that it converts an integer to a floating point.
Columns with BLOB similarity do not give priority to one storage column or forcibly convert data from one storage class to another.

2.1 columns of approximate deciding factors (ing)

The approximation of a column is determined by the declared type of the column, according to the following rules:

1) if the declared type contains an "INT" string, this column is assigned an INTEGER approximation.

2) If the declared type of this column contains "CHAR", "CLOB", or "TEXT", the column has a TEXT approximation. Note that the type VARCHAR contains the "CHAR" string, so it is given the TEXT approximation.

3) if the declared type of a column contains the string "BLOB" or is not declared for it, this column is assigned BLOB approximation.

4) if the declared types of a column include "REAL", "FLOA", and "DOUB", the column is a REAL approximation.

5) in other cases, columns are given NUMERIC approximation.
** The order of the above rules is very important for determining the approximation of columns. If the declared type of a column is "CHARINT", rules 1 and 2 are matched at the same time, but the 1st rules are given priority, so the approximate value of this column is INTEGER.

2.2 approximate name example (ing function)

(The following table shows the number of common data types from more traditional SQL operations. The five rules in the previous section are converted to the approximate type. This table only displays a subset of data class names accepted by sqlite. Note that the numerical parameters in parentheses following the type name are as follows: "VARCHAR (255 )") ignored by sqlite-sqlite does not impose any length restrictions on strings, BLOBS, or numeric values (except for a global SQLITE_MAX_LENGTH restriction ). // That is to say, adding parentheses is useless, so you don't need to add them. I have been adding them before.

Example Typenames From
Create table Statement
Or CAST Expression (input type)

Resulting Affinity (approximate result)

Rule Used To Determine Affinity

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8

INTEGER

1

CHARACTER (20)
VARCHAR (255)
Varying character (0, 255)
NCHAR (55)
Native character (70)
NVARCHAR (100)
TEXT
CLOB

TEXT

2

BLOB
No datatype specified

BLOB

3

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

REAL

4

NUMERIC
DECIMAL (10, 5)
BOOLEAN
DATE
DATETIME

NUMERIC

5

Note that if the declared type is "floating point", the INTEGER approximation will be assigned instead of the REAL approximation, because the "INT" is behind the "POINT.

If the Declaration type is "STRING", it will be assigned NUMERIC, not TEXT. (You can view the 2.1 rule without moving it. This is rule 5)