CODEWITHSUNDEEP
mysqldatabase
Ritesh Kadmawala
Ritesh Kadmawala

Reputation: 743

MySQL column value revision history

I have a column in a MySQL database for which I want to store all the revisions of its value along with the date when they were changed.

Is there any inbuilt mechanism in MySQL that allows me to do this or is there any recommended way of solving this problem?

Upvotes: 0

Views: 307

Answers (1)

Daniel Schneller
Daniel Schneller

Reputation: 13926

There is no such feature built in to MySQL. You will need to implement this yourself, depending on what you need to store. You might introduce a layer in your persistence logic that inserts the log data you need into a separate history table.

Depending on the amount of changes you have, make sure to think about a cleanup strategy, because these histories can get huge over time.

If you are talking about multiple tables for which such a history is required, you could, depending on the overall amount of changes expected, use either a single history table with an additional column that identifies the table the change belongs to, or a separate history table per data table.

Also, take a look at the different storage engines available. There is a compressed Archive Storage Engine, which does not allow modifications of data once it has been inserted and there are other limitations, but its storage requirements are lower that e. g. InnoDB's. It was designed for archival purposes and not for ad-hoc queries, so it might or might not be a good fit for you.

Upvotes: 2

Related Questions