Introduction to the MERGE storage engine in Mysql (code example)

This article brings you an introduction to the MERGE storage engine in Mysql (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The MERGE storage engine treats a set of MyISAM data tables as a logical unit, allowing us to query them at the same time. Each member of the MyISAM data table that constitutes a MERGE data table structure must have exactly the same table structure. The data columns of each member data 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, and their contents are log record items for each year in the past few years. Their definitions are as follows, YY represents the year (recommended course: MySQL Tutorial)

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

Assume that the current collection of log data tables includes log_2004, log_2005, log_2006, log_2007, and you can create a MERGE data table as shown below to group them into one 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);

ENGINE option The value must be MERGE. The UNION option lists the related data tables that will be included in this MERGE data table. After this MERGE is created, you can query it just like any other data table, except that each query will affect each member data table that makes up it 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 record entries there are in each of the past few years:

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

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

1) The MERGE data table can be used to create a 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 certain year, you should no longer add records to the corresponding log file, so you can use the myisampack tool to compress it to save space, and the MERGE data table can still work as usual
　3 ), MERGE data table also supports DELETE and UPDATE operations. The INSERT operation is more troublesome because MySQL needs to know which member table the new data row should be inserted into. The definition of the MERGE data table can include an INSERT_METHOD option. The possible values ​​of this option are NO, FIRST, and LAST. Their meanings are that the INSERT operation is prohibited and new data rows will be inserted into the current UNION option. the first data table or the last data table. For example, the following definition treats an INSERT operation on the log_merge table as if it were an INSERT on the log_2007 table, which is 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 and let it have the same table structure, and then modify the log_merge data table to include log_2009: 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);

The above is the detailed content of Introduction to the MERGE storage engine in Mysql (code example). For more information, please follow other related articles on the PHP Chinese website!