mysql 自动按日期分表，删除旧表

最新推荐文章于 2024-04-23 09:13:43 发布

今天开始是码农

最新推荐文章于 2024-04-23 09:13:43 发布

阅读量615

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u011022919/article/details/128185530

版权

CREATE DEFINER=`Easun`@`%` PROCEDURE `ctrlcctrlv_table`(var VARCHAR(20))
BEGIN
    DECLARE t_name varchar(64);
	DECLARE isFinished int default false;
	DECLARE log_table_list cursor for (select table_name from information_schema.tables  where table_schema = 'mqtt' and table_name like 'wp_mqtt_publish_logs%' and datediff(now(),create_time)>35);
	DECLARE continue handler for not found set isFinished=true; 

	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;

	open log_table_list;
	repeat 
		fetch log_table_li    st into t_name; 
		if isFinished = false then
			SET @param3 = concat('drop table ',t_name,';');
			PREPARE stmt2 FROM @param3;
			EXECUTE stmt2;
			DEALLOCATE PREPARE stmt2;
		end if;
		until isFinished  
	end repeat; 
	close log_table_list;
	
	
END