如何实现清除Mysql数据库的特定表无用数据的存储过程

如何实现清除Mysql数据库的特定表无用数据的存储过程，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

第一次玩存储过程，遇到好多问题，记录一下供日后参考。

说明：其中 logtable 是一个临时表用来调试用的，有“index,log1，log2，log3，log4，log5，log6，log7，log8”九个字段。

 CREATE TABLE IF NOT EXISTS logtable(
            `index` double NOT NULL AUTO_INCREMENT,
            `log1` varchar(255) DEFAULT NULL,
            `log2` varchar(255) DEFAULT NULL,
            `log3` varchar(255) DEFAULT NULL,
            `log4` varchar(255) DEFAULT NULL,
            `log5` varchar(255) DEFAULT NULL,
            `log6` varchar(255) DEFAULT NULL,
            `log7` varchar(255) DEFAULT NULL,
            PRIMARY KEY (`index`)
        );

#定义存储过程

#定义函数

DROP PROCEDURE IF EXISTS insertAsSelectProducts;

CREATE PROCEDURE insertAsSelectProducts ()

BEGIN
        DECLARE tablesNameVar VARCHAR (100);
        DECLARE done INT DEFAULT 0;
        DECLARE datatime VARCHAR (15) DEFAULT '201910010000';
        DECLARE columnVar VARCHAR (100) DEFAULT '0';
        DECLARE loginfo VARCHAR (100) DEFAULT '0';
        #SET datatime ='201910010000';
    #如果不存在日志表则创建

    #查出要清理数据的特定表
        DECLARE tableName CURSOR FOR SELECT    table_name FROM information_schema.TABLES
         WHERE table_schema = '26片区'
              AND table_name  NOT LIKE '%设定表'
      #        AND table_name  LIKE '%日统计%';
               AND table_name  NOT LIKE '%实时%'
                             AND table_name  <> 'logtable'
               AND table_name  NOT LIKE '%巡检%';

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        #打开游标
        OPEN tableName;

        group_loop :LOOP
            FETCH tableName INTO tablesNameVar;
            IF done = 1 THEN
        #日志
        INSERT INTO logtable(log1) SELECT 'done==1 so leave loop';
                LEAVE group_loop;
            END IF;

            #拼接sql 查询需要的数据复制到×_copy表
      #日志
      INSERT INTO logtable(log1) SELECT tablesNameVar;

      SET @columnflag = 0;
      #判断该表中是否有datatime列
      SET @columnVar1 = 0;
      SET @var_sql = concat(
                    'SELECT count(*) into @columnVar1 ',
          " FROM information_schema.columns WHERE table_schema = DATABASE()  AND table_name = '",
                    tablesNameVar,
                    "' AND column_name = 'datatime'"
                );

            PREPARE s FROM @var_sql;
            EXECUTE s;
            DEALLOCATE PREPARE s;
      
      IF @columnVar1<>1 THEN
                 #判断该表中是否有'统计日期'列
                    SET @columnVar1 = 0;
                    SET @var_sql = concat(
                            'SELECT count(*) into @columnVar1 ',
                            " FROM information_schema.columns WHERE table_schema = DATABASE()  AND table_name = '",
                            tablesNameVar,
                            "' AND column_name = '统计日期'"
                        );

                    PREPARE s FROM @var_sql;
                    EXECUTE s;
                    DEALLOCATE PREPARE s;
          
          IF @columnVar1=1 THEN
             SET @columnflag = 2;
          ELSE
             SET @columnflag = 3;
          END IF;
      ELSE
         SET @columnflag = 1;
      END IF;

      #日志
      UPDATE logtable SET log5 = @columnVar1 WHERE log1 = tablesNameVar;            

      IF @columnflag=1 THEN        
                SET @var_sql1 = concat(
                        'CREATE TABLE ',
                        tablesNameVar,
                        '_COPY ',
                        'SELECT * FROM ',
                        tablesNameVar,
                        ' WHERE DATATIME > ',
                        datatime
                    );

                 SET @var_sql1_count = concat(
                        'SELECT count(*) into @columnVar1_count FROM ',
                        tablesNameVar,
                        ' WHERE DATATIME > ',
                        datatime
                    );
            ELSEIF @columnflag=2 THEN      
                 SET @var_sql1 = concat(
                        'CREATE TABLE ',
                        tablesNameVar,
                        '_COPY ',
                        'SELECT * FROM ',
                        tablesNameVar,
                        ' WHERE 统计日期 > ',
                        LEFT(datatime,8)
                    );

          SET @var_sql1_count = concat(
                        'SELECT count(*) into @columnVar1_count FROM ',
                        tablesNameVar,
                        ' WHERE 统计日期 > ',
                        LEFT(datatime,8)
                    );
      ELSE
         SET @var_sql1 = concat(
                        'CREATE TABLE ',
                        tablesNameVar,
                        '_COPY ',
                        'SELECT * FROM ',
                        tablesNameVar,
                        ' WHERE 日期 > ',
                        LEFT(datatime,8)
                    );

          SET @var_sql1_count = concat(
                        'SELECT count(*) into @columnVar1_count FROM ',
                        tablesNameVar,
                        ' WHERE 日期 > ',
                        LEFT(datatime,8)
                    );
      END IF;
      
      #判断×_copy 表是否存在，存在就删除
      SET @sql_Exit = concat(
                        'drop table if exists ',
                        tablesNameVar,
            '_COPY '
                    );
      PREPARE s1_exit FROM @sql_Exit;
            EXECUTE s1_exit;
            DEALLOCATE PREPARE s1_exit;
     
            PREPARE s1_count FROM @var_sql1_count;
            EXECUTE s1_count;
            DEALLOCATE PREPARE s1_count;

            UPDATE logtable SET log7 = @columnVar1_count WHERE log1 = tablesNameVar;
      
      #判断该表是否有要备份的数据，没有要备份的数据直接清空该表，进入下一个循环，
            IF @columnVar1_count<1 THEN       
                 SET @clear_sql = concat(
                    'TRUNCATE TABLE ',
                    tablesNameVar
                );
                PREPARE s_clear FROM @clear_sql;
                EXECUTE s_clear;
                DEALLOCATE PREPARE s_clear;
        #日志
        UPDATE logtable SET log7 = CONCAT(@columnVar1_count,': no userful data,so continue next loop') WHERE log1 = tablesNameVar;
        ITERATE group_loop;
      END IF;
      
      #把要保留的数据拷贝到新表 ×_copy 中
            PREPARE s1 FROM @var_sql1;
            EXECUTE s1;
            DEALLOCATE PREPARE s1;
      #日志    
      SET loginfo = CONCAT('Copy useful data from ',tablesNameVar,' successful!');
      UPDATE logtable SET log2 = loginfo WHERE log1 = tablesNameVar;

            #删除原有表
            SET @var_sql2 = concat(
                'DROP TABLE ',
                tablesNameVar
            );

            PREPARE s2 FROM @var_sql2;
            EXECUTE s2;
            DEALLOCATE PREPARE s2;
      #日志
            SET loginfo = CONCAT('drop the old table：',tablesNameVar);
      UPDATE logtable SET log3 = loginfo WHERE log1 = tablesNameVar;

      #把×_copy表重命名为原表
            SET @var_sql3 = concat(
                'RENAME TABLE ',
                tablesNameVar,
                '_COPY ',
                'TO ',
                tablesNameVar
            );

            PREPARE s3 FROM @var_sql3;
            EXECUTE s3;
            DEALLOCATE PREPARE s3;
      #日志
      SET loginfo = CONCAT('rename the copy table：',tablesNameVar,'_copy  to original name');
      UPDATE logtable SET log4 = loginfo WHERE log1 = tablesNameVar;

        END LOOP group_loop;

        CLOSE tableName;

END

其中遇到的坑：

 1.当在游标循环过程中select的结果集如果为空，就会触发游标continue handler for not found 提前将done置为1，导致提前退出游标。
 2.游标循环中出现 select into 赋值 为 null 的时候，循环也会提前退出。

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。