2

Which is the best way of knowing whether a mysql table was modified (by an insert, update or delete)?

I'm looking for a mysql query which can tell me this. Is this possible? My first attempt, was to make another table with the logs, but there has to be a better way.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
dole doug
  • 34,070
  • 20
  • 68
  • 87
  • 1
    MySQL's `INFORMATION_SCHEMA` tables have been known to cache data - see this question for details: http://stackoverflow.com/questions/3240164/how-do-i-detect-if-a-table-exist-mysql – OMG Ponies Jul 06 '11 at 16:44
  • possible duplicate of [How can I tell when a MySQL table was last updated?](http://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated) – OMG Ponies Jul 06 '11 at 16:46

2 Answers2

3
SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'your_database_name'
       AND TABLE_NAME = 'your_table_name'

and then by checking the UPDATE_TIME, you can tell when was the last time this table was modified.

MD Sayem Ahmed
  • 28,628
  • 27
  • 111
  • 178
3

If you are on a recent version of MySQL you can examine the information schema

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

to find the last time the table was updated.

See How can I tell when a MySQL table was last updated? for additional comments.

Community
  • 1
  • 1
borrible
  • 17,120
  • 7
  • 53
  • 75