MySQL MERGE Storage Engine

Question

I am setting up a table in mysql engine type merge in mysql and wondered if I need to first create all my tables that I want to join. For instance:

 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; 

code>

Now, if I have code that automatically created the t3 table, would I have to modify the merge table to add this to the join? Would I use an ALTER query for this?

note: I do not use MySQL partitions because I have mysql version 5.0.

Answer 1

Now, if I have code that automatically created the t3 table, would I have to modify the merge table to add this to the join? Would I use an ALTER query for this?

From the documentation :

To reassign the MERGE table to another collection of MyISAM tables, you can use one of the following methods:

• DROP MERGE table and recreate it.
• Use ALTER TABLE tbl_name UNION=(...) to change the list of base tables.

Starting with MySQL 5.0.60, it is also possible to use A LTER TABLE ... UNION=() (that is, with an empty UNION clause) to delete all base tables.