long blogs

进一步有进一步惊喜


  • Home
  • Archive
  • Tags
  •  

© 2025 long

Theme Typography by Makito

Proudly published with Hexo

数据库分区

Posted at 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()函数来格式化

一个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- ----------------------------
-- 给考试记录分区
-- ----------------------------
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') )
);

Share 

 Previous post: 千奇百问 Next post: 前端路由 

© 2025 long

Theme Typography by Makito

Proudly published with Hexo