Menu
I am working on a database application with MySQL and PHP. At this moment I'm trying to get the changes caused by the last UPDATE. My first way to solve the problem is
These are three mysql-connections...
Is there any way to shorten this?
950
Syntax to know the last updated time. SELECT UPDATE_TIME FROM information_schema. tables WHERE TABLE_SCHEMA = 'yourDatabaseName' AND TABLE_NAME = 'yourTableName'; Let us implement the following query to get the last updated time.
Create a table called changes . It would contain the same fields as the master table but prefixed with old and new, but only for those fields which were actually changed and a TIMESTAMP for it. It would be indexed with an ID . This way, a SELECT report could be run to show the history of each record.
MySQL CDC Setup: Using Binary LogsMySQL binary logs provide a very efficient way to track data changes for MySQL CDC. They contain events that describe the modifications to data.
The only way for doing this is Using something like a Log file, in this case you will create a table that will contains the updated columns each time there is an update. In this way you will be able to get the last record that will contains the table_name and the column_name.
You could do an before update trigger that will push an entire copy of the record to a history table that also contains additional state data you wish to store (updated date, user etc.)
This way you will have a complete revision history of what happened with what records and it should happen transparently. only think to remember is you should drop any unique constraints from the history table.
Hope this helps.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
