SQLite tutorial (7) : details of data types

Storage type and data type:

      SQLite divides the storage of data values into the following storage types:
 

     NULL:  Means that the value is NULL Value. 

     INTEGER:  Unsigned integer value. 

     REAL:  Floating point value. 

     TEXT:  A text string that is encoded in the following way UTF-8 , UTF-16BE , UTF-16LE . 

     BLOB:  storage Blob Data, this type of data is exactly the same as the input data. 

  

      because SQLite USES dynamic data types, while other traditional relational databases use static data types, that is, the data types that a field can store are determined when a table is declared, there is still a big difference in data storage. In SQLite, there are also some differences between storage categories and data types. For example, the INTEGER storage category can contain six different lengths of Integer data types. However, once the INTEGER data is read into memory, SQLite will treat it as an 8-byte unsigned integer. So for SQLite, even if the field type is specified in the table declaration, we can still store other types of data in that field. However, it is important to note that although SQLite provides us with this convenience, once we consider the portability of the database platform, we should ensure as much consistency in the storage and declaration of data types as possible in the actual development. Unless you have a very good reason to not consider porting your database platform, you can use this feature provided by SQLite.

1. Boolean data type:

      SQLite does not provide a specific Boolean storage type, but instead stores integer type 1 for true and 0 for false.

Date and time data type:

     , like the Boolean type, SQLite also does not provide a dedicated datetime storage type, but instead represents the type in different formats for TEXT, REAL, and INTEGER types, such as
 

    TEXT: "YYYY-MM-DD HH:MM:SS.SSS"

    REAL:  In order to Julian Date format store 

    INTEGER:  In order to Unix Saves data values in time form, that is, from 1970-01-01 00:00:00 Number of seconds to the current time. 

 

Ii. Type affinity:

To maximize data type compatibility between       and other database engines, SQLite proposes the concept of type affinity (Type Affinity). We can understand the type of "affinity", after the table fields are declared SQLite will according to a statement from the field type for selecting a genetic type, when the data into the data field will be preferred genetic types as a way to store this value, unless the genetic type does not match or cannot convert the current data to the genetic type, so SQLite would consider other more suitable for the type of the value to store the value. The current version of SQLite supports the following five kin types:

亲缘类型	描述
TEXT	数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER	对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE	不做任何的转换，直接以该数据所属的数据类型进行存储。

1. Rule for determining field affinity:

The affinity of the       field is determined by the type of the field that is defined at the time of declaration. The specific rules can be found in the following list. It is important to note the order of the following list, that is, if a field type conforms to both types of affinity, the first rule will come first.
      1). If the type string contains "INT", the affinity type of the field is INTEGER.
      2). If the type string contains "CHAR", "CLOB", or "TEXT", then the affinity type of the field is TEXT, such as VARCHAR.
      3). If the type string contains "BLOB", the affinity type of the field is NONE.
      4). If the type string contains "REAL", "FLOA", or "DOUB", then the genetic type of the field is REAL.
      5). The rest of the time, the affinity type of the field is NUMERIC.

      2. Examples:

声明类型	亲缘类型	应用规则
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

Note: in SQLite, the length information 255 of type VARCHAR(255) does not have any practical meaning and is intended to be consistent with the declarations of other databases.

3. Comparison:

The comparison expressions supported by       in SQLite3 are: "=", "==", "< ", " < =", " > ", " > =", "!=", " < > ", "IN", "NOT IN", "BETWEEN", "IS" and "IS NOT".
The comparison results of       data mainly depend on how the operands are stored. The rule is
      1). The value of NULL is smaller than that of other storage types.
      2). Values stored in INTEGER and REAL are smaller than values of TEXT or BLOB type. If they are both INTEGER or REAL, the comparison is based on numerical rules.
      3). Values stored as TEXT are less than values of type BLOB. If they are both TEXT, comparisons are made based on text rules (ASCII values).
      4). If two values of type BLOB are compared, the result is the result of the C runtime function memcmp().

operator:

All mathematical operators of       (+, -, *, /, %, <) < , > > , &, and |) converts the operands to the NUMERIC storage type before execution, even though data information may be lost during the conversion. Furthermore, if one of the operands is NULL, their result is also NULL. In a mathematical operator, if one of the operands does not look like a numeric type, they result in 0 or 0.0.