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;
想不如做,要多看书,多动手实践,实践出真知。