How do I create a random 4 digit number in mysql

Question

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 another layer of authentication, they also need a password to login.

Answer 1

Is there a reason why you can't just do

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

?

Answer 2

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.

Answer 3

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