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

parameter	char	varchar
Is the length variable	Fixed length	Lengthen
storage capacity	0 ~ 255	0 ~ 65,535

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/refman/8.0/en/char.html

Article recommendation:

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

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

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

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

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

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

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/GreatSQL

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

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!