In Sqlite3, the storage type and data type are parsed in combination with the document ., Sqlite3 Data Type

In Sqlite3, the storage type and data type are parsed in combination with the document ., Sqlite3 Data Type
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 char and varchar do not actually exist? The following will be explained in detail in the document. I believe you will learn more after reading it. In fact, it is mainly about the translation documentation... sqlite Official Website: http://www.sqlite.org/sqlite3data 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 ):

· The NULL value is null.

· The INTEEGER value is a signed integer, which is stored in 1, 2, 3, 4, 6, or 8 bytes according to the value size.

· 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 that is stored using database encoding (UTF-8, UTF-16BE or UTF-16LE)

· BLOB, which is just a data block, is fully stored according to 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 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 there is no separate Boolean storage type for Boolean Sqlite. It uses INTEGER as the storage type, 0 is false, 1 is true
1.2 Date and Time Datatype Sqlite do 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) in order to maximize compatibility between sqlite and other databases, sqlite supports the "type Approximation" View on the column, the column type is similar to the recommended type of data stored in the column. 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)

(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);-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.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;text|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 t1;text|integer|integer|real|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 t1;text|integer|integer|real|integer-- BLOBs are always stored as BLOBs regardless of column affinity.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;blob|blob|blob|blob|blob-- NULLs are also unaffected by affinityDELETE FROM t1;INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;null|null|null|null|null

This is an example given in the document...

 

 

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.

To put it simply, BLOB> TEXT> INTEGER/REAL> NULL

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 ).

Note that the columns in each table have approximate columns, but the expressions do not necessarily exist.
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 comparison before type conversion
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 BLOB approximation or no approximation, the NUMERIC approximation is applied to another operand.

L if one operand has a TEXT approximation and the other has an 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 "aBETWEEN 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 examples

CREATE TABLE t1(    a TEXT,      -- text affinity    b NUMERIC,   -- numeric affinity    c BLOB,      -- no affinity    d            -- no affinity);-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectivelyINSERT 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 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 < '600' 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 < '600' 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 < '600' 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 < '600' FROM t1;1|1|1

It is also the source code given by the document...

 

 

 

The results in all examples are the same. If you replace the expression with the expression, the expression form "a <40" is rewritten to "40> ".

 

 

 

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.

5. sorting, grouping, and compound Selection

When the query results are sorted BY the order by clause, the storage class NULL is the first, followed by integer and REAL values interspersed in the numerical ORDER, and the TEXT value is sorted in ORDER, finally, BLOB is ordered in memcmp. No storage class conversion occurred before classification.

When groupingvalues with the group by clause values with different storage classes areconsidered distinct, counter t for INTEGER and REAL values which are consideredequal if they are numerically equal. no affinities are applied to any values asthe result of a GROUP by clause.

The compoundSELECT operators UNION, INTERSECT and wait t perform implicit EXCEPT values. No affinity is applied to comparison operands for the specified associated with UNION, INTERSECT, or equal t-the values arecompared as is.

6.0 sorting Sequence

When SQLite compares two strings, it uses a sort sequence or sort function (two types of words for the same thing) to determine which String is better or the two strings are the same. SQLite3 built-in sorting types: BINARY, NOCASE, and RTRIM ..

• BINARY-compares string data using memcmp (), ignoring text encoding.
• NOCASE-The same as binary, should t the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is already med. note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.
• .
• RTRIM-like BINARY, except the trailing space characters are ignored.

Applications can register the sqlite3_create_collation () interface for other functions.

6.1 AssigningCollating Sequences from SQL

Every column of every table has an associated collating function. if no collating function is explicitly defined, then the collating functionults ults to BINARY. the COLLATE clause of the column definition is used to define alternativecollating functions for a column.

The rules for determining which collating function to use for abinary comparison operator (=, <, >,<=, >= ,! =, IS, and is not) are as follows and in the order shown:

1. If either operand has an explicit collating function assignmentusing the postfix COLLATEoperator, then the explicit collating function is used forcomparison, with precedence to the collating function of the left operand.

2. if either operand is a column, then the collating function of thatcolumn is used with precedence to the left operand. for the purposes of theprevious sentence, a column name preceded by one or more unary "+" operators is still considered a column name.

3. Otherwise, the BINARY collating function is used for comparison.

An operand of a comparison is considered to have an explicitcollating function assignment (rule 1 above) if any subexpression of theoperand uses the postfix COLLATEoperator. thus, if a COLLATEoperator is usedanywhere in a comparision expression, the collating function defined by thatoperator is used for string comparison regardless of what table columns mightbe a part of that expression. if two or commandid anywhere in a comparison, the left most explicit collating function isused regardless of how deeply the COLLATE operators are nested in theexpression and regardless of how the expression is parenthesized.

The expression "x BETWEEN y and z" is logicallyequivalent to two comparisons "x> = y AND x <= z" and workswith respect to collating functions as if it were two separate comparisons. theexpression "x IN (SELECT y ...) "is handled in the same way as theexpression" x = y "for the purposes of determining the collatingsequence. the collating sequence used for expressions of the form "x IN (y, z ,...) "is the collating sequence of x.

Terms of the order by clause that is part of a SELECT statement may be assigned a collatingsequence using the COLLATEoperator, in which case the specified collating function is used forsorting. otherwise, if the expression sorted by an order by clause is a column, then the collating sequence of the column is used to determine sort order. ifthe expression is not a column and has no COLLATE clause, then the BINARYcollating sequence is used.

6.2 sorting sequence example

CREATE TABLE t1(    x INTEGER PRIMARY KEY,    a,                 /* collating sequence BINARY */    b COLLATE BINARY,  /* collating sequence BINARY */    c COLLATE RTRIM,   /* collating sequence RTRIM  */    d COLLATE NOCASE   /* collating sequence NOCASE */);                   /* x   a     b     c       d */INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc'); /* Text comparison a=b is performed using the BINARY collating sequence. */SELECT x FROM t1 WHERE a = b ORDER BY x;--result 1 2 3/* Text comparison a=b is performed using the RTRIM collating sequence. */SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;--result 1 2 3 4/* Text comparison d=a is performed using the NOCASE collating sequence. */SELECT x FROM t1 WHERE d = a ORDER BY x;--result 1 2 3 4/* Text comparison a=d is performed using the BINARY collating sequence. */SELECT x FROM t1 WHERE a = d ORDER BY x;--result 1 4/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;--result 1 2 3/* Text comparison c='abc' is performed using the RTRIM collating sequence. */SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;--result 1 2 3/* Grouping is performed using the NOCASE collating sequence (Values** 'abc', 'ABC', and 'Abc' are placed in the same group). */SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;--result 4/* Grouping is performed using the BINARY collating sequence.  'abc' and** 'ABC' and 'Abc' form different groups */SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;--result 1 1 2/* Sorting or column c is performed using the RTRIM collating sequence. */SELECT x FROM t1 ORDER BY c, x;--result 4 1 2 3/* Sorting of (c||'') is performed using the BINARY collating sequence. */SELECT x FROM t1 ORDER BY (c||''), x;--result 4 2 3 1/* Sorting of column c is performed using the NOCASE collating sequence. */SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;--result 2 4 3 1

　　

I can finish it. Although it is not perfect, it is enough for me to use it. I will try again later... Forward please indicate the source: http://www.cnblogs.com/jycboy/p/android.html my CSDN blog: http://blog.csdn.net/jycboy