Versioning of tables in MySQL

In this article we will see how we can create tables in MySQL in such a way that several versions of the same table are stored in the database and we can choose which one we want to currently use. This may be useful in situations where we have inserted data into a table and we want to update the table without the risk of damaging the old table while we we are inserting the new contents. Or we want to be able to easily switch back to an old version without having to re-insert this data.

The trick we use here is to create a view that points to the current table we want to use. To be able to atomically point the view to another table, we use a stored procedure.

We start with the SQL code to create the stored procedure createMyTable. This stored procedure has one argument: the name of the table to create, e.g. mytable_2010_11_22.

DROP PROCEDURE IF EXISTS createMyTable;

DELIMITER //
CREATE PROCEDURE createMyTable(IN table_name text)
BEGIN
   SET @sql = CONCAT("CREATE TABLE ", table_name, "(", "id int,", "name text");");

   PREPARE stmt FROM @sql;
   EXECUTE stmt;

   DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

At this point we can create a new table and fill it:

CALL createMyTable("mytable_2010_11_22");
INSERT INTO mytable_2010_11_22 (id, name) VALUES (1, "Alice");
INSERT ...

The second part we need is the stored procedure that changes the view to point to the newly created table. This stored procedure also has one argument: the name of the table to point to, e.g. mytable_2010_11_22.

DROP PROCEDURE IF EXISTS createMyView;

DELIMITER //
CREATE PROCEDURE createMyView(IN name text)
BEGIN
    SET @sql = CONCAT("CREATE OR REPLACE VIEW mytable AS SELECT * FROM ", name);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Call this stored procedure to point to our newly created table:

CALL createMyView("mytable_2010_11_22");

Now, if we want to list the content of the table that the view currently points to, we can use something like this:

SELECT * FROM mytable;