需求:将mysql数据库表中标识为已经删除的数据作物理删除,由于表比较多,手动删除太费时间,于是就想着写一个存储过程,实现一键删除的操作,该存储过程只有一个入参:数据库名
存储过程实现如下:
DELIMITER $$
USE `myDbName`$$
DROP PROCEDURE IF EXISTS `deleteTablesHasDeletedRows`$$
CREATE PROCEDURE `deleteTablesHasDeletedRows`(IN dbName VARCHAR(300))
BEGIN
DECLARE tableName VARCHAR(300);
DECLARE done INT DEFAULT 0; /*用于判断是否结束循环*/
#定义游标:将表中有删除标识字段的表过滤出来
DECLARE tablesCursor CURSOR FOR (SELECT TABLE_NAME FROM information_schema.`COLUMNS` WHERE TABLE_Schema = dbName AND COLUMN_NAME='delete_flag');
#定义结束标识
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
##打开游标
OPEN tablesCursor;
/* 循环开始 */
REPEAT
FETCH tablesCursor INTO tableName;
IF NOT done THEN
#拼接删除语句
SET @deleteSql = CONCAT('delete from ',dbName,'.',tableName,' where delete_flag=1;');
#执行删除语句
PREPARE tem FROM @deleteSql;
EXECUTE tem;
END IF;
UNTIL done
END REPEAT;
##关闭游标
CLOSE tablesCursor;
END$$
DELIMITER ;
调用存储过程的方法:
CALL deleteTablesHasDeletedRows('myDbName');