MySQL Merge table storage engine usage

In the MySQL database, the merge table is somewhat similar to the view. MySQL's merge engine type allows you to combine many tables of the same structure into a single table. After that, you can execute the query, and the results returned from multiple tables are like the results returned from a table. Each merged table must have exactly the same definition and structure of the table.

Advantages of Mysql Merge table:
A. Separating static and dynamic Data
B. Optimizing queries with data that is structurally close
C. Access to less data when querying
D. Easier to maintain large data sets
E. You can modify the merge table by modifying the. mrg file, which can also be modified with ALTER, which is modified to refresh the table cache by flush tables, which dynamically increases the reduction of the child table

The merge table storage engine is most useful for use in the following situations:
The merge table is very effective if you need to keep logging into the MySQL database and create a single table daily, weekly, or monthly, and to make aggregate queries from multiple tables. However, this feature has limitations. You can only merge MyISAM tables and must strictly adhere to the same table definition restrictions. While this may seem like a big problem, if you use a different table type (for example, InnoDB), this merge might not be necessary.
Here are a few tables defined below:

Basic table:

CREATE TABLETest_merge_1 (IDINT(5) not NULL, VALUEVARCHAR( -) not NULL,PRIMARY KEY(ID));CREATE TABLEtest_merge_2 (IDINT(5) not NULL, VALUEVARCHAR( -) not NULL,PRIMARY KEY(ID)); Merge table:CREATE TABLETest_merge (IDINT(5) not NULL, VALUEVARCHAR( -) not NULL,PRIMARY KEY(ID)) TYPE=Mrg_myisam Insert_method=LastUNION=(test_merge_1,test_merge_2);

Description
1. This table structure must be exactly the same as the base table, including the column name and order. The Union table must belong to a database.
2. This table is similar to the union mechanism in SQL.
3. The base table type must be MyISAM.
4. You can modify the merge table by modifying the. mrg file, one row for each base table name. Note: The table cache will be refreshed with flush tables after modification.
5. Changes to the base table can be reflected directly on this table.
6. The value of Insert_method can be: 0 Insert first table inserted into union is not allowed to insert into the last table in union. (available after 4.0)
7. The constraint defined above it has no effect, the constraint is controlled by the base table, for example, two basic tables have the same key value, then there will be two identical key values in the merge table.

Attention:
1. If you modify the merge table by modifying the. mrg file, be sure to refresh the table cache with flush tables, otherwise the modification will not take effect. Recently made such a mistake.
2. Do not attempt to use the merge table to achieve the functionality of a table partition similar to Oracle in the case of a large amount of data and query volume, which can affect performance. My feeling is almost equivalent to union.
3. The query results and order are related to the order of Union tables when the merge table was created.
Suppose there are two of these 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, this query:
SELECT * from Test_merge WHERE id=1;
Will only get a record (1, ' Ciray '), not two records, nor will it be (1, ' Blog.csdn.net/ciray '). This is because the ID is PRIMARY KEY, and if a record is queried in the first table, it is not recorded in the subsequent table. If the ID does not define a uniqueness constraint, the query gets two records.

MySQL Merge table storage engine usage