Skip to content

Instantly share code, notes, and snippets.

@musaid

musaid/remove-processed.sql

Created October 7, 2018 04:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save musaid/d57d4ed95739fe4549ac364a763cec8c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save musaid/d57d4ed95739fe4549ac364a763cec8c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. musaid created this gist Oct 7, 2018.
    41 changes: 41 additions & 0 deletions remove-processed.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    CREATE DEFINER=`admin`@`10.0.0.1` PROCEDURE `removeProcessed`(table_name VARCHAR(255), keyField VARCHAR(255), maxId INT, num_rows INT)
    BEGIN
    SET @table_name = table_name;
    SET @keyField = keyField;
    SET @maxId = maxId;
    SET @num_rows = num_rows;

    SET @sql_text1 = concat('SELECT MIN(',@keyField,') INTO @a FROM ',@table_name);
    PREPARE stmt1 FROM @sql_text1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    loop_label: LOOP
    SET @z = NULL;
    SET @sql_text2 = concat('SELECT ',@keyField,' INTO @z FROM ',@table_name,' WHERE ',@keyField,' >= ',@a,' ORDER BY ',@keyField,' LIMIT ',@num_rows,',1');
    PREPARE stmt2 FROM @sql_text2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    If @z is null THEN
    LEAVE loop_label;
    ELSEIF @z = "" THEN
    LEAVE loop_label;
    ELSEIF @z > @maxId THEN
    LEAVE loop_label;
    END IF;

    SET @sql_text3 = concat('DELETE FROM ',@table_name,' WHERE ',@keyField,' >= ',@a,' AND ',@keyField,' <= ',@z);
    PREPARE stmt3 FROM @sql_text3;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;

    SET @a = @z;
    SELECT SLEEP(1);
    END LOOP;

    SET @sql_text4 = concat('DELETE FROM ',@table_name,' WHERE ',@keyField,' <= ',@maxId);
    PREPARE stmt4 FROM @sql_text4;
    EXECUTE stmt4;
    DEALLOCATE PREPARE stmt4;
    END