How do I insert a random value into mysql?

It looks like RAND is what I need开发者_开发问答 but I'm having a bit of trouble understanding how it works.

I need to insert a random number between 60 and 120 into a couple thousand rows. Table name is: listing and the column name is: hits

Could you please help?

---

To make a random integer between 60 and 120, you need to do a bit of arithmetic with the results of RAND(), which produces only floating point values:

SELECT FLOOR(60 + RAND() * 61);

So what's going on here:

RAND() will produce a value like 0.847269199. We multiply that by 61, which gives us the value 51.83615194. We add 60, since that's your desired offset above zero (111.83615194). FLOOR() rounds the whole thing down to the nearest whole number. Finally, you have 111.

To do this over a few thousand existing rows:

UPDATE table SET randcolumn = FLOOR(60 + RAND() * 61) WHERE (<some condition if necessary>);

See the MySQL docs on RAND() for more examples.

Note I think I have the arithmetic right, but if you get values of 59 or 121 outside the expected range, change the +60 up or down accordingly.

---

Here is how to get the random number in a range. The following can bit a bit ambiguous simply because the 61 is actually your max value (120) minus your min value (60) + 1 to get inclusive results.

SELECT FLOOR(60 + (RAND() * 61));

SELECT FLOOR(MIN_Value + (RAND() * (MAX_Value - MIN_Value) + 1);

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

---

UPDATE X SET C = FLOOR(61 * RAND() + 60) WHERE ...;

to get a number between 60 and 120 (including 60 and 120);

RAND() creates a number in the interval [0;1) (that is excluding 1). So 61 * RAND() yields a number in [0, 61). 61 * RAND() + 60 is in [60;121) By rounding down you ensure that your number is indeed in [60;120].

---

When I faced this kind of issue, I tried manual, but I have over 500 lines, I logically brought a trick which helped me, because if you run RAND on query, you might end up getting error report due to Duplicates, OR PRIMARY KEY issue, especially if that column is a PRIMARY KEY and AUTO INCREMENT.

1. Firstly - I renamed the column in question, e.g. mine was ID -> IDS
2. Secondly - I created another column and Called it ID
3. Thirdly - I RAN this code

UPDATE history SET id = FLOOR( 217 + RAND( ) *2161 )

This created a random numbers automatically, later i deleted the renamed IDS colume

credit FROM MICHAEL. Thank you