0

I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO what I wanted to do is to create event scheduler that would drop temp tables that were created more than a day ago...For that purpose I am writing something like

SELECT (DROP) 
    *
FROM
    information_schema.TABLES
WHERE 
    table_schema = 'db' and CREATE_TIME < (NOW()-INTERVAL 24 HOUR)

But I also wanted to delete the rows containing just removed table names inside cached_tbl table... Please advise how I can do it... In the regular programming I would just through all the selected table_names from previous script into the array and go through the loop comparing this table names to the table names in cached_tbls... But I have no idea how implement it in mysql

0

To get a dynamically formed SQL statement you need to get it into a user variable, @drop, and then execute this:

SELECT CONCAT('DROP TABLE ',
              GROUP_CONCAT(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME))) INTO @drop
FROM information_schema.TABLES
WHERE  table_schema = 'db' AND
       table_name like 'temp%' AND
       CREATE_TIME < (NOW()-INTERVAL 24 HOUR);
PREPARE dropstmt FROM @drop;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;

To clean up cached_tbl, delete where there isn't match on the join:

DELETE c FROM cached_tbl c 
LEFT JOIN information_schema.TABLES t ON t.TABLE_NAME = c.table_name
WHERE t.TABLE_NAME IS NULL
2
  • Thank you so much! Can I only ask you what is c in your second statement? – Sofi Oct 12 '18 at 2:49
  • its an alias of cached_tbl at the end of the first line – danblack Oct 12 '18 at 2:50

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.