简介:
引用mysql官方文档中的一段话:merge存储引擎,也被认识为mrg_myisam引擎,是一个相同的可以被当作一个来用的myisam表的集合.相同意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩.
例子:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| engine | support | comment | transactions | xa | savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| memory | yes | hash based, stored in memory, useful for temporary tables | no | no | no |
| mrg_myisam | yes | collection of identical myisam tables | no | no | no |
| myisam | yes | myisam storage engine | no | no | no |
| blackhole | yes | /dev/null storage engine (anything you write to it disappears) | no | no | no |
| csv | yes | csv storage engine | no | no | no |
| performance_schema | yes | performance schema | no | no | no |
| archive | yes | archive storage engine | no | no | no |
| federated | no | federated mysql storage engine | null | null | null |
| innodb | default | supports transactions, row-level locking, and foreign keys | yes | yes | yes |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> create table test1 (id int not null auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam auto_increment=1;
query ok, 0 rows affected (0.01 sec)
mysql> create table test2 (id int not null auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam auto_increment=1;
query ok, 0 rows affected (0.00 sec)
mysql> insert into `test1` (`name`) values('beijing1');
query ok, 1 row affected (0.00 sec)
mysql> insert into `test2` (`name`) values('beijing2');
query ok, 1 row affected (0.00 sec)
mysql> create table test (id int not null auto_increment,name varchar(10) default null ,index(id)) engine=mrg_myisam union=(test1,test2) insert_method=last auto_increment=1;
query ok, 0 rows affected (0.03 sec)
mysql> select id,name from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
+----+----------+
2 rows in set (0.00 sec)
mysql> insert into `test` (`name`) values('beijing3');
query ok, 1 row affected (0.00 sec)
mysql> select id,name from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
3 rows in set (0.00 sec)