I need MySQL to store numbers in a integer field and maintain leading zeros. I cannot use the zerofill option as my current field is Bigint(16) and numbers can vary in amount of leading zeros. IE: 0001 - 0005, then 008 - 010 may need to be stored. I am not concerned about uniqueness of numbers (these aren't being used as IDs or anything) but I still need them to be stored preferably as INTS.
The issue using CHAR/VARCHAR and then typecasting the values as integers in PHP means that sorting results via queries leads to alphanumeric sorting, IE: SORT BY number ASC would produce
001
002
003
1
100
101
102
2
Clearly not in numerical order, but in alphanumeric order, which isn't wanted.
Hoping for some clever workarounds :)
Keep the numbers stored as integers.
Then use function LPAD()
to show the numbers (left) padded with zeros:
SELECT LPAD( 14, 7, '0') AS padded;
| padded |
-----------
| 0000014 |
If the number of zerofill characters is variable, add another column in the table with that (zerofill) length.