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
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读