CODEWITHSUNDEEP
mysqlsqldatatypes
Citricguy
Citricguy

Reputation: 421

When to use varchar for numeric data in InnoDB MySQL tables

When using MySQL, are there times that it would make sense to use a textual field (varchar, text, etc) instead of a numeric field (int, float, etc.) even if all data to be stored is numeric?

For example would it be better, faster or more efficient to store '5 digit zip code' data in a 5 char 'varchar' field rather than in an explicitly numeric field?

If yes, how can I determine when it is best to use a text field for numeric data?

I'm using InnoDB if relevant.

EDIT: I'm using a zipcode as an example above, but 'in general' when should choose a textual datatype over a numeric datatype when all data to be stored is numeric data.

Upvotes: 1

Views: 399

Answers (2)

Tudor Constantin
Tudor Constantin

Reputation: 26861

Ed's answer pretty much covers everything, as a general rule:

  • don't store numeric values in varchar columns - unless there are chances that other characters will have to be stored (examples are zip codes and phone numbers)

When you need computations/comparisons on the stored values, always use numerical data types (in string comparison 2 is bigger than 10)

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 59987

Depends on how you are using the data. For Zip codes I would use a VARCHAR - simply because it is a part of an address and will not be using it for calculations. Also you may wish to include other countries in the future (in the UK we have post codes that are alpha numeric).

Upvotes: 4

Related Questions