Differences between MySQL CHAR and VARCHAR when storing and reading

Introduction

Do you really know the difference between CHAR and VARCHAR types when storing and reading?

Let me first draw a few conclusions:

1. When storing, CHAR will always be padded with spaces before storing, regardless of whether the user contains spaces at the end when inserting data.

2. When storing, VARCHAR will not fill in spaces before storing, but if the user specifically adds spaces when inserting, it will be stored as it is and will not be deleted.

3. When reading data, CHAR always deletes trailing spaces (even if spaces are included when writing).

4. When reading data, VARCHAR always retrieves the previously stored value truthfully (if there is a trailing space when storing, it will continue to be retained and the trailing space will not be deleted like CHAR).

The following is the test verification process.

1. Test CHAR type

Table structure:

CREATE TABLE `tchar` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` char(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert a few records:

insert into tchar values ​​(1, concat('a', repeat(' ',19)));
insert into tchar values ​​(2, concat(' ', repeat('a',19)));
insert into tchar values ​​(3, 'a');
insert into tchar values ​​(4, ' ');
insert into tchar values ​​(5, '');

View the storage structure:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:255 heapno:5 ... <- id=4
(7) normal record offset:298 heapno:6 ... <- id=5

Are you a little confused when you see this stuff? Do you remember the tool I recommended to you? Look here: innblock | InnoDB page observation tool.

As you can see, no matter how long the string is, each record actually takes up 43 (169-126=43) bytes. Therefore, Conclusion 1 holds.
Briefly, the origin of 43 bytes:
DB_TRX_ID, 6 bytes.
DB_ROLL_PTR, 7 bytes.
id, int, 4 bytes.
c1, char(20), 20 bytes; because it is of CHAR type, an additional byte is required.
Each record always requires an additional 5 bytes of header information (row header).
This adds up to a total of 43 bytes.

Let's look at the results of reading the tchar table:

select id,concat('000',c1,'$$$'),length(c1) from tchar;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$') | length(c1) |
+----+----------------------------+------------+
| 1 | 000a$$$ | 1 | <- remove trailing spaces | 2 | 000 aaaaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000$$$ | 0 | <- Remove the trailing space, the result is the same as id=5 | 5 | 000$$$ | 0 |
+----+----------------------------+------------+

2. Test VARCHAR type

Table structure:

CREATE TABLE `tvarchar` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` varchar(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert a few records:

insert into tvarchar values ​​(1, concat('a', repeat(' ',19)));
insert into tvarchar values ​​(2, concat(' ', repeat('a',19)));
insert into tvarchar values ​​(3, 'a');
insert into tvarchar values ​​(4, ' ');
insert into tvarchar values ​​(5, '');
insert into tvarchar values ​​(6, '');

View the storage structure:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:236 heapno:5 ... <- id=4
(7) normal record offset:260 heapno:6 ... <- id=5
(8) normal record offset:283 heapno:7 ... <- id=6

It can be seen that the number of bytes of several records are: 43, 43, 24, 24, 23, 23 (the last record is the same as the record with id=5).
The above result is a bit surprising, isn't it? Especially the record with id=1 (inserted with 'a... followed by 19 spaces'), which also consumes 43 bytes. This proves the conclusion 2 above.
Similarly, the two records with id=3 and id=4 both take up 24 bytes, and the two records with id=5 and id=6 both take up 23 bytes (there is no additional byte count for storing the string, only 4 bytes for the id column).

Let's look at the results of reading the tvarchar table:

select id,concat('000',c1,'$$$'),length(c1) from tvarchar;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$') | length(c1) |
+----+----------------------------+------------+
| 1 | 000a $$$ | 20 | <- trailing spaces are not removed in the read result | 2 | 000 aaaaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000 $$$ | 1 | <- This space is not deleted in the read result | 5 | 000$$$ | 0 |
| 6 | 000$$$ | 0 |
+----+----------------------------+------------+

In general, two conclusions can be drawn:
1. From the reading results, it seems that the CHAR type column eats spaces when storing, but in fact it is only eaten when reading (the spaces are deleted at the display level).
2. From the reading results, it seems that the VARCHAR type column retains extra spaces. In fact, these spaces are only restored when reading (but these spaces are still deleted during actual physical storage).

Finally, let’s see what the documentation says:

When CHAR values ​​are stored, they are right-padded with spaces to the
In short, CHAR columns are stored with spaces at the end to make up the length.

When CHAR values ​​are retrieved, trailing spaces are removed unless the
PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
In short, CHAR columns are read with trailing spaces removed unless the sql_mode value PAD_CHAR_TO_FULL_LENGTH=1 is set.

VARCHAR values ​​are not padded when they are stored.
In short, no trailing space is added when storing VARCHAR.

Trailing spaces are retained when values ​​are stored and retrieved, in
conformance with standard SQL. In short, spaces are displayed when a VARCHAR is read.

The versions and environments used in the above tests are:

mysql> select version()\G
...
version(): 8.0.15

mysql> select @@sql_mode\G
...
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Reference Documentation

11.4.1 The CHAR and VARCHAR Types, https://dev.mysql.com/doc/refman/5.7/en/char.html

The above is the detailed content of the difference between MySQL CHAR and VARCHAR storage. For more information about MySQL CHAR and VARCHAR, please pay attention to other related articles on 123WORDPRESS.COM!