randomly shuffle MySQL records

[DoubleV]

I have a table in MySQL and I want shuffle its records into a random order once and keep the database that way. I don't want to pull off the records every time a query is made and then display them in some kind of random order. All I want is to shuffle the existing order of records within the table to random and for the db itself to stay that way. I use MySQLAdmin, but I do not see a button there to do something like that.

[sad69]

I'm not sure that you can do that..

But I can think of a hack:
MySQL allows you to have a table default sorted by some column. What you can do is introduce a new integer column. Then you can write a script or manually give each row a random integer value in that column. Finally, set the default sort to that column, and it will be randomly ordered.

Sadiq.

[bcarl314]

I don't think it's possible without some fairly major hacking. My understanding is that as is, the records in a table are "random" in that they are not necesisarily be returned in the same order every time. Just run the same query on several 1000 rows and you'll see. To order a result set, you need to use the ORDER BY clause in your SQL statement.

Now, I should note that although the result sets are not the same, they are usually very similar. Usually the 9583rd row of a 100,000 row table will be the same, but there's no guarantee of that.

[Kiwi]

I posted the answer elsewhere, but thought I should copy it here. It's not that tricky, really. You create a second table, duplicating the original structure, then dump all the records into it. Rename the tables and you're done. It'd be worth rebuilding any indexes on the new table and you might hit a few snags with mySql getting fussy about re-using old names.INSERT INTO <copy of source table>
SELECT *
FROM <source table>
ORDER BY RAND()The <copy of the source> table should make sure that any auto-increment primary-key is turned off when you do this, or mySql might get a little grumpy. I think you'll be OK to put it back on once you've completed this little operation, but you'd want to test that out. It'd be wise to re-build any indexes after doing this as well.

Finally, if you're using InnoDB tables, wrap the whole thing (create tables, move data, rename tables, build indexes) in a transaction and you'd have a seemless update.