The MERGE storage engine, also known as the MRG_MyISAM engine, 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 with myisampack. See Section 8.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS do not matter.

When 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 tables that should be used as one. The tables do not have to be in the same database as the MERGE table itself.

You can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables. You must have SELECT, UPDATE, and DELETE privileges on the MyISAM tables that you map to a MERGE table.

Note: The use of MERGE tables entails the following security issue: If a user has access to MyISAM table t, that user can create a MERGE table m that accesses t. However, if the user's privileges on t are subsequently revoked, the user can continue to access t by doing so through m. If this behavior is undesirable, you can start the server with the new --skip-merge option to disable the MERGE storage engine. This option is available as of MySQL 5.0.24.

If you DROP the MERGE table, you are dropping only the MERGE specification. The underlying tables are not affected.

To create a MERGE table, you must specify a UNION=(list-of-tables) clause that indicates which MyISAM tables you want to use as one. You can optionally specify an INSERT_METHOD option if you want inserts for the MERGE table to take place in the first or last table of the UNION list. Use a value of FIRST or LAST to cause inserts to be made in the first or last table, respectively. If you do not specify an INSERT_METHOD option or if you specify it with a value of NO, attempts to insert rows into the MERGE table result in an error.

The following example shows how to create a MERGE table:

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

Note that the a column is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce uniqueness over the set of underlying tables.

After creating the MERGE table, you can issue queries that operate on the group of tables as a whole:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

To remap a MERGE table to a different collection of MyISAM tables, you can use one of the following methods:

MERGE tables can help you solve the following problems:

The disadvantages of MERGE tables are:

Additional resources