Automatically generating 6 digit unique number in MySQL

Question

I have a table named 'users' with the follwing columns

id
email
password
pin

I want the 'pin' to be auto-filled with a unique 6 digit number every time a new record is added. It would be of a great help if anyone could suggest a good approach for this problem.

Possible duplicate of [How to Generate Random number without repeat in database using PHP?](http://stackoverflow.com/questions/11680025/how-to-generate-random-number-without-repeat-in-database-using-php) — Saket, Jan 14 '17 at 19:52

score 1 · Answer 1 · answered Jan 14 '17 at 19:50

1

Define the PIN column as

pin MEDIUMINT NOT NULL AUTO_INCREMENT

answered Jan 14 '17 at 19:50

mauro

5,730
2
26
25

1

Why `MEDIUMINT`? That's needlessly specific. – tadman Jan 14 '17 at 19:55

score -1 · Answer 2 · answered Jan 14 '17 at 20:36

Use the zerofill option for your int field. the Mysql fills it for your.

CREATE TABLE `int6` (
  `id` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sample

 MariaDB [l]> drop table int6;
 Query OK, 0 rows affected (0.00 sec)

MariaDB [l]> CREATE TABLE `int6` (
    ->   `id` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [l]> insert into int6 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [l]> select * from int6;
+--------+
| id     |
+--------+
| 000001 |
| 000002 |
| 000003 |
+--------+
3 rows in set (0.00 sec)

MariaDB [l]>

Automatically generating 6 digit unique number in MySQL

2 Answers2