I. Evolution:
The varchar type of MySQL database has a maximum length limit of 255 in the version below 5.0.3, and its data range can be 0~255.
In the MySQL5.0.3 and above versions, the length of the varchar data type is supported to 65535, which means that 65,532 bytes of data can be stored, and the starting and ending bits take up 3 bytes, that is, data stored in a fixed text or BLOB format in the following versions of 5.0.3 can be used in high The version uses variable-length varchar to store, which effectively reduces the size of the database file.
If you write in varchar longer than the set length, the following section is truncated by default.
Second, detailed (MySQL5.1):
Char and varchar types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing spaces are retained are also different. No case conversion is made 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 occupy 30 characters.
The length of the char column is fixed to the length declared when the table was created. The length can be any value from 0 to 255. When you save the char value, fill the space on their right to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversion is made during storage or retrieval.
The value in the varchar column is a variable-length string. The length can be specified as a value between 0 and 65,535. (The maximum valid length of the varchar is determined by the maximum row size and the character set used.) The overall maximum length is 65,532 bytes).
In contrast to char, the varchar value saves only the number of characters required, plus a single byte to record the length (two bytes if the column declaration is longer than 255).
The varchar value is saved without padding. The trailing blanks are still retained when the value is saved and retrieved, conforming to standard SQL.
If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is cropped to fit. If the character being trimmed is not a space, a warning is generated. If you crop a non-space character, it causes an error (not a warning) and disables the insertion of the value by using strict SQL mode.
The following table shows the results of saving various string values to the char (4) and varchar (4) columns, indicating the difference between char and varchar:
| Value |
CHAR (4) |
Storage requirements |
VARCHAR (4) |
Storage requirements |
| '' |
' ' |
4 bytes |
'' |
1 bytes |
| ' AB ' |
' AB ' |
4 bytes |
' AB ' |
3 bytes |
| ' ABCD ' |
' ABCD ' |
4 bytes |
' ABCD ' |
5 bytes |
| ' Abcdefgh ' |
' ABCD ' |
4 bytes |
' ABCD ' |
5 bytes |
Note that the last row in the previous table only applies when strict mode is not used; if MySQL runs in strict mode, values that exceed the length of the column are not saved and an error occurs.
The values retrieved from the char (4) and varchar (4) Columns are not always the same, because trailing spaces are removed from the Char column when retrieved.
The difference is illustrated by the following example:
Copy Code code as follows:
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)
Sorts and compares values in char and varchar columns based on the character set collation rules assigned to the column.
Please note that all MySQL proofing rules belong to the Padspace class. This means that you do not need to consider any trailing spaces when comparing all char and varchar values in MySQL.
For example:
Copy Code code as follows:
mysql> CREATE TABLE names (myname CHAR (), 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 all versions of MySQL are the same, and it is not affected by SQL Server mode.
For cases where the trailing padding characters are cropped out or ignored when they are compared, if the column's index requires a unique value, inserting a value in aligns that is just a different number of padding characters will result in an error in the replication key value.
Char Byte is an alias for char binary. This is to ensure compatibility.
The ASCII property assigns the Latin1 character set to the Char column. The Unicode property assigns the UCS2 character set.
