如何在 MySQL 数据库中存储电话号码？

How to store phone numbers on MySQL databases?

提问by StackOverflowNewbie

Possible Duplicate:
mysql datatype for telephne number and address

可能重复：
电话号码和地址的 mysql 数据类型

Any suggestions on best practice to store telephone numbers in a DB? Consider a US phone number:

关于在数据库中存储电话号码的最佳实践的任何建议？考虑一个美国电话号码：

• 555 555 1212
• 555-555-1212
• [phone]
• 5555551212
• 1-555-555-1212
• 1 [phone]
• and so on ...

• 555 555 1212
• 555-555-1212
• [phone]
• 5555551212
• 1-555-555-1212
• 1 [phone]
• 等等 ...

Should I remove formatting and store only numbers? Should I just use one field -- or split them up into: country code, area code, phone number, etc.? Suggestions?

我应该删除格式并只存储数字吗？我应该只使用一个字段——还是将它们拆分为：国家/地区代码、区号、电话号码等？建议？

回答by gbn

• All as varchar (they aren't numbers but "collections of digits")
• Country + area + number separately
• Not all countries have area code (eg Malta where I am)
• Some countries drop the leading zero from the area code when dialling internal (eg UK)
• Format in the client code

• 全部为 varchar（它们不是数字而是“数字集合”）
• 国家+地区+号码分开
• 并非所有国家/地区都有区号（例如我所在的马耳他）
• 某些国家/地区在拨打内部电话时会去掉区号的前导零（例如英国）
• 客户端代码中的格式

回答by Andreas Wederbrand

You should never store values with format. Formatting should be done in the view depending on user preferences.

你永远不应该用格式存储值。应根据用户偏好在视图中进行格式化。

Searching for phone nunbers with mixed formatting is near impossible.

搜索混合格式的电话号码几乎是不可能的。

For this case I would split into fields and store as integer. Numbers are faster than texts and splitting them and putting index on them makes all kind of queries ran fast.

对于这种情况，我将拆分为字段并存储为整数。数字比文本快，拆分它们并在它们上面放置索引可以使所有类型的查询运行得很快。

Leading 0 could be a problem but probably not. In Sweden all area codes start with 0 and that is removed if also a country code is dialed. But the 0 isn't really a part of the number, it's a indicator used to tell that I'm adding an area code. Same for country code, you add 00 to say that you use a county code.

领先 0 可能是一个问题，但可能不是。在瑞典，所有区号都以 0 开头，如果拨打国家/地区代码，则该区号将被删除。但 0 并不是数字的一部分，它是一个指示符，用于告诉我正在添加区号。国家/地区代码相同，您添加 00 表示您使用县代码。

Leading 0 shouldn't be stored, they should be added when needed. Say you store 00 in the database and you use a server that only works with + they you have to replace 00 with + for that application.

前导 0 不应存储，应在需要时添加。假设您将 00 存储在数据库中，并且您使用的服务器仅适用于 + 他们必须为该应用程序将 00 替换为 +。

So, store numbers as numbers.

因此，将数字存储为数字。

回答by Steve Rukuts

I suggest storing the numbers in a varchar without formatting. Then you can just reformat the numbers on the client side appropriately. Some cultures prefer to have phone numbers written differently; in France, they write phone numbers like 01-22-33-44-55.

我建议将数字存储在 varchar 中而不进行格式化。然后你可以适当地重新格式化客户端的数字。有些文化更喜欢用不同的方式写电话号码；在法国，他们写电话号码，如 01-22-33-44-55。

You might also consider storing another field for the country that the phone number is for, because this can be difficult to figure out based on the number you are looking at. The UK uses 11 digit long numbers, some African countries use 7 digit long numbers.

您还可以考虑为电话号码所在的国家/地区存储另一个字段，因为根据您查看的号码可能难以确定。英国使用 11 位长号码，一些非洲国家使用 7 位长号码。

That said, I used to work for a UK phone company, and we stored phone numbers in our database based on if they were UK or international. So, a UK phone number would be 02081234123 and an international one would be 001800300300.

也就是说，我曾经在一家英国电话公司工作，我们根据他们是英国还是国际电话号码将电话号码存储在我们的数据库中。因此，英国电话号码是 02081234123，国际电话号码是 001800300300。

回答by SurferJoe

varchar, Don't store separating characters you may want to format the phone numbers differently for different uses. so store [phone] as 6191234567 I work with phone directory data and have found this to be the best practice.

varchar，不要存储分隔字符，您可能希望针对不同用途对电话号码进行不同的格式化。所以将 [phone] 存储为 6191234567 我使用电话目录数据并发现这是最佳实践。

回答by apokryfos

I would suggest a varchar for the phone number (since phone numbers are known to have leading 0s which are important to keep) and having the phone number in two fields:

我建议为电话号码使用 varchar（因为已知电话号码有前导 0，这是很重要的）并在两个字段中包含电话号码：

Country Code and phone number i.e. for 004477789787you could store CountryCode=44 and phone number=77789787

国家代码和电话号码即004477789787您可以存储 CountryCode=44 和电话号码=77789787

however it could be very application specific. If for example you will only store US numbers and want to keep the capability of quickly performing queries like "Get all the numbers from a specific area" then you can further split the phone number field (and drop the country code field as it would be redundant)

但是它可能是非常特定于应用程序的。例如，如果您将只存储美国号码并希望保持快速执行查询的能力，例如“从特定区域获取所有号码”，那么您可以进一步拆分电话号码字段（并删除国家代码字段，原样多余的）

I don't think there is a general right and wrong way to do this. It really depends on the demands.

我不认为有一个普遍的正确和错误的方式来做到这一点。这真的取决于需求。

回答by Ahmed Masud

Suggest that you store the number as an extended alphanumeric made up of characters that you wish to accept and store it in a varchar(32) or something like that. Strip out all the spaces , dashes, etc. Put the FORMATTING of the phone number into a separate field (possibly gleaned from the locale preferences) If you wish to support extensions, you should add them in a separate field;

建议您将数字存储为由您希望接受的字符组成的扩展字母数字，并将其存储在 varchar(32) 或类似的东西中。去掉所有的空格、破折号等。将电话号码的格式放在一个单独的字段中（可能从区域设置首选项中收集）如果您希望支持扩展，您应该将它们添加到一个单独的字段中；

回答by Yahia

I would recommend storing these as numbers in columns of type varchar - one column per "field" (like contry code etc.).

我建议将这些作为数字存储在 varchar 类型的列中 - 每个“字段”一列（如国家代码等）。

The format should be applied when you interact with a user... that makes it easier to account for format changes for example and will help esp. when your application goes international...

当您与用户交互时应该应用该格式......例如，这可以更容易地说明格式更改，并且将有助于特别。当您的应用程序走向国际时...

回答by Elias Hossain

Form my point of view, below is my suggestions:

从我的角度来看，以下是我的建议：

1. Store phone number into a single field as varchar and if you need to split then after retrieve split accordingly.
2. If you store as number then preceding 0 will truncate, so always store as varchar
3. Validate users phone number before inserting into your table.

1. 将电话号码作为 varchar 存储到单个字段中，如果您需要拆分，则在检索后相应地拆分。
2. 如果您存储为数字，则前面的 0 将被截断，因此始终存储为 varchar
3. 在插入表格之前验证用户电话号码。

回答by mintobit

I would definitely split them. It would be easy to sort the numbers by area code and contry code. But even if you're not going to split, just insert the numbers into the DB in one certain format. e.g. 1-555-555-1212 Your client side will be thankfull for not making it reformat your numbers.

我肯定会把他们分开。按区号和国家代码对数字进行排序会很容易。但即使您不打算拆分，也只需将数字以某种特定格式插入数据库即可。例如 1-555-555-1212 您的客户端会感谢您没有重新格式化您的号码。

回答by Sudhir Bastakoti

You can use varchar for storing phone numbers, so you need not remove the formatting

您可以使用 varchar 来存储电话号码，因此您无需删除格式