MySQL 电话号码和地址的mysql数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1846254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql datatype for telephone number and address
提问by SUN Jiangong
I want to input telephone number in a form, including country code, extension
我想在表格中输入电话号码，包括国家代码、分机号
create table if not exists employee( `
country_code_tel int(11),
tel_number int(10),
extension int(10),
mobile bigint(20)
);
If tel_number is larger than 15 bit, which datatype can I use, I'd better use Bigint(20)?
如果 tel_number 大于 15 位，我可以使用哪种数据类型，我最好使用Bigint(20)?
create table address(
address varchar(255),
city varchar(255),
country varchar(255),
post_code int(11)
);
For example, if I have a country code for Canada I can use +2 or 002. Which is better for processing?
例如，如果我有加拿大的国家代码，我可以使用 +2 或 002。哪个更适合处理？
Thanks for your advice.
谢谢你的建议。
回答by o.k.w
Well, personally I do not use numeric datatype to store phone numbers or related info.
好吧，我个人不使用数字数据类型来存储电话号码或相关信息。
How do you store a number say 001234567? It'll end up as 1234567, losing the leading zeros.
你如何存储一个数字，比如 001234567？它将最终为 1234567，丢失前导零。
Of course you can always left-pad it up, but that's provided you know exactly how many digits the number should be.
当然，您始终可以左填充它，但前提是您确切知道数字应该是多少位。
This doesn't answer your entire post,
Just my 2 cents
这不能回答你的整个帖子，
只是我的 2 美分
回答by Vincent Ramdhanie
Actually you can use a varchar for a telephone number. You do not need an int because you are not going to perform arithmetic on the numbers.
实际上，您可以使用 varchar 作为电话号码。您不需要 int 因为您不会对数字执行算术运算。
回答by Dan Kelly
Store them as two fields for phone numbers - a "number" and a "mask" as TinyTexttypes which do not need more than 255 items.
并将其作为对电话号码的两个领域-一个“数字”和“面具”的TinyText类型不需要超过255项。
Before we store the files we parse the phone number to get the formatting that has been used and that creates the mask, we then store the number a digits only e.g.
在我们存储文件之前，我们解析电话号码以获取已使用的格式并创建掩码，然后我们仅将数字存储为数字，例如
Input: (0123) 456 7890
Number: 01234567890
Mask: (nnnn)_nnn_nnnn
输入： (0123) 456 7890
数字：01234567890
掩码： (nnnn)_nnn_nnnn
Theoretically this allows us to perform comparison searches on the Number field such as getting all phone numbers that begin with a specific area code, without having to worry how it was input by the users
理论上，这允许我们在 Number 字段上执行比较搜索，例如获取以特定区号开头的所有电话号码，而不必担心用户是如何输入的
回答by Curon
I usually store phone numbers as a BIGINT in E164 format.
我通常将电话号码存储为 E164 格式的 BIGINT。
E164 never start with a 0, with the first few digits being the country code.
E164 从不以 0 开头，前几位数字是国家/地区代码。
+441234567890
+44 (0)1234 567890
01234 567890
etc. would be stored as 441234567890.
等将存储为441234567890.
回答by kon
i would use a varchar for telephone numbers. that way you can also store + and (), which is sometimes seen in tel numbers (as you mentioned yourself). and you don't have to worry about using up all bits in integers.
我会使用 varchar 作为电话号码。这样您还可以存储 + 和 ()，这有时会在电话号码中看到（正如您自己提到的）。而且您不必担心用完整数中的所有位。
回答by nfechner
I'm not sure whether it's a good idea to use integers at all. Some numbers might contain special characters (# as part of the extension for example) which you should be able to handle too. So I would suggest using varchars instead.
我不确定使用整数是否是个好主意。某些数字可能包含特殊字符（例如，# 作为扩展名的一部分），您也应该能够处理这些字符。所以我建议改用 varchars。
回答by 00500005
Consider normalizing to E.164format. For full international support, you'd need a VARCHAR of 15 digits.
考虑标准化为E.164格式。要获得全面的国际支持，您需要一个 15 位的 VARCHAR。
See Twilio's recommendationfor more information on localization of phone numbers.
有关电话号码本地化的更多信息，请参阅Twilio 的建议。
回答by Stefan Rogin
If storing less then 1 mil records, and high performance is not an issue go for varchar(20)/char(20) otherwise I've found that for storing even 100 milion global business phones or personal phones, int is best. Reason : smaller key -> higher read/write speed, also formatting can allow for duplicates.
如果存储少于 100 万条记录，并且高性能不是问题，请使用 varchar(20)/char(20) 否则我发现即使要存储 1 亿个全球商务电话或个人电话，int 是最好的。原因：较小的键 -> 更高的读/写速度，格式化也可以允许重复。
1 phone in char(20) = 20 bytes vs 8 bytes bigint(or 10 vs 4 bytes intfor local phones, up to 9 digits) , less entries can enter the index block => more blocks => more searches, see thisfor more info (writen for Mysql but it should be true for other Relational Databases).
char(20) 中的 1 个电话 = 20 字节 vs 8 字节bigint（或 10 对 4 字节int本地电话，最多 9 位），更少的条目可以进入索引块 => 更多块 => 更多搜索，请参阅此了解更多信息（为 Mysql 编写，但对于其他关系数据库应该是这样）。
Here is an example of phone tables:
以下是电话表的示例：
CREATE TABLE `phoneNrs` (
`internationalTelNr` bigint(20) unsigned NOT NULL COMMENT 'full number, no leading 00 or +, up to 19 digits, E164 format',
`format` varchar(40) NOT NULL COMMENT 'ex: (+NN) NNN NNN NNN, optional',
PRIMARY KEY (`internationalTelNr`)
)
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin
or with processing/splitting before insert (2+2+4+1 = 9 bytes)
或在插入前进行处理/拆分（2+2+4+1 = 9 个字节）
CREATE TABLE `phoneNrs` (
`countryPrefix` SMALLINT unsigned NOT NULL COMMENT 'countryCode with no leading 00 or +, up to 4 digits',
`countyPrefix` SMALLINT unsigned NOT NULL COMMENT 'countyCode with no leading 0, could be missing for short number format, up to 4 digits',
`localTelNr` int unsigned NOT NULL COMMENT 'local number, up to 9 digits',
`localLeadingZeros` tinyint unsigned NOT NULL COMMENT 'used to reconstruct leading 0, IF(localLeadingZeros>0;LPAD(localTelNr,localLeadingZeros+LENGTH(localTelNr),'0');localTelNr)',
PRIMARY KEY (`countryPrefix`,`countyPrefix`,`localLeadingZeros`,`localTelNr`) -- ordered for fast inserts
)
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin
;
Also "the phone number is not a number", in my opinion is relative to the type of phone numbers. If we're talking of an internal mobile phoneBook, then strings are fine, as the user may wish to store GSM Hash Codes. If storing E164phones, bigint is the best option.
还有“电话号码不是数字”，在我看来是相对于电话号码的类型。如果我们谈论的是内部移动电话簿，那么字符串就可以了，因为用户可能希望存储GSM 哈希码。如果存放E164手机，bigint 是最好的选择。
回答by Irshad Khan
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).
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 个字节。
To Store "Country + area + number separately". You can try using a VARCHAR(20), this allows you the ability to store international phone numbers properly, should that need arise.
分别存储“国家+地区+数字”。您可以尝试使用 VARCHAR(20)，这使您能够在需要时正确存储国际电话号码。
回答by abhinawbharat04
varchar or text should be the best datatypes for storing mobile numbers I guess.
我猜 varchar 或 text 应该是存储手机号码的最佳数据类型。
