Is it ok to store numbers as varchar?
What’s the difference between
int 123456789012 and varchar 123456789012 ?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
No, it’s almost always a bad idea.
- will use more space
- indexes will not perform as well
- you can’t do arithmetic
- the data is not self-validating because of type
- auto-model generators will give you string type instead of numeric
- aggregates like SUM will no longer work
- the output may sort incorrectly
- you will need to CAST to use it as a number, causing performance hit
- etc.
Method 2
You can store leading zeroes to a varchar that you can’t do with integer columns (eg. it is possible to have difference between 123 and 0000123). For example zip codes in some countries. However, if you need to do that, then you are really dealing with textual information that should have varchar column. For example, phone numbers or zip codes should definitely go to a varchar column.
Otherwise if you are using your data like numbers (adding them together, comparing them, etc.) then you should put it into integer column. They consume far less space and are faster to use.
Method 3
You will not be able to do calculations with columns declared as varchar, so numeric types should be used. And since your SQL query is a string anyway, MySQL does all the conversion for you (that doesn’t mean that you don’t need to validate user provided values, of course).
Method 4
I think is ok to store numbers as varchar, as long as you don’t want to make calcs with it.
For example, a phone number or zip codes would be better to store in varchar fields because you could format them.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0