MYSQL's MERGE storage engine

MYSQL's MERGE storage engine MYSQL engine is not generally many. This time we talk about MERGE, which has many special features: the www.2cto.com MERGE engine type allows you to MERGE many tables with the same structure into one table. Then, you can execute a 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. The MERGE 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, in addition, to make a total query from multiple tables, the MERGE table will be very effective at this time. 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 needed. The method is as follows: www.2cto.com uses multiple tables as one table logically. There are two files after the creation ,. frm table structure definition. mrg union TABLE name list: create table TEST_MERGE_1 (id int (5) not null, value varchar (100) not null, primary key (ID )) ENGINE = MyISAM default charset = utf8 create table TEST_MERGE_2 (id int (5) not null, value varchar (100) not null, primary key (ID )) ENGINE = MyISAM default charset = utf8 merge table creation: create table TEST_MERGE (id int (5) not null, value varchar (100) not null, primary key (ID) ENGINE = MRG_MyISAM default charset = utf8 INSERT_METHOD = last union = (TEST_MERGE_1, TEST_MERGE_2); 1. This table is similar to the union mechanism in SQL. 2. The structure of the table 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. 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. Www.2cto.com 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.