Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Stored Procedure for Deleting in Chunks – LOOP not executing

Problem: The loop is not executed after the first loop.

CREATE PROCEDURE IF NOT EXISTS chunk_delete(IN table_name varchar(255), IN loop_count int, IN batch_size int)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE rows_affected INT DEFAULT 1;
    DECLARE delete_query TEXT;
WHILE counter <= loop_count AND rows_affected > 0 DO
        SET delete_query = CONCAT('DELETE FROM ', table_name, ' LIMIT ', batch_size);
        SELECT delete_query;
SET @sql_query = delete_query;
        PREPARE stmt FROM @sql_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
SET rows_affected = ROW_COUNT();
SET counter = counter + 1;
END WHILE;
END

Expected Result:
If the loop_count is 4 and batch_size is 10, the result is deletion of 40 rows after 4 loops. However, the loop is not implemented after deletion of first batch size of 10 rows.

Tags:

2

Answers


  1. 0

    ROW_COUNT() is called after DEALLOCATE PREPARE statement. So it returns 0. And WHILE cycle breaks.

    Even if you get the value immediately after EXECUTE, the function will still return 0. Because DELETE statement and EXECUTE statement which executes DELETE is not the same.

    If you want to know how many rows were deleted with EXECUTE then you’d count the amount of rows in the table before and after the prepared statement execution. Also you’d block the concurrent processes changes.

    And you do not need to prepare and deallocate each loop – prepare before and drop after.

    Login or Signup to reply.
  2. 0

    I would approach this slightly differently and test if there any more rows to delete

    eg

    CREATE PROCEDURE p(IN table_name varchar(255), IN loop_count int, IN batch_size int)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE rows_affected INT DEFAULT 1;
    DECLARE delete_query TEXT;
    
WHILE counter <= loop_count AND rows_affected > 0 DO
        SET delete_query = CONCAT('DELETE FROM ', table_name, ' LIMIT ', batch_size);
        SELECT delete_query;
        SET @sql_query = delete_query;
        PREPARE stmt FROM @sql_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        set @found = 0;
        SET @sql_query = 'set @found = (select 1 from t limit 1);';
        PREPARE stmt FROM @sql_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET rows_affected = @found;
        select rows_affected;
        SET counter = counter + 1;
END WHILE;
END $$

    note the setting of a user defined variable which can be accessed in the procedure

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search