33 Votes

MySQL: Add or Insert Random Number

Tip by Progger99 | 2012-05-09 at 20:26

Sometimes, we would like to insert a random value into a MySQL table, for example, to test a website using random data. That is, how it works:

UPDATE tab SET col=FLOOR(5+(RAND()*(10-5+1)));

This function sets in the table "tab" the column "col" to a random value between 5 and 10. To create other random data, simply substitute the values 5 and 10 with other numbers or use the variables of this PHP function for your boundaries:

$min = 5;
$max = 10;
$query = "UPDATE tab SET col=FLOOR($min+(RAND()*($max-$min+1)))";

For numbers between 0 and a maximum value (here $max), the command simplifies to:

UPDATE tab SET col=FLOOR(RAND()*$max+1);

In some cases, we want to add a random number to an existing value in a column. This can be done with this MySQL command:

UPDATE tab SET col=spalte+FLOOR(RAND()*$max+1);

Of course you can also use the PHP function from above in this case.


About the Author

AvatarThe author has not added a profile short description yet.
Show Profile


Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.