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.
我正在使用MySQL和Spring JDBC模板为我的web应用程序。我需要只存储10位数字的电话号码。我对使用数据类型的数据类型有点困惑。
- What is preferable data type for it in MySQL?
- 在MySQL中，什么是更好的数据类型?
- What should be Java data type in Bean (POJO) classes for that?
- Bean (POJO)类中的Java数据类型应该是什么?
- How can I validate that datatype with javax validations/constrains for length and also only digit allowed?
- 如何使用javax验证/约束来验证数据类型的长度，并且只允许数字?
7 个解决方案
#1
24
Strings & VARCHAR.
字符串和VARCHAR。
-
Do not try storing phone numbers as actual numbers. it will ruin the formatting, remove preceding
0s and other undesirable things.不要尝试将电话号码存储为实际的号码。它将破坏格式，删除前面的0和其他不需要的东西。
-
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).
在尝试实现任何长度限制、验证或掩码(如xxxx - xxxx - xx)之前，要了解更广泛的世界以及它们的数字长度和格式的不同。
-
Non numeric characters can be valid in phone numbers. A prime example being
+as a replacement for00at the start of an international number.非数字字符可以在电话号码中有效。一个最好的例子是在一个国际号码的开头用+替换00。
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".
- 这是一个较大的UI错误，电话号码与数字有关。更好的办法是把它们想象成地址，而不是电话“号码”。
#2
6
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).
在MySQL -> INT(10)中，不是指10位数，而是指显示宽度为10位数的整数。MySQL中的INT值的最大值是2147483647(如果没有签名，则是4294967295)。
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).
您可以使用BIGINT而不是INT来将其存储为数字。使用BIGINT可以在VARCHAR(10)上为每一行节省3字节。
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.
如果你想单独存储“国家+地区+数字”。试着用一个VARCHAR(20)。这使您能够正确地存储国际电话号码，如果需要的话。
#3
2
- varchar
- varchar
-
String
字符串
-
A simple regex. See: How to check if a string contains only digits in Java. Use javax.constraints.Pattern.
一个简单的正则表达式。参见:如何检查字符串是否只包含Java中的数字。使用javax.constraints.Pattern。
#4
2
Consider using the E.164 format. For full international support, you'd need a VARCHAR of 15 digits.
考虑使用E.164格式。要获得全面的国际支持，您需要15位数字的VARCHAR。
See Twilio's recommendation for more information on localization of phone numbers.
有关电话号码本地化的更多信息，请参阅Twilio的建议。
#5
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.
您可以使用var-char、String和int，这取决于您，如果您只使用带有移动号码的国家代码，而不使用int，如果您对数字使用特殊的formate而不使用String或var-char类型，如果您使用var-char，那么必须对数字的大小进行defile，并对用户进行限制。
#6
0
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.
长度大约为15-20的VARCHAR就足够了，是数据库的最佳选择。因为您可能需要各种连字符和加号连同您的电话号码。
#7
0
In mysql: BIGINT. In java: Long.
在mysql:BIGINT。在java:长。
#1
24
Strings & VARCHAR.
字符串和VARCHAR。
-
Do not try storing phone numbers as actual numbers. it will ruin the formatting, remove preceding
0s and other undesirable things.不要尝试将电话号码存储为实际的号码。它将破坏格式，删除前面的0和其他不需要的东西。
-
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).
在尝试实现任何长度限制、验证或掩码(如xxxx - xxxx - xx)之前，要了解更广泛的世界以及它们的数字长度和格式的不同。
-
Non numeric characters can be valid in phone numbers. A prime example being
+as a replacement for00at the start of an international number.非数字字符可以在电话号码中有效。一个最好的例子是在一个国际号码的开头用+替换00。
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".
- 这是一个较大的UI错误，电话号码与数字有关。更好的办法是把它们想象成地址，而不是电话“号码”。
#2
6
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).
在MySQL -> INT(10)中，不是指10位数，而是指显示宽度为10位数的整数。MySQL中的INT值的最大值是2147483647(如果没有签名，则是4294967295)。
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).
您可以使用BIGINT而不是INT来将其存储为数字。使用BIGINT可以在VARCHAR(10)上为每一行节省3字节。
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.
如果你想单独存储“国家+地区+数字”。试着用一个VARCHAR(20)。这使您能够正确地存储国际电话号码，如果需要的话。
#3
2
- varchar
- varchar
-
String
字符串
-
A simple regex. See: How to check if a string contains only digits in Java. Use javax.constraints.Pattern.
一个简单的正则表达式。参见:如何检查字符串是否只包含Java中的数字。使用javax.constraints.Pattern。
#4
2
Consider using the E.164 format. For full international support, you'd need a VARCHAR of 15 digits.
考虑使用E.164格式。要获得全面的国际支持，您需要15位数字的VARCHAR。
See Twilio's recommendation for more information on localization of phone numbers.
有关电话号码本地化的更多信息，请参阅Twilio的建议。
#5
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.
您可以使用var-char、String和int，这取决于您，如果您只使用带有移动号码的国家代码，而不使用int，如果您对数字使用特殊的formate而不使用String或var-char类型，如果您使用var-char，那么必须对数字的大小进行defile，并对用户进行限制。
#6
0
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.
长度大约为15-20的VARCHAR就足够了，是数据库的最佳选择。因为您可能需要各种连字符和加号连同您的电话号码。
#7
0
In mysql: BIGINT. In java: Long.
在mysql:BIGINT。在java:长。