How do I create a random 4 digit number in mysql

I have a table of several hundred users and I want to create a 4-digit pin code like those used at an ATM machine for each user using an UPDATE statement. They don't have to be unique, but I don't want to use an autonumber type of field so one company cannot easily guess the pin code of another user. What is the simplest and easiest way to do this?

Note: This pin code is used as anoth开发者_开发问答er layer of authentication, they also need a password to login.

From the Mysql docs...

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

update user set pin=(select floor(0+ RAND() * 10000)) where uid=<user_uid>;

Also I think you need to have the pin column be defined something like

pin int(4) zerofill not null 

I would think.

I think something similar to SUBSTRING(FLOOR(RAND() * 9999 + 10000), 2) would do it?

Is there a reason why you can't just do

UPDATE `users` set `pin`=FLOOR(10000*RAND());

?

In my case I try to create random password for users ,here password is null

update users set password =(FLOOR(RAND() * 9999) + 10000)  where password is null;

For your scenario

update users set pin =(FLOOR(RAND() * 9999) + 10000)  where pin is null;

This will help.

SELECT FLOOR ((RAND() * 10000));