CODEWITHSUNDEEP
mysql
Yeti
Yeti

Reputation: 5818

storing numbers as varchar

Is it ok to store numbers as varchar?

What's the difference between

int 123456789012 and varchar 123456789012 ?

Upvotes: 21

Views: 40233

Answers (4)

Juha Syrjälä
Juha Syrjälä

Reputation: 34271

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.

Upvotes: 4

robertokl
robertokl

Reputation: 1909

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.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

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.

Upvotes: 46

theDmi
theDmi

Reputation: 18034

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

Upvotes: 1

Related Questions