MySQL Merge Storage Engine

The merge storage engine agrees to merge a set of data tables that use the MyISAM storage engine with the same table structure (that is, the field order of each table, the field name, the field type, the order of the index definition, and the way it is defined) into a table, which facilitates the query of the data.

If there are four log records of log_2004, log_2005, log_2006, log_2007, their data are each year's data, the table structure such as the following (yy represents the year):

CREATE TABLE log_yy (    dt  DATETIME NOT NULL,    info VARCHAR (+) NOT NULL,    INDEX (DT)  ) ENGINE = MyISAM;

These tables can be wrapping to a logical unit by, for example, the following SQL statements:

CREATE TABLE log_merge (      DT DATETIME NOT NULL,      info VARCHAR (+) NOT NULL,      INDEX (DT)  ) ENGINE = Merge UNION = (log_2004, log_2005, log_2006, log_2007);

Description: The value of the engine option must be the merge;union option that lists all the tables that will be included in this Log_merge data table.

Once the Log_merge table is created successfully, it can be queried like a normal table. Only every query will be applied at the same time to each member data table that constitutes it. For example, query the total number of data rows for these log data tables:

SELECT COUNT (*) from Log_merge;

For example, how many logs are logged each year:

SELECT year (DT) as Y, COUNT (*) as entries from Log_merge GROUP by y;

The table that creates the merge storage engine also supports delete and update operations. The insert operation is cumbersome because MySQL needs to know which member table the new data row should be inserted into. In the definition of the merge storage engine datasheet, you can include a insert_method option (supported after MySQL4.0), this option has a value of no, first, last three, which in turn prohibits insert operations, Inserts a data row into the first table in the union or inserts a data row into the final table in the Union. For example, the following SQL statement specifies that new data be added to the LOG_2007 data table----It is the last data table listed in the Union option:

CREATE TABLE log_merge (      DT DATETIME NOT NULL,      info VARCHAR (+) NOT NULL,      INDEX (DT)  ) ENGINE = Merge UNION = (log_2004, log_2005, log_2006, log_2007)  insert_method = last;

Create a new Member data table log_2009 and let it have the same table structure, and then change the Log_merge data table to include the log_2009:

CREATE TABLE log_2009 like log_2008;  ALTER TABLE log_merge  UNION = (log_2004, log_2005, log_2006, log_2007,log_2008,log_2009);

Note: Using merge to "merge" the table structure of the same table is best not to have a primary key, otherwise it will happen: a common two member table, its primary key in two tables in the same situation, but wrote a query by the same primary key value SQL statement, Only the data from the first table in the Union list can be found.

MySQL Merge Storage Engine