Advantages of Mysql Merge table

In Mysql databases, Mysql Merge tables are somewhat similar to views. Next let's take a look at the advantages of Mysql Merge tables and hope to help you.

Advantages of Mysql Merge table:
A: Separate Static and Dynamic Data
B: optimize queries using data with close Structures
C: less data can be accessed during query.
D: It is easier to maintain large datasets.
E: You can modify the. mrg file to modify the Merge table. You can also use alter to modify the table. After modification, you must use flush tables to refresh the table cache. This method can dynamically increase or decrease the number of sub-TABLES.
Creation method, for example:
Mysql> create table t1 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table t2 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table mrg (a int not null primary key) ENGINE = merge union = (t1, t2) INSERT_METHOD = LAST;
Add test data below
Mysql> insert into t1 (a) VALUES (1), (2 );
Mysql> insert into t2 (a) VALUES (1), (2 );
Query the result
Mysql> SELECT a FROM mrg;
The data in tables t1 and t2 is displayed.
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 1 |
| 2 |
+ ------ +

For merge tables, note that
1. The structure of each sub-table must be consistent, and the structure of the master table and sub-table must be consistent,
2. The index of each sub-table exists in the merge table. Therefore, you cannot perform a unique search based on the index in the merge table.
3. The sub-table must be the MyISAM engine.
4. REPLACE does not work in the merge table
5 AUTO_INCREMENT will not work as expected.

The INSERT_METHOD parameter used to create a Mysql Merge table has several parameters.
LAST if you execute the insert command to operate the merge table, the insert operation adds the data to the LAST sub-table. Similarly, when inserting data, the data is added to the FIRST sub-table.
For example, in this example, the insert operation is performed on the merge table.
Mysql> insert into mrg (a) VALUES (18 );
Query
Mysql> SELECT a FROM t2;
As a result, 18 is displayed in table t2.
---------------------------------------------------------------
If you perform the DROP operation on the mrg table or sub-table, unexpected conditions may occur.
If you delete a mrg table, there is no association between the sub-tables. However, if you delete any of the sub-tables, the merge table structure and data still exist for GNU/LINUX.
Mysql> drop table t1, t2;
Mysql> SELECT a FROM mrg;
You will find that the query results of the mrg table remain unchanged.

How to install a MySQL source package

Method for randomly obtaining Mysql Data

Back up data based on Mysql tables

MySQL Show statement usage

How to clear a table in Mysql