数据库分区

2019-08-26

分区的坑

1、分区的值必须是主键,否则会出现
1503 - A PRIMARY KEY must include all columns in the table's partitioning function错误。必须要将分区判断的列设为主键。或者整个表都没有主键。
2、DateTime默认数据不能为0000-00-00 00:00:00会出现
1067 - Invalid default value for 'created_time'错误
3、使用dateTime数据作为分区依据，用TO_DAYS()函数来格式化

一个例子

-- ----------------------------
-- 给考试记录分区
-- ----------------------------
DROP TABLE IF EXISTS `t_exam_record`;
CREATE TABLE `t_exam_record` (
  `id` bigint(20) NOT NULL COMMENT '记录编号',
  `created_time` datetime NOT NULL DEFAULT '2019-08-01 00:00:01' COMMENT '创建时间',
  PRIMARY KEY (`id`,`created_time`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (TO_DAYS(created_time) ) (
    PARTITION P20190801 VALUES LESS THAN ( TO_DAYS('20190801') ),
    PARTITION P20191101 VALUES LESS THAN ( TO_DAYS('20191101') ),
    PARTITION P20200201 VALUES LESS THAN ( TO_DAYS('20200201') ),
    PARTITION P20200501 VALUES LESS THAN ( TO_DAYS('20200501') ),
    PARTITION P20200801 VALUES LESS THAN ( TO_DAYS('20200801') ),
    PARTITION P20201101 VALUES LESS THAN ( TO_DAYS('20201101') )
);