10

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.

2 Answers 2

6

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.

4
  • 1
    The UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is only updated for MyISAM tables, not for InnoDB. Refrain from depending on this value. Commented Jun 30, 2013 at 5:37
  • @ShlomiNoach what was I thinking? I just answered this one on that : stackoverflow.com/a/17341149/491757 Commented Jun 30, 2013 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.
    – dardar.moh
    Commented Jun 30, 2013 at 9:20
  • @dardar.moh I don't think you can do that unless you'd have permissions to information_schema's tables and the trigger would do the alteration there, which I'm assuming you don't. IMO mysql/mariadb are simply missing update_time in the columns table` ...
    – jave.web
    Commented Feb 26, 2023 at 16:38
4

What you could is change your table so it stores the timestamp whenever it is updated. For example

CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

The above code store the current timestamp to a new row and changes it the time edited whenever a row is edited. This way you can get the data, sort it (order by) in descending mode and get the top record. That way you get the last updated record in your table. If you want to know for the whole database then you have to compare the last updated record for each of your tables and get the most recent one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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