Considerations and advantages of using Mysql Merge tables

Considerations and advantages of using Mysql Merge tables in Mysql databases, Mysql Merge tables are somewhat similar to views. Next let's take a look at the advantages of Mysql Merge tables. Advantages of Mysql Merge table: www.2cto.com A: Separate Static and Dynamic Data B: Optimize Query with data close to the structure C: Access less data during query D: easier to maintain big data set E: You can modify it. the mrg file can be used to modify the Merge table. You can also use alter to modify the table. After the modification, you must use flush tables to refresh the table cache. This method can dynamically increase or decrease the subtable creation method, for example: www.2cto.com 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; mysql> insert into t1 (a) VALUES (1), (2); mysql> insert into t2 (a) VALUES (1 ), (2) query the result. mysql> SELECT a FROM mrg. The result is t1, data in table t2 is + ------ + | a | + ------ + | 1 | 2 | 1 | 2 | + ------ + Note 1 For the merge table. The structure of each sub-table must be consistent. The structure of the master table and sub-table must be consistent. The index of each sub-table exists in the merge table. Therefore, you cannot perform a unique search based on the index in the merge table. 3. The sub-table must be MyISAM engine. 4 REPLACE will not work in the merge table. 5 AUTO_INCREMENT will not work as expected. The INSERT_METHOD parameter used to create a Mysql Merge table has several parameters. LAST if you execute the insert command to operate the merge table, the insert operation adds the data to the LAST sub-table. Similarly, when inserting data, the data is added to the FIRST sub-table. For example, in this example, INSERT mysql> insert into mrg (a) VALUES (18) to the merge table; query mysql> SELECT a FROM t2; as a result, 18 is displayed in table t2. --------------------------------------------------------------- If you perform the DROP operation on the mrg table or sub-table, unpredictable conditions may occur. If you delete a mrg table, there is no association between the sub-tables. However, if you delete any of the sub-tables, the merge table structure and data still exist for GNU/LINUX. Mysql> drop table t1, t2; mysql> SELECT a FROM mrg; you will find that the query results of the mrg TABLE remain unchanged.