storing numbers as varchar

Question

Is it ok to store numbers as varchar?

What's the difference between

int 123456789012 and varchar 123456789012 ?

Answer 1

The size of the filed will greatly differ from varchar to int.

You will use a lot more storage by storing an INT as a varchar, rather thatn stoing it as an... INT

Is there any real use to YOU to store it that way?

Have a look at

Data Type Storage Requirements

Answer 2

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).

Answer 3

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.

Answer 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.

Answer 5

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.