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

This article will explain in detail how to achieve multi-column partition in MySQL5.5, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

What is the multi-column partition of MySQL5.5

The COLUMNS keyword now allows strings and date columns to be used as partition definition columns, as well as multiple columns to define a partition. You may have seen some examples in the official documentation, such as:

CREATETABLEp1 (aINT,bINT,cINT) PARTITIONBYRANGECOLUMNS (Arecore b) (PARTITIONp01VALUESLESSTHAN (10Mague 20), PARTITIONp02VALUESLESSTHAN (20Lecture 30), PARTITIONp03VALUESLESSTHAN (30Mague 40), PARTITIONp04VALUESLESSTHAN (40Lecture MAXVALUE), PARTITIONp05VALUESLESSTHAN (MAXVALUE,MAXVALUE); CREATETABLEp2 (aINT,bINT,cINT) PARTITIONBYRANGECOLUMNS (aLind b) (PARTITIONp01VALUESLESSTHAN (10Lance 10), PARTITIONp02VALUESLESSTHAN (10L20), PARTITIONp03VALUESLESSTHAN (10L30), PARTITIONp04VALUESLESSTHAN (10Lance MAXVALUE), PARTITIONp05VALUESLESSTHAN (MAXVALUE,MAXVALUE))

There are also other examples, such as PARTITIONBYRANGECOLUMNS (a _ r _ b _ r _ c). Since I've been using MySQL5.1 partitions for a long time, I don't know much about the meaning of multi-column partitions. What does LESSTHAN (10) mean? What happens if the next partition is LESSTHAN (1010)? On the contrary, what if it is (200.30)?

All of these questions need an answer, and before they can answer them, they need to better understand what we are doing.

What is the multi-column partition of MySQL5.5

It may be a little confusing at first, when all partitions have a different range of values, in fact, it is only partitioned on a column of the table, but this is not the case, in the following example

CREATETABLEp1_single (aINT,bINT,cINT) PARTITIONBYRANGECOLUMNS (a) (PARTITIONp01VALUESLESSTHAN (10), PARTITIONp02VALUESLESSTHAN (20), PARTITIONp03VALUESLESSTHAN (30), PARTITIONp04VALUESLESSTHAN (40), PARTITIONp05VALUESLESSTHAN (MAXVALUE))

It is not the same as the previous table p1, if you insert it in table p1 (10 p1_single), it will enter the first partition, on the contrary, in table p1, it will enter the second partition, the reason is that (10 minute 1) is less than (10 minute 10), if you only focus on the first value, you do not realize that you are comparing a tuple, rather than a single value.

Now let's analyze the most difficult part, what happens when you need to determine where a line should be placed? How do you determine something similar (10pd9)

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.