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.
Why not just use a transaction?
Comment by Sheeri — August 14, 2006 @ 2:54 am
Sure, if you can use InnoDB, you can use a transaction instead of locking the tables. If you’ve committed (har!) to MyISAM, you’ll need some other means.
There are, of course, any number of ways to tackle this same issue. One other approach would be to timestamp each row, and before every insert use DELETE with ORDER BY and LIMIT 1. That’s probably preferable, because then you have LAST_INSERT_ID() available. Of course, it means using two queries, which all new developers oppose for some mysterious reason.
Comment by snoyes — August 14, 2006 @ 5:43 am