I have 2 x int cols in a table in which user has to specify starting and ending numbers of a range. It can be anything, like in some cases its going to be: 000001 to 999999. But as soon as 000001 is added into mysql, it changes it to “1”, meaning all 0’s are omitted.
How can this be preserved ? If somehow this is preserved, then when I have to query the db for a number like say: 000123 then how to search for it ??
The Integer type will not store leading zeros. In mathematics 1 has the same numeric value as 000001 so they are effectively the same number, so will be stored in the exact same way.
I think the key is to understand why you want the leading zeros. Must they be stored in this way?
Usually if you need numbers (or any data) in a specific format, that formatting can be done by your scripts on data retrieval and the database just stores it in the most efficient and searchable way.
If the data is a String but typed as a numeric in the table, unless you need to do math operations with the value you should consider ALTERing the field.
Else you should be able to pad() the value when you need to.
Bascially there going to be 1000’s of records in the db, and all will be numbers but in different formats, like some can be 1 or 001 or 00001 or 0000001 etc. I don’t have to do any math on these numbers but just range comparison of user input, so 001 to 999 and user inputs 123 then I have to check if its in 001 to 999 or not. Can this be done if I change INT to VARCHAR ??
Secondly I also need to make sure that if range is 001 to 999 and user inputs 10 then it needs to be 010 and not just 10 and then compare 010 from the range. How do I do that ??