How to check when a MySQL table was last updated

I recently had to update a MySQL schema and import new data into the table. But before I could do that I needed to check that no one had updated the table during the last 7 days and no new data had been stored. As the table itself did not have any update field of itself the only other option was to look into the MySQL ‘information_schema’ database.

The ‘information_schema’ database contains a ‘tables’ table which contain the update information for each database and its tables. So all you have to do is grab that information.

The following small SQL script list all the databases and its tables that where updated within the last 7 days. You can change the interval as per your requirement.

USE information_schema;
 
SELECT DISTINCT TABLE_SCHEMA , TABLE_NAME 
FROM 
TABLES 
WHERE UPDATE_TIME IS NOT NULL 
   AND 
     UPDATE_TIME > NOW() - INTERVAL 7 DAY 
   AND 
     TABLE_SCHEMA  <> 'information_schema'

If you want rather to check if a particular table was modified during a specific period you can use the following. For example if we want to check if the table ‘prices’ from the database ‘store’ had been modified during the last 7 days.

USE information_schema;
 
SELECT TABLE_SCHEMA , TABLE_NAME 
FROM 
TABLES 
WHERE UPDATE_TIME IS NOT NULL 
   AND 
     UPDATE_TIME > NOW() - INTERVAL 7 DAY 
   AND 
     TABLE_SCHEMA = 'store' 
   AND
     TABLE_NAME = 'prices'

Another example : to check if some table from the ‘store’ database has been modified during the last 3 hours.

USE information_schema;
 
SELECT TABLE_SCHEMA , TABLE_NAME 
FROM 
TABLES 
WHERE UPDATE_TIME IS NOT NULL 
   AND 
     UPDATE_TIME > NOW() - INTERVAL 3 HOUR 
   AND 
     TABLE_SCHEMA = 'store'

Note that as far as I know update_time is available for MyISAM tables while InnoDB table stores the update_time field as null. Another thing I found is that update_time is not consistently updated on Windows so I would not trust that value, but it works on Linux systems.



4 thoughts on “How to check when a MySQL table was last updated

Comments are closed.