MERGE Storage Engine

The MERGE storage engine is also known as the MRG_MyISAM engine and it is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. However, any or all of the MyISAM tables can be compressed withWhen you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format, and an .MRG file contains the names of the underlying MyISAM tables that should be used as one. The tables do not have to be in the same database as the MERGE table.You can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.

Use of DROP TABLE with a MERGE table drops only the MERGE specification. The underlying tables are not affected.

We can create MERGE table using the option UNION={list-of-tables} that indicates which MyISAM tables to use. You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively. If you specify no INSERT_METHOD option or if you specify it with a value of NO, inserts into the MERGE table are not permitted and attempts to do so result in an error.

Example

CREATE TABLE blog (  id  INT NOT NULL AUTO_INCREMENT,  comment CHAR(50), INDEX(id)) ENGINE=MERGE UNION=(blog1,blog2) INSERT_METHOD=LAST;

Here blog1,blog2 should have identical column and index information .