Non-integer column partitions in MySQL 5.5

Last time we introduced:For MySQL 5.5Detailed description of multiple partition columns. This article describes how to partition MySQL 5.5Non-integer column PartitionNext, let's take a look at this part!

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, 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 ('2017-01-01 ')),
  
  
PARTITION p02 values less than (TO_DAYS ('2017-01-01 ')),
  
  
PARTITION p03 values less than (TO_DAYS ('2017-01-01 ')),
  
  
PARTITION p04 values less than (MAXVALUE ));
  
  
 
  
  
Show create table t2 \ G
  
  
* *************************** 1. row ***************************
  
  
Table: t2
  
  
Create Table: create table 't2 '(
  
  
'Dt' date DEFAULT NULL
  
  
) ENGINE = MyISAM default charset = latin1
  
  
/*! 50100 partition by range (TO_DAYS (dt ))
  
  
(PARTITION p01 values less than (733042) ENGINE = MyISAM,
  
  
PARTITION p02 values less than (733407) ENGINE = MyISAM,
  
  
PARTITION p03 values less than (733773) ENGINE = MyISAM,
  
  
PARTITION p04 values less than maxvalue engine = MyISAM )*/
 
 

It looks terrible. Of course there are some alternatives, but there are indeed a lot of troubles. It is confusing to define a partition using YEAR or TO_DAYS. You have to use bare columns when querying, because the query with a function cannot identify partitions.

But in MySQL 5.5, the situation has changed a lot. Now we can partition directly in the date column, and the method is also very simple.

 
 

  
  
/* In MySQL 5.5 */
  
  
Create table t2
  
  
(
  
  
Dt DATE
  
  
)
  
  
Partition by range columns (dt)
  
  
(
  
  
PARTITION p01 values less than ('2017-01-01 '),
  
  
PARTITION p02 values less than ('2017-01-01 '),
  
  
PARTITION p03 values less than ('2017-01-01 '),
  
  
PARTITION p04 values less than (MAXVALUE ));
  
  
 
  
  
Show create table t2 \ G
  
  
* *************************** 1. row ***************************
  
  
Table: t2
  
  
Create Table: create table 't2 '(
  
  
'Dt' date DEFAULT NULL
  
  
) ENGINE = MyISAM default charset = latin1
  
  
/*! 50500 partition by range columns (dt)
  
  
(PARTITION p01 values less than ('1970-01-01 ') ENGINE = MyISAM,
  
  
PARTITION p02 values less than ('2017-01-01 ') ENGINE = MyISAM,
  
  
PARTITION p03 values less than ('1970-01-01 ') ENGINE = MyISAM,
  
  
PARTITION p04 values less than (MAXVALUE) ENGINE = MyISAM )*/
 
 

Here, there is no conflict between function definitions and column queries, because the values are defined by columns and the values we insert in the definitions are retained.

Here is an introduction to the non-integer column partition of MySQL 5.5. I hope this introduction will be helpful to you!

Source: http://lujia35.iteye.com/blog/718899.