The difference between CHAR and VARCHAR in MySQL-Mysql Tutorial-php.cn

Foregoing

VARCHAR and CHAR are the two main string types. Unfortunately, it is difficult to explain exactly how these values are stored on disk and in memory, as it depends on the specific implementation of the storage engine. The following description assumes that the storage engine used is InnoDB and/or MyISAM. If you are not using these two storage engines, please refer to the documentation of the storage engine you are using.

Let’s first look at how VARCHAR and CHAR values are usually stored on disk. Please note that the way the storage engine stores CHAR or VARCHAR values may be different in memory than on disk, so the value read by the MySQL server from the storage engine may need to be converted to another storage format.

VARCHAR type

The VARCHAR type is used to store variable-length strings and is the most common string data type. It is more space-efficient than fixed-length types because it uses only the necessary space (for example, shorter strings use less space). There is an exception. If the MySQL table is created with ROW_FORMAT=FIXED, each row will use fixed-length storage, which will waste space.

VARCHAR requires 1 or 2 extra bytes to record the length of the string: if the maximum length of the column is less than or equal to 255 bytes, only 1 byte is used to represent it, otherwise 2 bytes are used. Assuming the latin1 character set is used, a VARCHAR(10) column requires 11 bytes of storage space. The VARCHAR(1000) column requires 1002 bytes because 2 bytes are needed to store the length information.

VARCHAR saves storage space, so it also helps performance. However, because the row is variable-length, the row may become longer than the original during UPDATE, which requires additional work. If the space occupied by a row grows and there is no more space to store in the page, different storage engines handle this situation differently. For example, MyISAM will split the rows into different fragments for storage, and InnoDB needs to split the page so that the rows can fit into the page. Some other storage engines may never update data in the original data location.

VARCHAR applicable situations

It is appropriate to apply VARCHAR in the following situations:

The maximum length of the string column is much larger than the average length

Columns are rarely updated, so fragmentation is not an issue

A complex character set like UTF-8 is used, each character is stored using a different number of bytes

CHAR type

The CHAR type is fixed-length: MySQL always allocates enough space according to the defined string length. When storing CHAR values, MySQL removes all trailing spaces. The CHAR value will be padded with spaces as needed to facilitate comparison.

CHAR is suitable for storing very short strings, or all values are close to the same length. For example, CHAR is ideal for storing the MD5 value of a password because it is a fixed-length value. For data that changes frequently, CHAR is also better than VARCHAR because the fixed-length CHAR type is not prone to fragmentation. CHAR is also more storage space efficient than VARCHAR for very short columns. For example, CHAR(1) is used to store only Y and N values. If a single-byte character set is used, only one byte is needed, but VARCHAR(1) requires two bytes because there is an extra byte for the record length. .

Test

The following is an example to illustrate the difference in behavior between CHAR and VARCHAR. First, we create a table with only one CHAR(10) field, and Insert some values into it:

CREATE TABLE char_test ( char_col CHAR(10) ); INSERT INTO char_test VALUES ('string1'). (' string2'). ('string3 ');

Copy after login

When we retrieve these values, we will find that the spaces at the end of string3 are truncated.

SELECT CONCAT("'", char_col, "'") FROM char_test;

Copy after login

Execution results:

The difference between CHAR and VARCHAR in MySQL

If you use the VARCHAR(10) field to store the same value, you can get the following results:

CREATE TABLE varchar_test ( varchar_col VARCHAR(10) ); INSERT INTO varchar_test VALUES ('string1'). (' string2'). ('string3 '); SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;

Copy after login

Execution Result

The difference between CHAR and VARCHAR in MySQL

The difference between VARCHAR(5) and VARCHAR(200)

If we use VARCHAR(5) and VARCHAR(200) ) to store 'hello', we know that the space overhead of the two is the same. So can we keep the length of VARCHAR always large? Are there any advantages to using shorter columns?

Facts have proved to have great advantages. Longer columns consume more memory because MySQL typically allocates fixed-size blocks of memory to hold internal values. This is especially bad when using in-memory temporary tables for sorting or operations. It's equally bad when sorting using disk temporary tables.

So the best strategy is to allocate only the space you really need.

Summary

When we select a type for a string type field, to determine whether to choose VARCHAR or CHAR, we can consider the following aspects:

Is there a very small difference between the average length and the maximum length of the field data set? If the difference is small, the CHAR type will be given priority. Otherwise, the VARCHAR type will be considered.

If the field stores a hash value after MD5, or some fixed-length value, the CHAR type is preferred.

If the field needs to be updated frequently, the CHAR type is given priority. Since the CHAR type is of fixed length, it is not prone to fragmentation.

For field values that store very small information, such as gender, the CHAR type is preferred, because the VARCHAR type will occupy additional bytes to store string length information.

In a word, when we can choose the CHAR type, or when space consumption is relatively not the focus of the influencing factors, try to choose the CHAR type, because in other aspects, the CHAR type has more or less The advantages. When space consumption becomes a big influencing factor, we will consider using the VARCHAR type.