MERGE Storage Engine in MySQL
MERGE Storage Engine in MySQL
The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one.
“Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. However, any or all of the MyISAM tables can be compressed with myisampack.
Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS do not matter.
Example:
CREATE TABLE t1 (msgid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
CREATE TABLE t2 (msgid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
here, Table t1 and t2 is Created with fields msgid and message.
Now, the records in the table t1 and table t2 is merged and stored in the table total. by using MERGE Engine.
CREATE TABLE total (msgid INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(msgid)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
