选择所有MySQL行并重新插入

Question

So I have a table in my database which has some 1000 rows in it.

Now I don't want to display these records in the same order they are inserted into the table.

I want all the rows in the table to be somehow randomly shuffled. Ex : if I have 10 records in the order 1 2 3 4 5 6 7 8 9 10, I want it like 8 2 4 6 3 1 7 9 10 5.

The rand() method can be used but it's not efficient for large data. I can fetch the data and shuffle in PHP but I don't want to use it because then I would have to shuffle it every time I fetch the data.

So basically I don't want to shuffle and fetch the data but shuffle all the rows in table itself.

Just once shuffling my rows in table is more than enough for me.

Answer 1

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()