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

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

In MySQL -> INT(10) does not mean a 10-digit number, it means an integer with a display width of 10 digits. The maximum value for an INT in MySQL is 2147483647 (or 4294967295 if unsigned).

You can use a BIGINT instead of INT to store it as a numeric. Using BIGINT will save you 3 bytes per row over VARCHAR(10).

If you want to Store "Country + area + number separately". Try using a VARCHAR(20). This allows you the ability to store international phone numbers properly, should that need arise.

Consider using the E.164 format. For full international support, you'd need a VARCHAR of 15 digits.

See Twilio's recommendation for more information on localization of phone numbers.

1. varchar

2. String

3. A simple regex. See: How to check if a string contains only digits in Java. Use javax.constraints.Pattern.