MySQL’s RAND() function can be used to set random values or fill a table with random data.
The following SQL snippet will change column1 in all rows to a random number 0-50.
UPDATE tablename SET column1 = (rand() * 50)
Popularity: 8%
Most Popular Posts
{ 3 comments… read them below or add one }
Thanks, handy stuff! Question tho: will the values in these tables be unique? (for say the primary key)
I tried to come up with a positive test case, but nothing seemed to work. MySQL is just going to fill in random numbers based on the formula supplied. You would need to add an external check to see if the row was created or updated properly to know if you could proceed to the next row. Internally thats what MySQL would have to do… get a random number, will it violate primary key constraint to insert or update current row? yes = done, next row; no = repeat.
To test we would need a large table with an intact primary key. Then we execute the query to randomize the primary key column. In my tests I always received a duplicate key error on a random row. Whichever row first updated to create a duplicate key threw the error. Re-run the query and get a different row.
#1062 - Duplicate entry '4' for key 1Try:
UPDATE tablename SET column1 = sha1( rand()*rand()*rand()*rand() );