Data Type in SQLite 3

. SQLite uses a dynamic type system. In SQLite, the data type and value of a value are associated with its container rather than the value itself. The dynamic type system of SQLite is compatible with the static type system of other database engines, so that the SQL statements executed on the static type database can also be executed in SQLite.

1.0 storage and data types

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

• Null. Null Value.
• Integer. The signed integer is stored in 1, 2, 3, 4, 6, or 8 bytes.
• Real. Floating Point number, which is an 8-byte IEEE floating point number.
• Text. Text string, stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE.
• Blob. Big Data.

Note that the storage class is more general than the data type. Integer storage class, for example, contains six integer data types with different lengths. This is different in the disk. However, once the integer value is processed from the disk, it is converted to a more common data type (eight-bit signed integer ). Therefore, in most cases, the storage class and data type are difficult to distinguish. These two terms can be used in exchange.

In the SQLite 3 database, any column except the integer primary key column can store the values of any storage class.

All values in SQL statements, whether embedded in SQL statements or pre-compiled SQL statements, have an implicit storage class. Under the conditions described below, in the query execution phase, the database engine may convert between the digital storage class (integer and real) and the text storage class.

1.1 boolean data type

SQLite does not have a separate Boolean storage class. On the contrary, booean values are stored as integers 0 (false) and 1 (true.

1.2 Date and Time Data Types

SQLite does not set a special storage class for the storage date and/or time. On the contrary, the built-in date and time functions can store the date and time as text, real, or integer values:

• Text: As an iso8601 string ("YYYY-MM-DD hh: mm: Ss. Sss ").
• Real: Number of days as Julian ,......
• Integer: Unix time, that is, the number of seconds since 00:00:00 UTC.

Type affinity (2.0)

To maximize compatibility between SQLite and other database engines, SQLite supports the concept of "similar types" of columns. The important idea here is that the type is recommended and not necessary. Any column can still store any type of data. Only some columns can choose to prioritize certain storage classes. The priority storage class for this column is called its "similarity".

Each column in The SQLite 3 database is assigned one of the following similar types:

• Text
• Numeric
• Integer
• Real
• None

Columns with similar text use the storage class null, text, or blob to store all the data. If data is inserted into columns with similar text, it is converted to text format before insertion.

Columns with numeric similarity can use all five storage classes to include values. When text data is inserted into a numeric column, the storage class of the text is converted to integer or real (for priority), if the conversion is lossless and reversible. If the conversion from text to integer or real is impossible, the value will be stored in the text storage class. It does not try to convert null or blob values.

......

Determine the similarity of the 2.1 Columns

The column is determined by the type declared by the column. The rule is in the following order:

1. If the declared type contains the string "int", it is given integer similarity.

2. If the type declared by the column contains any string "char", "clob", or "text", the column has the same text. Note that the type varchar contains "char", so it also gives text similarity.

3. If the declared type of a column contains "blob" or the type is not specified, this column is similar to none.

4. If the column declaration type contains any "real", "floa", or "doub", this column is similar to real.

5. In other cases, it is like numeric.

Note that the order of rules is important. Columns declared as "charint" must match rule 1 and rule 2 at the same time, but the first rule takes precedence. Therefore, this column is like an integer.

2.2 similarity examples

Example	Similar results	Rules
Int Integer Tinyint Smallint Mediumint Bigint Unsigned big int Int2 Int8	Inetger	1
Character (20) Varchar (255) Varying character (0, 255) Nchar (55) Native character (70) Nvarchar (100) Text Clob	Text	2
Blob Data Type not specified	None	3
Real Double Double Precision Float	Real	4
Numeric Decimal (10, 5) Boolean Date Datetime	Numeric	5

Note that the "Floating Point" type specifies integer similarity, rather than real similarity, because "int" is at the end ." String "type will have numeric like, rather than text.