CODEWITHSUNDEEP
mysqlsql
Mehur
Mehur

Reputation: 141

Generate 6 digit random number in mysql

I want to generate random 6 digit number in mysql but sometime it generate only 5 digit.

UPDATE member SET updates = FLOOR(RAND() * 999999)

Upvotes: 6

Views: 20760

Answers (3)

galianet
galianet

Reputation: 119

This generate 6 digit number exactly:

UPDATE member SET updates = FLOOR((RAND() * (999999-100000+1))+100000);

Upvotes: 3

shashikant pandit
shashikant pandit

Reputation: 556

You can generate random 6 digit number in mysql

UPDATE member SET updates = LPAD(FLOOR(RAND() * 999999.99), 6, '0');
+--------+
| Value  |
+--------+
| 499540 |
| 550607 |
| 254419 |
| 620272 |
| 338104 |
| 829705 |
+--------+

or you can also random number generate in mysql 1 to 200 in increment form. you can change in place of 200 to **** as you like

UPDATE member SET updates = FLOOR(1 + (RAND() * 200));
    +--------+
    | Value  |
    +--------+
    | 1      |
    | 180    |
    | 50     |
    | 85     |
    | 33     |
    | 165    |
    +--------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270411

If the problem is that you are missing leading zeros, you can left pad with spaces:

UPDATE member
    SET updates = LPAD(FLOOR(RAND() * 999999.99), 6, '0');

I hope you understand that "random" means "random" and different rows can get the same value.

Upvotes: 17

Related Questions