The maximum length of the MySQL database varchar type in versions earlier than 5.0.3 is 255, and its data range can be 0 ~ 255
The maximum length of the MySQL database varchar type in versions earlier than 5.0.3 is 255, and its data range can be 0 ~ 255
I. Evolution:
The maximum length of the MySQL database varchar type in versions earlier than 5.0.3 is 255, and its data range can be 0 ~ 255.
In MySQL 5.0.3 and later versions, the length of the varchar data type can reach 65535, that is, 65532 bytes of data can be stored, and the start and expires bits occupy 3 bytes. That is to say, in versions earlier than 5.0.3, data stored in fixed TEXT or BLOB formats can be stored using varchar with Variable Length in higher versions, which can effectively reduce the size of database files.
If the write length in varchar is greater than the set length, the subsequent part is intercepted by default.
Ii. Details (MySQL5.1 ):
CHAR and VARCHAR types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing space is retained are also different. Case-insensitive conversion is not performed during storage or retrieval.
The length of the CHAR and VARCHAR types declaration indicates 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 during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval.
The value in the VARCHAR column is a variable-length string. Length can be set to a value between 0 and 65,535. (The maximum valid length of a VARCHAR is determined by the maximum row size and the character set used. The total length is 65,532 bytes ).
Compared with CHAR, The VARCHAR value only saves the number of characters and adds a byte to record the length (if the declared length of a column exceeds 255, two bytes are used ).
The VARCHAR value is not filled when it is saved. When the value is saved and retrieved, the space at the end is retained, which complies with the standard SQL.
If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, crop the value to make it suitable. If the character to be dropped is not a space, a warning is generated. If you crop non-space characters, it will cause an error (rather than warning) and disable value insertion 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 differences between CHAR and VARCHAR:
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 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 spaces at the end of the CHAR column are deleted during retrieval.
The following example shows the difference:
The Code is 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 + |
+ ---------------- +
1 row in set (0.00 sec)
Sort and compare the values in the CHAR and VARCHAR columns Based on the character set proofreading rules assigned to the columns.
Note that all MySQL proofreading rules belong to the PADSPACE class. This indicates that no trailing space is required when comparing all CHAR and VARCHAR values in MySQL.
For example:
The Code is as follows:
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 applies to all MySQL versions and is not affected by the SQL Server mode.
When trailing padding characters are cropped or compared, ignore them. If the column index requires a unique value, inserting a value that only contains different characters in the column will result in a duplicate key value error.
Char byte is the alias of char binary. This is to ensure compatibility.
The ASCII attribute is assigned to the CHAR column with the latin1 character set. The UNICODE attribute is assigned to the ucs2 character set.
