mysql分区,如何使用时间戳来按月分区 。这个该如何实现。
解决方案 »
- (急!)求解:大学生社团信息管理系统 数据库设计
- plpgsql 中的赋值也可以用‘=’,而不用‘:=’?
- 一句SQL文,请教!
- 請教Postgresql使用check檢查唯一性,請大家幫幫忙
- Mysql 的 chage db是指什么操作?
- 怎样让mysql显示更新日志,已经查看更新日志
- jdbc 批量更新 求助
- 我要通过 id1+id 2 在表2中拿数据 而且通过在id1在表1中拿数据 怎么写sql
- mysql 5.7.20 mysqldump 备份数据库 不需要账号密码
- 求助!25日这一天的身份证为key,追溯到前一周该身份证关联的合作方去重个数。
- MySQL Nested Loop Join/Block Nested-Loop Join 理解
- Mysql的利器Mycat,轻松抵御海量并发
(
PARTITION p_2015_01 VALUES LESS THAN (201501),
PARTITION p_2015_02 VALUES LESS THAN (201502),
PARTITION p_2015_03 VALUES LESS THAN (201503),
PARTITION p_2015_04 VALUES LESS THAN (201504),
PARTITION p_2015_05 VALUES LESS THAN (201505),
PARTITION p_2015_06 VALUES LESS THAN (201506),
PARTITION p_2015_07 VALUES LESS THAN (201507),
PARTITION p_2015_08 VALUES LESS THAN (201508),
PARTITION p_2015_09 VALUES LESS THAN (201509),
PARTITION p_2015_10 VALUES LESS THAN (201510),
PARTITION p_2015_11 VALUES LESS THAN (201511),
PARTITION p_2015_12 VALUES LESS THAN (201512),
PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
我在mysql 5.6中试了一下,如果是timestamp类型,在创建range分区表时会报错:mysql> CREATE TABLE t1 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(YEAR(t)*100+MONTH(t))
-> (
-> PARTITION p201001 VALUES LESS THAN (201002),
->
-> PARTITION p201002 VALUES LESS THAN (201003),
->
-> PARTITION p201003 VALUES LESS THAN (201004),
->
-> PARTITION p201004 VALUES LESS THAN (201005),
->
-> PARTITION p201005 VALUES LESS THAN (201006),
->
-> PARTITION p201006 VALUES LESS THAN (201007),
->
-> PARTITION p201007 VALUES LESS THAN (201008),
->
-> PARTITION p201008 VALUES LESS THAN (201009),
->
-> PARTITION p201009 VALUES LESS THAN (201010),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql>
如果是采用这种方法,也会报错:
mysql> CREATE TABLE t2 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(t)
-> (
-> PARTITION p201001 VALUES LESS THAN ('2010-02-01'),
->
-> PARTITION p201002 VALUES LESS THAN ('2010-02-03'),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> );
ERROR 1697 (HY000): VALUES value for partition 'p201001' must have type INT
mysql>
你看看以前的例子:mysql 如何利用timestamp字段来按月份分区?
http://bbs.csdn.net/topics/310192950
我们也是这种处理方法,转成时间戳,没发现什么bug,但分区字段也需要存时间戳