Principles for selecting char and varchar data types in MySQL
- 黄舟Original
- 2017-03-01 13:34:291585browse
In many places, the storage engine is often ignored when analyzing the selection principles of char and varchar data types. In fact, we should determine how to choose the appropriate data type based on the selected storage engine.
Comparison of char and varchar
| Value | char(3) | Actual storage character length | varchar(3) | Actual storage 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 is of fixed length.
Char is a fixed-length type, varchar is a non-fixed-length type, and varchar is a variable-length type. Someone may ask why the length of varchar is larger than the data length? Because VARCHAR is saved with a one-byte or two-byte long prefix + data. If the declared length of the VARCHAR column is greater than 255, the length prefix is two bytes, otherwise it is one byte.
MyISAM Storage Engine
MyISAM data table, it is best to use fixed-length data columns instead of variable-length data columns.
InnoDB storage engine
It is recommended to use VARCHAR type.
For InnoDB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use head pointers pointing to data column values), so in essence, using fixed-length CHAR columns is not necessarily Simpler than using variable length VARCHAR columns. Therefore, the main performance factor is the total 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 total storage and disk I/O of data rows that need to be processed.
MEMORY Storage Engine
MEMORY data tables currently use fixed-length data row storage, so it does not matter whether you use CHAR or VARCHAR columns. Both are handled as CHAR types.
Misunderstanding
1. We all know that Chinese utf-8 occupies 3 bytes and gbk occupies two bytes, so can varchar(20) only store 20 characters? What about storing 6 Chinese characters?
Answer: This is not the case (it is said on the Internet that versions before 5.0 were like this, but there is no test). mysql varchar(20) can store 20 in both Chinese and English, but the maximum length of the varchar field depends on the encoding. If it is utf-8, then it is about 65535/3, if it is gbk, then it is about 65535/2
Many places often ignore the storage engine when analyzing the selection principles of char and varchar data types. In fact, we should determine how to choose the appropriate data type based on the selected storage engine.
The above is the content of the selection principles of char and varchar data types in MySQL. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!