mysql按时间日期拆分表

1、用存储过程和绑定事件的方式来做

(1)、创建存储过程：

/* 按日期拆分表 */
CREATE PROCEDURE ctrlcctrlv_table(var VARCHAR(20))
BEGIN
SET @param1 = concat('ALTER TABLE ', var, ' RENAME ', var, DATE_FORMAT(NOW(), '%Y_%m_%d'));
PREPARE stmt FROM @param1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @param2 = concat('CREATE table ', var, ' like ', var, DATE_FORMAT(NOW(), '%Y_%m_%d'));
PREPARE stmt1 FROM @param2;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;

 

(2)、创建事件 下面例子是隔一天拆表

create event ctrlcctrlv_event
on schedule every 1 DAY STARTS '2020-09-10 00:00:00'
on completion preserve disable 
do call ctrlcctrlv_table("user"); 

 

(3)、启动事件生效

alter event ctrlcctrlv_event on completion preserve enable;