Enhanced MySQL 5.5 partition functions

Figure 1 you have not noticed that MySQL has a strong partition function.

　　Non-integer column Partition

Anyone who uses too many partitions should have encountered many problems. Especially for non-integer column partitions, MySQL 5.1 can only process integer column partitions. If you want to partition on a date or string column, you have to use the function to convert it.

MySQL 5.5 adds two types of partitioning methods, RANG and LIST partitioning, and adds a COLUMNS keyword to the new function. Suppose we have a table like this:

Create table expenses (
Expense_date date not null,
Category VARCHAR (30 ),
Amount DECIMAL (10, 3 ));

If you want to use the partition type in MySQL 5.1, you must convert the type to an integer and use an additional table to search for it in MySQL 5.5, you do not need to convert the type, for example:

Alter table expenses partition by list columns (category )(
PARTITION p01 values in (lodging, food ),
PARTITION p02 values in (flights, ground transportation), PARTITION p03 values in (leisure, customer entertainment ),
PARTITION p04 values in (communications ),
PARTITION p05 values in (fees ));

In addition to easier reading, such partition statements clearly organize and manage data. The preceding example only partitions the category column.

Another headache for using partitions in MySQL 5.1 is the date type (that is, the date columns). You cannot use them directly. You must use YEAR or TO_DAYS to convert these columns, for example:

/* In MySQL 5.1 */
Create table t2
(
Dt DATE
)
Partition by range (TO_DAYS (dt ))
(
PARTITION p01 values less than (TO_DAYS (2007-01-01 )),
PARTITION p02 values less than (TO_DAYS (2008-01-01 )),
PARTITION p03 values less than (TO_DAYS (2009-01-01 )),
PARTITION p04 values less than (MAXVALUE ));
Show create table t2G ***************************** 1