Android SQLite Data Type

Http://dev.10086.cn/cmdn/wiki/index.php? Doc-view-4206.html

It's just a record of knowledge points, the problems you encounter when writing a project, and a record of each other...

SQLite Data Type

The biggest difference between SQLite and other common DBMS is its support for data types. Other common DBMS usually supports strong data types, that is, the type of each column must be specified in advance, but SQLite uses weak fields. In fact, there are only five types of internal storage:

Null: indicates a null value.

Integer: used to store an integer. The value can be 1, 2, 3, 4, 6, or 8 bits in size.

Real: IEEE floating point number

Text: stored by string

BLOB: stores data based on binary values without any changes.

Note that these types are the attributes of values rather than column attributes.

However, to be compatible with other DBMS (and SQL standards), you can specify the column type in the CREATE TABLE statement. Therefore, SQLite has a column similarity concept (column affinity ). column similarity is the attribute of a column. SQLite has the following column similarity:

Text: The text column uses null, text, or blob to store any data inserted into this column. If the data is a number, it is converted to text.

Numeric: the numeric column can use any storage type. It first tries to convert the inserted data to real or Integer type. If it succeeds, it is stored as real and integer type, otherwise, no change will be added to the storage.

Integer: similar to numeric, But it converts all values that can be converted to integer. If it is real and has no decimal part, it is also converted to integer.

Real: the real and numeric types only convert the values that can be converted to real and integer to real.

None: do not try to make any changes.

SQLite determines the similarity of each column based on the create table statement. The rules are as follows (Case Insensitive ):

1. If the data type includes int, It is integer.

2. If the data types include char, clob, and text, the data type is text.

3. If the data type includes blob or the data type is not specified, it is none.

4. If the data type includes real, floa, or doub

5. In other cases, numeric

From the above we can see that for SQLite, Char, varchar, nchar, nvarchar, etc. are all equivalent, and the maximum length is meaningless. However, it is not the same for other DBMS. In addition, column similarity only provides SQLite with a suggestion to store data. Even if the actual data type and column similarity are inconsistent, SQLite can still be inserted successfully, the following is an example to illustrate the above. Note that this example needs to be run in the SQLite command line. If it is executed in the SQLite expert tool, SQLite will perform some additional processing. Note that this feature of SQLite may cause some trouble to the ADO driver of SQLite, because. net is a strongly typed language, and the fields in the database must be converted to the appropriate type. Therefore, when inserting data, we should strictly follow the create
Insert data as defined in table.

(2) Auto-increment Column

In SQL Server, you only need to specify identity () to set the auto-incrementing column. However, this is not supported in SQLite. In SQLite, any table has an integer field type and is auto-incrementing. This column is used as the index of the B-tree and its name is rowid, as shown in:

Although the Test2 table only has one column, the rowid column still exists. In the program, you can use rowid as the auto-increment column for any table. However, this may cause incompatibility with other databases. In SQLite, if the declared type of a column is integer and it is the primary key, the column name becomes the alias of rowid. Note that the declared type must be integer rather than Int or bigint. For example:

Note that the last three statements in the preceding example show that the default auto-incrementing column algorithm of SQLite is to add 1 to the maximum number in the current table, this may result in the ID being reused-when the last data item is deleted. This is inconsistent with the behavior of the SQL Server identity column, for example:

SQL Server remembers the serial number of each insert, even if it has been deleted. To achieve the effect of SQL Server, you need to use the autoincrement keyword. For example:

However, the autoincrement keyword is not supported by SQL Server (I do not know whether this keyword exists in SQL 92). Similarly, the indentity keyword of SQL Server cannot be used in SQLite, because SQLite only requires that the declared type be integer to enable auto-incrementing column. Therefore, I have no idea how to make the scripts for database creation be used by the two databases without modification.

(3) date functions

The date functions of SQLite are quite distinctive. In essence, they call the strftime function of library C. The basic usage is as follows:

(4) unsupported features

User-Defined Functions and stored procedures

Foreign key constraints (but can be replaced by custom triggers)

Right out join, full out join

Grant revoke