MySQLMerge storage engine _ MySQL

The MERGE storage engine treats a set of MyISAM data tables as a logical unit so that we can query them at the same time. This article introduces the MySQLMerge storage engine. For more information, see the MERGE storage engine. the MERGE storage engine treats a set of MyISAM data tables as a logical unit so that we can query them at the same time. The MyISAM data table members that constitute a MERGE data table structure must have the same structure. The data columns of each member data table must define the same name and type in the same order, and the indexes must be defined in the same order and manner.

The Merge storage engine allows a group to use the MyISAM storage engine and the table structure is the same (that is, the field order, field name, field type, index definition order of each table and the way they are defined must be the same) to facilitate data query.

Assume that there are four log record tables, log_2004, log_2005, log_2006, and log_2007, whose data is the data of each year. the table structure is as follows (YY indicates the year ):

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

You can use the following SQL statement to aggregate these tables 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);

Note: the value of the ENGINE option must be MERGE. The UNION option lists all tables that will be included in this log_merge data table. After the log_merge table is created successfully, you can query it like a normal table, but each query will simultaneously act on each member data table that forms it. For example, to query the total number of data rows in these log data tables:

SELECT COUNT（*） FROM log_merge;

For example, you can query the number of log records each year:

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

Tables that create the MERGE storage engine also support the DELETE and UPDATE operations. The INSERT operation is troublesome because MySQL needs to know which Member table the new data row should be inserted. The definition of the MERGE storage engine data table can include an INSERT_METHOD option (supported after MySQL4.0), which has three values: NO, FIRST, and LAST, the meaning is to prohibit the INSERT operation, INSERT data rows to the first table in the UNION, or INSERT data rows to the last table in the UNION. For example, the following SQL statement specifies to add new data to the log_2007 data table-it is the last data 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 data table log_2009 with the same table structure and modify the log_merge data table to include 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: it is best not to have a primary key for a table with the same structure when using Merge to "Merge". Otherwise, this problem occurs: There are two member tables with the same primary keys in the two tables, however, if you write an SQL statement that queries data based on the same primary key value, you can only query the data in the first table in the UNION List.

For more information about the MySQLMerge storage engine, visit the Chinese PHP website (www.php1.cn )!