MySQL Reference Manual

MERGE Tables

MERGE tables are new in MySQL Version 3.23.25. The code is still in gamma, but should be reasonable stable.

A MERGE table (also known as a MRG_MyISAM table) is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.

Note that DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables. (We plan to fix this in 4.1.)

With identical tables we mean that all tables are created with identical column and key information. You can’t put a MERGE over tables where the columns are packed differently, don’t have exactly the same columns, or have the keys in a different order. Some of the tables can, however, be compressed with myisampack. See Section 4.7.4.

When you create a MERGE table, you will get a .frm table definition file and a .MRG table list file. The .MRG just contains a list of the index files (.MYI files) that should be used as one. All used tables must be in the same database as the MERGE table itself.

For the moment, you need to have select, update, and delete privileges on the tables you map to a MERGE table.

MERGE tables can help you solve the following problems:

• Easily manage a set of log tables. For example, you can put data from different months into separate files, compress some of them with myisampack, and then create a MERGE to use these as ...