Staging Table

Can MySQL handle a staging table, one where a fixed number of rows are reused in a round-robin basis (e.g., temporarily store AJAX-driven auto-saved forms, without overwriting the real values so the user can still abandon changes), without an external script?

First thoughts turned to events, triggers, stored procedures, and such toys in the most recent versions of MySQL – none of which worked quite right. Turns out we can do it with a little one-time setup and a single statement that will work as far back as version 4.0.4.

We need two tables, one to store the current marker, and one for the staging table.
CREATE TABLE theNum (placeHolder int);
CREATE TABLE theData (id int, theFile blob);

We need to populate theNum with some starting value, and theData with as many distinct rows as needed:
INSERT INTO theNum VALUES (1);
INSERT INTO theData VALUES (1, null), (2, null), (3, null), (4, null), (5, null);

Then, use this update instead of an insert to add a record to the database:

UPDATE
    theData
    JOIN theNum ON (id = placeHolder)
SET
    placeHolder = placeHolder % 5 + 1,
    theFile = 'contentOfTheFileHere';

Because of the way updates and joins work, we’ll actually end up with two copies of the most recent file – one with id matching the current value of theNum.placeHolder (which will be overwritten on the next update), and one matching the previous value (which will remain until the value wraps around again). That’s perhaps annoying, but not a show-stopper. If you really want five staging rows, use six instead.

The only tricky part will be getting the correct id for the user’s insert, so we know which rows to copy from the staging table to a permanent location. We’ll need to lock the tables, select the current placeHolder, and then run the above update, to avoid race conditions.