MySQL: Fill with random iterated position values

Question

I suppose this will be easy to MySQL pros. Given an existing filled MySQL-InnoDB table like

key1 | key2 | foobar | position

where key1 and key2 together make up the primary key, and position is mostly still NULL, with what SQL would I best fill the "position" field with a randomly ordered but iterated integer value? E.g. if there were 4 rows, then I would want to have (just as an example order):

3
1
4
2

Background: I want to display the table to the user in a web app, and the order should be static but random, i.e. once decided randomly but then with a predictable order. Sometimes I need to completely reshuffle the order, though, while the rest of the table remains intact. Also, I sometimes need to add a couple of rows to the table with new positions, but these new rows must not be given a position lower than any existing ones, hence the need to have it be iterated, and not say using a completely random float.

Answer 1

You might want to try something like this:

CREATE TEMPORARY TABLE IF NOT EXISTS temptable (
    key1 int NOT NULL,
    key2 int NOT NULL,
    pos int NOT NULL auto_increment,
    PRIMARY KEY (key1, key2)
    )
SELECT key1, key2 
FROM yourtable
ORDER BY RAND();

UPDATE yourtable
SET position = pos
FROM yourtable INNER JOIN temptable
               ON yourtable.key1 = temptable.key1
                  AND yourtable.key2 = temptable.key2;