Sqlite Data types

Regular data uses fixed static data types, and SQLite uses Dynamic data types, which are automatically judged based on the value of the deposit. SQLite has the following five types of data:

1.NULL: null value.
2.INTEGER: A signed integer, depending on the size of the range in which the number is deposited.
3.REAL: Floating point number, stored as 8-byte IEEE floating-point numbers.
4.TEXT: string literal.
5.BLOB: Binary object.

But in fact, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
Decimal (P,S) p exact value and S-Size decimal integer, the exact value p is the total number (digits) size value, s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.

DateTime contains date-time format, which must be written as ' 2010-08-05 ' cannot be written as ' 2010-8-5 ', otherwise it will produce an error when read!

sqlite Common data Types ,

This sentence itself is problematic, because: SQLite is untyped. This means that you can save any type of data to any column of any table you want to save, regardless of what type of data the column declares (only automatically incrementing the integer Primary key is useful). It is completely valid for SQLite to not specify a type for a field. Such as:

Create Table ex3 (A, B, c);

Even if SQLite allows data types to be ignored, it is still recommended that you specify the data type in your CREATE TABLE statement. Because the data type is very useful for you to communicate with other programmers, or you are ready to replace your database engine. SQLite supports common data types, such as:

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. );

The difference between char, varchar, text, and nchar, nvarchar, ntext

1, CHAR. Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, take up 10 bytes of space, insufficient to automatically fill with spaces.

2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

3, TEXT. Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

4, NCHAR, NVARCHAR, NTEXT. These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. It can be seen that the use of nchar, nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.

So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar

Sqlite Data types