MySQL performance tuning (ii) the selected character type of the field type

Character type:

Char () is used to hold a fixed-length string with a maximum length of 255, and a value larger than the specified length is truncated, and a value smaller than the specified length is padded with spaces.

Varchar () is used to hold variable-length strings, up to a length of 65535, to store only the length that the string actually requires, but to store the length of the worth of bytes. If the maximum length of a column is less than or 255, 1 bytes is used, otherwise it is 2 bytes.

Char and varchar are also closely related to character encoding, GBK occupies 2 bytes and UTF8 occupies three bytes.

1. GBK Character Set

Parsing: Because the varchar type is longer than 255, the length of the value is stored in 2 bytes.

Calculation formula: (65535-2)/2 = 32766.5, that is to say, cannot be greater than 32767

2. UTF8 Character Set

Parsing: Because the varchar type is longer than 255, the length of the value is stored in 3 bytes.

Formula: (65535-2)/3 = 21844.3, which means no more than 21845

For multiple fields in a table:

Calculation formula: (65535-4-20*2+1-8-2)/2 = 32740, that is, the length cannot be greater than 32740.

"Summary": Under what circumstances are char and varchar used?

Frequently changing values, such as the length of a home address, are suitable for use with varchar.

For fixed and known values, such as the 32-bit character type after MD5, char (32), use char to save space. Because varchar also needs to use 1 bytes to store the length of the value.

MySQL performance tuning (ii) the selected character type of the field type