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? — Saket
– Saket, Commented Jan 14, 2017 at 19:52

mauro · Accepted Answer · 2017-01-14 19:50:58Z

1

Define the PIN column as

pin MEDIUMINT NOT NULL AUTO_INCREMENT

answered Jan 14, 2017 at 19:50

mauro

6,0262 gold badges31 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

Why MEDIUMINT? That's needlessly specific.

Bernd Buffen · Accepted Answer · 2017-01-14 20:36:10Z

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]>

Collectives™ on Stack Overflow

Automatically generating 6 digit unique number in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related