Shulou(Shulou.com)05/31 Report--

This article shows you how to use the MERGE storage engine in Mysql. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The MERGE storage engine treats a set of MyISAM tables as a logical unit, allowing us to query them at the same time. The member MyISAM data tables that make up a MERGE data table structure must have exactly the same table structure. The data columns of 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. Suppose you have several log tables, which are log entries for each year over the past few years. They are all defined as follows, and YY represents the year.

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

Suppose the current collection of log data tables includes log_2004, log_2005, log_2006, and log_2007, and you can create a MERGE data table like this to group them 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)

The value of the ENGINE option must be the MERGE,UNION option that lists the relevant data tables that will be included in this MERGE data table. Once the MERGE is created, it can be queried like any other data table, except that each query will function with each of its member tables at the same time. The following query lets us know the total number of rows in the above log tables:

SELECT COUNT (*) FROM log_merge

The following query is used to determine how many log entries there have been in each of these 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 having to issue multiple queries, MERGE data tables provide the following convenience.

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, at the end of a certain year, you should no longer add records to the corresponding log file, so you can compress it with the myisampack tool to save space, while the MERGE datasheet can still work as usual.

3), MERGE datasheet also supports DELETE and UPDATE operations. The INSERT operation is troublesome because MySQL needs to know which member table the new data row should be inserted into. An INSERT_METHOD option can be included in the definition of a MERGE data table. The available values for this option are NO, FIRST, and LAST, which in turn means that INSERT operations are disabled and that 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 the INSERT operation on the log_merge data table as on 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 (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 and have it have the same table structure, then modify the log_merge data table to include log_2009: log_2009:

CREATE TABLE log_2009 LIKE log_2008; / / create a new table from the old table ALTER TABLE log_merge UNION = (log_2004, log_2005, log_2006,log_2007,log_2008,log_2009); the above is how to use the MERGE storage engine in Mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.