MySQL按年切割表_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;