What data types does SQLite support?

SQLite supports multiple data types and uses a dynamic type system:

1. Storage Classes

   • NULL: Null value
   • INTEGER: Signed integer, automatically chooses 1, 2, 3, 4, 6, or 8 bytes for storage based on value size
   • REAL: Floating point number, 8-byte IEEE floating point number
   • TEXT: String, stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE)
   • BLOB: Binary Large Object, stored exactly as input

2. Type Affinity SQLite uses type affinity to handle data types instead of strict type checking:

   • TEXT: Used for storing strings, uses TEXT storage class
   • NUMERIC: Values that may contain integers or floating point numbers, converted to INTEGER or REAL
   • INTEGER: Similar to NUMERIC, but prioritizes conversion to INTEGER
   • REAL: Similar to NUMERIC, but prioritizes conversion to REAL
   • NONE: No type conversion performed

3. Dynamic Type Characteristics

   • Can store different types of data in the same column
   • When inserting data, SQLite selects the appropriate storage class based on the value type and column type affinity
   • Data types can be dynamically converted during queries

4. Type Conversion Rules

   • When inserting text into a column with INTEGER affinity, SQLite attempts to convert it to an integer
   • When inserting an integer into a column with REAL affinity, it converts to a floating point number
   • If conversion fails, the original value is stored

This flexible type system allows SQLite to adapt to various application scenarios, but also requires developers to pay more attention to data type consistency.