On the difference of Char, Varcahr in char, varchar, Nvarcahr and MySQL in SQL Server

Mysql:

Char defines stored characters, the actual storage is also stored as characters, size char (255 characters), note is not byte.

What is the difference between a character and a byte?

A byte is a unit of information stored in a computer, a byte is eight bits, a character is a, B, C, and some symbols, usually in a computer with a byte, that is, generally accounted for a byte, but there is not generally, according to the specific encoding each character occupies a different number of bytes, as in GBK, gb2312 A Chinese character occupies two bytes, but the international encoding format utf-8 a Chinese character in 3 bytes.

Therefore, Char can store up to 255*3 bytes of data in UTF8 encoding, whether it is a single Chinese character, or the English letter is a char, only the byte is different, Soga,char (255 characters),

Whether it is SQL Server or MySQL storage, if no 255 characters are stored, they will be filled in the form of a space character in English. This is called the fixed length.

Here's what it says. varchar (0-65532 bytes) defines a character, but is actually stored as a byte, storing max (65532) bytes, thus storing up to 65532/3-1 characters in UTF8 encoded form, under GBK encoding, Save up to 65532/2-1, why would I lose one? Because the MySQL mechanism will leave 1-3 bytes to store the length of the string. The result of this record length can be changed dynamically, without the need to fill the remaining space with a space like char, reduce memory consumption, but also increase the internal friction, affecting performance.

For example, define the Name field: varchar (60), fill it with data Zhang San, use only 6 bytes, the remaining 54 bytes are not occupied, the above example if the name is defined as char (60), the same fill data Zhang San, although also occupies 6 bytes, However, the remaining 58 characters will be filled with spaces, the actual space consumed by 60 characters

Sql server:

first talk about the difference between nvarchar and Varcahr:

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.

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

From the above can be seen nvarchar is stored as a character, because Unicode encoding, here you can look at this blog: various codes of the detailed: http://blog.csdn.net/lvxiangan/article/details/8151670

So it can be said that the nvarchar is a double-byte storage, whether you store the Chinese characters or the English alphabet is accounted for two bytes,

analysis of Pros and Cons:

Advantages: When judging the string can not consider the difference between the two characters, 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.

Char does not do a long explanation in accordance with the MySQL situation.

General usage notes:

The length of the stored data is determined such as phone number, encoding, etc. fixed, and does not pack Chinese, you can choose the char type.

Stored data length is uncertain, only the best use of English and digital varchar

The stored data length is indeterminate, and it is possible to have Chinese, optionally nvarchar type.

On the difference of Char, Varcahr in char, varchar, Nvarcahr and MySQL in SQL Server