看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。
但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。
/*在MySQL 5.5中*/
CREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
PARTITION p01 VALUES LESS THAN ('2007-01-01'),
PARTITION p02 VALUES LESS THAN ('2008-01-01'),
PARTITION p03 VALUES LESS THAN ('2009-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)
CREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
PARTITION p01 VALUES LESS THAN ('2007-01-01'),
PARTITION p02 VALUES LESS THAN ('2008-01-01'),
PARTITION p03 VALUES LESS THAN ('2009-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 ('2007-01-01') ENGINE = MyISAM,
PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,
PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
PARTITION p02 VALUES LESS THAN ('2008-01-01') ENGINE = MyISAM,
PARTITION p03 VALUES LESS THAN ('2009-01-01') ENGINE = MyISAM,
PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。
多列分区
COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区，你可能在官方文档中已经看到了一些例子，如：
CREATE TABLE p1
(
a INT,
b INT,
c INT )
PARTITION BY RANGE COLUMNS (a,b)
(
PARTITION p01 VALUES LESS THAN (10,20),
PARTITION p02 VALUES LESS THAN (20,30),
PARTITION p03 VALUES LESS THAN (30,40),
(
a INT,
b INT,
c INT )
PARTITION BY RANGE COLUMNS (a,b)
(
PARTITION p01 VALUES LESS THAN (10,20),
PARTITION p02 VALUES LESS THAN (20,30),
PARTITION p03 VALUES LESS THAN (30,40),
PARTITION p04 VALUES LESS THAN (40,MAXVALUE),
PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE p2
(
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS (a,b)
(
PARTITION p01 VALUES LESS THAN (10,10),
PARTITION p02 VALUES LESS THAN (10,20),
PARTITION p03 VALUES LESS THAN (10,30),
PARTITION p04 VALUES LESS THAN (10,MAXVALUE),
PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
)
PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE p2
(
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS (a,b)
(
PARTITION p01 VALUES LESS THAN (10,10),
PARTITION p02 VALUES LESS THAN (10,20),
PARTITION p03 VALUES LESS THAN (10,30),
PARTITION p04 VALUES LESS THAN (10,MAXVALUE),
PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
)
同样还有PARTITION BY RANGE COLUMNS (a,b,c)等其它例子。由于我很长时间都在使用MySQL 5.1的分区，我对多列分区的含义不太了解，LESS THAN (10,10)是什么意思?如果下一个分区是LESS THAN (10,20)会发生什么?相反，如果是(20,30)又会如何?
所有这些问题都需要一个答案，在回答之前，他们需要更好地理解我们在做什么。
开始时可能有些混乱，当所有分区有一个不同范围的值时，实际上，它只是在表的一个列上进行了分区，但事实并非如此，在下面的例子中：
CREATE TABLE p1_single
(
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS (a)
(
PARTITION p01 VALUES LESS THAN (10),
PARTITION p02 VALUES LESS THAN (20),
PARTITION p03 VALUES LESS THAN (30),
PARTITION p04 VALUES LESS THAN (40),
PARTITION p05 VALUES LESS THAN (MAXVALUE)
);
(
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS (a)
(
PARTITION p01 VALUES LESS THAN (10),
PARTITION p02 VALUES LESS THAN (20),
PARTITION p03 VALUES LESS THAN (30),
PARTITION p04 VALUES LESS THAN (40),
PARTITION p05 VALUES LESS THAN (MAXVALUE)
);
