SQLite learning Manual (data type)

Source: Internet
Author: User

I. Storage types and data types:

SQLite divides data values into the following Storage types:
Null : Indicates that the value is null.
Integer : Unsigned integer value.
Real : Floating point value.
Text : Text string that stores the encoding method used for UTF-8, UTF-16BE, UTF-16LE.
Blob : Stores BLOB data, which is exactly the same as input data.
Since SQLite uses dynamic data types, other traditional relational databases use static data types, that is, the data types that can be stored by fields are determined when the table is declared, therefore, there is a big difference between them in terms of data storage. In SQLite, storage classes and data types are also different. For example, an integer storage class can contain six Integer Data Types of different lengths, however, once the integer data is read into the memory, SQLite will regard it as occupying 8 bytes of unsigned integer. Therefore, for SQLite, even if the field type is specified in the Table Declaration, we can still store other types of data in this field. However, we need to note that, although SQLite provides us with this convenience, once we consider the portability of the database platform, in actual development, we should try our best to ensure consistency of data type storage and declarations. Unless you have a very good reason and no longer consider porting the database platform, you can use this feature provided by SQLite in this case.
1. boolean data type:
SQLite does not provide a special Boolean storage type. Instead, the storage integer 1 indicates true, and 0 indicates false.

2. Date and Time data types:
Like the boolean type, SQLite does not provide a special date and time storage type, but it is represented in different formats of the text, real, and integer types, such:
Text: "YYYY-MM-DD hh: mm: Ss. Sss"
Real: stored in Julian Date Format
Integer: the number of seconds that the data value flows from 00:00:00 to the current time.

Ii. Type affinity:

To maximize the compatibility between SQLite and other database engines, SQLite proposes the concept of "type affinity. We can understand "type affinity" in this way. After a table field is declared, SQLite selects a kinship type based on the type declared for this field. When data is inserted, the data of this field will take the kinship type as the storage method of this value first, unless the kinship type does not match or the current data cannot be converted to this kinship type, in this way, SQLite will consider other types that are more suitable for storing this value. Currently, SQLite supports the following five kinship types:

Kinship type Description
Text Before the numeric data is inserted, it must be converted to the text format before being inserted into the target field.
Numeric When text data is inserted into a field with the affinity of numeric, if the conversion operation does not cause data loss or completely reversible, SQLite converts the text data to integer or real data. If the conversion fails, SQLite will still store the data in text format. For new data of the null or blob type, SQLite will not perform any conversion and store the data directly in the null or blob mode. It should be noted that for constant text in floating-point format, such as "30000.0", if the value can be converted to an integer without losing the value information, then SQLite converts it to an integer storage method.
Integer For fields with an integer kinship, the rule is equivalent to numeric. The only difference is that when the cast expression is executed.
Real The rule is basically equivalent to numeric. The only difference is that text data such as "30000.0" is not converted into integer storage.
None Without any conversion, the data type to which the data belongs is stored directly.

1. rules for determining field affinity:
The affinity of a field is determined by the type defined when the field is declared. For specific rules, refer to the following list. Note that the order of the following list is that if a field type meets both affinity, the rule that comes first takes effect.
1). If the type string contains "int", the kinship type of this field is integer.
2) If the type string contains "char", "clob", or "text", the kinship type of this field is text, such as varchar.
3) if the type string contains "blob", the kinship type of this field is none.
4). If the type string contains "real", "floa", or "doub", the kinship type of this field is real.
5) in other cases, the kinship type of the field is Numeric.

2. Example:

declaration type kinship type application rules
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 of Type varchar (255) 255 does not have any practical significance, just to ensure the Declaration consistency with other databases.

3. Comparison expression:

The supported comparison expressions in sqlite3 are: "=", "=", "<", "<=", ">", "> = ","! = "," <> "," In "," not in "," between "," is "and" is not ".
The comparison result mainly depends on the Storage Method of the operands. The rule is as follows:
1) The value of the storage method null is smaller than the value of other storage types.
2) the storage method is that the values of integer and real are smaller than the values of the text or blob type. If they are both integer or real, the comparison is based on the numerical rules.
3). The storage method is that the value of text is smaller than the value of Blob type. If it is the same as text, it is compared based on text rules (ASCII value.
4) if two blob-type values are compared, the result is the result of the memcmp () function in the C runtime.

Iv. operators:

All mathematical operators (+,-, *,/, %, <, >>, &, and |) convert the operands to the numeric storage type before execution, even in the conversion process, data information may be lost. In addition, if one of the operands is null, their results are also null. In mathematical operators, if one of the operands does not look like a numerical value, the result is 0 or 0.0.

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.