How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that value i represents the ith occurrence of that key (but I want that ordering to be random). I thought perhaps some sort of auto increment would work, but I have multiple keys and thus would need multiple columns (I believe only one auto increment is permitted).

The solution I came up with doesn't seem to be working. First I created a new table to store the columns I am interested in, plus one extra column, rnd. I would use rnd later as a way to to ORDER BY rand() in a context where I need an actual column and not a function. Here's how I accomplished the first bit:

SET @rnd = 0;
INSERT INTO new_table SELECT col1, ..., colN, @rnd := @rnd+1 FROM original_table ORDER BY rand();

Next I would add an auto increment based upon the key, and I would make use of the rnd column I just created. The ALTER TABLE...ORDER BY does not permit ordering by a function (like rand()), so that's why I needed to create the rnd column first:

ALTER TABLE new_table ADD first_index INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD KEY(col1, col2, first_index), ORDER BY rnd;

I would then remove the auto increment status and repeat for each key:

ALTER TABLE new_table MODIFY COLUMN first_index INT UNSIGNED NOT NULL;

At first this seemed to do the trick. However, upon closer inspection, it appears as though the random ordering was not respected. The index columns appeared to be created in the same order that the original table happened to use. I'm rather frustrated as I went through several very awkward steps to ensure a random ordering, and that did not even work. Why didn't my solution work, and is there a more elegant way to do this?

EDIT: It was apparently not clear what I was looking for, so I'll try a simple example:

```
Col1 Col2
1997 A
199

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

• create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc

• create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):

SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

• add a second INT field to be the first_index

• update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:

UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

• optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

StackExchange Database Administrators Q#1932, answer score: 2