MySQL MERGE Storage Engine

Summary: in this tutorial, you will learn about MySQL MERGE storage engine and how to use it effectively.

Introduction to MySQL MERGE storage engine

The MERGE storage engine allows you to create a special table called a MERGE table that consists of multiple MyISAM tables with identical column data types.

Once you have the merged table, you can manage multiple underlying MyISAM tables as if they were one table.

In other words, the MERGE storage engine is a collection of identical MyISAM tables that can be used as one.

The MERGE storage engine is also known as MRG_MyISAM.

The underlying MyISAM tables need to meet the following conditions to participate in a MERGE table:

All tables have the same number of columns with the same data type in the same order.
All tables have the same indexes in the same order.

Creating a MERGE table

To create a MERGE table, you use the following CREATE TABLE statement:

CREATE TABLE merge_table_name(
    column_list
) ENGINE=MERGE UNION = (t1, t2, ...) [INSERT_METHOD=FIRST|LAST|NO];Code language: SQL (Structured Query Language) (sql)

In this syntax:

ENGINE=MERGE clause specifies that the table is a MERGE table.
UNION=(t1, t2, ...) allows you to list the MyISAM table for inclusion in the MERGE table.
INSERT_METHOD controls how inserts into the MERGE table occur. It has three options: FIRST, LAST, and NO. You use the FIRST and LAST to instruct the storage engine to insert to the first or last underlying table respectively. If you use NO, the storage engine won’t allow you to insert data into the MERGE table.

When you create a MERGE table in MySQL, it generates a .MRG file containing the names of the underlying MyISAM tables, and it also stores the MERGE table format in the data dictionary.

MySQL also allows you to create a MERGE table that combines underlying tables from different databases.

Removing a MERGE table

To drop a MERGE table, you use the DROP TABLE statement:

DROP TABLE merge_table_name;Code language: SQL (Structured Query Language) (sql)

When you drop a MERGE table, MySQL drops only the MERGE specification and doesn’t delete the underlying MyISAM tables.

Changing the underlying tables of a MERGE table

To remap a MERGE table to a different collection of MyISAM tables, you can use one of the following methods:

1) Drop the MERGE table and re-create it:

DROP TABLE merge_table_name;

CREATE TABLE merge_table_name(
   ...
) ENGINE=MERGE UNION=(t1, t2, ...);Code language: SQL (Structured Query Language) (sql)

2) Use ALTER TABLE to change the list of underlying tables:

ALTER TABLE merge_table_name
UNION=(t1,t2,...);Code language: SQL (Structured Query Language) (sql)

Manipulating data of a MERGE table

MySQL allows you to perform INSERT, UPDATE, DELETE, and SELECT on MERGE tables. However, you need to have corresponding privileges on the underlying MyISAM tables that map to the MERGE tables.

If a user has access to MyISAM table t, the user can create MERGE table m that accesses the MyISAM table t. But if the user’s privilege on table t is revoked, the user continues to have access to table t via the merge table m. This is the security issue that you need to be aware of when working with MERGE tables.

MySQL MERGE storage engine example

First, create a new MyISAM table called t1:

CREATE TABLE t1(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) NOT NULL
) ENGINE=MyISAM;Code language: SQL (Structured Query Language) (sql)

Second, create another MyISAM table called t2 with identical columns and data types as the table t1:

CREATE TABLE t2(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) NOT NULL
) ENGINE=MyISAM;Code language: SQL (Structured Query Language) (sql)

Third, insert rows into the t1 and t2 tables:

INSERT INTO t1(name) VALUES('John');
INSERT INTO t1(name) VALUES('Jane');
INSERT INTO t2(name) VALUES('Bob');
INSERT INTO t2(name) VALUES('Alice');Code language: SQL (Structured Query Language) (sql)

Fourth, create a MERGE table that includes the MyISAM table t1 and t2:

CREATE TABLE t(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) NOT NULL
) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;Code language: SQL (Structured Query Language) (sql)

Fifth, query data from the MERGE table:

SELECT 
  id, 
  name 
FROM 
  t;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Jane  |
|  1 | Bob   |
|  2 | Alice |
+----+-------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Sixth, insert a new row into the MERGE table:

INSERT INTO t(name) VALUES("Peter");Code language: SQL (Structured Query Language) (sql)

The storage engine inserts the row into the t2 table because we set the INSERT_METHOD to LAST in the CREATE TABLE statement.

Seventh, query data from the t2 table:

SELECT * FROM t2;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------+
| id | name  |
+----+-------+
|  1 | Bob   |
|  2 | Alice |
|  3 | Peter |
+----+-------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Eighth, delete a row from the MERGE table with id of 2:

DELETE FROM t WHERE id = 2;Code language: SQL (Structured Query Language) (sql)

The storage engine deletes rows from the table t1 first. If you query data from the t1 table, you’ll see that the row with id 2 is deleted:

SELECT * FROM t1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If you execute the DELETE statement that deletes the row with id 2, the storage engine will delete the row in the t2 table:

DELETE FROM t WHERE id = 2;Code language: SQL (Structured Query Language) (sql)

Ninth, query data from the t2 table:

SELECT * FROM t2;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------+
| id | name  |
+----+-------+
|  1 | Bob   |
|  3 | Peter |
+----+-------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the MERGE storage engine to create a MERGE table which is a collection of identical MyISAM tables that can be used as one.

Was this tutorial helpful?