简介:
引用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)
mysql> select id, name from test2
-> ;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
2 rows in set (0.00 sec)
mysql> system ls -l /mysql/data/test
total 164
-rw-rw---- 1 mysql mysql 8586 Feb 2 16:40 test1.frm
-rw-rw---- 1 mysql mysql 20 Feb 2 16:40 test1.MYD
-rw-rw---- 1 mysql mysql 2048 Feb 2 16:40 test1.MYI
-rw-rw---- 1 mysql mysql 8586 Feb 2 16:40 test2.frm
-rw-rw---- 1 mysql mysql 40 Feb 2 16:44 test2.MYD
-rw-rw---- 1 mysql mysql 2048 Feb 2 16:44 test2.MYI
-rw-rw---- 1 mysql mysql 8586 Feb 2 16:43 test.frm
-rw