MENU


MERGE

The MERGE storage engine, also known as MRG_MyISAM, presents a collection of identical MyISAM tables as a single virtual table. "Identical" means every underlying table has the same column and index definitions. See Storage Engines for how it compares to the others.

CREATE TABLE t1 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20)) ENGINE=MyISAM;

CREATE TABLE t2 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (
   a INT NOT NULL AUTO_INCREMENT,
   message CHAR(20), INDEX(a))
   ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Two identically-structured MyISAM tables combined into a single queryable MERGE table. INSERT_METHOD=LAST directs new rows inserted through total into t2, the last table in the UNION list.
FeatureMERGE
Storage limitsSum of the underlying MyISAM tables (each up to 256TB)
TransactionsNo
Locking granularityTable
MVCCNo
Geospatial data type supportNo
Geospatial indexing supportNo
B-tree indexesYes
T-tree indexesNo
Hash indexesNo
Full-text search indexesNo
Clustered indexesNo
Data cachesNo
Index cachesYes
Compressed dataNo
Encrypted dataNo
Cluster database supportNo
Replication supportYes
Foreign key supportNo
Backup / point-in-time recoveryNo (back up the underlying MyISAM tables instead)
Query cache supportYes
Update statistics for data dictionaryNo
MERGE tables do not support Partitioning. As with MyISAM, a MERGE table's underlying tables must not be transactional, and it inherits MyISAM's table-level locking. 

CREATE TABLE sales_2023 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_2024 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_all (
    id INT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10,2),
    INDEX(id)
) ENGINE = MERGE UNION = (sales_2023, sales_2024) INSERT_METHOD = LAST;

INSERT INTO sales_all (amount) VALUES (150.00); -- lands in sales_2024
SELECT SUM(amount) FROM sales_all;               -- queries both years at once

Query OK, 1 row affected
SUM(amount)
150.00