Reputation: 295

How can I set autoincrement format to 0001 in MySQL?

How can I make MySQL auto increment in 4 digit format?

So instead of '1' make '0001'?

Upvotes: 26

Answers (6)

Paul Dixon

Reputation: 301035

Could you leave it as an integer and format it for humans in your SQL, for example, to pad with zeros to 4 chars wide

select lpad(idcolumn,4,'0') from mytable;

Or use zerofill and specify the desired width when declaring the table:

create table tmpfoo (
   mykey int(6) zerofill not null auto_increment, 
   primary key(mykey)
);

insert into tmpfoo values(1),(2);

select * from tmpfoo;
+--------+
| mykey  |
+--------+
| 000001 |
| 000002 |
+--------+

Upvotes: 19

Hawk Kroeger

Reputation: 2334

To pad in the database set the id column to ZEROFILL

But if its for display purposes only I recommend using LPAD SELECT RIGHT('000000' + yourNum, 6);

Upvotes: 2

gahooa

Reputation: 137492

MySQL supports ZEROFILL on integer columns:

mysql> create table foo (the_key int unsigned zerofill not null 
       auto_increment primary key);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into foo SET the_key = Null;
Query OK, 1 row affected (0.00 sec)

...

mysql> insert into foo SET the_key = Null;
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+------------+
| the_key    |
+------------+
| 0000000001 |
| 0000000002 |
| 0000000003 |
| 0000000004 |
| 0000000005 |
| 0000000006 |
| 0000000007 |
| 0000000008 |
+------------+
8 rows in set (0.00 sec)

You may need to look into using a smallint (5 digits), or trimming/padding.

Upvotes: 7

jonstjohn

Reputation: 60306

If you need the auto_increment column in a zero padded format, I suggest that you display it as such and not attempt to store it in the database that way.

In PHP, you could use the following code to display or otherwise use the id: