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

本文介绍了如何为当年的每个月创建一个分区的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想按"MONTH"对数据进行分区

I want to partition my data by "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 不允许使用此分区功能

Error Code: 1564 This partition function is not allowed

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

How do I create a partition for every month of the current year?

推荐答案

这可能是错误或限制.

对于每月分区，您可以尝试以下操作:

For Monthly partitioning you may try this:

SQLFiddle演示

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作为数据类型，则可以在每月分区内的每日分区"中尝试使用此方法: 尝试在MySQL中使用子分区

If you have a column having DATE as data type then you may try this for Daily partitioning inside monthly paritioning: Try using sub-partitioning in MySQL

SQLFiddle演示

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 );