Detailed introduction to mysql table Merging

The creation and merging of mysql tables is similar to that of partition tables. It is a feature of mysql that combines multiple myisam tables with the same structure into a virtual table. The surviving engine must be merge, the query from a merged table is similar to that from a subtable and similar to that from a view. When you create a merged table, you also have your own frm and mrg files.
How to Create a merged table
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;
Note that when two sub-tables have the same columns, creating a merged table is not unique.
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)
The column a = 2 is not unique in the parent table, even if you use the primary key constraint.
The insert_method = last option has two values. "first" and "last" indicate whether the inserted value is in the first or most table. Generally, the query accesses the table order in union sequentially.
When the parent table is deleted, the child table can still 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;
If the definition of your sub-table is inconsistent, the parent table can be created successfully, but cannot be accessed normally.

A merged table contains more file descriptors than a non-merged table. Its access actually opens a sub-table, so the cached data may have multiple file descriptors, although the same value may exist in the merged table, the query stops as long as the first expected value is found in the query.