0

I will delete millions records from a table in mysql dbms, but i don't want to delete all records at one time not to force database server cpu. That's why i want to use my query many times. My whille loops doesn't work.I don't know why. How to do that? My query is below:

while 1=1 DO
delete  FROM customer.generallogs  where DateEnter < '2019-04-03 16:13:28' limit 100000;
end while;
0

SP can be like (draft code):

CREATE PROCEDURE DeleteFromGeneralLogs (IN DeleteTill DATETIME)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=1;
-- IF DeleteTill IS NULL THEN LEAVE;
WHILE done = 0 DO
    SELECT SLEEP(1) INTO done; -- small pause between chunks
    DELETE 
        FROM customer.generallogs  
        WHERE DateEnter < DeleteTill  
        LIMIT 100000;
END WHILE;
END;

And then

CALL DeleteFromGeneralLogs('2019-04-03 16:13:28');
  • @Omer Pay attention to commented line which checks input parameter... uncomment it and make more strong condition - simple error/misprint in it can cause actual data damage. And I'd recommend to add ORDER BY DateEnter ASC into DELETE query - of course, it decreases speed, but decreases the problems when the procedure is called errorneously or with wrong parameter, because it will delete the most old records first. – Akina Aug 1 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.