MERGE Storage engine MyISAM The data table is treated as a logical unit , So we can query them at the same time . Constitute a MERGE Members of the data table structure MyISAM Data tables must have exactly the same structure . The data columns of each member data table must have the same name and type defined in the same order , Indexes must also be defined in the same order and in the same way .

Suppose you have several log tables , Their contents are the log entries of each year in recent years , Their definition is as follows ,YY Represents the year :

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

Suppose the current set of log data tables includes log_2004、log_2005、log_2006、log_2007 , And you can create one like this MERGE Data tables bring them together into a logical unit :
[sql]
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 The value of the option must be MERGE,UNION The options listed will be included in this MERGE The data table is separated from the relevant data tables . Put this MERGE When it's created , You can query it as you would any other data table , It's just that every query will act on every member data table that forms it at the same time . The following query can let us know the total number of data rows in the above log data tables :
[sql]
SELECT COUNT(*) FROM log_merge;

The following query is used to determine how many log entries each year in the past few years :
[sql]
SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_merge GROUP BY y;

In addition to the convenience of referencing multiple data tables at the same time without issuing multiple queries ,MERGE The data table also provides the following convenience .
MERGE Data tables can be used to create a dimension over each MyISAM The maximum length of a logical unit allowed by a data table
You see a compressed data table that includes MERGE In the data sheet . for instance , At the end of a year , You should not add records to the corresponding log file , So you can use myisampack Tools compress it to save space , and MERGE Data tables still work as usual
MERGE Data tables also support DELETE and UPDATE operation .INSERT The operation is more troublesome , because MySQL You need to know which member table to insert the new data row into . stay MERGE The definition of a data table can include a INSERT_METHOD Options , The value of this option is NO、FIRST、LAST, What they mean in turn is INSERT Operation is forbidden 、 The new data line will be inserted into the present UNION The first or last data table listed in the options . for instance , The following definitions will be useful for log_merge Data table INSERT The operation is treated as a pair of log_2007 Data sheet ---- It is UNION Option :

[sql]
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 let him have the same table structure , And then modify log_merge Data sheet log_2009 Include in :
[sql]
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);

from :http://blog.csdn.net/leiyonglin/article/details/7008659

MySQL MERGE Storage engine More related articles in the introduction

  1. MySQL MERGE Storage engine Introduction and usage

    MERGE Storage engine MyISAM The data table is treated as a logical unit , So we can query them at the same time . Constitute a MERGE Members of the data table structure MyISAM Data tables must have exactly the same structure . The data column of each member data table must be ...

  2. MySQL MERGE Storage engine

    Write this article , Mainly because during the interview , The interviewer asked me how to count all the sub tables ( Let's say according to the talent table ) data , I said two options , The first is the dumbest way , It is to query all table data in a loop ( Definitely not ): The second way is , Using middleware , Every day, I put the previous one ...

  3. MySql Study - Introduction to storage engine

    mysql Data can be stored in different technologies in ( Memory ) in , This technology is called a storage engine . Each storage engine uses a different storage mechanism . Indexing techniques . Lock level , Ultimately, it offers a wide range of different functions . MySQL Supported storage engines : MyISA ...

  4. Mysql Of Merge The storage engine implements query by table

    For a table with a large amount of data ,i/o Under efficiency , Sub table is imperative ! Use program to divide , For different queries , Assign to different subtables , It's a solution , But change the code , Opaque to queries . Fortunately mysql There are two solutions : Partition( branch ...

  5. The first 3 Chapter MySQL Introduction to storage engine

    The first 3 Chapter MySQL Introduction to storage engine Preface 3.1 MySQL Storage engine overview MyISAM The storage engine is MySQL The default storage engine , Also present MySQL One of the most widely used storage engines . His predecessor was us in ...

  6. MySQL Performance tuning and architecture design —— The first 3 Chapter MySQL Introduction to storage engine

    The first 3 Chapter MySQL Introduction to storage engine 3.1 MySQL Storage engine overview MyISAM The storage engine is MySQL The default storage engine , Also present MySQL One of the most widely used storage engines . His predecessor was us in MySQL In the course of development ...

  7. MySql A brief introduction to the two storage engines in common use

    MyISAM Introduction to storage engine MyISAM The tables of the storage engine are in the database , Each table is stored as three physical files named after the table . First of all, there must be a table structure definition information that is indispensable to any storage engine .frm file , And then there is .MYD and .M ...

  8. Use Merge Storage engine implementation MySQL table

    One . Use scenarios Merge A table is a bit like a view . Use Merge Storage engine implementation MySQL table , This method is more suitable for those who do not consider the sub table in advance , With the increase of data , There has been a slow data query . At this time, if we want to scale the existing big data ...

  9. Mysql And MERGE Storage engine

    MERGE Storage engine MyISAM The data table is treated as a logical unit , So we can query them at the same time . Constitute a MERGE Members of the data table structure MyISAM Data tables must have exactly the same table structure . Every member ...

Random recommendation

  1. Nodejs The event engine libuv Source analysis of : Efficient queues (queue) The implementation of the

      Statement : This is an original blog post , Reprint please indicate the source . stay libuv in , There is an efficient queue encapsulated in simple macros (queue), Now let's see how it works . First , to glance at queue Some of the most basic macros in : typede ...

  2. How to make local jar Packages uploaded to maven In the local warehouse

    First of all, the local jar Make it mvn install To the local warehouse mvn install:install-file -Dfile=D:\skyeye-ruleInfo\lib\lucene-querypa ...

  3. ArcGIS10 The accessory function of

    from   Jisiyuan  http://blog.csdn.net/linghe301/article/details/6386176 Always forgetting how to use this ArcGIS10 The accessory function of , Make a record this time . In the project should ...

  4. git More basic commands

    We need to know more about git The organizational form of : git clone *.git Download it to git Mode management If you can't download the compressed package directly git branch Branch related commands git checkout You can change branches git ...

  5. JS Jump out of the frame and go back to the previous page

    Link part <a class="link" href="javascript:;" target="_top" onclick=&quo ...

  6. VS2012 To configure Cocos2d-x The problem of

    cocos2d-x The configuration is always unsuccessful , Solution reference :http://blog.csdn.net/yangjingui/article/details/9408007 Complete configuration process : 1 download , Best to pass SVN ...

  7. Tesseract Engine translation

    Tesseract Engine translation Category: Image recognition Last Edited: Sep 17, 2018 10:29 AM Tags: tesseract, Character recognition , translate 1. The original English text ( Chinese translation ...

  8. [openjudge- Search for ] The castle problem (The Castle)

    Title Description describe chart 1 It's a topographic map of a castle . Please write a program , Count the total number of rooms in the castle , How big is the biggest room . The castle is divided into mn(m≤50,n≤50) Square block , Each square can have 0~4 face a wall . Input Program from standard input device ...

  9. python Ways to speed up data processing

    1. It's best to use intranet connection for all database operations , 2. Use batch operation interface to operate database , Instead of multithreading a single piece of data frequently 3. If python Process cpu Utilization rate reaches 100% 了 , You need to start multiple processes .java Single process cpu Usage rate in ...

  10. _ programing language _C++_std

    Normal use cout << "Count is "<<i<<endl; contain std std::cout << "Count ...