前言：最近的记忆力越来越差了，好多项目中刚刚做过的技术，过段时间就忘记了。所以不得不开始写博客了，把平时遇到的一些bug排除过程和尝试的新技术记录下来。
项目中有一张表有上亿的数据，根据业务的需求，需要对该表按照时间（每个月）进行表分区。PS：该项目的数据库之前一直是sql server，现在由于客户的要求，改为Mysql。
先说一下总结，再慢慢讲一下填坑的过程：
对于mysql5.6.27版本里有两种时间格式的数据类型：datetime和timestamp。RANGE分区，如果是datetime：应使用TO_DAYS()这个函数。我的具体代码如下：
ALTER TABLE t_ec_energyitemhourresult
PARTITION BY RANGE(TO_DAYS(F_StartHour))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p5 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p6 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION p7 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p8 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p9 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p10 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION p11 VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION p12 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p13 VALUES LESS THAN (TO_DAYS('2013-02-01')),
PARTITION p14 VALUES LESS THAN (TO_DAYS('2013-03-01')),
PARTITION p15 VALUES LESS THAN (TO_DAYS('2013-04-01')),
PARTITION p16 VALUES LESS THAN (TO_DAYS('2013-05-01')),
PARTITION p17 VALUES LESS THAN (TO_DAYS('2013-06-01')),
PARTITION p18 VALUES LESS THAN (TO_DAYS('2013-07-01')),
PARTITION p19 VALUES LESS THAN (TO_DAYS('2013-08-01')),
PARTITION p20 VALUES LESS THAN (TO_DAYS('2013-09-01')),
PARTITION p21 VALUES LESS THAN (TO_DAYS('2013-10-01')),
PARTITION p22 VALUES LESS THAN (TO_DAYS('2013-11-01')),
PARTITION p23 VALUES LESS THAN (TO_DAYS('2013-12-01')),
PARTITION p24 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION p25 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION p26 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION p27 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION p28 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION p29 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION p30 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION p31 VALUES LESS THAN (TO_DAYS('2014-08-01')),
PARTITION p32 VALUES LESS THAN (TO_DAYS('2014-09-01')),
PARTITION p33 VALUES LESS THAN (TO_DAYS('2014-10-01')),
PARTITION p34 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION p35 VALUES LESS THAN (TO_DAYS('2014-12-01')),
PARTITION p36 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION p37 VALUES LESS THAN (TO_DAYS('2015-02-01')),
PARTITION p38 VALUES LESS THAN (TO_DAYS('2015-03-01')),
PARTITION p39 VALUES LESS THAN (TO_DAYS('2015-04-01')),
PARTITION p40 VALUES LESS THAN (TO_DAYS('2015-05-01')),
PARTITION p41 VALUES LESS THAN (TO_DAYS('2015-06-01')),
PARTITION p42 VALUES LESS THAN (TO_DAYS('2015-07-01')),
PARTITION p43 VALUES LESS THAN (TO_DAYS('2015-08-01')),
PARTITION p44 VALUES LESS THAN (TO_DAYS('2015-09-01')),
PARTITION p45 VALUES LESS THAN (TO_DAYS('2015-10-01')),
PARTITION p46 VALUES LESS THAN (TO_DAYS('2015-11-01')),
PARTITION p47 VALUES LESS THAN (TO_DAYS('2015-12-01')),
PARTITION p48 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p49 VALUES LESS THAN (TO_DAYS('2016-02-01')),
PARTITION p50 VALUES LESS THAN (TO_DAYS('2016-03-01')),
PARTITION p51 VALUES LESS THAN (TO_DAYS('2016-04-01')),
PARTITION p52 VALUES LESS THAN (TO_DAYS('2016-05-01')),
PARTITION p53 VALUES LESS THAN (TO_DAYS('2016-06-01')),
PARTITION p54 VALUES LESS THAN (TO_DAYS('2016-07-01')),
PARTITION p55 VALUES LESS THAN (TO_DAYS('2016-08-01')),
PARTITION p56 VALUES LESS THAN (TO_DAYS('2016-09-01')),
PARTITION p57 VALUES LESS THAN (TO_DAYS('2016-10-01')),
PARTITION p58 VALUES LESS THAN (TO_DAYS('2016-11-01')),
PARTITION p59 VALUES LESS THAN (TO_DAYS('2016-12-01')),
PARTITION p60 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p61 VALUES LESS THAN (TO_DAYS('2017-02-01')),
PARTITION p62 VALUES LESS THAN (TO_DAYS('2017-03-01')),
PARTITION p63 VALUES LESS THAN (TO_DAYS('2017-04-01')),
PARTITION p64 VALUES LESS THAN (TO_DAYS('2017-05-01')),
PARTITION p65 VALUES LESS THAN (TO_DAYS('2017-06-01')),
PARTITION p66 VALUES LESS THAN (TO_DAYS('2017-07-01')),
PARTITION p67 VALUES LESS THAN (TO_DAYS('2017-08-01')),
PARTITION p68 VALUES LESS THAN (TO_DAYS('2017-09-01')),
PARTITION p69 VALUES LESS THAN (TO_DAYS('2017-10-01')),
PARTITION p70 VALUES LESS THAN (TO_DAYS('2017-11-01')),
PARTITION p71 VALUES LESS THAN (TO_DAYS('2017-12-01')),
PARTITION p72 VALUES LESS THAN (TO_DAYS('2018-01-01')),
PARTITION p73 VALUES LESS THAN (TO_DAYS('2018-02-01')),
PARTITION p74 VALUES LESS THAN (TO_DAYS('2018-03-01')),
PARTITION p75 VALUES LESS THAN (TO_DAYS('2018-04-01')),
PARTITION p76 VALUES LESS THAN (TO_DAYS('2018-05-01')),
PARTITION p77 VALUES LESS THAN (TO_DAYS('2018-06-01')),
PARTITION p78 VALUES LESS THAN (TO_DAYS('2018-07-01')),
PARTITION p79 VALUES LESS THAN (TO_DAYS('2018-08-01')),
PARTITION p80 VALUES LESS THAN (TO_DAYS('2018-09-01')),
PARTITION p81 VALUES LESS THAN (TO_DAYS('2018-10-01')),
PARTITION p82 VALUES LESS THAN (TO_DAYS('2018-11-01')),
PARTITION p83 VALUES LESS THAN (TO_DAYS('2018-12-01')),
PARTITION p84 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p85 VALUES LESS THAN (TO_DAYS('2019-02-01')),
PARTITION p86 VALUES LESS THAN (TO_DAYS('2019-03-01')),
PARTITION p87 VALUES LESS THAN (TO_DAYS('2019-04-01')),
PARTITION p88 VALUES LESS THAN (TO_DAYS('2019-05-01')),
PARTITION p89 VALUES LESS THAN (TO_DAYS('2019-06-01')),
PARTITION p90 VALUES LESS THAN (TO_DAYS('2019-07-01')),
PARTITION p91 VALUES LESS THAN (TO_DAYS('2019-08-01')),
PARTITION p92 VALUES LESS THAN (TO_DAYS('2019-09-01')),
PARTITION p93 VALUES LESS THAN (TO_DAYS('2019-10-01')),
PARTITION p94 VALUES LESS THAN (TO_DAYS('2019-11-01')),
PARTITION p95 VALUES LESS THAN (TO_DAYS('2019-12-01')),
PARTITION p96 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p97 VALUES LESS THAN (TO_DAYS('2020-02-01')),
PARTITION p98 VALUES LESS THAN (TO_DAYS('2020-03-01')),
PARTITION p99 VALUES LESS THAN (TO_DAYS('2020-04-01')),
PARTITION p100 VALUES LESS THAN (TO_DAYS('2020-05-01')),
PARTITION p101 VALUES LESS THAN (TO_DAYS('2020-06-01')),
PARTITION p102 VALUES LESS THAN (TO_DAYS('2020-07-01')),
PARTITION p103 VALUES LESS THAN (TO_DAYS('2020-08-01')),
PARTITION p104 VALUES LESS THAN (TO_DAYS('2020-09-01')),
PARTITION p105 VALUES LESS THAN (TO_DAYS('2020-10-01')),
PARTITION p106 VALUES LESS THAN (TO_DAYS('2020-11-01')),
PARTITION p107 VALUES LESS THAN (TO_DAYS('2020-12-01')),
PARTITION p108 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p109 VALUES LESS THAN (MAXVALUE)
);
如果是timestamp类型，应使用UNIX_TIMESTAMP函数转换一下。
ALTER TABLE t_ec_energyitemresult
PARTITION BY RANGE( UNIX_TIMESTAMP(F_StartTime))
(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2012-01-01')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2012-02-01')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2012-03-01')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2012-04-01')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2012-05-01')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2012-06-01')),
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2012-07-01')),
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2012-08-01')),
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-01')),
PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2012-10-01')),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2012-11-01')),
PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2012-12-01')),
PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01')),
PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2013-02-01')),
PARTITION p14 VALUES LESS THAN (UNIX_TIMESTAMP('2013-03-01')),
PARTITION p15 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),
PARTITION p16 VALUES LESS THAN (UNIX_TIMESTAMP('2013-05-01')),
PARTITION p17 VALUES LESS THAN (UNIX_TIMESTAMP('2013-06-01')),
PARTITION p18 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),
PARTITION p19 VALUES LESS THAN (UNIX_TIMESTAMP('2013-08-01')),
PARTITION p20 VALUES LESS THAN (UNIX_TIMESTAMP('2013-09-01')),
PARTITION p21 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),
PARTITION p22 VALUES LESS THAN (UNIX_TIMESTAMP('2013-11-01')),
PARTITION p23 VALUES LESS THAN (UNIX_TIMESTAMP('2013-12-01')),
PARTITION p24 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01')),
PARTITION p25 VALUES LESS THAN (UNIX_TIMESTAMP('2014-02-01')),
PARTITION p26 VALUES LESS THAN (UNIX_TIMESTAMP('2014-03-01')),
PARTITION p27 VALUES LESS THAN (UNIX_TIMESTAMP('2014-04-01')),
PARTITION p28 VALUES LESS THAN (UNIX_TIMESTAMP('2014-05-01')),
PARTITION p29 VALUES LESS THAN (UNIX_TIMESTAMP('2014-06-01')),
PARTITION p30 VALUES LESS THAN (UNIX_TIMESTAMP('2014-07-01')),
PARTITION p31 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01')),
PARTITION p32 VALUES LESS THAN (UNIX_TIMESTAMP('2014-09-01')),
PARTITION p33 VALUES LESS THAN (UNIX_TIMESTAMP('2014-10-01')),
PARTITION p34 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01')),
PARTITION p35 VALUES LESS THAN (UNIX_TIMESTAMP('2014-12-01')),
PARTITION p36 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01')),
PARTITION p37 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01')),
PARTITION p38 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-01')),
PARTITION p39 VALUES LESS THAN (UNIX_TIMESTAMP('2015-04-01')),
PARTITION p40 VALUES LESS THAN (UNIX_TIMESTAMP('2015-05-01')),
PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP('2015-06-01')),
PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP('2015-07-01')),
PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP('2015-08-01')),
PARTITION p44 VALUES LESS THAN (UNIX_TIMESTAMP('2015-09-01')),
PARTITION p45 VALUES LESS THAN (UNIX_TIMESTAMP('2015-10-01')),
PARTITION p46 VALUES LESS THAN (UNIX_TIMESTAMP('2015-11-01')),
PARTITION p47 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-01')),
PARTITION p48 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01')),
PARTITION p49 VALUES LESS THAN (UNIX_TIMESTAMP('2016-02-01')),
PARTITION p50 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01')),
PARTITION p51 VALUES LESS THAN (UNIX_TIMESTAMP('2016-04-01')),
PARTITION p52 VALUES LESS THAN (UNIX_TIMESTAMP('2016-05-01')),
PARTITION p53 VALUES LESS THAN (UNIX_TIMESTAMP('2016-06-01')),
PARTITION p54 VALUES LESS THAN (UNIX_TIMESTAMP('2016-07-01')),
PARTITION p55 VALUES LESS THAN (UNIX_TIMESTAMP('2016-08-01')),
PARTITION p56 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-01')),
PARTITION p57 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')),
PARTITION p58 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')),
PARTITION p59 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')),
PARTITION p60 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01')),
PARTITION p61 VALUES LESS THAN (UNIX_TIMESTAMP('2017-02-01')),
PARTITION p62 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01')),
PARTITION p63 VALUES LESS THAN (UNIX_TIMESTAMP('2017-04-01')),
PARTITION p64 VALUES LESS THAN (UNIX_TIMESTAMP('2017-05-01')),
PARTITION p65 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-01')),
PARTITION p66 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-01')),
PARTITION p67 VALUES LESS THAN (UNIX_TIMESTAMP('2017-08-01')),
PARTITION p68 VALUES LESS THAN (UNIX_TIMESTAMP('2017-09-01')),
PARTITION p69 VALUES LESS THAN (UNIX_TIMESTAMP('2017-10-01')),
PARTITION p70 VALUES LESS THAN (UNIX_TIMESTAMP('2017-11-01')),
PARTITION p71 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-01')),
PARTITION p72 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01')),
PARTITION p73 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01')),
PARTITION p74 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01')),
PARTITION p75 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01')),
PARTITION p76 VALUES LESS THAN (UNIX_TIMESTAMP('2018-05-01')),
PARTITION p77 VALUES LESS THAN (UNIX_TIMESTAMP('2018-06-01')),
PARTITION p78 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01')),
PARTITION p79 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01')),
PARTITION p80 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01')),
PARTITION p81 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-01')),
PARTITION p82 VALUES LESS THAN (UNIX_TIMESTAMP('2018-11-01')),
PARTITION p83 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-01')),
PARTITION p84 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01')),
PARTITION p85 VALUES LESS THAN (UNIX_TIMESTAMP('2019-02-01')),
PARTITION p86 VALUES LESS THAN (UNIX_TIMESTAMP('2019-03-01')),
PARTITION p87 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-01')),
PARTITION p88 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-01')),
PARTITION p89 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-01')),
PARTITION p90 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-01')),
PARTITION p91 VALUES LESS THAN (UNIX_TIMESTAMP('2019-08-01')),
PARTITION p92 VALUES LESS THAN (UNIX_TIMESTAMP('2019-09-01')),
PARTITION p93 VALUES LESS THAN (UNIX_TIMESTAMP('2019-10-01')),
PARTITION p94 VALUES LESS THAN (UNIX_TIMESTAMP('2019-11-01')),
PARTITION p95 VALUES LESS THAN (UNIX_TIMESTAMP('2019-12-01')),
PARTITION p96 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01')),
PARTITION p97 VALUES LESS THAN (UNIX_TIMESTAMP('2020-02-01')),
PARTITION p98 VALUES LESS THAN (UNIX_TIMESTAMP('2020-03-01')),
PARTITION p99 VALUES LESS THAN (UNIX_TIMESTAMP('2020-04-01')),
PARTITION p100 VALUES LESS THAN (UNIX_TIMESTAMP('2020-05-01')),
PARTITION p101 VALUES LESS THAN (UNIX_TIMESTAMP('2020-06-01')),
PARTITION p102 VALUES LESS THAN (UNIX_TIMESTAMP('2020-07-01')),
PARTITION p103 VALUES LESS THAN (UNIX_TIMESTAMP('2020-08-01')),
PARTITION p104 VALUES LESS THAN (UNIX_TIMESTAMP('2020-09-01')),
PARTITION p105 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-01')),
PARTITION p106 VALUES LESS THAN (UNIX_TIMESTAMP('2020-11-01')),
PARTITION p107 VALUES LESS THAN (UNIX_TIMESTAMP('2020-12-01')),
PARTITION p108 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01')),
PARTITION p109 VALUES LESS THAN (MAXVALUE)
);
下面是从mysql官网上copy下来的：
In MySQL 5.6, range optimization can be used for the TO_DAYS(), TO_SECONDS(), and YEAR() functions. In addition, beginning with MySQL 5.6.3, UNIX_TIMESTAMP() is treated as monotonic in partitioning expressions.
个人理解的就是：Mysql中Range分区不支持时间类型，需分别转换到正整数。其中的函数就是上述英文中蓝色的几个函数。
说说这个坑：
由于同事的mysql里的数据是通过SQLlog这个软件从sqlserver里导过来的。原来sqlserver分区列的datetime类型被转换成了mysql中的timestamp(6),当他执行分区语句的时候，总是报错：
The PARTITION function returns the wrong type，提示返回了错误类型。而我的mysql里的数据是通过navicat这个软件从sqlserver里导过来的。一切正常。
后来经过排查：发现我的timestamp长度为0.
mysql> select UNIX_TIMESTAMP(CURRENT_TIMESTAMP);
+-----------------------------------+
| UNIX_TIMESTAMP(CURRENT_TIMESTAMP) |
+-----------------------------------+
| 1445306197 |
+-----------------------------------+
1 row in set
mysql> select UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6));
+--------------------------------------+
| UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6)) |
+--------------------------------------+
| 1445306210.812460 |
+--------------------------------------+
1 row in set
mysql> select CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2015-10-20 09:57:42 |
+---------------------+
1 row in set
mysql> select CURRENT_TIMESTAMP(6);
+----------------------------+
| CURRENT_TIMESTAMP(6) |
+----------------------------+
| 2015-10-20 09:57:53.970072 |
+----------------------------+
1 row in set
因为timestamp(6)存储了6位微秒级，所以无法转换成整数了。