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

Today, I will talk to you about how to achieve the partition function in MySQL 5.5. many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Non-integer column partition

Anyone who has used partitions should have encountered a lot of problems, especially when dealing with non-integer column partitions, MySQL 5.1 can only handle integer column partitions, if you want to partition on date or string columns, you have to use functions to convert them.

Two new partitioning methods, RANG and LIST partitioning, have been added to MySQL 5.5.At the same time, a COLUMNS keyword has been added to the new function. Let's assume that there is a table like this:

CREATE TABLE expenses (

Expense_date DATE NOT NULL

Category VARCHAR (30)

Amount DECIMAL (10par 3))

If you want to use the partition type in MySQL 5.1, you must convert the type to an integer, and you need to use an additional lookup table. In MySQL 5.5. you can no longer do type conversion, such as:

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 being easier to read, such partitioning statements are also very clear about the organization and management of the data. The above example only partitions the category column.

Another headache in using partitions in MySQL 5.1 is the date type (that is, date columns). You can't use them directly, you must convert them using YEAR or TO_DAYS, such as:

/ * in MySQL 5.1s * /

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

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.