如何實現清除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 的時候，循環也會提前退出。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。