2021-04-20

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
card varchar(32) DEFAULT NULL COMMENT '编号',
name varchar(32) DEFAULT NULL COMMENT '姓名',
equipment_id int(11) DEFAULT NULL COMMENT '设备id',
area_id int(10) DEFAULT NULL COMMENT '区域ID',
start_time datetime DEFAULT NULL COMMENT '开始时间',
end_time datetime DEFAULT NULL COMMENT '结束时间',
ifdel int(11) DEFAULT '0' COMMENT '0:使用，1：已移除',
PRIMARY KEY (id)
) ENGINE=MYISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='用户管理';

CREATE PROCEDURE insert_data_p (IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO user (card, name, equipment_id, area_id, start_time)
#VALUES(concat('A',n), concat('li',n), 0, 0, now());
VALUES( concat('A',n), concat('li',n), 0, 0, concat('2019-0',floor(8+rand(5)),'-0', floor(1+rand(30)),' ', floor(10+rand(10)),':',floor(10+rand(49)),':',floor(10+rand(49))));
set n=n+1;
end while;
UPDATE user SET end_time=DATE_ADD(start_time, INTERVAL rand(60) DAY);
END;

DROP PROCEDURE IF EXISTS P_BackupDataTable;  -- 删除之前的存储过程
CREATE PROCEDURE `P_BackupDataTable`(`copy_tablename` varchar(255))
BEGIN
  if exists (select * from information_schema.statistics where table_name = copy_tablename ) then
    set @oldTable = CONCAT(copy_tablename,"_",date_format(now(), '%Y%m%d%H%i%s'));    
    if not exists (select * from information_schema.statistics where table_name = @oldTable ) then
      set @beginNum=(SELECT IFNULL(AUTO_INCREMENT,0) as a FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME=copy_tablename LIMIT 1);
      set @newTable=CONCAT("new_",copy_tablename);
            
      CALL P_Statement(CONCAT("drop table if exists ",@newTable,";"));
      CALL P_Statement(CONCAT("CREATE TABLE ",@newTable," LIKE ",copy_tablename,";"));
      CALL P_Statement(CONCAT("alter table ",@newTable," auto_increment=",@beginNum,";"));
      CALL P_Statement(CONCAT("RENAME TABLE ",copy_tablename," TO ",@oldTable,",",@newTable," TO ",copy_tablename,";"));
    else 
      SELECT '备份表已存在' as Message;
    end if;
  ELSE
    SELECT '复制的表不存在' as Message;
  end if;    
END;

DROP PROCEDURE IF EXISTS P_Statement;
CREATE PROCEDURE P_Statement(IN dynamic_statement TEXT)
BEGIN
  SET @dynamic_statement := dynamic_statement;
  PREPARE prepared_statement FROM @dynamic_statement; -- 预编译
  EXECUTE prepared_statement; -- 执行预编译sql                    
  DEALLOCATE PREPARE prepared_statement; -- 要解除分配生成的预准备语句PREPARE
END;

CALL P_BackupDataTable('user');   -- 执行存储过程

DROP PROCEDURE IF EXISTS P_DeleteHisTable; 
CREATE PROCEDURE `P_DeleteHisTable`(`copy_tablename` varchar(30))
BEGIN
DECLARE v_tableName varchar(40) DEFAULT 0;
DECLARE done INT DEFAULT FALSE; -- 找出要删除的表名
DECLARE cursor_table CURSOR FOR 
SELECT TABLE_NAME FROM information_schema.`TABLES` 
WHERE TABLE_NAME like CONCAT(copy_tablename,"%") AND TABLE_SCHEMA = 'test' AND LENGTH(TABLE_NAME) = 19;  -- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_table;   -- 打开游标
read_loop: LOOP
FETCH cursor_table INTO v_tableName;
IF done THEN
LEAVE read_loop;
END IF;
#IF DATE(SUBSTR(v_tableName, 6)) <= DATE_SUB(CURDATE(),INTERVAL 1 WEEK) THEN
IF DATE(SUBSTR(v_tableName, 6)) <= now() THEN
SET @STMT :=CONCAT("DROP TABLE ",v_tableName);
PREPARE STMT FROM @STMT;   
EXECUTE STMT; 
DEALLOCATE PREPARE STMT;
END IF;
END LOOP;
END;

CALL P_DeleteHisTable('user'); 

--select DATE(SUBSTR('user_20210419202009', 6)) <= now();

CREATE EVENT
IF NOT EXISTS myEvent_Backup_tablehis ON SCHEDULE EVERY 1 HOUR ON COMPLETION PRESERVE DO
    CALL myEvent_Backup_tablehis();
    
-- 查看事件状态
SHOW VARIABLES LIKE '%event_scheduler%'; 

-- 启用事件
SET GLOBAL event_scheduler =1