SQLite Data Type Summary

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.