CODEWITHSUNDEEP
mysql
jimiyash
jimiyash

Reputation: 2504

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

Upvotes: 4

Views: 18228

Answers (5)

Benjamin Sinzore
Benjamin Sinzore

Reputation: 119

This will help.

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

Upvotes: 0

Ganesan J
Ganesan J

Reputation: 646

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;

Upvotes: 0

Matt Phillips
Matt Phillips

Reputation: 11529

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.

Upvotes: 12

Onkelborg
Onkelborg

Reputation: 4007

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

Upvotes: 4

zebediah49
zebediah49

Reputation: 7611

Is there a reason why you can't just do

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

?

Upvotes: 3

Related Questions