在 MySQL 中，哪种数据类型是电话号码的最佳数据类型，Java 类型映射应该是什么？

我将 MySQL 与 SpringJDBC 模板一起用于我的 Web 应用程序。我需要存储电话号码只有数字(10)。我对使用数据类型的数据类型有点困惑。

  1. 在 MySQL 中哪种数据类型更适合它？
  2. Bean (POJO)类中的 Java 数据类型应该是什么？
  3. 如何使用 javax 长度验证/约束以及只允许数字验证该数据类型？
337171 次浏览
小开

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".
小开
  1. varchar

  2. String

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

小开

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.

小开

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.

小开

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.

小开

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.

小开

In mysql: BIGINT. In java: Long.

小开

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!

小开

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.

小开
  1. String
  2. Varchar

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

小开

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.


提交答案