Is there any version control in mysql?

Question

It is possible to use a version control system with mysql databases? Or, is there a version control system already implemented?

I want to say e.g.: SELECT foo FROM bar WHERE version = X

Whereeby version is a mysql internal colum with last update date.

AFAIK no, there's no such thing in any RDBMS. Guess you should add that functionality yourself. For example an additional "version" column in all of your tables (well, at least in those which would need version control). Also, this VC feature should probably be managed at the ORM level. — Marcello Romani, Jun 02 '13 at 18:52

adamf · Answer 1 · 2013-12-30T18:51:23.237

Very late response... similar to Ruben's suggestion, I have setup triggers to update a version_control table to increment version number every time there is an INSERT, UPDATE & DELETE.

I laid out the steps on my site mradamfrancis.tumblr.com

** update **
I’ve decided to use triggers to assist with version control. Here’s how…

I have a table containing players, if there are changes (INSERT, DELETE, or UPDATE) I want to increment the version number in my version_control table.

This is how the version_control table looks: version_id (key), table_name (varchar), version (integer)

I then create 3 triggers on the players table, one for INSERT, DELETE & UPDATE.

INSERT:

    delimiter //
    CREATE TRIGGER `player_table_INSERT` AFTER INSERT ON `players`
    FOR EACH ROW BEGIN
    UPDATE version_control SET version=version+1 WHERE table_name=’players’;
    END;//
    delimiter ;

DELETE:

    delimiter //
    CREATE TRIGGER `player_table_DELETE` AFTER DELETE ON `players`
    FOR EACH ROW BEGIN
    UPDATE version_control SET version=version+1 WHERE table_name=’players’;
    END;//
    delimiter ;

UPDATE:

    delimiter //
    CREATE TRIGGER `player_table_UPDATE` AFTER UPDATE ON `players`
    FOR EACH ROW BEGIN
    UPDATE version_control SET version=version+1 WHERE table_name=’players’;
    END;//
    delimiter ;

** I have additional SQL statements in the FOR EACH section of the trigger, hence I’ve used delimiter (1st line and last line) along with BEGIN & END.

Nice answer. As a general rule though it's better if you copy as much of the answer here as possible rather than just linking to another site; that way the answer'll still be good even if the link dies for any reason — StackExchange What The Heck, Dec 30 '13 at 18:05
thanks for the tip yochannah (first time answering). I'll update my original response — adamf, Dec 30 '13 at 18:39

score 1 · Accepted Answer · answered Jun 03 '13 at 13:27

You could also define extra tables for logging. For instance if you already have a table news, you can duplicate it as news_log. then add columns for logging data such as: modified date, action (update, delete, add) and so on.

next you define triggers on the original tables that will insert the data into your logging table. for instance when you update a record in you news table the news_log_trigger that you define is executed and a new record is inserted into new_log with the action value "UPDATE" and the current date as modified date.

for more info on mysql triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

In case you want to do this for every project you can problably write a generic stored procedure to do the actual logging. that way you can reuse it and you only have to define the triggers and logging tables.

Is there any version control in mysql?

2 Answers2