| |
The difference
Similar to VARCHAR CHAR type, but a different way to save and retrieve them. CHAR fixed length, while VARCHAR belong to a variable-length character type. Their maximum length and whether trailing spaces are retained, it is also different. In the storage and retrieval process does not perform case conversion.
The following table shows the various string values are saved to CHAR (4) and VARCHAR (4) The results are shown after the description of the differences between CHAR and VARCHAR:
Value CHAR (4) storage requirements VARCHAR (4) storage requirements
'' '' Four-byte 'byte 1
'Ab' 'ab' 4 bytes 'ab' 3 bytes
'Abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'Abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes
From the above you can see that CHAR is fixed length, regardless of the data you store is how much he would have a fixed length. While at the VARCHAR variable length but he wants to increase the total length of 1 byte, which is used to store characters in length (if the length of the statement exceeds 255, use two bytes). So the actual application user can do according to their data type.
Note that the value in the last row in the table apply only when not using strict mode; if MySQL is running in strict mode, over the length of the column value is not saved, and you receive an error.
From CHAR (4) and VARCHAR (4) columns retrieved values are not always the same, because when retrieving deleted trailing spaces from CHAR columns. Explained by differences in the following example:
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 +
From the above it can be seen, for some reason CHAR have a fixed length, than in the processing speed is much faster VARCHAR, but the relative waste of storage space, so storage is not big, but the speed requirements can use the CHAR type, Conversely VARCHAR type can be achieved.
Suggest
MyISAM storage engine is recommended to use a fixed length data column instead of the variable length data columns
INNODB storage engine type recommended VARCHAR |
|
|
|