Differences between VARCHAR and CHAR data in MySQL _ MySQL

Char is a fixed-length type, and varchar is a variable-length type. What are the differences between them? Differences

CHAR is similar to VARCHAR, but they are saved and retrieved in different ways. CHAR has a fixed length, and VARCHAR is a variable-length character type. Their maximum length is different from whether the trailing space is retained. Case-insensitive conversion is not performed during storage and retrieval.

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

Value CHAR (4) storage requirements VARCHAR (4) storage requirements
''' 4 byte ''' 1 byte
'AB' 4 bytes 'AB' 3 bytes
'ABCD' 4 bytes 'ABCD' 5 bytes
'Abcdefgh' 'abccd' 4 bytes 'abccd' 5 bytes

From the above we can see that the CHAR length is fixed, no matter how much data you store, it will be fixed length. VARCHAR is a variable length, but it must be added to the total length by 1 byte. this is used to store the character length (if the declared length exceeds 255, 2 bytes are used ). Therefore, in actual applications, users can do this based on their own data types.

Note that the value of the last row in the table above applies only when the strict mode is not used. if MySQL runs in the strict mode, the value exceeding the column length is not saved and an error occurs.

The values retrieved from the CHAR (4) and VARCHAR (4) columns are not always the same, because spaces at the end of the CHAR column are deleted during retrieval. The following example shows the differences:

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 result is as follows:

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

It can be seen from the above that, for some reason, CHAR has a fixed length, so the processing speed is much faster than VARCHAR, but it is a waste of storage space, so it is not suitable for storage, however, if the speed requirement is met, the CHAR type can be used. Otherwise, the VARCHAR type can be used.

Suggestions

We recommend that you use a fixed length for the MyISAM storage engine. data columns replace variable-length data columns.
We recommend that you use VARCHAR for The INNODB storage engine.

Summary:

If the length of a text field is fixed, such as the ID card number, do not use varchar or nvarchar. char or nchar should be used.
For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues.
If the length of a text field is not fixed, such as an address, varchar or nvarchar is used. In addition to saving storage space, it is more efficient to access the hard disk.