The difference between char, varchar, nvarchar, and text in SQL

Char

Char is fixed-length, that is, when you enter a character that is less than the number you specify, char (8), the character you enter is less than 8 o'clock, and it will then fill the empty value back. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds.

nvarchar (n)The middle of the N represents the number of characters, not the number of bytes

A variable-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered. The input data character length can be zero.

VARCHAR (n)

Variable-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. Storage size is the actual length of bytes of input data, not n bytes. The input data character length can be zero.

Text

Takes up space by the number of characters and stores the data in 2-byte records, which 2 bytes do not occupy the space of the text data. There is no space to waste. Slow, especially when creating temporary tables is a tragedy.

Like what:

char (10), save ' AB ' to occupy 10byte, because the deposit is ' AB ', followed by 8 spaces.

varchar (10), save ' AB ' to occupy 3byte, data accounted for 2 characters, another byte to record data size

Speed

1, char, fixed length, basically no fragmentation, indexing speed extremely fast.

2, varchar, indefinite length, index speed no char fast. It is theoretically possible to add all indexes, but when the data is too large, the index intercepts the previous part of the data.

3, text, indefinite length, slow speed, index can only be prefix index.

Storage limits

1, char (n), n Max 255.

2, varchar (n), n maximum 65535, in addition, according to the character set, cannot exceed 65525 bytes. These 65535 bytes cannot be used to store data, because there are 1-2 bytes to be used to save the length, 255 bytes for the 1 bytes of storage length, and 255 bytes for storage length of 2 bytes.

3, text, the upper limit of 65535 bytes, and then more can be saved, because there is mediumtext upper limit 2^24-3 bytes probably 16m,longtext the upper limit 2^32-4 bytes about 4G.

Automatically remove trailing spaces

1, char (n), will remove the trailing space

2, varchar (n), do not remove the trailing space

3, text, will not remove the trailing space

Like what:

1, char (10), Save the string ' AB ' (a space), save to the database is ' AB ' (there are 8 spaces), query out is ' AB ' (no space), accounting for 10byte.

2, varchar (10), Save the string ' AB ' (there is a space), stored in the database is ' AB ' (there are 1 spaces), query out is also ' AB ' (a space), accounting for 3byte.

Record the size of the data

1, varchar (n), occupies space by character length, but will have 1-2 bytes to record the data size.

2, text, also occupies space by the character length, but records in the data outside, does not occupy the data space.

Attention

1, storage fixed length string, as far as possible with Char, index speed extremely fast.

2, the length of more than 255 string, only with varchar and text.

3, you can use varchar do not use text.

4. Text cannot set the default value.

5. Unlike fields such as char or varchar, the content stored in text does not exist with the row data, but the database is stored in a different place, and the database has its own pointer. It is said that varchar (255+) is also such a deposit.

6, the text upper limit is high, the security needs to notice, in the abnormal state may store very big data, causes many problems, even if with varchar (10000) can give truncation.

7, the length of the theory varchar maximum 65535 characters, but can not be set up also depends on the character set, because MySQL has a rule, in addition to text and blob type, the single field length can not exceed 65535 bytes, so if the character set is the most basic latin1, One character at a byte, it is possible to define varchar (65535), but if set to UTF8, a character of 3 bytes, then only varchar (21485) can be defined.

Summarize

1, Char:char storage of fixed-length data is very convenient, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store the data reached 10 bytes, to take up 10 bytes of space.

2, VARCHAR: Storage variable-length data, but the storage efficiency is not high 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 store variable-length non-Unicode data 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.

The difference between char, varchar, nvarchar, and text in SQL