MERGE 存储引擎，也称为 MRG_MyISAM 引擎，是一组相同的 MyISAM 表，可以作为一个整体使用。“相同” 意味着所有表的列数据类型和索引信息完全一致。你不能合并那些列顺序不同、对应列的数据类型不完全相同或索引顺序不同的 MyISAM 表。然而，任何或所有的 MyISAM 表都可以使用 myisampack 进行压缩。参见 第 6.6.6 节，“myisampack — 生成压缩的只读 MyISAM 表”。像这样的表间差异无关紧要：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 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 6.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences between tables such as these do not matter:

MERGE 表的替代方案是分区表，它将单个表的分区存储在不同的文件中，并使某些操作能够更高效地执行。有关更多信息，请参见 第 26 章，分区。An alternative to a MERGE table is a partitioned table, which stores partitions of a single table in separate files and enables some operations to be performed more efficiently. For more information, see Chapter 26, Partitioning.

当你创建一个 MERGE 表时，MySQL 会在磁盘上创建一个 .MRG 文件，该文件包含应作为一个整体使用的底层 MyISAM 表的名称。MERGE 表的表格式存储在 MySQL 数据字典中。底层表不必与 MERGE 表位于同一个数据库中。When you create a MERGE table, MySQL creates a .MRG file on disk that contains the names of the underlying MyISAM tables that should be used as one. The table format of the MERGE table is stored in the MySQL data dictionary. The underlying tables do not have to be in the same database as the MERGE table.

您可以在 MERGE 表上使用 SELECT、DELETE、UPDATE 和 INSERT。您必须在映射到 MERGE 表的 MyISAM 表上拥有 SELECT、DELETE 和 UPDATE 权限。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.

使用 DROP TABLE 删除 MERGE 表时，仅删除 MERGE 指定内容。底层表不受影响。Use of DROP TABLE with a MERGE table drops only the MERGE specification. The underlying tables are not affected.

要创建一个 MERGE 表，必须指定一个 UNION=(list-of-tables) 选项，用于指示使用哪些 MyISAM 表。您可以选择性地指定一个 INSERT_METHOD 选项，以控制如何向 MERGE 表插入数据。使用 FIRST 或 LAST 的值可以分别将插入操作应用于第一个或最后一个基础表。如果未指定 INSERT_METHOD 选项，或者指定的值为 NO，则不允许向 MERGE 表插入数据，尝试插入将导致错误。To create a MERGE table, you must specify a UNION=(list-of-tables) option 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.

下面的示例展示了如何创建一个 MERGE 表：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;

列a 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. (Similarly, a column with a UNIQUE index in the underlying tables should be indexed in the MERGE table but not as a UNIQUE index.)Column a 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. (Similarly, a column with a UNIQUE index in the underlying tables should be indexed in the MERGE table but not as a UNIQUE index.)

创建 MERGE 表后，您可以使用它对整个表组执行查询：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      |
+---+---------+

要将 MERGE 表重新映射到不同的 MyISAM 表集合，可以使用以下方法之一：To remap a MERGE table to a different collection of MyISAM tables, you can use one of the following methods:

底层表的定义和索引必须与 MERGE 表的定义严格一致。当打开属于 MERGE 表的表时，会进行一致性检查，而不是在创建 MERGE 表时。如果任何表未通过一致性检查，则触发表打开的操作将失败。这意味着对 MERGE 中的表定义所做的更改，可能会导致访问 MERGE 表时失败。对每个表应用的一致性检查如下：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 MERGE 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:

如果由于底层表的问题而无法打开或使用 MERGE 表，CHECK TABLE 会显示导致问题的表的信息。If a MERGE table cannot be opened or used because of a problem with an underlying table, CHECK TABLE displays information about which table caused the problem.

附加资源Additional Resources