Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the advantages of MysqlMerge table". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What are the advantages of MysqlMerge table

A: separate static and dynamic data

B: optimize queries using data with similar structures

C: you can access less data when querying

D: easier to maintain big data collection

E: you can modify the Merge table by modifying the .mrg file, of course, you can also modify it with alter. After modification, you need to refresh the table cache through FLUSHTABLES. This method can dynamically increase or decrease the number of child tables.

Creation method, for example:

Mysql > CREATETABLEt1 (aINTNOTNULLPRIMARYKEY) ENGINE=MyISAM

Mysql > CREATETABLEt2 (aINTNOTNULLPRIMARYKEY) ENGINE=MyISAM

Mysql > CREATETABLEmrg (aINTNOTNULLPRIMARYKEY) ENGINE=MERGEUNION= (T1 penny T2) INSERT_METHOD=LAST

Let's add test data

Mysql > INSERTINTOt1 (a) VALUES (1), (2)

Mysql > INSERTINTOt2 (a) VALUES (1), (2)

Check to see the results.

Mysql > SELECTaFROMmrg

The result will show the data in the two tables of T1 and T2

+-+

| | a |

+-+

| | 1 |

| | 2 |

| | 1 |

| | 2 |

+-+

What aspects should be paid attention to in MysqlMerge table

one. The structure of each child table must be consistent, and the structure of the main table and child table must be consistent.

two. The index of each child table exists in the merge table, so it cannot be uniquely retrieved in the merge table based on that index.

3 subtables need to be MyISAM engines

4REPLACE does not work in the merge table

5AUTO_INCREMENT doesn't work the way you want it to.

The parameter to create the MysqlMerge table, INSERT_METHOD, has several parameters.

LAST if you execute the insert instruction to manipulate the merge table, the insert operation adds the data to the last child table. Similarly, FIRST adds data to the first child table when inserting data is performed.

For example, in this example, an insert operation is performed on the merge table

Mysql > INSERTINTOmrg (a) VALUES (18)

Inquire about

Mysql > SELECTaFROMt2

As a result, you will find 18 in the T2 table.

If you DROP a mrg table or child table, some unpredictable situations may occur.

If you delete the mrg table, there will be no association between the child tables. But if you delete any of these child tables, the merge table structure and data still exist for GNU/LINUX.

Mysql > DROPTABLEt1,t2

Mysql > SELECTaFROMmrg

As a result, you will find that the query result of the mrg table remains the same.

This is the end of the content of "what are the advantages of MysqlMerge Table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.