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'?

Pastoral

Answer 1

Try adding ZEROFILL attribute to the field.

Aureomycin

More about numeric datatypes: dev.mysql.com/doc/refman/5.0/en/numeric-types.html – Coonan

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

Nasalize

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.

Danidania

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

Breechblock

Nice way, didn't knew about it. – Pastoral

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

Secretarygeneral

Answer 6

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

Varien

@[32]: I mean, what's the point of zero-padding an integer? It doesn't change the value. Is this just for display purposes? – Varien

@[32]: nevermind, answered my own question - this is just for display purposes (see dev.mysql.com/doc/refman/5.0/en/numeric-types.html) – Varien

Steven, you are right about integer, it will make no diffrence in mysql but outside mysql its diffrent story (although str_pad from php will do the same job) – Pastoral