USE sys; #数据库名称,过程存储依赖于数据库生效.
DROP PROCEDURE IF EXISTS Del_tables; #如果已有该存储过程则删除旧的
DELIMITER $$ #定义临时分隔符
CREATE PROCEDURE Del_tables() #创建存储过程
BEGIN #存储过程开始
#定义变量名称和类型
DECLARE db_name VARCHAR(20);
DECLARE table_name VARCHAR(20);
DECLARE table_pro CHAR(18);
DECLARE sql_text VARCHAR(3000);
DECLARE i INT;
DECLARE j VARCHAR(2);
#对变量进行赋值
SET db_name='sys';
SET table_name='';
SET table_pro = 'destfileinserver_';
SET sql_text='';
SET i=0;
SET i=0;
#通过遍历,生成129张分表的前缀
WHILE i<=79 DO
IF i<10 THEN
SET j=CONCAT('0',i);
ELSE
SET j=LPAD(LCASE(HEX(i)),2,'0');
END IF;
#拼接分表名称
-- SET table_name=CONCAT(j,table_pro);
SET table_name=CONCAT(table_pro,j);
#拼接删除分表的SQL语句
-- SET sql_text=CONCAT('DROP TABLE IF EXISTS ', db_name,'.',table_name,';');
SET sql_text=CONCAT('delete from ', db_name,'.',table_name,' where time between ', '"2018-01-01 00:00:00"', ' and ', '"2019-01-01 00:00:00"',';');
-- SET sql_text=CONCAT('create table ', db_name,'.',table_name,' (id int(20), time timestamp);');
#执行SQL语句
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#准备进入下一轮循环
SET i=i+1;
#结束循环
END WHILE;
#存储过程结束
END$$
#恢复分隔符
DELIMITER ;
#调用存储过程并执行
CALL Del_tables();
https://blog.csdn.net/weixin_34128237/article/details/92387364