Evolution and elaboration of CHAR and VARCHAR types in MySQL

1. The evolution:

The varchar type of the MySQL database is limited to a maximum length of 255 in versions below 5.0.3, and the data range can be from 0 to 255.

In MySQL5. Version 0.3 and above, varchar the length of the data types supported by 65535, that is to say, can hold 65532 bytes of data, the start bit and end occupies three bytes, that is to say, under 5.0.3 version need to use a fixed TEXT or BLOB format stored in the data can be used in the high version of variable-length varchar to store, so it can effectively reduce the size of the database file.

If you write more than the set length in varchar, the rest is truncated by default.

2. Details (MySQL 5.1) :

The CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing Spaces are retained. Case conversion is not performed during storage or retrieval.

The length of the CHAR and VARCHAR type declarations represents the maximum number of characters you want to save. For example, CHAR(30) can take up to 30 characters.

The length of the CHAR column is fixed to the length declared when the table is created. The length can be any value from 0 to 255. When saving CHAR values, fill in Spaces to the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is removed. Case conversion is not performed during storage or retrieval.

The values in the VARCHAR column are variable-length strings. The length can be specified as a value between 0 and 65,535. The maximum effective length of VARCHAR is determined by the maximum line size and the character set used. The overall maximum length is 65,532 bytes.

In contrast to CHAR, the VARCHAR value saves only the required number of characters, plus one byte to record the length (two bytes if the column declaration is longer than 255).

The VARCHAR value is saved without padding. When the value is saved and retrieved, the space at the end of the value remains, in accordance with standard SQL.

If the value assigned to an CHAR or VARCHAR column exceeds the maximum length of the column, the value is cropped to fit. If the character being cut is not a space, a warning is generated. If you crop non-space characters, you create an error (rather than a warning) and disable the insertion of values by using strict SQL mode.

The following table shows the results of saving various string values into columns CHAR(4) and VARCHAR(4), 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' Note that the value of the last row in the table above applies only when strict schema 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 the CHAR(4) and VARCHAR(4) columns are not always the same, because the trailing Spaces are removed from the CHAR column during retrieval.
This difference is illustrated by the following example:

 
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); 
Query OK, 0 rows affected (0.02 sec) 

mysql> INSERT INTO vc VALUES ('ab ', 'ab '); 
Query OK, 1 row affected (0.00 sec) 

mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc; 
+----------------+----------------+ 
| CONCAT(v, '+') | CONCAT(c, '+') | 
+----------------+----------------+ 
| ab + | ab+ | 
+----------------+----------------+ 
1 row in set (0.00 sec) 

Sort and compare the values in the CHAR and VARCHAR columns according to the character set collation rules assigned to the columns.

Note that all MySQL proofreading rules belong to the PADSPACE class. This means that all CHAR and VARCHAR values in MySQL are compared without considering any trailing Spaces.

Such as:

 
mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10)); 
Query OK, 0 rows affected (0.09 sec) 

mysql> INSERT INTO names VALUES ('Monty ', 'Monty '); 
Query OK, 1 row affected (0.00 sec) 

mysql> SELECT myname = 'Monty ', yourname = 'Monty ' FROM names; 
+-----------------------------+-------------------------------+ 
| myname = 'Monty ' | yourname = 'Monty ' | 
+-----------------------------+-------------------------------+ 
| 1 | 1 | 
+-----------------------------+-------------------------------+ 
1 row in set (0.00 sec) 

Note that this is true for all versions of MySQL, and it is not affected by the SQL server mode.

If the index of a column requires a value of only 1, inserting a value with a different number of padding characters in the column will cause a copy key value error.

CHAR BYTE is the alias for CHAR BINARY. This is to ensure compatibility.

The ASCII attribute assigns the latin1 character set to the CHAR column. The UNICODE property allocates the ucs2 character set.