The selection principle of char and varchar data types in MySQL

Many places in the analysis of char and varchar data types are chosen in principle, often ignoring the storage engine. In fact, we should determine how to choose the appropriate data type based on the selected storage engine.

Char vs. varchar comparison

value	char (3)	actual stored character length	varchar (3)	actual stored character length
”	’ ‘	3	”	1
' AB '	' AB '	3	' AB '	3
' ABC '	' ABC '	3	' ABC '	4
' ABCDEFG '	' ABC '	3	' ABC '	4

Summary: As can be seen from the above table, char belongs to the fixed length.
Char is a fixed-length type, and varchar is a non-fixed-length type, and varchar belongs to variable length. One might ask why is the length of the varchar larger than the length of the data? Because varchar is saved with a byte or two bytes long prefix + data. If the varchar column declaration is longer than 255, the length prefix is two bytes, otherwise it is a byte.

MyISAM Storage Engine

MyISAM data tables, it is best to use fixed-length data columns instead of variable-length data columns.

InnoDB Storage Engine

It is recommended to use a varchar type.
For INNODB data tables, the internal row storage format does not differentiate between fixed-length and variable-length columns (all data rows use the head pointer pointing to data column values), so in essence, using fixed-length char columns is not necessarily simpler than using variable-length varchar columns. Thus, the main performance factor is the total amount of storage used by the data rows. Since Char takes up more space on average than varchar, it is better to use varchar to minimize the amount of storage and disk I/O for the rows of data that need to be processed.

Memory Storage Engine

Memory data tables are currently stored using fixed-length data rows, so there is no relationship between using char or varchar columns. Both are handled as char types.

Misunderstanding

1. As we all know, Utf-8 's Chinese account for 3 bytes, gbk two bytes, then varchar (20) is not only storage 20 bytes, that is, store 6 characters?
A: Not like this, (Online said 5.0 before the version is this, no test). MySQL varchar (20) Whether Chinese or English can save 20, but the maximum length of the varchar field is related to encoding, if it is utf-8, then about 65535/3, if it is GBK, then about 65535/2

The selection principle of char and varchar data types in MySQL