SQLite Data Type Summary

SQLite Data Type Summary

1. Strictly speaking, SQLite has no data type. SQLite uses dynamic data types, that is: the type of data depends on the data itself, not its container (field)

2. Storage type (Storage Class): The representation of data after it is saved to a file. Including:

• Null-null value
• Interger-Signed integer type
• REAL-floating-point type
• TEXT-string (its encoding depends on the encoding of the DB)
• BLOB-Binary representation

3. Affinity type (type Affinity): Data in a column of a data table tendency to storage class

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

4. Claim type (declared type): The type of the column declared in the CREATE table/alter TABLE statement

Declared the relationship between type, type Affinity, Storage Class:

Declared Type	Type Affinity	Storage class (by Priority)
Int	Interger	(with numeric)
CHAR TEXT	TEXT	Text/null/blob
Blob (not specified)	NONE	(AS IS)
REAL FLOAT DOUBLE	REAL	(with numeric)
Other	NUMERIC	Integer/real/text/null/blob

Exception:

(1) Blob data is always saved as a blob of Storage Class

5. Comparison of data. Comparison operator (=,>,>= ... ), in, Sort by, and so on, follow these 5 steps to compare the left and right operands:

• Step 1: Determine the Type Affinity of the left and right operands according to the following rules:

1. Column name expression-the column's Affinity (see section 4th, table 2nd above)
2. CAST ... As ...-the specified Affinity (see table 2nd of the 4th section above)
3. Other cases-NONE Affinity

• Step 2:type Affinity Conversion. If the left and right operands are one of the following 3 type Affinity, the lower priority is converted to high priority in the following order of precedence:

1. Numeric Class (Integer/real/numeric)
2. TEXT
3. NONE

• Step 3: Determine its Storage Class by type Affinity and the actual type of data (see section 3rd of Table 4th above)

• Step 4: If the left and right operands belong to a different Storage Class, the comparison results are determined in the following order (from small to Large):

1. Null
2. Numeric Class (Integer/real)
3. TEXT
4. Blob

• Step 5: If the left and right operands belong to the same Storage Class, compare them according to the following rules

1. NULL-The left value is always less than the right value
2. Numeric Class (Integer/real)-Comparison by value
3. TEXT-character-by-word comparison
4. BLOB-Compare with memcmp () function

SQLite Data Type Summary