mysql 如何利用timestamp字段来按月份分区？

Question

数据表中有个timestamp类型的字段tmstmp,但是打算如下按照月份分区却被告之：
ERROR 1564 (HY000): This partition function is not allowed
大概是FROM_UNIXTIME不能用于分区，试问有没有什么解决办法？
ALTER TABLE E_info PARTITION BY RANGE( FROM_UNIXTIME(tmstmp) DIV 100000000) )  
(   
    PARTITION p_2008_11 VALUES LESS THAN (200812),
    PARTITION p_2008_12 VALUES LESS THAN (200901),
    PARTITION p_2009_01 VALUES LESS THAN (200902),
    PARTITION p_2009_02 VALUES LESS THAN (200903),
    PARTITION p_2009_03 VALUES LESS THAN (200904),
    PARTITION p_2009_04 VALUES LESS THAN (200905),
    PARTITION p_2009_05 VALUES LESS THAN (200906),
    PARTITION p_2009_06 VALUES LESS THAN (200907),
    PARTITION p_2009_07 VALUES LESS THAN (200908),
    PARTITION p_2009_08 VALUES LESS THAN (200909),
    PARTITION p_catch_all VALUES LESS THAN MAXVALUE
); 

Answer 1

你的问题主要是更改分区表问题，不能这样更改的，对于range分区的更改，要用reorganize去更改。示例：表tb_key_part11已有最大分区值的P3，要增加分区，则如下：alter table tb_key_part11 reorganize partition p3 into 
(partition p3 values less than (30),
partition p4 values less than maxvalue
)

Answer 2

谢谢，不过没明白你的意思！用上面的语句创建分区的时候出现的错误信息是 ERROR 1564 (HY000): This partition function is not allowed我如果改成：
ALTER TABLE E_info PARTITION BY RANGE( tmstmp DIV 100000000) )  
(   
    PARTITION p_2008_11 VALUES LESS THAN (200812),
    PARTITION p_2008_12 VALUES LESS THAN (200901),
    PARTITION p_2009_01 VALUES LESS THAN (200902),
    PARTITION p_2009_02 VALUES LESS THAN (200903),
    PARTITION p_2009_03 VALUES LESS THAN (200904),
    PARTITION p_2009_04 VALUES LESS THAN (200905),
    PARTITION p_2009_05 VALUES LESS THAN (200906),
    PARTITION p_2009_06 VALUES LESS THAN (200907),
    PARTITION p_2009_07 VALUES LESS THAN (200908),
    PARTITION p_2009_08 VALUES LESS THAN (200909),
    PARTITION p_catch_all VALUES LESS THAN MAXVALUE
); 
就可以建立分区，但是这样的结果显然不正确！我是想通过时间戳算出月份进行分区，即用表达式FROM_UNIXTIME(tmstmp) DIV 100000000，每月一个分区!
但是mysql在分区的时候好像禁止使用FROM_UNIXTIME这个函数，所以，我想知道我如何利用时间戳按月对表进行分区！

Answer 3

我测试了下，初步发现如下：
range每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠
list类型只支持整形字段或返回整形数的表达式，每个分区列表里的值列表必须整数
hash类型只支持整形字段或返回整形数的表达式
key类型只支持列名形式（可一个或多个列名），不支持表达式--------------------------------
所以，你上面的错误，估计是mysql为了固定进行range分区为整数，特意对一些结果非整数的函数进行了屏蔽，to_days函数是可以用的。

Answer 4

另外还发现：若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里。所以，这是很不方便的

Answer 5

FROM_UNIXTIME 的结果本身也可以看做整数吧？mysql>SELECT FROM_UNIXTIME(1249290492) DIV 1000000; 
20090803通过整数运算，算出了8月3号的结果，证明FROM_UNIXTIME的结果是个整数！

Answer 6

FROM_UNIXTIME返回值不是整数，所以，不支持的

Answer 7

Partitioning  中仅可以使用以下函数。ABS() CEILING() (see CEILING() and FLOOR(), immediately following this list) DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() DATEDIFF() EXTRACT() FLOOR() (see CEILING() and FLOOR(), immediately following this list) HOUR() MICROSECOND() MINUTE() MOD() MONTH() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() WEEKDAY() YEAR() YEARWEEK() 

Answer 8

mysql> select TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
|         733994 |
+----------------+
1 row in set (0.01 sec)mysql>看到未？主要是因为TO_DAYS的返回值也是整型。

Answer 9

mysql> SELECT FROM_UNIXTIME(1249885811) DIV 100000000 AS month;
+--------+
| month  |
+--------+
| 200908 | 
+--------+
1 row in set (0.00 sec)这个何尝不是整数？ 强制转换一下，也不是不能被看做整数，只是按照官方说法，返回值应该是线性的，估计是这个原因！

Answer 10

SELECT TO_DAYS('2007-10-07');TO_DAYS的参数是日期，datetime类型的，但是我要运算的的是形如1249885811的时间戳！我的数据表中的id字段隐含着时间戳的信息，我想通过运算id得出时间戳，然后再用时间戳换算成月份,像这样 200908，
每个分区便可如此类似设计成
 PARTITION p_2008_07 VALUES LESS THAN (200908)
很方便。可是现在我得自己算
PARTITION p_2008_07 VALUES LESS THAN (2498858110)上边那个一目了然，下边这个看着不解其意！

Answer 11

mysql> SELECT FROM_UNIXTIME(1249885811) DIV 100000000 AS month; 
+--------+ 
| month  | 
+--------+ 
| 200908 | 
+--------+ 
1 row in set (0.00 sec) 这个何尝不是整数？ 强制转换一下，也不是不能被看做整数，只是按照官方说法，返回值应该是线性的，估计是这个原因！
-------------------------------------
你上面是“FROM_UNIXTIME(1249885811) DIV 100000000 ”整个表达式的值为整型罢啦，这其中包含了FROM_UNIXTIME返回值非整型的函数，正如前面所说的，mysql对这个作了限制mysql> select FROM_UNIXTIME(1249885811);
+---------------------------+
| FROM_UNIXTIME(1249885811) |
+---------------------------+
| 2009-08-10 14:30:11       |
+---------------------------+
1 row in set (0.00 sec)mysql>