MySQL 如何在mysql中插入一个随机值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7129374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert a random value into mysql?
提问by Andi
It looks like RAND is what I need but I'm having a bit of trouble understanding how it works.
看起来 RAND 正是我所需要的，但我在理解它的工作原理时遇到了一些麻烦。
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
我需要将 60 到 120 之间的随机数插入几千行。表名是：listing，列名是：hits
Could you please help?
能否请你帮忙？
回答by Michael Berkowski
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:
要生成 60 到 120 之间的随机整数，您需要对 的结果进行一些算术运算，RAND()它只产生浮点值：
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.
RAND()会产生一个像0.847269199. 我们将其乘以 61，得到值 51.83615194。我们添加 60，因为这是您想要的高于零的偏移量 (111.83615194)。FLOOR()将整个事物向下舍入到最接近的整数。最后，你有 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.
有关更多示例，请参阅MySQL 文档RAND()。
NoteI think I have the arithmetic right, but if you get values of 59 or 121 outside the expected range, change the +60up or down accordingly.
请注意，我认为我的算术是正确的，但是如果您得到超出预期范围的 59 或 121 值，请相应地+60向上或向下更改。
回答by Bueller
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.
以下是如何获取范围内的随机数。以下可能有点模棱两可，因为 61 实际上是您的最大值 (120) 减去您的最小值 (60) + 1 以获得包含结果。
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
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
回答by emboss
UPDATE X SET C = FLOOR(61 * RAND() + 60) WHERE ...;
to get a number between 60 and 120 (including 60 and 120);
得到一个 60 到 120 之间的数字（包括 60 和 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].
RAND()在区间 [0;1) 中创建一个数字（即不包括 1）。所以 61 * RAND() 在 [0, 61) 中产生一个数字。61 * RAND() + 60 在 [60;121) 中）通过四舍五入确保您的数字确实在 [60;120] 中。
回答by ExploreTech
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 KEYand AUTO INCREMENT.
当我遇到这种问题时，我尝试了手动，但我有 500 多行，逻辑上我带来了一个对我有帮助的技巧，因为如果您在查询上运行 RAND，您最终可能会因重复或主键而得到错误报告问题，特别是如果该列是 aPRIMARY KEY和AUTO INCREMENT。
- Firstly -I renamed the column in question, e.g.mine was ID -> IDS
- Secondly -I created another column and Called it ID
- Thirdly -I RAN this code
- 首先 -我重命名了有问题的列，例如我的是 ID -> IDS
- 其次 -我创建了另一列并将其称为 ID
- 第三 -我运行这个代码
UPDATE history SET id = FLOOR( 217 + RAND( ) *2161 )
更新历史 SET id = FLOOR( 217 + RAND( ) *2161 )
This created a random numbers automatically, later i deleted the renamed IDS colume
这会自动创建一个随机数，后来我删除了重命名的 IDS colume
credit FROM MICHAEL. Thank you
来自迈克尔的信用。谢谢
