What is the difference between varchar and char in MySQL

1. Experimental scenario

GreatSQL 8.0.25 InnoDB

2. Experimental tests

2.1 the difference between

parameter char varchar
Is the length variable Fixed length longer
Storage capacity 0 ~ 255 0 ~ 65535

2.2 Build test tables

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

2.3 Test without exceeding the set value

Fields V and C are written with a 4+ space character

[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 the set (0.00 SEC)Copy the code

As a result, the char length remains the same at 2 characters, while the vARCHar space length is changed to 1 character.

2.4 Test beyond the set value

An error occurs when the write length is greater than the specified length

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

Adjust SQL_mode to automatically intercept the content in the limit 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 the set (0.00 SEC)Copy the code

2.5 Field length test

SQL > alter table set CHAR length to 256

[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
Copy the code

3. Usage suggestions

  • You are advised to use char for frequently changed field types.
  • It is recommended to use vARCHAR for field types that do not change much to save some storage space.
  • If you need to create an index, you are advised to use the CHAR type because the CHAR type can effectively avoid index fragmentation caused by field changes and improve index performance.

For more details, please refer to the official website:

Dev.mysql.com/doc/refman/…

Article recommendation:

Technology sharing | MGR Best practices (MGR Best Practice) mp.weixin.qq.com/s/66u5K7a9u…

Share | wanli database MGR Bug fixes mp.weixin.qq.com/s/IavpeP93h road…

Macos system compile percona and some functions on Macos systems operation difference mp.weixin.qq.com/s/jAbwicbRc…

Technology sharing | use systemd manage MySQL standalone multi-instance mp.weixin.qq.com/s/iJjXwd0z1…

Products | GreatSQL, create a better ecological mp.weixin.qq.com/s/ByAjPOwHI MGR…

Products | GreatSQL MGR optimization reference mp.weixin.qq.com/s/5mL_ERRIj…

About GreatSQL

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

Gitee: gitee.com/GreatSQL/Gr…

Making: github.com/GreatSQL/Gr…

Wechat &QQ Group:

Scan code to add GreatSQL Community Assistant wechat friends, send verification message “add group” to join GreatSQL/MGR exchange wechat group, or directly scan code to join GreatSQL/MGR exchange QQ group.

This article is published by OpenWrite!