The difference between char, varchar, nchar, nvarchar and text
1. Those with var prefix indicate actual storage space It is variable length, varchar, nvarchar
The so-called fixed length means that the length is fixed. When the input data length does not reach the specified length, it will automatically Fill with English spaces after it to make the length reach the corresponding length. When the characters you enter are greater than the specified number, it will intercept the excess part; and variable-length character data It will not be filled with spaces. The exception is that text is also stored in variable length.
2.n represents Unicode characters, that is, all characters occupy two bytes, nchar, nvarchar,
## Among the
# characters, English characters only need one byte to store, but there are many Chinese characters, which require two bytes to store. When English and Chinese characters exist at the same time, it is easy to cause confusion. The Unicode character set is to solve the problem of character sets. Caused by incompatibility issues, it All characters are represented by two bytes, that is, English characters are also represented by two bytes
3. Based on the above two points, let’s look at the field capacity
char(n), varchar(n) | up to 8000 English, 4000 Chinese characters | n represents the actual length of the bytes of the input data |
nchar(n), nvarchar(n) | can Store 4000 characters, regardless of English or Chinese characters | n represents the number of characters |
4. Use (personal preference)
a. If the amount of data is very large, the length can be 100% determined and only ansi characters are saved, then char
b. If you can determine the length but it is not necessarily ansi characters, then use nchar;
c. For very large data, such as article content, use nText
d. Other general nvarchar
Feature comparison
1, CHAR . CHAR is very convenient for storing fixed-length data. The index on the CHAR field is highly efficient, such as defining char (10), then no matter whether the data you store reaches 10 bytes, it will occupy 10 bytes of space.
#2. VARCHAR. Store variable-length data, , but the storage efficiency is not as high as CHAR, if the possible value of a field is not It is of fixed length. We only know that it cannot exceed 10 characters. It is most cost-effective to define it as VARCHAR(10). The actual length of the VARCHAR type is the actual length of its value + 1. Why "+1"? This byte is used to save the actual length used.
From a space perspective, varchar is appropriate; from an efficiency perspective, char is appropriate. The key is to find the trade-off point based on the actual situation.
3. TEXT. text stores variable-length non-Unicode data, with a maximum length of 2^31-1 (2,147,483,647) characters.
4, NCHAR, NVARCHAR, NTEXT. These three types have an extra "N" in their names than the previous three. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether English or Chinese characters; while 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 don't have to worry about whether the input characters are English or Chinese characters, which is more convenient, but there is some loss in quantity when storing English.
#So generally speaking, if it contains Chinese characters, use nchar/nvarchar, and if it contains pure English and numbers, use char/varchar.
The above is the detailed content of What are the differences between char, varchar, nchar, nvarchar and text?. For more information, please follow other related articles on the PHP Chinese website!
