mysql定时分区

华创信息技术

发布于 2019-11-08 15:52:36

1.7K0

发布于 2019-11-08 15:52:36

文章被收录于专栏：华创信息技术

#创建表时建立分区

DROP TABLE IF EXISTS `hc_lot_history`;
CREATE TABLE `hc_lot_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(32) DEFAULT NULL COMMENT '来源设备',
  `attr_code` varchar(32) DEFAULT NULL COMMENT '参数code，取自字典表',
  `attr_name` varchar(32) DEFAULT NULL COMMENT '参数名称',
  `time` timestamp NULL DEFAULT NULL COMMENT '时间',
  `data` varchar(32) DEFAULT NULL COMMENT '数值',
  `unit` varchar(32) DEFAULT NULL COMMENT '单位',
  `mark` varchar(32) DEFAULT NULL COMMENT '标识',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `entry_time` timestamp NOT NULL COMMENT '录入时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除\r\n0未删除\r\n1删除',
  PRIMARY KEY (`id`,`entry_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='历史记录'
PARTITION BY RANGE (UNIX_TIMESTAMP(entry_time))
(PARTITION p20190603 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-03 23:59:59')) ENGINE = InnoDB);

注意：分区的字段必须是主键或主键的一部分；对已有数据的表进行分区时始终报错，应该创建同样的表结构同时创建分区，然后将原有数据导入新表。

#查看分区（分区名称、分区时间）

select partition_name,FROM_UNIXTIME(partition_description)
from information_schema.`PARTITIONS`
where table_name='hc_lot_history'

#删除分区

alter table hc_lot_history drop partition p20190605;

#创建添加分区存储过程（原表需要手动添加一个分区）

CREATE  PROCEDURE  auto_add_partition ( IN  table_name  varchar(32) )
BEGIN
    #Routine body goes here...
    DECLARE nextDate date;
    DECLARE p_name varchar(16);
    DECLARE p_time varchar(32);    

    SELECT DATE_ADD(CURDATE(),INTERVAL 1 DAY) INTO nextDate FROM DUAL;
select replace(DATE_FORMAT(nextDate,'%Y-%m-%d'),'-','') INTO p_name FROM DUAL;
SET p_time=CONCAT(DATE_FORMAT(nextDate,'%Y-%m-%d'),' 23:59:59');
    select p_time;

    SET @v_add=CONCAT('alter table ',table_name
                ,' add partition (partition '
                , CONCAT('p',p_name)
                ,' values less than (unix_timestamp("'
                ,p_time,'")))');

     PREPARE stmt from @v_add;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

END

#查看定时状态

show variables like 'event_scheduler';

#将系统定时打开

SET GLOBAL event_scheduler = on;

#定时事件

CREATE DEFINER=`root`@`%` EVENT `event_day` ON SCHEDULE EVERY 1 DAY STARTS '2019-06-04 23:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
    CALL auto_add_partition('hc_lot_history');
END

开启事件event_scheduler教程：https://blog.csdn.net/chenlu5201314/article/details/80017040

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019-06-5 1，如有侵权请联系 cloudcommunity@tencent.com 删除

event