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
Best Answer
To get a dynamically formed SQL statement you need to get it into a user variable,
@drop
, and then execute this:To clean up
cached_tbl
, delete where there isn't match on the join: