mysql– 如何为当年的每个月创建一个分区

概述我想按“MONTH”对数据进行分区我在试试这个,ALTER TABLE t1 PARTITION BY RANGE(TO_DAYS(FROM_UNIXTIME(transaction_date)))( PARTITION JAN VALUES LESS THAN (TO_DAYS('2013-02-01')), PARTITION FEB VALUES LE

我想按“MONTH”对数据进行分区

我在试试这个,

ALTER table t1 PARTITION BY RANGE(TO_DAYS(FROM_UNIXTIME(transaction_date)))(PARTITION JAN VALUES LESS THAN (TO_DAYS('2013-02-01')),PARTITION FEB VALUES LESS THAN (TO_DAYS('2013-03-01')),PARTITION MAR VALUES LESS THAN (TO_DAYS('2013-04-01')),PARTITION APR VALUES LESS THAN (TO_DAYS('2013-05-01')),PARTITION MAY VALUES LESS THAN (TO_DAYS('2013-06-01')),PARTITION JUN VALUES LESS THAN (TO_DAYS('2013-07-01')),PARTITION Jul VALUES LESS THAN (TO_DAYS('2013-08-01')),PARTITION AUG VALUES LESS THAN (TO_DAYS('2013-09-01')),PARTITION SEP VALUES LESS THAN (TO_DAYS('2013-10-01')),PARTITION `OCT` VALUES LESS THAN (TO_DAYS('2013-11-01')),PARTITION NOV VALUES LESS THAN (TO_DAYS('2013-12-01')),PARTITION `DEC` VALUES LESS THAN (TO_DAYS('2014-01-01'))

);

transaction_date INT NOT NulL //store date by UNIX_TIMESTAMP()

错误代码：1564@H_419_20@不允许使用此分区功能

如何为当年的每个月创建一个分区？

最佳答案这可能是bug或限制.

对于每月分区,您可以尝试这样做：

SQLFiddle Demo

ALTER table t1 PARTITION BY RANGE(transaction_date)(PARTITION JAN VALUES LESS THAN (UNIX_TIMESTAMP('2013-02-01')),PARTITION FEB VALUES LESS THAN (UNIX_TIMESTAMP('2013-03-01')),PARTITION MAR VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),PARTITION APR VALUES LESS THAN (UNIX_TIMESTAMP('2013-05-01')),PARTITION MAY VALUES LESS THAN (UNIX_TIMESTAMP('2013-06-01')),PARTITION JUN VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),PARTITION Jul VALUES LESS THAN (UNIX_TIMESTAMP('2013-08-01')),PARTITION AUG VALUES LESS THAN (UNIX_TIMESTAMP('2013-09-01')),PARTITION SEP VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),PARTITION `OCT` VALUES LESS THAN (UNIX_TIMESTAMP('2013-11-01')),PARTITION NOV VALUES LESS THAN (UNIX_TIMESTAMP('2013-12-01')),PARTITION `DEC` VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01')));

如果您有一个以DATE作为数据类型的列,那么您可以尝试使用每月分区内的每日分区：@H_419_20@尝试在MysqL中使用sub-partitioning

SQLFiddle Demo

ALTER table t1 PARTITION BY RANGE( MONTH(FROM_UNIXTIME(transaction_date) )SUBPARTITION BY HASH( DAY(FROM_UNIXTIME(transaction_date)) )SUBPARTITIONS 31 (    PARTITION p0 VALUES LESS THAN (2),PARTITION p1 VALUES LESS THAN (3),PARTITION p2 VALUES LESS THAN (4),PARTITION p3 VALUES LESS THAN (5),PARTITION p4 VALUES LESS THAN (6),PARTITION p5 VALUES LESS THAN (7),PARTITION p6 VALUES LESS THAN (8),PARTITION p7 VALUES LESS THAN (9),PARTITION p8 VALUES LESS THAN (10),PARTITION p9 VALUES LESS THAN (11),PARTITION p10 VALUES LESS THAN (12),PARTITION p11 VALUES LESS THAN MAXVALUE);

总结

以上是内存溢出为你收集整理的mysql – 如何为当年的每个月创建一个分区全部内容，希望文章能够帮你解决mysql – 如何为当年的每个月创建一个分区所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。