Usage of the merge table storage engine in MySQL

In Mysql databases, Merge tables are somewhat similar to views. Mysql's merge engine type allows you to merge many tables with the same structure into one table. Then, you can execute the query. The results returned from multiple tables are the same as those returned from one table. Each merged table must have the same table definition and structure.

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 the modification, you must use flush tables to refresh the table cache. This method can dynamically increase or decrease the number of sub-TABLES.

The merge table storage engine is most useful in the following scenarios:
If you need to keep logging into the MySQL database, and create a single table every day, every week, or every month, and make a total query from multiple tables, the MERGE table is very valid. However, this feature has limitations. You can only merge MyISAM tables and strictly abide by the restrictions of the same table definition. Although this seems to be a big problem, if you use another table type (such as InnoDB), this merge may not be required.
The following table is defined:

Basic table:
Create table TEST_MERGE_1 (
Id int (5) not null,
Value varchar (100) not null,
Primary key (ID)
);
Create table TEST_MERGE_2 (
Id int (5) not null,
Value varchar (100) not null,
Primary key (ID)
);
MERGE table:
Create table TEST_MERGE (
Id int (5) not null,
Value varchar (100) not null,
Primary key (ID)
) TYPE = MRG_MyISAM INSERT_METHOD = last union = (TEST_MERGE_1, TEST_MERGE_2 );

Note:
1. The table structure must be exactly the same as that of the basic table, including the column name and sequence. The UNION table must belong to the same DATABASE.
2. This table is similar to the union mechanism in SQL.
3. The basic table type must be MyISAM.
4. You can modify the. mrg file to modify the MERGE table. Each basic table name occupies one row. Note: After modification, use flush tables to refresh the table cache.
5. Changes to the basic table can be directly reflected in this table.
6. The value of INSERT_METHOD can be: 0. inserting FIRST into the FIRST table in the UNION is not allowed to be inserted to the LAST table in the UNION. (Available after 4.0)
7. the constraint defined on it does not have any effect. The constraint is controlled by the basic table. For example, two basic tables have the same Key value, then there will be two identical Key values in the MERGE table.

Note:
1. If you modify the MERGE table by modifying the. mrg file, you must use flush tables to refresh the table cache. Otherwise, the modification will not take effect. I made this mistake recently.
2. When the data volume and query volume are large, do not try to use the Merge table to implement the table partition function similar to Oracle, which will affect the performance. I think it is almost equivalent to union.
3. the query results and sequence are related to the sequence of the joined tables when the Merge table is created.
Suppose there are two statements:
Insert into TEST_MERGE_1 (ID, VALUE) VALUES (1, 'ciray ');
Insert into TEST_MERGE_2 (ID, VALUE) VALUES (1, 'blog .csdn.net/ciray ');
Then, the query:
SELECT * FROM TEST_MERGE where id = 1;
Only one record (1, 'ciray') will be obtained, neither of which is (1, 'blog .csdn.net/ciray '). This is because ID is the primary key. If records are queried in the first table, they are not queried in the subsequent table. If the ID does not define the uniqueness constraint, this query will get two records.