A detailed introduction to Mysql merge Table _mysql

MySQL Create a merge table and partition table A bit similar, he is a MySQL feature, you can combine the MyISAM table with multiple structures to a virtual table, the surviving engine must be the merge, when the query from the merged table is like a query from the child table, and the view has write similar, When you create a merge table, you also have your own frm and MRG files.
Merge Table Creation method
CREATE table t3 (a int primary key);
CREATE table t4 (a int primary key);
CREATE TABLE MRG (a int primary key) union= (T3,T4) insert_method=last;
Notice here that when you have the same column in 2 child tables, creating the merged table does not uniquely
Mysql> select * from MRG;
+---+
| A |
+---+
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
+---+
5 rows in Set (0.00 sec)
Mysql> select * from T3;
+---+
| A |
+---+
| 1 |
| 2 |
+---+
2 rows in Set (0.00 sec)
Mysql> select * from T4;
+---+
| A |
+---+
| 2 |
| 3 |
| 4 |
+---+
3 Rows in Set (0.00 sec)
a=2 columns are not unique in the parent table even if you use the primary KEY constraint.
The Insert_method=last option has 2 values, first and last he shows that when the value is inserted, it is in a table or most, and the order of the tables in the Union is sequentially accessed by the general query access.
When the parent table is deleted, the child table can be accessed
mysql> drop table MRG;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from T3;
+---+
| A |
+---+
| 1 |
| 2 |
+---+
2 rows in Set (0.00 sec);
When you delete a child table, the parent table cannot be accessed
mysql> drop table T3;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from MRG;
ERROR 1168 (HY000): Unable to open underlying table which is differently define
or of Non-myisam type or doesn ' t exist;
At the same time, if your child table definition is not consistent, the parent table can be created successfully, but not properly accessed.

A merged table contains more file descriptors than a non-merged table. His visit actually opened the child table, so the data in the cache might have multiple file descriptors, although it might have the same value in the merged table, but as long as the query finds the first desired value, the query stops