Merge是基于MyISAM存储引擎的表，所以Merge存储引擎也叫做MGR_MyISAM存储引擎，作用是将一组MyISAM存储引擎的表聚合成一张表。
Merge中要聚合的MyISAM表的列和索引必须有相同的定义及顺序。
建立MERGE存储引擎的表
建立两个基于MyISAM存储引擎的表table_myisam1、tabl_myisam2：
mysql> create table table_myisam1(id intprimary key, name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql> create table table_myisam2(id intprimary key, name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into table_myisam1 values(1,'myisam1');
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into table_myisam2 values(1,' myisam2');
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
建立基于MERGE存储引擎的表table_merge，聚合上面的table_myisam1、tabl_myisam2两张表：
mysql> create table table_merge(id intprimary key, name varchar(20)) engine=merge union(table_myisam1,table_myisam2);
Query OK, 0 rows affected (0.05 sec)
查询下table_merge表(就是聚合table_myisam1、tabl_myisam2表的数据)：
mysql> select * from table_merge;
+----+---------+
| id | name |
+----+---------+
| 1| myisam1 |
| 1| myisam2 |
+----+---------+
2 rows in set (0.00 sec)
其实这里有个奇怪现象，就是Merge表中的ID字段是主键，但现在重复了竟然没事。。。
感兴趣同学可以自己做个试验研究一下
查看下table_merge表的数据文件：
注：. MRG不是存储数据，而是指向数据来源地的文件； .frm是表结构定义文件
[mysql@localhost test]$ ll table_merge.*
-rw-rw---- 1 mysql mysql 8586 Sep 2 16:02 table_merge.frm
-rw-rw---- 1 mysql mysql 28 Sep 2 16:02 table_merge.MRG
查看下 .MRG文件内容(指向了聚合的两张表)：
[mysql@localhost test]$ more table_merge.MRG
table_myisam1
table_myisam2
注：使用MERGE存储引擎的表不会存储数据，只是汇总了MyISAM表的数据而已
MERGE表的插入操作
向Merge表插入数据有两种操作方式：
1：向MyISAM子表插入数据
2：向Merge表插入数据
向MyISAM子表插入数据，然后在Merge表中体现
向table_myisam1表新增一条数据：
mysql> insert into table_myisam1 values(2,'new_myisam1');
Query OK, 1 row affected (0.00 sec)
查看table_merge表数据：
mysql> select * from table_merge;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam1 |
| 1| myisam1 |
| 1| myisam2 |
+----+-------------+
3 rows in set (0.00 sec)
直接向MERGE表插入数据
直接向MERGE表插入数据的时候，需要加上插入的参数：insert_method
insert_method参数的可选项：
1：first 向第一个表插入数据
2：last 向最后一个表插入数据
3：no 不插入数据(默认值)
向MERGE表中聚合的最后一个表插入数据
mysql> alter table table_merge insert_method=last;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into table_merge values(2,'new_myisam2');
Query OK, 1 row affected (0.00 sec)
查看下MERGE表table_merge：
mysql> select * from table_merge;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam1 |
| 1| myisam1 |
| 2| new_myisam2 |
| 1| myisam2 |
+----+-------------+
4 rows in set (0.00 sec)
查看下MERGE表中聚合的最后一个MyISAM表table_myisam2：
mysql> select * from table_myisam2;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam2 |
| 1| myisam2 |
+----+-------------+
2 rows in set (0.00 sec)
MERGE表的DROP操作
对Merge表的Drop操作
Merge表是聚合了MyISAM表的操作，当drop一个Merge表时，只会drop掉Merge表本身，不会对聚合的MyISAM子表产生影响，同时各个MyISAM子表也不再有联系。
再建立一个用于测试drop的Merge表table_merge_drop：
mysql> create table table_merge_drop(id int primary key, name varchar(20)) engine=merge union(table_myisam1,table_myisam2);
Query OK, 0 rows affected (0.03 sec)
mysql> select * from table_merge_drop;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam1 |
| 1| myisam1 |
| 2| new_myisam2 |
| 1| myisam2 |
+----+-------------+
4 rows in set (0.00 sec)
Drop这个Merge表table_merge_drop：
mysql> drop table table_merge_drop;
Query OK, 0 rows affected (0.00 sec)
再次查看MyISAM子表的数据，依然存在：
mysql> select * from table_myisam1;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam1 |
| 1| myisam1 |
+----+-------------+
2 rows in set (0.00 sec)
mysql> select * from table_myisam2;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam2 |
| 1| myisam2 |
+----+-------------+
2 rows in set (0.00 sec)
对Merge表中聚合的MyISAM子表的Drop操作
如果删除了Merge表中某个MyISAM子表， Merge表会报错，需要重新更改union聚合的表即可
删除Merge表中MyISAM子表table_myisam2：
mysql> drop table table_myisam2;
Query OK, 0 rows affected (0.00 sec)
查询Merge表会报错：
mysql> select * from table_merge;
ERROR 1168 (HY000): Unable to openunderlying table which is differently defined or of non-MyISAM type or doesn'texist
更改Merger表的union表聚合，只保留table_myisam1这一个MyISAM子表
mysql> alter table table_merge engine=merge union(table_myisam1) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查询Merge表，正常：
mysql> select * from table_merge;
+----+-------------+
| id | name |
+----+-------------+
| 2| new_myisam1 |
| 1| myisam1 |
+----+-------------+
2 rows in set (0.00 sec)