What data is duplicated when MySQL/MariaDB BLOB columns are copied?

Let table_1 be created as follows:

table_1

CREATE TABLE table_1 (
id INT AUTO_INCREMENT PRIMARY KEY,
some_blob BLOB
);


Let table_2 be created as follows:

table_2

CREATE TABLE table_2 (
id INT AUTO_INCREMENT PRIMARY KEY,
some_blob BLOB
);


What I want to know is, after I run this table-copying query

INSERT INTO table_2 (id, some_blob) SELECT id, some_blob FROM table_1;


will the actual text within each some_blob field of the table_1 table be duplicated and stored on disk, or will the DB have only duplicated pointers to the disk locations containing the BLOB data?

some_blob

table_1

One argument for why BLOB copying must involve the duplication of actual content reasons as follows:

Duplication of BLOB content is necessary because changes to BLOB data in table_1 should not also take place in table_2. If only the disk pointers were duplicated then content changes in one table would be reflected in the other table, which violates the properties of a correct copy operation.

table_1

table_2

Now I present an alternative method that the DB could implement to satisfy this copy operation. This alternative shows the above argument is not necessarily true. The DB could only duplicate disk pointers during the execution of the given INSERT statement, then whenever an UPDATE occurs which seeks to modify the BLOB data in one of the tables, the DB would only then allocate more space on disk to store the new data which is part of the UPDATE query. BLOB data segments then would only be deleted then when there no longer existed any disk pointers to it, and a particular BLOB data segment could potentially have many disk pointers pointing to it.

INSERT

UPDATE

UPDATE

So which of these strategies does MySQL/MariaDB use when executing the given INSERT statement, or does it use a different strategy?

INSERT

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.