Storing leading zeros of integers in MySQL database as INTEGER
Solution 1
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.
Solution 2
Change the structure of the field and make the Attributes UNSIGNED_ZEROFILL
to keep the zeros.
But you should be careful to the Length of the field, because it's gonna return all the rest numbers to zeros so put the length of your field
Solution 3
You can still sort the string(CHAR/VARCHAR) columns like integer using CAST
ORDER BY CAST(`col_name` AS SIGNED) DESC
So, you can store them in CHAR/ VARCHAR type fields.
Solution 4
You can not store integer with leading zeroes. One way is keeping it in varchar as well as int columns. In this case, you need two columns, one value and the other intValue and while sorting, you can use the intValue for sorting. This is easy to implement.
Select Value from Table order by intValue;
The other option can be using two columns, one valu and othe NumOfZero and use these for the desired results. This is complex, but might be light on the DB.
Michael
Updated on March 24, 2020Comments
-
Michael about 4 years
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 :)
-
Damien_The_Unbeliever almost 13 yearsintegers don't have leading zeroes. Only string representations of them can. So if you "need" leading zeroes, you'll have to store a string representation. Why can't you do this kind of formatting at display time, rather than putting it in the database?
-
ZygD almost 13 years1 and 001 and different values. But as int they are the same: how should these be sorted and compared? as int or as varchar?
-
-
Aaron W. almost 13 yearsCould also do
ORDER BY col_name + 0 DESC
to sort a varchar like integer -
Isaiah Turner about 10 yearsNote: if you know that the column will always be the same length, use CHAR, not VARCHAR.
-
Manuel Jordan over 4 yearsEven when I can confirm that your query works, I have a doubt, Does exist an impact about performance when
lpad
is used for numeric types? It becauselpad
is astring-function
. Consider the scenario about having a query retrieving a lot of records andldap
is used to format the output of a numeric type.