Which is best data type for phone number in MySQL and what should Java type mapping for it be?

Vishal Zanzrukia picture Vishal Zanzrukia · Jun 22, 2014 · Viewed 233.3k times · Source

I am using MySQL with Spring JDBC template for my web application. I need to store phone number with only digits (10). I am little bit confused about data type using data type.

  1. What is preferable data type for it in MySQL?
  2. What should be Java data type in Bean (POJO) classes for that?
  3. How can I validate that datatype with javax validations/constrains for length and also only digit allowed?

Answer

indivisible picture indivisible · Jun 22, 2014

Strings & VARCHAR.

  • Do not try storing phone numbers as actual numbers. it will ruin the formatting, remove preceding 0s and other undesirable things.

  • You may, if you choose to, restrict user inputs to just numeric values but even in that case, keep your backing persisted data as characters/strings and not numbers.

  • Be aware of the wider world and how their number lengths and formatting differ before you try to implement any sort of length restrictions, validations or masks (eg XXX-XXXX-XX).

  • Non numeric characters can be valid in phone numbers. A prime example being + as a replacement for 00 at the start of an international number.

Edited in from conversation in comments:

  • It is one of the bigger UI mistakes that phone numbers have anything to do with numerics. It is much better to think of and treat them like addresses, it is closer to what they actually are and represent than phone "numbers".