How do I copy a table which has been updated within the last 5 minutes?-CodePudding

I’m trying to detect if a table has been updated within the last 5 minutes, and if so, make a copy of it. I can create queries for both parts, however I’m struggling to find an appropriate way to combine them.

Part 1 - Detecting updates:

SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
   WHERE 
    DATE_SUB(NOW(), INTERVAL 5 HOUR_MINUTE) < `UPDATE_TIME`
    AND TABLE_NAME = 'admin'

Part 2 - Making a copy:

CREATE TABLE admin_copy LIKE admin
INSERT admin_copy
SELECT * FROM admin

Any assistance would be appreciated.

CodePudding user response：

See if this works

CREATE TABLE admin_copy LIKE admin; 
INSERT admin_copy SELECT * FROM admin WHERE `UPDATE_TIME` BETWEEN (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND NOW();

CodePudding user response：

You can create a second table to log changes to the table and use triggers to update the time of the last change, as seen here.