Answer #11: what is the difference between char and varchar in MySQL

1. Experimental scenario

GreatSQL 8.0.25 InnoDB

2. Experimental test

2.1 differences

2.2 construction test table

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

2.3 test without exceeding the set value

Fields V and C write a character of 4 + space

[root@GreatSQL][test]> INSERT INTO vc VALUES ('4 ', '4 ');

[root@GreatSQL][test]> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (4 )                | (4)                 |
+---------------------+---------------------+
1 rows in set (0.00 sec)

The test results show that the length of char remains unchanged, accounting for 2 characters, and the space length of varchar changes, accounting for 1 character.

2.4 test of exceeding the set value

When the write length is greater than the set length, an error is reported

[root@GreatSQL][test]>INSERT INTO vc VALUES ('123456', '123456');
ERROR 1406 (22001): Data too long for column 'v' at row 1

Adjust sql_mode, which automatically intercepts the content within the limited capacity when writing again

[root@GreatSQL][test]>set session sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

[root@GreatSQL][test]>INSERT INTO vc VALUES ('123456', '123456');            
Query OK, 1 row affected, 2 warnings (0.02 sec)

[root@GreatSQL][test]>select * from vc;
+------+------+
| v    | c    |
+------+------+
| 1234 | 1234 |
+------+------+
1 row in set (0.00 sec)

2.5 field length test

Create a table, set the CHAR length to 256, and the result will prompt an error

[root@GreatSQL][test]>CREATE TABLE vc (v VARCHAR(255), c CHAR(256));
ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead

3. Usage suggestions

• char is recommended for frequently changed field types.
• It is recommended to use varchar type for field types that will not change, which can save some storage space.
• If you need to create an index, it is also recommended to use char type, because char type can effectively avoid index fragmentation caused by field changes and improve index performance.

For more details, please refer to the official website:

https://dev.mysql.com/doc/ref...

Article recommendation:

Technology sharing | MGR best practice
https://mp.weixin.qq.com/s/66...

Technology sharing | the way to repair MGR Bug in Wanli database
https://mp.weixin.qq.com/s/Ia...

Macos system compiler percona and the difference of some functions on Macos system
https://mp.weixin.qq.com/s/jA...

Technology sharing | using systemd to manage single machine and multiple instances of MySQL
https://mp.weixin.qq.com/s/iJ...

Product | GreatSQL to create a better MGR ecology
https://mp.weixin.qq.com/s/By...

Product | GreatSQL MGR optimization reference
https://mp.weixin.qq.com/s/5m...

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Wechat & QQ group:

You can scan code to add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR exchange wechat group, or directly scan code to join GreatSQL/MGR exchange QQ group.

This article is composed of blog one article multi posting platform OpenWrite release!