Data Type in sqlite3

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.

 

1. Storage Class and Data Type

Each value stored in the SQLite database (or operated by this database engine) has the following storage class:

Null. The value is null.

Integer. The value is a signed integer. The value is stored in 1, 2, 3, 4, 6, or 8 bytes.

Real, the value is a floating point value, which is stored as an 8-byte IEEE floating point number.

Text, the value is a text string, stored using database encoding (UTF-8, UTF-16BE or UTF-16LE)

Blob, which is just a data block, stored completely according to the input (I .e., there is no change)

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 the values of any storage column. All values in SQL statements, whether they are embedded in SQL text or bound to a pre-compiled SQL statement as parameters, their storage types are not fixed. 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 an is08601 string ("YYYY-MM-DD hh: mm: Ss. Sss ")

L real's number of days since, November 24 B .C, January 1, 4174, Greenwich Mean Time

L 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.0Type Approximation

 

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:

Text

Numeric

Integer

Real

None

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 a numeric similarity can use all the 5 storage classes in 1 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 similar to none do not give priority to one storage column or forcibly convert data from one storage class to another.

 

2.1 approximate deciding factors

The approximation of a column is determined by the declared type of the column. The following sequence rules apply:

<1> If the declared type contains an "int" string, this column is assigned an integer approximation.

<2> If the declared type of this column includes "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 the none approximation.

<4> in other cases, columns are given numeric approximation.

The order of the preceding rules is very important for determining the similarity of columns. If the declared type of a column is "charint", the rule <1> and <2> are matched at the same time, but the first rule takes priority, so the approximate value of this column is integer.

 

2.2 approximate name example

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 the numerical parameters (for example, "varchar (255)") in parentheses that follow the type name )") ignored by SQLite-SQLite does not impose any length restrictions on strings, blobs, or numeric values (except for a global sqlite_max_length restriction ).

From CREATE TABLEExample Type name of a statement or forced conversion statement	Generated approximation	Used to determine similarity rules
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 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, rather than the real approximation, because the "int" in "point ". If the declared type is "string", it will be assigned to numeric, not text (because the type defined in the preceding table does not exist in string, it is attributed to rule <4>, in other cases ).

(As can be seen from the above, sqlite3 only searches for the declared types it knows from the declared type string. For example, "xint" will be given integer approximation because the string contains "int ", therefore, a correct declaration type is not required here, but only the declared type string contains the declared type that SQLite knows)

 

2.3-column approximate 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

); // The column type approximation is determined based on the declared type.

 

Insert into T1 values ('2014. 0', '2014. 0', '2014. 0', '2014. 0', '2014. 0 ');

Select typeof (t), typeof (Nu), typeof (I), typeof (R), typeof (NO) from T1;

// Result: Text | 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;

// Result: Text | integer | 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;

// Result: Text | integer | real | integer

(The fourth value corresponds to a column with a real approximation, And the transmitted value is shaping, but it will convert it to real data according to the real approximation rule)

// The data block (BLOB) is always saved as blob regardless of the column similarity.

Delete from T1;

Insert into T1 values (x '000000', X '000000', X '000000', X '000000', X '000000 ');

Select typeof (t), typeof (Nu), typeof (I), typeof (R), typeof (NO) from T1;

// Result: blob | blob

 

// Nulls is not affected by column approximation.

Delete from T1;

Insert into T1 values (null, null );

Select typeof (t), typeof (Nu), typeof (I), typeof (R), typeof (NO) from T1;

// Result: NULL | null

 

 

3.0 comparison expression

SQLite V3 has a series of useful comparison operators, including "=", "=", "<", "<=", ">", "> = ","! = "," <> "," In "," not in "," between "," is ", and" is not"

 

3.1 sorting

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

L The value of storage type null is considered to be smaller than any other value (including the value of another storage type null)

L an integer or real value is smaller than any text or blob value. When an integer or real value is compared with another integer or real value, a numerical comparison is performed.

L The text value is smaller than the Blob value. When two text values are compared, the result is determined based on the comparison of the sequence.

L when two blob values are compared, memcmp is used to determine the result.

 

3.2 affinity)

SQLite may convert the comparison value between integer, real, or text before executing a comparison. Whether to convert the operand-based approximation (type) before the comparison operation ). The operand approximation (type) is determined by the following rules:

L The simple expression referenced by a column has the same affinity as this column. Note that if X and y.z are column names, + X and + y.z are considered to be expressions used to determine affinity.

L an expression in the form of "cast (expr as type)" has the same affinity as a column declared as "type ".

L in other cases, one expression is none affinity.

 

3.3 type conversion before comparison

Only when the conversion is lossless and reversible does "application Approximation" mean to convert the operands to a specific storage class. Follow the following rules (in order) to apply the comparison operations before comparison ):

L if one operand has an integer, real or numeric approximation, And the other operand has a text or none approximation, numeric approximation is applied to another operand.

L if one operand has a text approximation and the other has a none approximation, the text approximation is applied to another operand.

L in other cases, the approximation is not applied, and the two operands are compared in the original way.

Expression "A between B and C" indicates two separate two-Value Comparison "A> = B and A <= C ", even if different approximation in two comparisons is applied to 'A '.

 

3.4 Comparison example

Create Table T1 (

A text, -- text affinity

B numeric, -- numeric affinity

C blob, -- no affinity

D -- no affinity

);

 

Insert into T1 values ('20170101', '20160301', '20160301', 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

-- Right-hand + side of the comparisons are converted to text before

-- The Comparison occurs.

Select a <40, A <60, a <600 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 <'40', A <'60', A <'000000' from T1;

0 | 1 | 1

 

-- Column "B" has numeric affinity and so numeric affinity is 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 <40, B <60, B <600 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 <'40', B <'60', B <'000000' 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 <40, C <60, C <600 from T1;

0 | 0 | 0

 

-- No affinity conversions occur. values are compared as text.

Select C <'40', C <'60', C <'000000' from T1;

0 | 1 | 1

 

-- No affinity conversions occur. Right-hand side values all have

-- Storage Class Integer Which compare numerically with the integer

-- Values on the left.

Select d <40, d <60, d <600 from T1;

0 | 0 | 1

 

-- No affinity conversions occur. integer values on the left are

-- Always less than text values on the right.

Select d <'40', d <'60', d <'000000' from T1;

1 | 1 | 1

 

We can see from this that if we can use the rules in 3.1 for comparison, we do not need to perform type conversion. Otherwise, we need to perform type conversion.

 

4.0 Operator

All mathematical operators (+,-, *,/, %, <, >>, &, |), before being executed, both operands are converted to the value storage type (integer and real ). Even if the conversion is lossy and irreversible, the conversion will still be executed. The null operand on a mathematical operator produces the null result. The operands on a mathematical operator are converted to 0 or 0.0 if they do not look like numbers in any way and are not empty.