Discussion on Char,varchar,nchar,nvarchar difference in SQL Server and MySQL

In a recent interview, the interviewer asked the difference between varchar and nvarchar, remembering the difference between fixed length and variable lengths, but failed to speak. Later, on the Internet to find the next user summary of the difference. Make a memo here:

One, the difference between Char,varchar,nchar,nvarchar in SQL Server (cited by: https://www.cnblogs.com/limeiky/p/5313312.html)

1, definition:

Char: fixed length, storing ANSI characters, insufficient complement English half-width space.

NCHAR: fixed length, storing Unicode characters, insufficient complement to English half-width space

VARCHAR: variable length, storing ANSI characters, automatically changing according to data length.

NVARCHAR: variable length, storing Unicode characters, automatically changing according to data length.

　　

nvarchar (n): 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.

Note: ANSI mainly stores data in single byte, which is generally suitable for English. Our commonly used Chinese characters need to be stored in two bytes, so we will use Unicode data type, otherwise the data read out may be garbled.

2, Difference:

① from the storage mode, nvarchar is stored by character, while varchar is stored in bytes;

② from storage, varchar saves space because the storage size is the actual length of bytes, while nvarchar is a double-byte storage;

③ in use, if the storage contents are English characters and no Chinese characters and other language symbols, the proposed use of varchar, including the use of Chinese characters nvarchar, because nvarchar is the use of Unicode encoding, that is, a unified character encoding standard, will reduce the chance of garbled;

④ If you do a project that may involve conversion between different languages, it is recommended to use nvarchar.

3, Advantages and disadvantages:

Nvarchar advantages: When judging the string can not consider the difference between the two characters in English, you can avoid the program garbled problem.

Disadvantage: Storing English characters will increase storage space by one more times. But given the low cost of storage, prioritizing compatibility gives you more benefits, without the efficiency of a varchar high.

4, why use nvarchar?

with n prefixes, n denotes Unicode characters, that is, all characters account for two bytes, Nchar,nvarchar

Character, the English characters only need one byte storage is sufficient, but the Chinese character is numerous, requires two bytes of storage, English and Chinese characters are prone to confusion, the Unicode character set is to solve the problem of incompatible character sets, all of its characters are represented by two bytes, That is, the English character is also represented in two bytes.

5, a brief introduction to Var:

With the Var prefix, it means that the actual storage space is getting longer, Varchar,nvarchar

The so-called length is fixed, when the input length of the data does not reach the specified length will be automatically filled with English space after it, so that the length of the corresponding length, and the variable length character data will not be filled with spaces, the exception is that the text storage is also variable length.

6, how do I use these types?

If you are sure of the length of the stored data and do not pack in Chinese, you can choose the char type.

If you are sure of the stored data length, but may include Chinese, you can choose the nchar type.

If you are unsure of the length of the stored data, store only English, the best use of the number varchar

If you are unsure of the length of the stored data, it is possible to have Chinese, you can choose the nvarchar type, and in SQL Server2005 is the more commonly used character data type.

Second, the difference between Char,varchar,nchar,nvarchar in MySQL (citation: http://blog.csdn.net/ivanluc/article/details/5174536)

Differences in usage of char, varchar, nvarchar data types in MySQL:

1, Char:
Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
2. VarChar:
Variable-length non-Unicode data with a maximum of 8,000 characters.
3, nvarchar:
Variable-length Unicode data with a maximum length of 4,000 characters.
4, nchar
Fixed-length Unicode data with a maximum length of 4,000 characters.
5, char and varchar are string types.
A Unicode-encoded string that results in an integer value of the character
Usage differences:
If the length of the text field is fixed, such as: Identity card number, do not use varchar or nvarchar, should be used char or nchar.
Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues
Text field if the length is not fixed, such as: address, then the use of varchar or nvarchar. In addition to saving storage space, access to the hard disk is also more efficient.

Discussion on Char,varchar,nchar,nvarchar difference in SQL Server and MySQL