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

Keywords: MySQL

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

Is the length variableFixed lengthLengthen
storage capacity 0 ~ 2550 ~ 65,535

2.2 construction test table


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]>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:

Article recommendation:

Technology sharing | MGR best practice

Technology sharing | the way to repair MGR Bug in Wanli database

Macos system compiler percona and the difference of some functions on Macos system

Technology sharing | using systemd to manage single machine and multiple instances of MySQL

Product | GreatSQL to create a better MGR ecology

Product | GreatSQL MGR optimization reference

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.



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!

Posted by terje-s on Mon, 29 Nov 2021 07:43:18 -0800