MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待,让我们可以同时对他们进行查询。构成一个MERGE数据表结构的各成员MyISAM数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型,索引也必须按照同样的顺序和同样的方式定义。
下面建立一个merge引擎的表:
mysql> create table t1(id int,msg char(5)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(id int,msg char(5)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t(id int,msg char(5)) engine=merge union=(t1,t2);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)
也可以添加一张表到merge表中:
mysql> create table t3(id int,msg char(5)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table t union(t1,t2,t3);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t3 values(3,'c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
直接向表t添加数据是不允许的,除非指定Insert_mothed参数:
mysql> insert into t values(4,'d');
ERROR 1036 (HY000): Table 't' is read only
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t(id int,msg char(5)) engine=merge union=(t1,t2,t3) insert_ method=last;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(4,'d');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from t3;
+------+------+
| id | msg |
+------+------+
| 3 | c |
| 4 | d |
+------+------+
2 rows in set (0.00 sec)
另外操作merge的表也要注意锁的问题,查询merge表的过程中会所有子表加上read锁,所以要明确知道要查询哪一张表时不要查询merge表。