MySql Performance Tuning (2) Selection of Field Types Character Types

MySql Performance Tuning (2) Selection of Field Types Character Types

Character Type:

Char () is used to save a string of a fixed length. The maximum length is 255. a value greater than the specified length will be truncated. A value smaller than the specified length will be filled with spaces.

Varchar () is used to save a variable-length string. The maximum length is 65535. It only stores the actual length required by the string, but 1 ~ 2 bytes to store the length. If the maximum length of a column is less than or 255, 1 byte is used; otherwise, 2 bytes is used.

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

1. GBK character set

Resolution: because the length of the varchar type is greater than 255, the length of the value must be 2 bytes.

Calculation formula: (65535-2)/2 = 32766.5, that is, it cannot exceed 32767

2. UTF8 Character Set <Strong? Http: www.bkjia.com kf ware vc "target =" _ blank "class =" keylink "> VcD4KPHA + pgltzybzcm9 "http://www.2cto.com/uploadfile/Collfiles/20150529/2015052910185613.jpg" alt = "\">

Resolution: because the length of the varchar type is greater than 255, the length of the value must be 3 bytes.

Calculation formula: (65535-2)/3 = 21844.3, that is, it cannot exceed 21845

For multiple fields in a table:

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

[Conclusion]: Under what conditions should char and varchar be used?

Frequently changing values, such as the length of the home address is different, it is appropriate to use varchar.

For fixed and known values, such as the 32-bit character type after md5 and char (32), char is used to save space. Because varchar also needs to store the value length in 1 byte.