How to choose between MySQL CHAR and VARCHAR

VARCHAR and CHAR Types

VARCHAR and CHAR are the two main string types, used to store characters. Unfortunately, since the implementation depends on the storage engine, it is difficult to explain how these strings are stored on disk and in memory. In addition to the commonly used InnoDB and MyISAM, if you use other storage engines, you should read the documentation of the storage engine carefully.​

VARCHAR stores variable-length strings and is the most commonly used character data type. VARCHAR requires less storage space than fixed-length types, and it uses as little storage space as possible (for example, the space occupied by short character strings). For MyISAM, if ROW_FORMAT=FIXED is specified when creating a table, a fixed space will be used to store the fields, resulting in space waste. VARCHAR uses 1-2 extra bytes to store the length of the string: 1 byte if the maximum length is less than 255 bytes, 2 bytes if it is more. Therefore, a Latin character set VARCHAR(10) would use 11 bytes of storage, while a VARCHAR(1000) would use 1002 bytes of storage.​

VARCHAR can improve performance because it saves space. However, due to the variable length, the storage space of the data row will change when the data table is updated, which will bring additional overhead to a certain extent. If the length of the data row causes the original storage location to be unable to accommodate it, different storage engines will handle it differently. For example, MyISAM may generate fragmented data rows, while InnoDB requires disk paging to store updated data rows.​

In general, it is cost-effective to use VARCHAR if the largest column length is much longer than the average length (such as an optional memo field), and if the frequency of updates is low so that fragmentation is not a problem. It should be noted that if the UTF-8 character set is used, the actual byte length stored is determined by the character. For Chinese, the recommended storage character set is utf8mb4.​

The length of the CHAR type is fixed, and MySQL will allocate sufficient storage space for each field. When storing CHAR type values, MySQL removes any extra null characters that follow. Values ​​are aligned using null characters for comparison purposes. For short strings, CHAR is more advantageous, and if all values ​​are of approximately the same length, CHAR can be used. For example, it is more appropriate to use CHAR when storing the MD5 value of a user password, because the length of MD5 is always fixed. At the same time, for data types whose field values ​​change frequently, CHAR is more advantageous than VARCHAR because CHAR does not generate fragmentation. For very short data columns, using CHAR is more efficient than VARCHAR. For example, using CHAR(1) to store logical values ​​Y and N only requires 1 byte, while VARCHAR requires 2 bytes. It may feel strange to remove the null character. Let's take an example:

CREATE TABLE t_char_varchar_test (
  id INT PRIMARY KEY,
  char_col CHAR(10),
  varchar_col VARCHAR(10)
);

INSERT INTO t_char_varchar_test 
VALUES 
(1, 'string1', 'string1'),
(2, ' string2', ' string2'),
(3, 'string3 ', 'string3 ');

After inserting the above results into the data table, the leading spaces in string2 will not be removed, but when using CHAR type storage, the trailing spaces in string3 will be removed. Use the SQL query results to verify:

SELECT CONCAT("'", char_col, "'"), CONCAT("'", varchar_col, "'") 
FROM t_char_varchar_test WHERE 1

The results are as follows. You can see that the space after string3 of CHAR type is removed, but not of VARCHAR type. This situation will not cause any problems most of the time. In practice, the trim function is often used in applications to remove the empty characters at both ends. However, if you really need to store spaces, you need to be careful not to use the CHAR type:

How data is stored is determined by the storage engine, and storage engines handle fixed-length and variable-length data differently. The Memory engine uses fixed-size rows, so it needs to allocate the largest possible storage space - even if the data length is variable. However, string alignment and null truncation are done by the MySQL server and are therefore the same for all storage engines.​

Similar to CHAR and VARCHAR are BINARY and VARBINARY, which are used to store binary byte characters. BINARY is aligned using the byte value of character 0, and the value is not truncated when it is retrieved. If you need to use the byte value of a character instead of the character, it is more efficient to use BINARY because, on the one hand, you do not need to consider case when comparing, and on the other hand, MySQL only compares one byte at a time.​

Conclusion:

In actual data table design, VARCHAR is chosen in most cases, but VARCHAR requires an extra 1-2 bytes to store the string length. It should be noted that it is best to limit the maximum length of the field in the application, so that the data table can use the shortest VARCHAR possible to improve efficiency. At the same time, for character types with fixed length, very short length, or very small length changes, it is recommended to use CHAR class storage to improve storage efficiency.

The above is the details of the selection of MySQL CHAR and VARCHAR. For more information about MySQL CHAR and VARCHAR, please pay attention to other related articles on 123WORDPRESS.COM!