Detailed Explanation of MERGE Storage Engine of Mysql

The MERGE storage engine treats a set of MyISAM data tables as a logical unit, allowing us to query them simultaneously. Each member MyISAM data table that constitutes an MERGE data table structure must have a completely 1-type table structure. The data columns of every 1 member table must be defined with the same name and type in the same order, and the indexes must be defined in the same order and in the same way. Suppose you have several log data tables, their contents are log entries for each year in these years, and their definitions are all as follows, YY stands for the year

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

Suppose the current set of log tables includes log_2004, log_2005, log_2006, and log_2007, and you can create an MERGE table as shown below to group them into one LU:

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 data tables that will be included in this MERGE data table. Once this MERGE is created, it can be queried just like any other table, except that every query will be applied to every member table that makes up it at the same time. The following query lets us know the total number of data rows in the above several log data tables:

SELECT COUNT ( * ) FROM log_merge;

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

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

In addition to the convenience of referencing multiple tables at the same time without issuing multiple queries, MERGE tables also provide the following one convenience.

1), MERGE data tables can be used to create 1 logical unit whose size exceeds the maximum length allowed by each MyISAM data table

2) The compressed data table is included in the MERGE data table. For example, after the end of a year, you should not add records to the corresponding log file, so you can use the myisampack tool to compress it to save space, while the MERGE data table can still work as usual

3), the MERGE data table also supports DELETE and UPDATE operations. The INSERT operation is cumbersome because MySQL needs to know which 1 member table to insert the new data row into. INSERT_METHOD option can be included in the definition of MERGE data table. The desirable values of this option are NO, FIRST and LAST, which mean that INSERT operation is prohibited and new data rows will be inserted into the first or last data table listed in the current UNION option. For example, the following definition treats an INSERT operation on the log_merge table as if it were on the log_2007 table--which is the last table listed by 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 from an old table 
ALTER TABLE log_merge UNION = (log_2004, log_2005, log_2006,log_2007,log_2008,log_2009);