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
data types and index information. You cannot merge
MyISAM tables in which the columns are listed in
a different order, do not have exactly the same data types in
corresponding columns, or have the indexes in different order.
However, any or all of the
MyISAM tables can be
compressed with myisampack. See
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences between tables such as
these do not matter:
Names of corresponding columns and indexes can differ.
Comments for tables, columns, and indexes can differ.
Table options such as
An alternative to a
MERGE table is a partitioned
table, which stores partitions of a single table in separate files.
Partitioning enables some operations to be performed more
efficiently and is not limited to the
storage engine. For more information, see
Chapter 22, Partitioning.
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 underlying
MyISAM tables that should be used as one. The
tables do not have to be in the same database as the
The use of
MERGE tables entails the following
security issue: If a user has access to
t, that user can create a
t. However, if the user's
t are subsequently
revoked, the user can continue to access
t by doing so through
DROP TABLE with a
MERGE table drops only the
MERGE specification. The underlying tables are
To create a
MERGE table, you must specify a
option that indicates which
MyISAM tables to use.
You can optionally specify an
option to control how inserts into the
table take place. Use a value of
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
NO, inserts into the
MERGE table are not permitted and attempts to do
so result in an error.
The following example shows how to create a
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;
a is indexed as a
KEY in the underlying
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. (Similarly, a column with a
UNIQUE index in the underlying tables should be
indexed in the
MERGE table but not as a
After creating the
MERGE table, you can use it to
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
MyISAM tables, you can use one of the
MERGEtable and re-create it.
ALTER TABLEto change the list of underlying tables.
It is also possible to use
ALTER TABLE ... UNION=()(that is, with an empty
UNIONclause) to remove all of the underlying tables. However, in this case, the table is effectively empty and inserts fail because there is no underlying table to take new rows. Such a table might be useful as a template for creating new
CREATE TABLE ... LIKE.
The underlying table definitions and indexes must conform closely to
the definition of the
MERGE table. Conformance is
checked when a table that is part of a
table is opened, not when the
MERGE table is
created. If any table fails the conformance checks, the operation
that triggered the opening of the table fails. This means that
changes to the definitions of tables within a
MERGE may cause a failure when the
MERGE table is accessed. The conformance checks
applied to each table are:
The underlying table and the
MERGEtable must have the same number of columns.
The column order in the underlying table and the
MERGEtable must match.
Additionally, the specification for each corresponding column in the parent
MERGEtable and the underlying tables are compared and must satisfy these checks:
The column type in the underlying table and the
MERGEtable must be equal.
The column length in the underlying table and the
MERGEtable must be equal.
The column of the underlying table and the
MERGEtable can be
The underlying table must have at least as many indexes as the
MERGEtable. The underlying table may have more indexes than the
MERGEtable, but cannot have fewer.Note
A known issue exists where indexes on the same columns must be in identical order, in both the
MERGEtable and the underlying
MyISAMtable. See Bug #33653.
Each index must satisfy these checks:
The index type of the underlying table and the
MERGEtable must be the same.
The number of index parts (that is, multiple columns within a compound index) in the index definition for the underlying table and the
MERGEtable must be the same.
For each index part:
Index part lengths must be equal.
Index part types must be equal.
Index part languages must be equal.
Check whether index parts can be
MERGE table cannot be opened or used because
of a problem with an underlying table,
TABLE displays information about which table caused the
A forum dedicated to the
MERGEstorage engine is available at https://forums.mysql.com/list.php?93.