InnoDB mySQL table has a VARCHAR type field. There are many entries in the table.

CREATE TABLE `t1`( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `str` VARCHAR(330) NULL DEFAULT NULL, # другие поля таблицы ) ENGINE=InnoDB;

Suppose an entry with id = 100 in the VARCHAR field contains a string that is 100 characters long. (for example, we will assume that one character is one byte) After some time, the program changes the value in this field to

1) shorter string

2) a longer string

How will this record be "physically" stored on disk?

In the first case, I believe that the entry in the table file will remain in its place, and the unfilled characters will be empty.

And in the second case, the line will be marked as deleted and a new line will be created at the end of the file, thereby increasing the size of the table file, and the “previous version of record” marked for deletion will be present in the file until the OPTIMIZE TABLE?

Or the increased VARCHAR string will be split into two parts. The second part will be added to the end of the file. So?

Same question if you use TEXT type instead of VARCHAR.

Please explain.

  • The answer depends on the storage format. See InnoDB Row Formats and further links, and the same in MySQL Internals. In general, at least the page is rewritten; if necessary, it is split or merged. Since each page has its own title, the records and fields in it can be freely shifted depending on the change in the length of the data stored in the fields. - Akina

0