Mysqlのストレージエンジンの:MERGEストレージエンジン
MergeはMyISAMストレージエンジンに基づくテーブルなので、MergeストレージエンジンはMGR_とも呼ばれますMyISAMストレージエンジンは、MyISAMストレージエンジンのテーブルのセットを1枚のテーブルに集約する役割を果たします.
Mergeで集約するMyISAMテーブルのカラムとインデックスは、同じ定義と順序でなければなりません.
MERGEストレージエンジンのテーブルの作成
MyISAMストレージエンジンに基づく2つのテーブルtable_を作成myisam1、tabl_myisam2:
Mergeで集約するMyISAMテーブルのカラムとインデックスは、同じ定義と順序でなければなりません.
MERGEストレージエンジンのテーブルの作成
MyISAMストレージエンジンに基づく2つのテーブル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)