mysqltypescharvarcharinternals

Why MySQL CHAR datatype has no preceding length byte?

In MySQL documentation you can find that CHAR datatype has no preceding byte with string length.

In contrast VARCHAR datatype has 1 or 2 preceding bytes where string length is stored. Which is understandable. Database engine needs to know what is the string length in order to read it.

How does database engine know what is CHAR datatype length? What logic/algorithm is behind it?

Solution

  • I am using MySQL 5.6.

    I was surprised to see that CHAR and VARCHAR fields have the same storage format in *.MYD files (MyISAM). CHAR also has an additional first byte that specifies the length of the value in each rows. CHAR is stored exactly the same as VARCHAR in *.MYD files. I tested this in different CHARACTER SET - utf8 (utf8mb3) and utf8mb4.

    DROP TABLE IF EXISTS test.table1;
CREATE TABLE test.table1 (
  char_1 CHAR(1) NOT NULL,
  char_10 CHAR(10) NOT NULL,
  varchar_1 VARCHAR(1) NOT NULL,
  varchar_10 VARCHAR(10) NOT NULL
)
ENGINE = MYISAM,
CHARACTER SET utf8mb4,
CHECKSUM = 0,
COLLATE utf8mb4_unicode_ci;

INSERT INTO table1 VALUES('A','ABCDEFGHIJ', 'A','ABCDEFGHIJ');

    If you open the table1.MYD file in a HEX reader, you will see this. I marked with red squares the places where the length of the CHAR and VARCHAR fields is stored.

    MyISAM CHAR storage

    So CHAR datatype also has 1 byte where the length of the string is stored.

    EDIT 1. InnoDB CHAR storage

    Later I checked the storage of CHAR in InnoDB. In InnoDB, CHAR also has an additional byte in each row that determines the length of the field.

    DROP TABLE IF EXISTS test.table_idb2;
CREATE TABLE test.table_idb2 (
  char_2 CHAR(2) NOT NULL,
  char_4 CHAR(4) NOT NULL,
  varchar_1 VARCHAR(1) NOT NULL,
  varchar_10 VARCHAR(10) NOT NULL
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
CHECKSUM = 0,
COLLATE utf8mb4_unicode_ci;

INSERT INTO table_idb2 VALUES('A','AAAA', 'A','ABCDEFGHIJ');
INSERT INTO table_idb2 VALUES('BB','DDD', 'B','ABCDEFGHIJ');
INSERT INTO table_idb2 VALUES('','CC', 'D','DDDD');
FLUSH TABLES;

    If you open the table_idb2.ibd file in a HEX reader, you will see this. I marked with squares the places where the length of the CHAR.

    So in InnoDB, the CHAR data type also has 1 byte in each row, where the length of the string is stored.

    InnoDB CHAR storage