Menu
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 :)
868
Is there way to insert leading zeros to a integer datatype column? No, because leading zeros are meaningless on numeric data.
Introduction to MySQL INT typeAn integer can be zero, positive, and negative. MySQL supports all standard SQL integer types INTEGER or INT and SMALLINT . In addition, MySQL provides TINYINT MEDIUMINT , and BIGINT as extensions to the SQL standard.
MySQL supports all standard SQL numeric data types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC.
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
