SQLite full data type (from: http://blog.csdn.net/jin868/article/details/5961263)

Generally, the static data type is fixed, while SQLite uses the dynamic data type, which is automatically determined based on the stored value. SQLite has the following five data types:

1. null: null.
2. Integer: A signed integer, depending on the size of the number to be saved.
3. Real: floating point number, which is stored as an 8-byte IEEE floating point number.
4. Text: String text.
5. Blob: binary object.

But in fact, sqlite3 also accepts the following data types:
Smallint is an integer of 16 bits.
An integer of 32-bit interger.
Decimal (P, S) P exact value and the decimal integer of s size. Exact value P refers to the number of digits after the decimal point. If not specified, the system is set to P = 5; S = 0.
Float 32-bit real number.
The real number of the double 64-bit element.
Char (n) n length string, N cannot exceed 254.
A string with an unfixed varchar (n) length and a maximum length of N. N cannot exceed 4000.
Graphic (n) is the same as char (N), but it is measured in double-bytes. N cannot exceed 127. This form supports two character-length fonts, such as Chinese characters.
A dual-character string with a variable vargraphic (n) length and a maximum length of N. N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp includes year, month, day, hour, minute, second, And 1‰ seconds.

Datetime contains the date and time format. It must be written as '2017-08-05 'and cannot be written as '2017-8-5'. Otherwise, an error will occur during reading!

Common SQLite Data Types,

This statement is problematic because SQLite is non-typed. this means that you can save any type of data to any column in any table you want to save, no matter what data type this column declares (only integer primary key is automatically incremented ). for SQLite, it is completely valid for fields without specifying the type. for example:

Create Table EX3 (A, B, C );

We recommend that you specify the data type in your create table statement even if SQLite allows data types to be ignored. because the data type is very useful for you to communicate with other programmers, or you are preparing to replace your database engine. SQLite supports common data types, such:

SQL code

1. Create Table ex2 (
2. A varchar (10 ),
3. B nvarchar (15 ),
4. C text,
5. D integer,
6. E float,
7. F Boolean,
8. G clob,
9. H blob,
10. I timestamp,
11. J numeric (10, 5 ),
12. K varying character (24 ),
13. L National varying character (16)
14. );

Differences between char, varchar, text, nchar, nvarchar, and ntext

1. Char. It is very convenient for Char to store fixed-length data, and the indexing efficiency of char fields is high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. If not, it is automatically filled with spaces.

2. varchar. Variable-length data is stored, but the storage efficiency is not as high as char. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as varchar (10. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation.

3. Text. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. nchar, nvarchar, and ntext. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains pure English characters and numbers, use Char/varchar.