Search code examples
mysqlsqldatatypes

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.

Solution

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