Difference between varchar and char types in MySQL

aforementioned

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 this 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 typically stored on disk. Note that the storage engine may store CHAR or VARCHAR values ​​differently in memory and on disk, so the value read from the storage engine by the MySQL server 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 one exception. If the MySQL table is created using ROW_FORMAT=FIXED, each row will be stored using a fixed length, 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, a VARCHAR(10) column requires 11 bytes of storage space. A 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 rows are variable-length, an UPDATE may make the row longer than it was before, which requires extra work. If a row grows in size and there is no more room to store it on the page, different storage engines handle this situation differently. For example, MyISAM will split the row into different fragments for storage, while InnoDB needs to split the page so that the row can fit into the page. Some other storage engines may never update data in-place.

VARCHAR applicable situations

VARCHAR is appropriate in the following cases:

• The maximum length of a string column is much larger than the average length
• Updates to the column are rare, so fragmentation is not an issue
• When using complex character sets like UTF-8, each character is stored using a different number of bytes.

CHAR Type

The CHAR type is fixed-length: MySQL always allocates enough space for the defined string length. When storing CHAR values, MySQL removes all trailing spaces. CHAR values ​​are padded with spaces as necessary to facilitate comparison.

CHAR is suitable for storing very short strings, or when all values ​​are close to the same length. For example, CHAR is very suitable for storing the MD5 value of a password because it is a fixed-length value. CHAR is also better than VARCHAR for data that changes frequently, because fixed-length CHAR types are less likely to be fragmented. For very short columns, CHAR is also more efficient in storage space than VARCHAR. For example, using CHAR(1) to store values ​​containing only Y and N only requires one byte if a single-byte character set is used, but VARCHAR(1) requires two bytes because there is an extra byte for the record length.

test

The following example illustrates 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');

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

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

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

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

Execution Results

Differences 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 make the length of VARCHAR always remain large? Is there any advantage to using shorter columns?

It turns out there are huge advantages. Longer columns consume more memory because MySQL usually allocates fixed-size blocks of memory to hold internal values. This is especially bad when sorting or manipulating temporary tables in memory. It is equally bad when sorting using disk temporary tables.

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

Summarize

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

• Check whether the average length of the field data set is very close to the maximum length. If the difference is very small, consider the CHAR type first. Otherwise, consider the VARCHAR type.
• 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 because the CHAR type has a fixed length and is less likely to be fragmented.
• For field values ​​that store very small information, such as gender, the CHAR type is preferred because the VARCHAR type takes up extra 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 advantages. When space consumption becomes a major factor, we consider using the VARCHAR type.

This is the end of this article about the difference between varchar and char types in MySQL. For more information about MySQL varchar and char types, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!