sqlite3-Data types

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 the database engine) has one of the following storage classes: L NULL, the value is Nulll INTEGER, the value is a signed shape, and the value is 1,2,3,4,6 or 8 bytes in L REAL, The value is a floating-point value, with a 8-byte IEEE floating-point number holding the L text, the value being a text string, and using the database encoding (UTF-8,UTF-16BE or Utf-16le) to store the L BLOB, just a block of data, The storage analogy data type is more generalized, as you can see from the above input storage (that is, no change). 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, 0 is false,1 to true 1.2 date and time datatypesqlite do not have another storage class set for the storage date and times, The built-in SQLite date and time function can store the date and time in Text,real or integer as the IS08601 string ("Yyyy-mm-dd HH:MM:SS.SSS") L REAL from GMT November 24, 4174 B.C days since noon L INTEGER seconds from 1970-01-01 00:00:00 UTCThe program can optionally select these storage types to store the date and time, and can use the built-in date and time functions to freely convert 2.0 type approximations between these formats in order to maximize compatibility between SQLite and other databases, SQLite supports the idea of "type approximation" on the column, The type approximation of a column 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 TEXTL numericl integerl reall None columns with text approximation can store data in Null,text or BLOB type. If the value data is inserted into a column with a text approximation, it is converted to textual form before it is stored a column with a 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. 2.1 Columns Approximate determinant of the 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 this column is given an integer approximation <2> if the declaration type of this column contains "CHAR", "CLOB", or any of the "TEXT", then this column has TEXT approximation. Note that the type varchar contains a "CHAR" string, then it is given the text approximation <3> if the column's declaration type contains the string "BLOB" or is not declared as a type, the column is given a none approximation <4> other case, The column is given a numeric approximation of the above rule order is important for determining the approximation of the column. The declaration type of a column is "Charint" and matches the rules <1> and &LT;2&GT, but the first rule takes precedence so the approximation of this column will be an integer. 2.2 Approximate name example the following table shows how many common data type names from more traditional SQL operations, using the 5 rules in the previous section, are converted to approximate types. 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). An approximation of the example type name from the CREATE TABLE statement or the strongly-turned statement is used to determine the approximate rule intintegertinyintsmallintmediumintbigintunsigned BIG Intint2int8integer1character () VARCHAR (255) VARYING CHARACTER (255) NCHAR (in) NATIVE CHARACTER (+) NVARCHAR (100) Textclobtext2blobno datatype specifiednone3realdoubledouble precisionfloatreal4numericdecimal (10,5) BOOLEANDATEDATETIMENUMERIC5 Notice that the declaration 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 simply finds the declared type from the declaration type string, such as "Xint" will be given an integer approximation because the string contains "INT", so there is no need for a separateThe correct claim type, but as long as the declaration type string contains the type of declaration that SQLite knows about it) 2.3-column approximation operation example create TABLE T1 (t text,--text affinity by rule 2 Nu nu MERIC,--numeric affinity by rule 5 I integer,--integer affinity by rule 1 R REAL,--Real affinity by Ru Le 4 no BLOB--no affinity by rule 3); Here the type of the column is determined according to the type of declaration of the 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|integer|real|textdelete 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|integer|real|realdelete 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|integer|real|integer (fourth value here , the corresponding column is real approximate, the transferred value is shaped, but according to real approximate rules it will convert it to real data)//Data block (BLOB) regardless of what column approximation has been saved as BLOB type Delete from T1;insert to 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;//result: Blob|blob|blob|blob|blob// Nulls is also not affected by the column approximation of the delete from T1;insert to 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 Expression Sq Lite V3 has a number of useful comparison operators, including "=", "= =", "<", "<=", ">", ">=", "! =", "<>", "in", "not", "between", "is", and " Is the result of the sort comparison operation of 3.1 based on the storage type of the operand, according to the following rule: 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 less than any text or blob value. When an integer or real value is compared to another integer or real value, the numeric comparison is performed, and the value of the L text is less than the BLOB value. When the two text values are compared, the result is determined by the comparison of the sequence L when two blob values are compared, using memcmp to determine the approximation of the result 3.2 comparison operand (Affinity) SQLite may be in integer,real or T before performing a comparison Convert the comparison value between ext. Whether the conversion occurs before the comparison operation is based on the approximation (type) of the operand. The operand approximation (type) is determined by the following rules: l The expression for a simple reference to a column has the same affinity as this 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 a "CAST (expr as Type) "In the form of an expression with the same affinityl as the column with the declaration type" type ", an expression of none affinity 3.3 before the comparison of the type conversion only when the conversion is lossless, reversible" apply approximation "means the operand is convertedTo a specific storage class.  Approximately before comparison is applied to the operands of the comparison, followed by the following rules (in 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 another operand l other case, no approximation is applied, and two operands are compared in the same way as the expression "a between B and C" represents two separate two value comparisons "a >= B and A <= C ", even if the different approximations in both comparisons are applied to ' a '. 3.4 Comparison Example Create TABLE T1 (a text,--text affinity b NUMERIC,--NUMERIC affinity C BLOB,--No Affini Ty 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" have text Affinit Y, numeric values on the--right-hand +side of the comparisons is converted to text before--the comparison occurs. SELECT a < B, a <, a < 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 < ' t1;0|1|1--Column "B" has numeric affinity and so numeric affinity are applied--to the operands in 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 < b <, b < t1;0|0|1--Numeric affinity is applied to operands on the right, convert  ing them--from the 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 have--storage class INTEGER which is always less than the TEXT values--on the left.  SELECT C < b, C <, C < 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 have--storage class INTEGER which compare numerically with the integer--values on the left. SELECT D < A, d <, D < from t1;0|0|1--No affinity conversions occur. INTEGER values on the left are--always less than TEXT values in the right. SELECT D < ' + ', D < ' + ', D < ' + ' from t1;1|1|1 it can be seen from here that if you can compare the rules in 3.1, you do not need to type conversion, otherwise you will have to type 4.0 operator all Mathematical operators (+,-, *,/,%, <<, >>, &, |), all two operands are converted to the numeric storage type (integer and real) before being executed. 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.

　　

sqlite3-Data types