How to insert random value in mysql?

Question

RAND seems to be what I need, but I have trouble understanding how this works.

I need to insert a random number from 60 to 120 in a couple of thousand lines. Table Name: List and Column Name: hits

Could you help me?

Answer 1

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

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

So what is going on here:

RAND() will 0.847269199 value similar to 0.847269199 . We multiply this by 61, which gives us the value 51.83615194. Add 60, as this is your offset above zero (111.83615194). FLOOR() rounds everything to the nearest integer. Finally, you have 111.

To do this over several thousand existing lines:

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

See MySQL docs on RAND() more details.

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

Answer 2

Here's how to get a random number in a range. The following might beat a bit, because 61 is your maximum value (120) minus the minimum 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

Answer 3

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

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

RAND() creates a number in the interval [0; 1) (i.e., excluding 1). So, 61 * RAND () gives the number in [0, 61]. 61 * RAND () + 60 is located at [60; 121). When rounding, you will make sure that your number is indeed at [60; 120].

Answer 4

When I encountered such a problem, I tried the manual, but I have more than 500 lines, I logically gave a trick that helped me, because if you run RAND on demand, you may get an error due to an error Duplicates, OR PRIMARY KEY, especially if this column is PRIMARY KEY and AUTO INCREMENT .

• First of all . I renamed the corresponding column , for example . My ID → IDS
• Secondly - I created another column and named it ID
• Third - I RAN this code

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

This created random numbers automatically, later I deleted the renamed IDS cols

LOAN FROM MICHAEL. Thanks you