mysql - Stored Procedure for Deleting in Chunks - LOOP not executing - Stack Overflow

admin管理员组

文章数量:1125023

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.

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.

• mysql
• stored-procedures

Share Improve this question Follow asked 2 days ago yoooooyooooo 322 bronze badges New contributor yooooo is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• What is the value of rows_affected first time through? – P.Salmon Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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.

本文标签： mysqlStored Procedure for Deleting in ChunksLOOP not executingStack Overflow