mysql创建分区、存储统计、定时事件

DROP TABLE IF EXISTS  monitor_printer;
CREATE TABLE monitor_printer
(
  id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
  monitorId int NOT NULL COMMENT '监控项ID',
  tonerLow int(11) DEFAULT '0' COMMENT '墨粉不足',
  tonerUtilization decimal(18,2) DEFAULT '0' COMMENT '墨粉使用率',
  lineState int(11) DEFAULT '0' COMMENT '工作状态',
  paperOut int(11) DEFAULT '0' COMMENT '缺纸状态',
  paperJam int(11) DEFAULT '0' COMMENT '卡纸状态',
  busyStatus int(11) DEFAULT '0' COMMENT '繁忙状态',
  waitStatus int(11) DEFAULT '0' COMMENT '等待状态',
  initializeStatus int(11) DEFAULT '0' COMMENT '初始化状态',
  doorOpenStatus int(11) DEFAULT '0' COMMENT '机盖打开状态',
  createTime datetime COMMENT '创建时间',
  PRIMARY KEY (id,createTime),
  index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
DROP TABLE IF EXISTS  monitor_printer_latest;
CREATE TABLE monitor_printer_latest
(
  id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
  monitorId int NOT NULL COMMENT '监控项ID',
  tonerLow int(11) DEFAULT '0' COMMENT '墨粉不足',
  tonerUtilization decimal(18,2) DEFAULT '0' COMMENT '墨粉使用率',
  lineState int(11) DEFAULT '0' COMMENT '工作状态',
  paperOut int(11) DEFAULT '0' COMMENT '缺纸状态',
  paperJam int(11) DEFAULT '0' COMMENT '卡纸状态',
  busyStatus int(11) DEFAULT '0' COMMENT '繁忙状态',
  waitStatus int(11) DEFAULT '0' COMMENT '等待状态',
  initializeStatus int(11) DEFAULT '0' COMMENT '初始化状态',
  doorOpenStatus int(11) DEFAULT '0' COMMENT '机盖打开状态',
  createTime datetime COMMENT '创建时间',
  PRIMARY KEY (id,createTime),
  index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
DROP TABLE IF EXISTS  monitor_printer_stat_hour;
CREATE TABLE monitor_printer_stat_hour
(
    id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
    monitorId int NOT NULL COMMENT '监控项ID',
    tonerLow_max int(11) DEFAULT '0' COMMENT '最大墨粉不足',
    tonerLow_min int(11) DEFAULT '0' COMMENT '最小墨粉不足',
    tonerLow_ave decimal(18,2) DEFAULT '0' COMMENT '平均墨粉不足',
    tonerUtilization_max decimal(18,2) DEFAULT '0' COMMENT '最大墨粉使用率',
    tonerUtilization_min decimal(18,2) DEFAULT '0' COMMENT '最小墨粉使用率',
    tonerUtilization_ave decimal(18,2) DEFAULT '0' COMMENT '平均墨粉使用率',
    lineState_max int(11) DEFAULT '0' COMMENT '最大工作状态',
    lineState_min int(11) DEFAULT '0' COMMENT '最小工作状态',
    lineState_ave decimal(18,2) DEFAULT '0' COMMENT '平均工作状态',
    doorOpenStatus_max int(11) DEFAULT '0' COMMENT '最大机盖打开状态',
    doorOpenStatus_min int(11) DEFAULT '0' COMMENT '最小机盖打开状态',
    doorOpenStatus_ave decimal(18,2) DEFAULT '0' COMMENT '平均机盖打开状态',
    createTime datetime COMMENT '创建时间',
    PRIMARY KEY (id,createTime),
    index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
-- 以天为单位 创建 分区
alter table  monitor_printer
PARTITION BY RANGE (TO_DAYS(createTime))
(PARTITION pmax VALUES LESS THAN MAXVALUE);
​
DELIMITER $$
DROP EVENT IF EXISTS event_create_partition_printer$$
CREATE EVENT event_create_partition_printer ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE(), '00:00:00') ON COMPLETION PRESERVE ENABLE DO BEGIN
    DECLARE partition_name VARCHAR(32);
    DECLARE partition_desc INT UNSIGNED;
    SET partition_name = CONCAT('pd', DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'%Y%m%d'));
    SET partition_desc = TO_DAYS(CURRENT_DATE());
    SET @partition_alter = CONCAT('ALTER TABLE  monitor_printer REORGANIZE PARTITION pmax INTO (
        PARTITION ', partition_name, ' VALUES LESS THAN (', partition_desc, '),
        PARTITION pmax VALUES LESS THAN MAXVALUE)');
    PREPARE stmt FROM @partition_alter;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
​
-- 小时统计表存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS proce_printer_stat_hour$$
CREATE PROCEDURE proce_printer_stat_hour(IN startTime TIMESTAMP,IN endTime TIMESTAMP)
BEGIN
    INSERT INTO  monitor_printer_stat_hour(
        monitorId,
        tonerLow_max,  tonerUtilization_max,  lineState_max, doorOpenStatus_max,
        tonerLow_min,  tonerUtilization_min,  lineState_min, doorOpenStatus_min,
        tonerLow_ave,  tonerUtilization_ave,  lineState_ave, doorOpenStatus_ave,
        createTime
    )SELECT
        monitorId,
        MAX(tonerLow) AS tonerLow_max,  MAX(tonerUtilization) AS tonerUtilization_max,  MAX(lineState) AS lineState_max,  MIN(doorOpenStatus) AS doorOpenStatus_min,
        MIN(tonerLow) AS tonerLow_min,  MIN(tonerUtilization) AS tonerUtilization_min,  MIN(lineState) AS lineState_min,  MIN(doorOpenStatus) AS doorOpenStatus_min,
        AVG(tonerLow) AS tonerLow_ave,  AVG(tonerUtilization) AS tonerUtilization_ave,  AVG(lineState) AS lineState_ave, (select FLOOR(AVG(doorOpenStatus))) AS doorOpenStatus_ave,
        endTime AS createTime
FROM   monitor_printer_latest WHERE createTime >= startTime AND  createTime <= endTime GROUP BY monitorId;
END $$
​
-- 创建事件 调用 存储过程
DELIMITER $$
DROP EVENT IF EXISTS event_hongshan_stat_hour$$
CREATE EVENT event_hongshan_stat_hour ON SCHEDULE EVERY 1 HOUR STARTS NOW()+INTERVAL 3600-(TIME_TO_SEC(NOW()))%3600 SECOND ON COMPLETION PRESERVE ENABLE DO
BEGIN
    DECLARE endTime TIMESTAMP;
    DECLARE startTime TIMESTAMP;
    SET endTime=now()-INTERVAL (TIME_TO_SEC(NOW()))%3600 SECOND;
    SET startTime = endTime - INTERVAL 1 hour;
​
    call proce_printer_stat_hour(startTime,endTime);
END $$
​
/* 定时清理最新小时过度表*/
DELIMITER $$
DROP EVENT IF EXISTS event_clear_table_latest$$
CREATE EVENT event_clear_table_latest ON SCHEDULE EVERY 1 HOUR STARTS NOW() ON COMPLETION PRESERVE ENABLE DO
BEGIN
     DECLARE clearTime TIMESTAMP;
     SET clearTime=now()- INTERVAL 1 hour;
    
     delete from imp_monitordb.monitor_printer_latest where createTime <= clearTime ;
END $$ TABLE IF EXISTS  monitor_printer;
CREATE TABLE monitor_printer
(
  id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
  monitorId int NOT NULL COMMENT '监控项ID',
  tonerLow int(11) DEFAULT '0' COMMENT '墨粉不足',
  tonerUtilization decimal(18,2) DEFAULT '0' COMMENT '墨粉使用率',
  lineState int(11) DEFAULT '0' COMMENT '工作状态',
  paperOut int(11) DEFAULT '0' COMMENT '缺纸状态',
  paperJam int(11) DEFAULT '0' COMMENT '卡纸状态',
  busyStatus int(11) DEFAULT '0' COMMENT '繁忙状态',
  waitStatus int(11) DEFAULT '0' COMMENT '等待状态',
  initializeStatus int(11) DEFAULT '0' COMMENT '初始化状态',
  doorOpenStatus int(11) DEFAULT '0' COMMENT '机盖打开状态',
  createTime datetime COMMENT '创建时间',
  PRIMARY KEY (id,createTime),
  index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
DROP TABLE IF EXISTS  monitor_printer_latest;
CREATE TABLE monitor_printer_latest
(
  id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
  monitorId int NOT NULL COMMENT '监控项ID',
  tonerLow int(11) DEFAULT '0' COMMENT '墨粉不足',
  tonerUtilization decimal(18,2) DEFAULT '0' COMMENT '墨粉使用率',
  lineState int(11) DEFAULT '0' COMMENT '工作状态',
  paperOut int(11) DEFAULT '0' COMMENT '缺纸状态',
  paperJam int(11) DEFAULT '0' COMMENT '卡纸状态',
  busyStatus int(11) DEFAULT '0' COMMENT '繁忙状态',
  waitStatus int(11) DEFAULT '0' COMMENT '等待状态',
  initializeStatus int(11) DEFAULT '0' COMMENT '初始化状态',
  doorOpenStatus int(11) DEFAULT '0' COMMENT '机盖打开状态',
  createTime datetime COMMENT '创建时间',
  PRIMARY KEY (id,createTime),
  index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
DROP TABLE IF EXISTS  monitor_printer_stat_hour;
CREATE TABLE monitor_printer_stat_hour
(
    id int NOT NULL AUTO_INCREMENT COMMENT '自增长id（主键）',
    monitorId int NOT NULL COMMENT '监控项ID',
    tonerLow_max int(11) DEFAULT '0' COMMENT '最大墨粉不足',
    tonerLow_min int(11) DEFAULT '0' COMMENT '最小墨粉不足',
    tonerLow_ave decimal(18,2) DEFAULT '0' COMMENT '平均墨粉不足',
    tonerUtilization_max decimal(18,2) DEFAULT '0' COMMENT '最大墨粉使用率',
    tonerUtilization_min decimal(18,2) DEFAULT '0' COMMENT '最小墨粉使用率',
    tonerUtilization_ave decimal(18,2) DEFAULT '0' COMMENT '平均墨粉使用率',
    lineState_max int(11) DEFAULT '0' COMMENT '最大工作状态',
    lineState_min int(11) DEFAULT '0' COMMENT '最小工作状态',
    lineState_ave decimal(18,2) DEFAULT '0' COMMENT '平均工作状态',
    doorOpenStatus_max int(11) DEFAULT '0' COMMENT '最大机盖打开状态',
    doorOpenStatus_min int(11) DEFAULT '0' COMMENT '最小机盖打开状态',
    doorOpenStatus_ave decimal(18,2) DEFAULT '0' COMMENT '平均机盖打开状态',
    createTime datetime COMMENT '创建时间',
    PRIMARY KEY (id,createTime),
    index monitorId_time(monitorId,createTime)
)DEFAULT CHARSET=utf8;
​
alter table  monitor_printer
PARTITION BY RANGE (TO_DAYS(createTime))
(PARTITION pmax VALUES LESS THAN MAXVALUE);
​
-- 以天为单位 创建 分区
DELIMITER $$
DROP EVENT IF EXISTS event_create_partition_printer$$
CREATE EVENT event_create_partition_printer ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE(), '00:00:00') ON COMPLETION PRESERVE ENABLE DO BEGIN
    DECLARE partition_name VARCHAR(32);
    DECLARE partition_desc INT UNSIGNED;
    SET partition_name = CONCAT('pd', DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'%Y%m%d'));
    SET partition_desc = TO_DAYS(CURRENT_DATE());
    SET @partition_alter = CONCAT('ALTER TABLE  monitor_printer REORGANIZE PARTITION pmax INTO (
        PARTITION ', partition_name, ' VALUES LESS THAN (', partition_desc, '),
        PARTITION pmax VALUES LESS THAN MAXVALUE)');
    PREPARE stmt FROM @partition_alter;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
​
-- 小时统计表存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS proce_printer_stat_hour$$
CREATE PROCEDURE proce_printer_stat_hour(IN startTime TIMESTAMP,IN endTime TIMESTAMP)
BEGIN
    INSERT INTO  monitor_printer_stat_hour(
        monitorId,
        tonerLow_max,  tonerUtilization_max,  lineState_max, doorOpenStatus_max,
        tonerLow_min,  tonerUtilization_min,  lineState_min, doorOpenStatus_min,
        tonerLow_ave,  tonerUtilization_ave,  lineState_ave, doorOpenStatus_ave,
        createTime
    )SELECT
        monitorId,
        MAX(tonerLow) AS tonerLow_max,  MAX(tonerUtilization) AS tonerUtilization_max,  MAX(lineState) AS lineState_max,  MIN(doorOpenStatus) AS doorOpenStatus_min,
        MIN(tonerLow) AS tonerLow_min,  MIN(tonerUtilization) AS tonerUtilization_min,  MIN(lineState) AS lineState_min,  MIN(doorOpenStatus) AS doorOpenStatus_min,
        AVG(tonerLow) AS tonerLow_ave,  AVG(tonerUtilization) AS tonerUtilization_ave,  AVG(lineState) AS lineState_ave, (select FLOOR(AVG(doorOpenStatus))) AS doorOpenStatus_ave,
        endTime AS createTime
FROM   monitor_printer_latest WHERE createTime >= startTime AND  createTime <= endTime GROUP BY monitorId;
END $$
​
-- 创建事件 调用 存储过程
DELIMITER $$
DROP EVENT IF EXISTS event_hongshan_stat_hour$$
CREATE EVENT event_hongshan_stat_hour ON SCHEDULE EVERY 1 HOUR STARTS NOW()+INTERVAL 3600-(TIME_TO_SEC(NOW()))%3600 SECOND ON COMPLETION PRESERVE ENABLE DO
BEGIN
    DECLARE endTime TIMESTAMP;
    DECLARE startTime TIMESTAMP;
    SET endTime=now()-INTERVAL (TIME_TO_SEC(NOW()))%3600 SECOND;
    SET startTime = endTime - INTERVAL 1 hour;
​
    call proce_printer_stat_hour(startTime,endTime);
END $$
​
/* 定时清理最新小时过度表*/
DELIMITER $$
DROP EVENT IF EXISTS event_clear_table_latest$$
CREATE EVENT event_clear_table_latest ON SCHEDULE EVERY 1 HOUR STARTS NOW() ON COMPLETION PRESERVE ENABLE DO
BEGIN
     DECLARE clearTime TIMESTAMP;
     SET clearTime=now()- INTERVAL 1 hour;
    
     delete from imp_monitordb.monitor_printer_latest where createTime <= clearTime ;
END $$