MySQL分区按日期

最新推荐文章于 2024-08-24 21:11:32 发布

yoyoyoyoya

最新推荐文章于 2024-08-24 21:11:32 发布

阅读量693

点赞数 1

分类专栏： MySQL 文章标签： mysql 分区

本文链接：https://blog.csdn.net/yoyoyoyoya/article/details/74315662

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL分区按日期：

按天分区：

CREATE TABLE `t_test` (
`MRID` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '表记ID',
`MEASTYPE` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '量测类型',
`YMDHMS` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '年月日时分秒',
`ORIGIN` decimal(38,6) NOT NULL COMMENT '原始数据',
`VALUE` decimal(38,6) DEFAULT NULL COMMENT '电度量的值',
`UPD_DATE` datetime NOT NULL COMMENT '入库时间',
`ERROR` varchar(255) DEFAULT NULL,
PRIMARY KEY (`MEASTYPE`,`MRID`,`YMDHMS`),
UNIQUE KEY `idx_t_bj_cj_ls` (`MEASTYPE`,`MRID`,`YMDHMS`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='数据采集历史'
PARTITION BY RANGE(TO_DAYS (YMDHMS))
(
PARTITION p20170101 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p20170102 VALUES LESS THAN (TO_DAYS('2017-01-02')),
PARTITION p20170103 VALUES LESS THAN (TO_DAYS('2017-01-03')),
PARTITION p20170104 VALUES LESS THAN (TO_DAYS('2017-01-04')),
PARTITION p20170105 VALUES LESS THAN (TO_DAYS('2017-01-05')),
PARTITION p20170106 VALUES LESS THAN (TO_DAYS('2017-01-06')))