使用 MYSQL 创建一个随机数

我想知道是否有一种方法来选择随机生成的数字之间的100和500以及选择查询。

例句: SELECT name, address, random_number FROM users

我不需要将这个数字存储在 db 中，只是用来显示用途。

我试过这样的方法，但是不起作用。

SELECT name, address, FLOOR(RAND() * 500) AS random_number FROM users

希望有人能帮帮我。 谢谢你

126028 次浏览
小开
最佳答案

This should give what you want:

FLOOR(RAND() * 401) + 100

Generically, FLOOR(RAND() * (<max> - <min> + 1)) + <min> generates a number between <min> and <max> inclusive.

Update

This full statement should work:

SELECT name, address, FLOOR(RAND() * 401) + 100 AS `random_number`
FROM users
小开

You could create a random number using FLOOR(RAND() * n) as randnum (n is an integer), however if you do not need the same random number to be repeated then you will have to somewhat store in a temp table. So you can check it against with where randnum not in (select * from temptable)...

小开

As RAND produces a number 0 <= v < 1.0 (see documentation) you need to use ROUND to ensure that you can get the upper bound (500 in this case) and the lower bound (100 in this case)

So to produce the range you need:

SELECT name, address, ROUND(100.0 + 400.0 * RAND()) AS random_number
FROM users
小开

This is correct formula to find integers from i to j where i <= R <= j

FLOOR(min+RAND()*(max-min))
小开

Additional to this answer, create a function like 

CREATE FUNCTION myrandom(
pmin INTEGER,
pmax INTEGER
)
RETURNS INTEGER(11)
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
BEGIN
RETURN floor(pmin+RAND()*(pmax-pmin));
END;

and call like

SELECT myrandom(100,300);

This gives you random number between 100 and 300

小开

these both are working nicely:

select round(<maxNumber>*rand())

FLOOR(RAND() * (<max> - <min> + 1)) + <min> // generates a number
between <min> and <max> inclusive.

提交答案