The difference between varchar and char in MySQL

Tag: Equals tab div Create specifies charset size fixed code

The relationship between character and Byte and encoding

ASCII code, an English letter (not case) occupies a byte of space, a Chinese character occupies two bytes of space. A binary number sequence, which is used as a digital unit in a computer, typically a 8-bit binary number, converted to decimal. The minimum value is 0, and the maximum value is 255.

In UTF-8 encoding, an English character equals one byte, and a Chinese (with traditional) equals three bytes.

In Unicode encoding, one English equals two bytes, and one Chinese (with traditional) equals two bytes.

Two, the difference between varchar and char

char fixed length type:char (m) Type of data column, each value occupies M bytes, if a length of less than M,mysql will be on its right with a space character to complement. (The space characters that are filled out in the retrieval operation will be removed).
varchar Variable-length type: in a varchar (M) Type of data column, each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes)

A rule used in MySQL to determine whether a data column type conversion is required

• In a data table, if the length of each data column is fixed, then the length of each data row will be fixed.

• As long as the data table has a variable length of data column, the length of each data row is variable.

• If the length of the data row in a table is variable, MySQL converts the data column of the fixed-length type in the table to the corresponding variable-length type in order to save storage space. exception: A char data column that is less than 4 characters long is not converted to a varchar type

In MySQL, both char and varchar represent string types. But they do not store and retrieve data in the same way.

When declaring char and varchar types in a table structure definition, you must specify the length of the string. That is, how many characters (not bytes, characters) can be stored in the column. For example: char (10) and varchar (10) can store 10 characters.

The length of the column declared as char is fixed, and the length of the char is selectable in the range of 0-255. That is, char can store up to 255 characters. If the column is UTF8 encoded, the number of bytes that the column occupies = the number of characters 3. If it is GBK encoding, the number of bytes that the column occupies = the number of characters 2.

The length of the column declared as varchar is variable, and the mysql5.7.2 varchar has a length range of 0-65535 bytes. storing data in varchar type requires 1-2 bytes (2 bytes longer than 255) To store the actual length of the string. If the column is encoded as GBK, each character occupies a maximum of 2 bytes and the maximum length cannot exceed 32,766 characters. If the column is encoded as UTF8, each character is up to 3 bytes and the maximum character length is 21845.

Summarize:

1. The biggest difference between char and varchar is that one is fixed length and one is variable length. Because it is variable length, it stores the actual string plus a byte of the length of the record string. If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is cropped.

2. varchar (m) and char (m), m all represent the number of characters. VarChar has a maximum length of 65,535 bytes (character length of UTF8 21845), and different encodings correspond to a maximum number of characters that can be stored. Char can hold up to 255 characters, The maximum number of bytes available for different encodings is different.

Examples of calculation methods under UTF8 and GBK coding

GBK: If a table has only one varchar type, as defined as

CREATE table t4 (c varchar (N)) CHARSET=GBK;

The maximum value for n here is (65535-1-2)/2= 32766.

The reason for minus 1 is that the actual row storage starts with the second byte ';

The reason for minus 2 is that the 2 bytes of the varchar header represent the length;

The reason for the addition of 2 is that the character encoding is GBK.

UTF8 under:
CREATE table t4 (c int, C2 char (+), C3 varchar (N)) Charset=utf8;

The maximum value of n here is (65535-1-2-4-30*3)/3=21812

Minus 1 and minus 2 are the same as in the previous example;

The reason for minus 4 is that the int type C accounts for 4 bytes;

The reason for reducing 30*3 is that char (30) occupies 90 bytes and the encoding is UTF8.

The difference between varchar and char in MySQL