MYSQL利用存储过程清理所有表逻辑删除数据

MYSQL利用存储过程清理所有表逻辑删除数据

• 注意每个表都必须统一由逻辑删除的标记字段status
• （维护的数据库不多的情况下可以隔一段事件手动执行一下）需要自动定时执行存储过程的，后期可以加个定时任务事件

CREATE DEFINER=`root`@`localhost` PROCEDURE `del_data`()
BEGIN 
		DECLARE strClear VARCHAR(255);
		DECLARE done INT DEFAULT 0;
    #定义游标
    DECLARE curOne CURSOR FOR select CONCAT(" DELETE FROM ",table_name," where `status` = '0'; ") from information_schema.tables where TABLE_SCHEMA = 'cloud_course';	
    #绑定控制变量到游标，游标循环结束自动转为True
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    #打开游标
    OPEN curOne;

    FETCH curOne INTO strClear;
    REPEAT
        FETCH curOne INTO strClear;
        #SELECT strClear;

        #动态执行SQL语句
        SET @mysql = strClear;
        PREPARE stmt from @mysql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        UNTIL done = 1 END REPEAT;

    #关闭游标
    CLOSE curOne;
END

清空所有表的数据，自增重置为1

• 创建存储过程

	delimiter //
CREATE PROCEDURE clean_data()  
	BEGIN
    DECLARE strClear VARCHAR(256);
    DECLARE done INT DEFAULT 0;
    #定义游标
    DECLARE curOne CURSOR FOR SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') as ClearTable FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cloud_course';
    #绑定控制变量到游标，游标循环结束自动转为True
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    #打开游标
    OPEN curOne;

    FETCH curOne INTO strClear;
    REPEAT
        FETCH curOne INTO strClear;
        #SELECT strClear;

        #动态执行SQL语句
        SET @mysql = strClear;
        PREPARE stmt from @mysql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        UNTIL done = 1 END REPEAT;

    #关闭游标
    CLOSE curOne;
END