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. Each member MyISAM data table that forms a merge data table structure must have exactly the same structure. The data columns for each member data table must define the same name and type in the same order, and the index must be defined in the same order and in the same way.

The merge storage engine allows a set of data tables that use the MyISAM storage engine and have 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 its definition must be identical) to a table that facilitates the query of the data.

Suppose there are log_2004, log_2005, log_2006, log_2007 these four log records, the data is each year data, table structure as follows (yy represents the year):

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

These tables can be wrapping as a logical unit by using the following SQL statements:

CREATE TABLE log_merge ( 
DT DATETIME NOT NULL, 
info VARCHAR (MB) 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 datasheet. Once the Log_merge table is successfully created, it can be queried like a normal table, except that each query will act on each of the member data tables that make up it. For example, query the total number of data rows for these log data tables:

SELECT COUNT (*) from Log_merge;

For example, how many log records are in 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 the delete and update operations. The insert operation is cumbersome because MySQL needs to know which member table to insert the new data rows into. In the definition of the merge storage engine datasheet, you can include a insert_method option (supported after MySQL4.0), which has a value of no, first, and last three, which in turn prohibits insert operations, Inserts a row of data into the first table in the union or inserts a data row into the last table in the union. For example, the following SQL statement specifies that new data be added to the log_2007 datasheet----It is the last data table listed in the Union option:

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

Create a new member datasheet log_2009 and let it have the same table structure, and then modify the Log_merge datasheet 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: Tables with the same table structure using the merge merge are best not to have primary keys, otherwise, this happens when a total of two member tables have the same primary key in two tables, but a SQL statement that queries with the same primary key value is written that only data from the first table in the Union list can be found.

About Mysqlmerge Storage Engine Small series to introduce so many people, I hope to help you!