Character types in the database store the number of characters and Chinese characters

Time:2020-2-13

Sqlserver2012 (936 simplified Chinese GBK) as an example:

For example:

Varchar (10), can only store 10 English characters or numbers, can only store 5 Chinese characters;

Char (10), can only store 10 English characters or numbers, and can only store 5 Chinese characters;

Nvarchar (10), which can store 10 English characters or numbers, can also store 10 Chinese characters;

Nchar (10) can store 10 English characters or numbers as well as 10 Chinese characters;

Varchar (max), large value data type can store up to 2 ^ 30-1 bytes of data (varchar (max), nvarchar (max), varbinary (max))

Summary:

Unicode character set is to solve the problem of character set incompatibility. All its characters are represented by two bytes, that is, English characters are also represented by two bytes.
The prefix n represents Unicode characters, such as nchar and nvarchar, which use the Unicode character set.

 

Exceeding the size will prompt an error.

Supplementary notes:

The difference between varchar and nvarchar in SQL Server:
1. Varchar is stored according to the actual byte length, 1 Chinese character 1 byte, 1 English 1 byte, the length is between 1 and 8000, and the storage size is the actual length of the bytes of the input data
2. Nvarchar is stored according to the number of characters. No matter Chinese or English, it is 2 bytes, with a length between 1 and 4000. The storage size is twice the number of characters entered (n prefix, n represents Unicode character, that is, all characters occupy two bytes)
3. In terms of storage mode, nvarchar is stored by character, while varchar is stored by byte
4. Considering the storage capacity, varchar saves space, because the storage size is the actual length of bytes, and nvarchar is double byte storage
5. If your project may involve the conversion between languages in different countries, nvarchar is recommended, because nvarchar is encoded in Unicode, which will reduce the probability of garbled code
6. Char / nchar fixed length data type, insufficient space for half width in English.

Len() function: returns the number of characters (not bytes) of a given string expression, without trailing spaces. (len only returns the number of characters, a Chinese character represents a character)
Datalength() function: returns the number of bytes occupied by any expression. (datalength returns the number of bytes, one Chinese character and two bytes)
Len() does not contain spaces in length, while datalength() contains spaces.

 

mysql:

In version 5.5.49, when the character set is UTF-8, the number of Chinese characters and English stored in char (10) and varchar (10) is 10