creating a random number using MYSQL
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
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 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