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

Question

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    What is preferable data type for it in MySQL  What should be Java data type in Bean  POJO  classes for that  How can I validate that datatype with javax validations constrains for length and also only digit allowed

User · Answer

Strings  amp  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

User · Answer

In mysql  BIGINT  In java  Long

User · Answer

My requirement is to display 10 digit phone number in the jsp  So here s the setup for me  MySQL  numeric 10   Java Side    NumberFormat pattern          private long mobileNumber    and it worked

User · Answer

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

User · Answer

VARCHAR with probably 15-20 length would be sufficient and would be the best option for the database  Since you would probably require various hyphens and plus signs along with your phone numbers

User · Answer

varchar String A simple regex  See  How to check if a string contains only digits in Java  Use javax constraints Pattern

User · Answer

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

User · Answer

It s all based on your requirement  if you are developing a small scale app and covers only specific region  target audience   you can choose BIGINT to store only numbers since VARCHAR consumes more byte than BIGINT   having optimal memory usage design matters    but if you are developing a large scale app and targets global users and you have enough database capabilities to store data  you can definitely choose VARCHAR

User · Answer

you can use var-char String and int  it depends on you  if you use only country code with mobile number than  you can use int if you use special formate for number than use String or var-char type   if you use var-char then must defile size of number and restrict from user

User · Answer

String Varchar    This is my opinion for my database as recommended by my mentor

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

Examples related to java

Examples related to mysql

Examples related to phone-number

Examples related to digit