Detailed explanation of MySQL's MERGE storage engine

The MERGE storage engine treats a group of MyISAM tables as a logical unit, allowing us to query them simultaneously. Each member MyISAM data table that constitutes a MERGE data table structure must have exactly the same table structure. The columns of each member table must be defined with the same name and type in the same order, and the indexes must also be defined in the same order and in the same way. Suppose you have several log data tables, each of which contains log entries for each year in the past few years. Their definitions are as follows, where YY represents the year.

CREATE TABLE log_YY ( 
  dt DATETIME NOT NULL, 
  info VARCHAR(100) NOT NULL, 
  INDEX (dt) 
)ENGINE = MyISAM;

Assume that the current set of log data tables includes log_2004, log_2005, log_2006, and log_2007, and you can create a MERGE data table as shown below to group them into a logical unit:

CREATE TABLE log_merge ( 
  dt DATETIME NOT NULL, 
  info VARCHAR(100) NOT NULL, 
  INDEX(dt) 
) ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007);

The value of the ENGINE option must be MERGE, and the UNION option lists the related tables that will be included in this MERGE table. Once this MERGE is created, you can query it like any other data table, except that each query will act on each of its member data tables at the same time. The following query allows us to know the total number of data rows in the above log data tables:

SELECT COUNT(*) FROM log_merge;

The following query is used to determine how many log entries there are in each year for each of these years:

SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_merge GROUP BY y;

In addition to making it easy to reference multiple tables at the same time without issuing multiple queries, MERGE tables also provide the following conveniences.

1) The MERGE table can be used to create a logical unit whose size exceeds the maximum length allowed by each MyISAM table.

2) The compressed data table is included in the MERGE data table. For example, after a certain year is over, you probably won't be adding records to the corresponding log file, so you can compress it with the myisampack tool to save space, and the MERGE table will still work as usual.

3) MERGE data tables also support DELETE and UPDATE operations. The INSERT operation is more cumbersome because MySQL needs to know which member table to insert the new row of data into. The definition of a MERGE table can include an INSERT_METHOD option whose possible values ​​are NO, FIRST, and LAST, which respectively mean that the INSERT operation is prohibited and the new data row will be inserted into the first or last table listed in the current UNION option. For example, the following definition treats INSERT operations on the log_merge table as INSERTs on the log_2007 table—the last table listed in the UNION option:

CREATE TABLE log_merge( 
  dt DATETIME NOT NULL, 
  info VARCHAR(100) NOT NULL, 
  INDEX(dt) 
) ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007) INSERT_METHOD = LAST;

Create a new member table log_2009 with the same table structure, then modify the log_merge table to include log_2009: log_2009:

CREATE TABLE log_2009 LIKE log_2008; //Create a new table based on the old table ALTER TABLE log_merge UNION = (log_2004, log_2005, log_2006,log_2007,log_2008,log_2009);