The difference between char and varchar in MYSQL

CHAR and VARCHAR types are similar, differing primarily in storage, trailing spaces, and retrieval methods.

The similarities between CHAR and VARCHAR are: both CHAR and VARCHAR specify the character length, note that it is the character length. For example, char(30) and varchar(30) can both store 30 characters. One thing to note is that in utf8mb4 encoding, each character occupies 4 nodes. In utf8, each character takes 3 bytes. When the characters to be stored exceed the maximum length specified by CHAR/VARCHAR. When sql mode is not enabled, the string to be stored is truncated and only the first 30 characters are stored.

The values ​​in a CHAR column are fixed-length strings. The length can be specified as a value between 0 and 255 characters (for example, utf8mb4: 0-255*4 bytes). When CHAR is used to store characters, for example, CHAR(30) is used as an example. If the length is less than 30 characters, it is padded to the right with spaces. When CHAR values ​​are retrieved, trailing spaces are removed unless the sqlmode:PAD_CHAR_TO_FULL-LENGTH SQL mode is enabled.

The values ​​in a VARCHAR column are variable-length strings. The length can be specified as a value between 0 and 65535 bytes. The effective maximum length of a VARCHAR depends on the maximum row size (65535 bytes, shared among all columns) and the character set used.

Unlike CHAR, VARCHAR stores an additional 1-2 bytes when storing data (for example, varchar(4), these 1-2 bytes are not counted in the 4-character length, so there is no need to worry about truncation). These 1-2 bytes are used to record the length of the data characters. If the data character length is less than or equal to 255, use 1 byte to record the data length. If it is greater, two bytes are used. CHAR only stores the data itself. As shown in the figure below.

(Myth: varchar is slower than char when querying because the byte length of varchar (X) is calculated)

In latin1 encoding, Latin1 cannot store Chinese characters. English letters are 1 byte.

Note that VARCHAR values ​​are not padded with spaces when stored. Trailing spaces are preserved when storing and retrieving values, consistent with standard SQL. CHAR is the opposite. CHAR will be padded with spaces when stored, and the trailing spaces will be removed when retrieved, regardless of whether the trailing spaces are automatically filled or in the data itself. For example:

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 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.06 sec)<br><br><br>

Do three experiments.

mysql> show create table vc;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| vc | CREATE TABLE `vc` (
 `v` varchar(4) DEFAULT NULL,
 `c` char(4) DEFAULT NULL,
 UNIQUE KEY `v_UNIQUE` (`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1. Query statement where field = 'ab';

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where v=' ab';
Empty set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where c=' ab';
Empty set (0.00 sec)

2. Query statement where field = 'ab ';

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where v='ab ';
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
+--------------------+--------------------+
1 row in set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where c='ab ';
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
+--------------------+--------------------+
1 row in set (0.00 sec)<br><br><br>In all string comparisons, whether varchar, text, or char, trailing spaces are ignored, except for the like clause, which does not remove trailing spaces as shown below<br><br>

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where c like 'ab'; (no trailing space)
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
| ( ab ) | ( ab ) |
+--------------------+--------------------+
2 rows in set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where c like 'ab '; (with trailing space)
Empty set (0.00 sec)
 
 
 
CHAR is padded with spaces when stored, and trailing spaces are removed when retrieved, regardless of whether the trailing spaces are automatically padded or are part of the data itself. This action of removing the trailing space occurs before the comparison mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where v like 'ab '; (there is a trailing space)
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
+--------------------+--------------------+
1 row in set (0.01 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where v like 'ab'; (no trailing space)
Empty set (0.00 sec)

3. Set a unique index on varchar to see if 'ab' and 'ab' can exist at the same time

mysql> insert into vc (v,c)values('ab ', 'ab ')
 -> ;
ERROR 1062 (23000): Duplicate entry 'ab ' for key 'v_UNIQUE'
mysql> insert into vc (v,c)values(' ab ', 'ab ')
 -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)<br>This indicates that although the trailing space of varchar can be retained, additional restrictions seem to be imposed on the index. If varchar is a unique index, and the inserted values ​​differ only in the number of trailing spaces, a Duplicate key will be reported.

Speaking of byte limits, I would also like to remind you. The index length is also limited:

The length of each index column of the InnoDB engine is limited to 767 bytes, and the total length of all index columns cannot be greater than 3072 bytes. Note that it is bytes. The byte calculation of varchar(256) is different in different encodings. For example, in utf8mb4, one character occupies 4 bytes, and 256 characters = 1024 bytes. The effect of index coverage cannot be achieved. This is equivalent to only making a prefix index (very important, the prefix index needs to be returned to the (primary key) table for secondary query)

The innodb engine can increase the length limit of a single index column to 3072 bytes by configuring innodb_large_prefix=on (global parameter, dynamically effective).

• In MySQL 5.7, the default innodb_large_prefix=1 removes the 767-byte length limit, but the maximum length of a single-column index cannot exceed 3072 bytes.
• In MySQL 5.6, the default innodb_large_prefix=0 limits the length of a single column index to no more than 767 bytes. You need to set innodb_large_prefix=ON, innodb_file_format=barracuda, innodb_file_per_table=ON, and the storage format of the Innodb table is DYNAMIC or COMPRESSED, then the prefix index can contain up to 3072 bytes

The above is the detailed content of the difference between char and varchar in MYSQL. For more information about MYSQL char and varchar, please pay attention to other related articles on 123WORDPRESS.COM!