Differences between VARCHAR and CHAR format data in MySQL

The difference between

CHAR is similar to the VARCHAR type, but they are saved and retrieved differently. CHAR has a fixed length, while VARCHAR is a variable length character type. They also differ in terms of maximum length and whether trailing Spaces are retained. Case conversion is not performed during storage and retrieval.

The following table shows the results of saving various string values to CHAR(4) and VARCHAR(4) columns, illustrating the difference between CHAR and VARCHAR:

Value CHAR(4) Storage requirements VARCHAR(4) storage requirements
"" 4 bytes" "1 byte
'ab' 'ab '4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

As you can see above, CHAR has a fixed length, no matter how much data you store. VARCHAR, on the other hand, has a variable length but adds 1 byte to the total length, which is used to store the character length (2 bytes if the declared length exceeds 255). So in practice users can do this according to their own data types.

Note that the value of the last row in the table above applies only when strict mode is not used; If MySQL is running in strict mode, values that exceed the column length are not saved and an error occurs.

The values retrieved from CHAR(4) and VARCHAR(4) columns are not always the same, because the trailing Spaces are removed from CHAR columns when retrieved. Illustrate the difference with the following examples:

mysql> CREATE TABLE test(a VARCHAR(4), b CHAR(4));

mysql> INSERT INTO test VALUES ('ab ', 'ab ');

mysql> SELECT CONCAT(a, '+'), CONCAT(b, '+') FROM test;

The results are as follows:

CONCAT(a, '+') CONCAT(b, '+')
ab + ab+

As can be seen from the above, for some reason, CHAR has fixed length, so it is much faster than VARCHAR in processing speed. However, it is a waste of storage space, so it is not big for storage, but has speed requirements. CHAR type can be used, whereas VARCHAR type can be used to achieve.

advice

The MyISAM storage engine recommends using fixed length data columns instead of variable length data columns
The INNODB storage engine recommends using the VARCHAR type

Summary and analysis:

varchar or nvarchar should be used instead of varchar or nchar if the length of a text field is fixed, such as an IDENTITY card number.
Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues
If the length of the text field is not fixed, such as address, varchar or nvarchar should be used. In addition to saving storage space, accessing the hard disk is also more efficient