Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This command gives the date of the last update for a table

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'MyDB'
AND TABLE_NAME = 'MyTable'

But I want to find the time of last update of a particular column of a table. I can't use triggers because I want to know the time of last update of a specific column from a system table.

I hope I explained well my problem.

share|improve this question

1 Answer 1

None of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes. Any time that

  • one or more columns change in any row
  • a new row is added
  • an old is deleted
  • an ALTER TABLE of any kind

the UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is updated.

SUGGESTION

This may be a bitter pill to swallow, but you must do the following:

  • Create a custom table that records
    • primary key
    • column name
    • the old value of that column
    • the new value of that column
    • timestamp of the change
  • Create BEFORE UPDATE trigger to compare old and new columns and record it if they do not match

EPILOGUE

This may not be so bad if you are customizing for a few tables and a few columns.

share|improve this answer
    
The UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is only updated for MyISAM tables, not for InnoDB. Refrain from depending on this value. –  Shlomi Noach Jun 30 '13 at 5:37
    
@ShlomiNoach what was I thinking? I just answered this one on that : stackoverflow.com/a/17341149/491757 –  RolandoMySQLDBA Jun 30 '13 at 5:52
    
So bad, for ur suggestion I can't use triggers because I want to know the time of last update of a specific column from a system table. And Thanks for ur answer. –  FÔx Gênki Jun 30 '13 at 9:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.