TL;DR:<\/p>\n\n\n\n
SET @quit=IF(ROW_COUNT()=0,'QUIT','SELECT 0 quit'); PREPARE quit FROM @quit; EXECUTE quit;<\/code><\/pre>\n\n\n\nDetail: Sometimes you want to delete records older than 45 days from a table:<\/p>\n\n\n\n
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;<\/code><\/pre>\n\n\n\nBut you don’t want to make a huge long-running transaction of it, so you do it in chunks of LIMIT 1000<\/code>:<\/p>\n\n\n\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day LIMIT 1000;<\/code><\/pre>\n\n\n\nBut you want to do more than 1 chunk, so you make a repetitive script:<\/p>\n\n\n\n
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;<\/code><\/pre>\n\n\n\nBut 5 times is likely wrong: we need to quit<\/strong> when the number of rows affected is zero<\/strong>, ie. ROW_COUNT()=0<\/code> : but MySQL doesn’t have conditional execution in the comand line interface.<\/p>\n\n\n\nWe can fudge it: here’s the recipe, to run from the shell:<\/p>\n\n\n\n
yes \"\nDELETE FROM livetable WHERE acctstarttime < now()-interval 45 day LIMIT 1000;\nSET @quit=IF(ROW_COUNT()=0,'QUIT','SELECT 0 quit'); PREPARE quit FROM @quit; EXECUTE quit;\n\" | mysql livedatabase<\/code><\/pre>\n\n\n\nThe yes<\/code> command prints out the string over and over, and the line that says EXECUTE<\/code> runs a string which makes an error (QUIT<\/code> is not a mysql command), and the error causes mysql<\/code> to quit.<\/p>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
TL;DR: Detail: Sometimes you want to delete records older than 45 days from a table: But you don’t want to make a huge long-running transaction of it, so you do it in chunks of LIMIT 1000: But you want to … Continue reading