Get the Time of Last update of a column

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.

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.

StackExchange Database Administrators Q#45470, answer score: 6