MySQL Merge table introduction

In the MySQL database, the MySQL merge table is somewhat similar to the view. Let's take a look at the advantages of the MySQL merge table and hopefully it will help you.

mysql Merge Table Benefits:
A: Separating static and Dynamic Data
B: Optimizing queries with data that is close to the structure
C: Less data can be accessed at query
D: Easier to maintain large datasets
E: You can modify the merge table by modifying the. mrg file, which can also be modified by using ALTER, which refreshes the table cache by flush tables, which dynamically increases the reduction of the sub-table
Create method, Example:
mysql>create TABLE T1 (a INT not NULL PRIMARY KEY) Engine=myisam;
Mysql>create TABLE T2 (a INT not NULL PRIMARY KEY) Engine=myisam;
Mysql>create TABLE MRG (a INT not NULL PRIMARY KEY) engine=merge union= (T1,T2)          Insert_method=last;
Add test data below
Mysql>insert into T1 (a) VALUES (1), (2);
Mysql>insert into T2 (a) VALUES (1), (2);
Check the results
Mysql>select a from MRG;
The result shows the data in T1,t2 two tables
+------+
|  a     |
+------+
|       1|
|       2|
|       1|
|       2|
+------+

for the merge table, you need to be aware of    
1. The structure of each child table must be consistent, and the structure of the primary and child tables needs to be consistent,
2. The index of each child table will be present in the merge table, so it is not possible to retrieve it uniquely from the merge table based on that index.
3  child table needs to be MyISAM engine
4   Replace does not work in the merge table
5   auto_increment does not work the way you expect.

The parameters for creating MySQL merge table Insert_method have several parameters.
last  If you perform an insert instruction to manipulate the merge table, the insert operation adds the data to the last child table. first  in the same vein, data is added to the first child table when the data is inserted.
For example, perform an insert operation on the merge table
Mysql>insert into MRG (a) VALUES;
Check the
Mysql>select a from T2;
As a result you will find that 18 appears in the T2 table.
---------------------------------------------------------------
If you drop an MRG table or a child table, you might have unpredictable conditions.
If you delete the MRG table, there will be no association between the individual child tables. However, if you delete any of these child tables, the merge table structure and data still exist for Gnu/linux.
Mysql>drop TABLE t1,t2;
Mysql>select a from MRG;
Results you will find that the query results for the MRG table do not change.

He queries multiple tables logically as a table. After he was established there were two documents,
. FRM table Structure definition
List of names of. MRG Union tables

--
--the structure of the merger table ' Test_merge '
--

CREATE TABLE IF not EXISTS ' Test_merge ' (
' ID ' int (5) not NULL auto_increment,
' Names ' varchar (+) not NULL,
PRIMARY KEY (' id ')
) Engine=mrg_myisam DEFAULT charset=gb2312 insert_method=last union= (' test_merge_1 ', ' test_merge_2 ', ' test_merge_3 ');

--
--Export the data in the table ' Test_merge '
--

INSERT into ' test_merge ' (' id ', ' names ') VALUES
(1, ' AA '),
(1, ' BB '),
(1, ' cc ');

-- --------------------------------------------------------

--
--The structure of the basic table ' Test_merge_1 '
--

CREATE TABLE IF not EXISTS ' test_merge_1 ' (
' ID ' int (5) not NULL auto_increment,
' Names ' varchar (+) not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=gb2312 auto_increment=3;

--
--Export the data in the table ' Test_merge_1 '
--

INSERT into ' test_merge_1 ' (' id ', ' names ') VALUES
(1, ' AA ');

-- --------------------------------------------------------

--
--The structure of the basic table ' test_merge_2 '
--

CREATE TABLE IF not EXISTS ' test_merge_2 ' (
' ID ' int (5) not NULL auto_increment,
' Names ' varchar (+) not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=gb2312 auto_increment=2;

--
--Export the data in the table ' test_merge_2 '
--

INSERT into ' test_merge_2 ' (' id ', ' names ') VALUES
(1, ' BB ');

-- --------------------------------------------------------

--
--The structure of the basic table ' Test_merge_3 '
--

CREATE TABLE IF not EXISTS ' Test_merge_3 ' (
' ID ' int (5) not NULL auto_increment,
' Names ' varchar (+) not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=gb2312 auto_increment=2;

--
--Export the data in the table ' Test_merge_3 '
--

insert into ' test_merge_3 ' (' id ', ' names ') VALUES
(1, ' cc ');
1. This table is similar to the union mechanism in SQL.  
2. This table structure must be exactly the same as the base table, including the column name and order. The Union table must belong to a database.  
3. The base table type must be MyISAM.  
4. You can modify the merge table by modifying the. mrg file, one row for each base table name. Note: The table cache will be refreshed with flush tables after modification.  
5. Changes to the base table can be reflected directly on this table.  
6. The value of the Insert_method can be: 0 inserting first into the Union is not allowed to insert the last table that is inserted into the final table in the Union. (available after 4.0)  
7. The constraint defined above it has no effect, and the constraint is controlled by the base table, such as the existence of the same key value in the two base table, then there will be two identical key values in the merge table.