Mysql MERGE engine is used in a sub-table environment

Application scenario: When we are doing the sub-table, we usually split the data of a large table into several sub-tables, which is also the concept of the sub-table in database optimization. The problem with the sub-table is the multi-table query.

Now there are t1,t2 two data tables, need to meet the different query conditions at the same time from the two tables to query data, but also to achieve sorting, paging and other requirements.

[T1 table]

1 CREATE TABLE' T1 ' (2' ID 'int(Ten) unsigned not NULLAuto_increment,3' Name 'varchar(Ten) not NULL,4   PRIMARY KEY(' id '),5   UNIQUE KEY' name ' (' name ')6) ENGINE=MyISAM auto_increment=3 DEFAULTCHARSET=Utf8

[T2 Table]

1 CREATE TABLE' T2 ' (2' ID 'int(Ten) unsigned not NULLAuto_increment,3' Name 'varchar(Ten) not NULL,4   PRIMARY KEY(' id '),5   UNIQUE KEY' name ' (' name ')6) ENGINE=MyISAM auto_increment=4 DEFAULTCHARSET=Utf8

[t Table]

1 CREATE TABLE' t ' (2' ID 'int(Ten) unsigned not NULLAuto_increment,3' Name 'varchar(Ten) not NULL,4   PRIMARY KEY(' id '),5   UNIQUE KEY' name ' (' name ')6) ENGINE=Mrg_myisamDEFAULTCHARSET=UTF8 Insert_method=LastUNION=(' T1 ', ' T2 ')

Using SQL to see that the engine specifying the T table is the MERGE (Mrg_myisam also), the Insert_method meaning is how we handle the table t when we insert it

Engine (specify table engine)

Union (tables that need to be combined)

Syntax: UNION (' t1 ', ' T2 ', ' T3 ' ..., ' TN ')

Insert_method (insert mode)

• 0 Insert not allowed
• First inserts into the second table
• Last inserted into the final table

Attention:

• The MERGE Word table must be a Myisam engine
• If the same primary key exists in a federated table, when the primary key is a query condition, the waiting query is stopped after the winner key in the first table.
• Deleting a federated table directly causes the MERGE table to become corrupted, and it is a good practice to remove the union relationship first.

Mysql MERGE engine is used in a sub-table environment