How can I set autoincrement format to 0001 in MySQL?

Question

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

So instead of '1' make '0001'?

Answer 1

Try adding ZEROFILL attribute to the field.

Answer 2

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

Answer 3

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.

Answer 4

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:

$padded_id = str_pad($id, 4, '0');

Answer 5

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);

Answer 6

is the field an integer? if so, the answer is, "why? it's an integer!" ;-)