97

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

  1. What is the preferable data type for it in MySQL?
  2. What should be the Java data type in Bean (POJO) classes for that?
  3. How can I validate that datatype with javax validations/constraints for length and also only digit allowed?
Sarwar Sateer
  • 395
  • 3
  • 16
Vishal Zanzrukia
  • 4,902
  • 4
  • 38
  • 82

11 Answers11

112

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".
indivisible
  • 4,892
  • 4
  • 31
  • 50
  • You may want to not restrict the chars, either, if a human will read this. Different countries have different conventions, and there's a good chance that over reads a number in country C will recognize the convention that a person in country C used when entering it. – yshavit Jun 22 '14 at 17:33
  • 1
    I would personally restrict it to [0-9], space or a hyphen `-` only. let the user format as they like. At least in early development - number formatting would not be very high on my feature list. – indivisible Jun 22 '14 at 17:36
  • but suppose I want only digits..and also my system will be for only one country..then? – Vishal Zanzrukia Jun 22 '14 at 17:40
  • 1
    Does you country use area codes? Will people ever want to sore a number from abroad? These start with a zero (`0`) that would get lost if you were to try to save a number instead of text. It's one of the bigger UI mistakes that phone numbers have anything to do with numerics. It is much better to think of and tread them like addresses, it is closer to what they actually are and represent. – indivisible Jun 22 '14 at 17:45
  • In whatever input box your user is entering the phone number you should be able to monitor and restrict/reject any character entered that is not a number, a space or a hyphen. If you want to write automatic number masks then that is of course your prerogative. – indivisible Jun 22 '14 at 17:47
  • @VishalZanzrukia - you can do whatever you want. This site offers advice and other users rate that advice. You are free to take the advice or not. I would also add that the + sign is used frequently for international numbers. – CramerTV Mar 10 '15 at 22:05
  • +1 I appreciate your argument for using string/varchar. I've seen others recommend numerical datatypes, but it seems like even extensions or things like this would not be allowed (and would require another column...For simplicity I'm just going to use varchar...thanks for your post... – twknab Sep 25 '18 at 21:50
  • 1
    A agree that `VARCHAR` is best fit but not for the reasons mentioned in this answer. The question is about **storage** and not about formatting. Phone number should be converted into [E.164](https://en.wikipedia.org/wiki/E.164) before storage. There is no phone number exist that can start with `0` in this format, so if you remove starting `+` sign (that you can add after fetch from DB) then you can store it as `BIGINT`. The only downside of this - it is harder to search by partially entered phone numbers then. But the advantage - it takes up less bytes and MySQL works faster. – Ruslan Stelmachenko Nov 14 '19 at 21:32
  • 1
    "It is one of the bigger UI mistakes" what does storing data in sql has to do with the UI when you can store them as numbers (dialcode,number) while formating them as you wish in the client-side? why storing unnecessary chars `+` or `-` or even a leading `0` when you can efficiently use `smallint` for `dialcode` and `longint` for `number` thus minimizing space and improving select performance? – Ali Kleit Aug 15 '20 at 11:49
30

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.

Irshad Khan
  • 5,670
  • 2
  • 44
  • 39
21

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.

amucunguzi
  • 1,203
  • 1
  • 12
  • 16
00500005
  • 3,727
  • 3
  • 31
  • 38
5
  1. varchar

  2. String

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

Community
  • 1
  • 1
GreyBeardedGeek
  • 29,460
  • 2
  • 47
  • 67
3

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.

NcDreamy
  • 785
  • 6
  • 14
1

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.

damon
  • 65
  • 7
0

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.

bala raja
  • 1
0

best data type for phone number is ulong why ? varchar and string type is for character and when you want doing search on it sql will do that on bit on bit long is good but not best because conains negative numbers . so the ulong type is numbers model and not contains negative numbers.

Amir Mohamadi
  • 1
  • 1
    This answer seems to ask the same question as the original post. Integer types are not appropriate for storing phone numbers because some phone numbers include characters that are not numeric, and may have leading 0's. – Robert Rapplean Aug 08 '22 at 18:15
-2

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!

Suresh
  • 1,491
  • 2
  • 22
  • 27
-2
  1. String
  2. Varchar

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

duytung112
  • 1
  • 2
-4

In mysql: BIGINT. In java: Long.

Александр Аверьянов
  • 39
  • 4