make all auto increment integers the same number of digits with leading zeros

Question

I was wondering if there was a way to set my id column(auto-incremented) to always have 5 digits using leading zeros in my mysql table. So the first value would be 00001 then 00002 and so on up to 99999 instead of 1,2,3 up to 99999. Thanks

Answer 1

Try adding ZEROFILL attribute to the field;

Declaration:

`ID` INT(5) ZEROFILL AUTO_INCREMENT NOT NULL

Answer 2

Your ID column is a numeric value, so it's always going to have the same value. If you want the integer to be formatted a certain way, then you're going to have to do that formatting as part of the SELECT. Look at the LPAD function.

LPAD with leading zero