在数据库中存储规范化电话号码是否有标准？

在数据库字段中存储电话号码的好的数据结构是什么？我正在寻找一些足够灵活的东西，以处理国际数字，也可以让数字的各个部分被有效地查询。

编辑: 只是为了澄清这里的用例: 我当前将数字存储在一个单一的 varchar 字段中，并且当客户输入它们时，我将它们保留下来。然后，当代码需要这个数字时，我将其标准化。问题是，如果我想查询几百万行以找到匹配的电话号码，它涉及一个函数，如

where dbo.f_normalizenum(num1) = dbo.f_normalizenum(num2)

这是非常低效的。此外，当只有一个 varchar 字段时，查找诸如区号之类的内容的查询变得非常棘手。

[编辑]

人们在这里提出了很多好的建议，谢谢！作为一个更新，这里是我现在正在做的: 我仍然保存数字完全一样，他们输入，在一个 varchar 字段，但不是规范化的东西在查询时，我有一个触发器，所有的工作作为记录插入或更新。因此，对于需要查询的任何部分，我都有 int 或 bigint，并且对这些字段进行索引以使查询运行得更快。

61949 次浏览
小开

Perhaps storing the phone number sections in different columns, allowing for blank or null entries?

小开

I think free text (maybe varchar(25)) is the most widely used standard. This will allow for any format, either domestic or international.

I guess the main driving factor may be how exactly you're querying these numbers and what you're doing with them.

小开

KISS - I'm getting tired of many of the US web sites. They have some cleverly written code to validate postal codes and phone numbers. When I type my perfectly valid Norwegian contact info I find that quite often it gets rejected.

Leave it a string, unless you have some specific need for something more advanced.

小开

What about storing a freetext column that shows a user-friendly version of the telephone number, then a normalised version that removes spaces, brackets and expands '+'. For example:

User friendly: +44 (0)181 4642542

Normalized: 00441814642542

小开

I find most web forms correctly allow for the country code, area code, then the remaining 7 digits but almost always forget to allow entry of an extension. This almost always ends up making me utter angry words, since at work we don't have a receptionist, and my ext.# is needed to reach me.

小开

I personally like the idea of storing a normalized varchar phone number (e.g. 9991234567) then, of course, formatting that phone number inline as you display it.

This way all the data in your database is "clean" and free of formatting

小开

I find most web forms correctly allow for the country code, area code, then the remaining 7 digits but almost always forget to allow entry of an extension. This almost always ends up making me utter angry words, since at work we don't have a receptionist, and my ext.# is needed to reach me.

I would have to check, but I think our DB schema is similar. We hold a country code (it might default to the US, not sure), area code, 7 digits, and extension.

小开
最佳答案

First, beyond the country code, there is no real standard. About the best you can do is recognize, by the country code, which nation a particular phone number belongs to and deal with the rest of the number according to that nation's format.

Generally, however, phone equipment and such is standardized so you can almost always break a given phone number into the following components

  • C Country code 1-10 digits (right now 4 or less, but that may change)
  • A Area code (Province/state/region) code 0-10 digits (may actually want a region field and an area field separately, rather than one area code)
  • E Exchange (prefix, or switch) code 0-10 digits
  • L Line number 1-10 digits

With this method you can potentially separate numbers such that you can find, for instance, people that might be close to each other because they have the same country, area, and exchange codes. With cell phones that is no longer something you can count on though.

Further, inside each country there are differing standards. You can always depend on a (AAA) EEE-LLLL in the US, but in another country you may have exchanges in the cities (AAA) EE-LLL, and simply line numbers in the rural areas (AAA) LLLL. You will have to start at the top in a tree of some form, and format them as you have information. For example, country code 0 has a known format for the rest of the number, but for country code 5432 you might need to examine the area code before you understand the rest of the number.

You may also want to handle vanity numbers such as (800) Lucky-Guy, which requires recognizing that, if it's a US number, there's one too many digits (and you may need to full representation for advertising or other purposes) and that in the US the letters map to the numbers differently than in Germany.

You may also want to store the entire number separately as a text field (with internationalization) so you can go back later and re-parse numbers as things change, or as a backup in case someone submits a bad method to parse a particular country's format and loses information.

小开

Look up E.164. Basically, you store the phone number as a code starting with the country prefix and an optional pbx suffix. Display is then a localization issue. Validation can also be done, but it's also a localization issue (based on the country prefix).

For example, +12125551212+202 would be formatted in the en_US locale as (212) 555-1212 x202. It would have a different format in en_GB or de_DE.

There is quite a bit of info out there about ITU-T E.164, but it's pretty cryptic.

小开

I would go for a freetext field and a field that contains a purely numeric version of the phone number. I would leave the representation of the phone number to the user and use the normalized field specifically for phone number comparisons in TAPI-based applications or when trying to find double entries in a phone directory. Of course it does not hurt providing the user with an entry scheme that adds intelligence like separate fields for country code (if necessary), area code, base number and extension.

小开

Ok, so based on the info on this page, here is a start on an international phone number validator:

function validatePhone(phoneNumber) {
var valid = true;
var stripped = phoneNumber.replace(/[\(\)\.\-\ \+\x]/g, '');


if(phoneNumber == ""){
valid = false;
}else if (isNaN(parseInt(stripped))) {
valid = false;
}else if (stripped.length > 40) {
valid = false;
}
return valid;
}

Loosely based on a script from this page: http://www.webcheatsheet.com/javascript/form_validation.php

小开

The Wikipedia page on E.164 should tell you everything you need to know.

小开

I've used 3 different ways to store phone numbers depending on the usage requirements.

  1. If the number is being stored just for human retrieval and won't be used for searching its stored in a string type field exactly as the user entered it.
  2. If the field is going to be searched on then any extra characters, such as +, spaces and brackets etc are removed and the remaining number stored in a string type field.
  3. Finally, if the phone number is going to be used by a computer/phone application, then in this case it would need to be entered and stored as a valid phone number usable by the system, this option of course, being the hardest to code for.
小开

Here's my proposed structure, I'd appreciate feedback:

The phone database field should be a varchar(42) with the following format:

CountryCode - Number x Extension

So, for example, in the US, we could have:

1-2125551234x1234

This would represent a US number (country code 1) with area-code/number (212) 555 1234 and extension 1234.

Separating out the country code with a dash makes the country code clear to someone who is perusing the data. This is not strictly necessary because country codes are "prefix codes" (you can read them left to right and you will always be able to unambiguously determine the country). But, since country codes have varying lengths (between 1 and 4 characters at the moment) you can't easily tell at a glance the country code unless you use some sort of separator.

I use an "x" to separate the extension because otherwise it really wouldn't be possible (in many cases) to figure out which was the number and which was the extension.

In this way you can store the entire number, including country code and extension, in a single database field, that you can then use to speed up your queries, instead of joining on a user-defined function as you have been painfully doing so far.

Why did I pick a varchar(42)? Well, first off, international phone numbers will be of varied lengths, hence the "var". I am storing a dash and an "x", so that explains the "char", and anyway, you won't be doing integer arithmetic on the phone numbers (I guess) so it makes little sense to try to use a numeric type. As for the length of 42, I used the maximum possible length of all the fields added up, based on Adam Davis' answer, and added 2 for the dash and the 'x".

小开

Where are you getting the phone numbers from? If you're getting them from part of the phone network, you'll get a string of digits and a number type and plan, eg

441234567890 type/plan 0x11 (which means international E.164)

In most cases the best thing to do is to store all of these as they are, and normalise for display, though storing normalised numbers can be useful if you want to use them as a unique key or similar.

小开

User friendly: +44 (0)181 464 2542 normalised: 00441814642542

The (0) is not valid in the international format. See the ITU-T E.123 standard.

The "normalised" format would not be useful to US readers as they use 011 for international access.

小开

The standard for formatting numbers is e.164, You should always store numbers in this format. You should never allow the extension number in the same field with the phone number, those should be stored separately. As for numeric vs alphanumeric, It depends on what you're going to be doing with that data.

小开

Storage

Store phones in RFC 3966 (like +1-202-555-0252, +1-202-555-7166;ext=22). The main differences from E.164 are

  • No limit on the length
  • Support of extensions

To optimise speed of fetching the data, also store the phone number in the National/International format, in addition to the RFC 3966 field.

Don't store the country code in a separate field unless you have a serious reason for that. Why? Because you shouldn't ask for the country code on the UI.

Mostly, people enter the phones as they hear them. E.g. if the local format starts with 0 or 8, it'd be annoying for the user to do a transformation on the fly (like, "OK, don't type '0', choose the country and type the rest of what the person said in this field").

Parsing

Google has your back here. Their libphonenumber library can validate and parse any phone number. There are ports to almost any language.

So let the user just enter "0449053501" or "04 4905 3501" or "(04) 4905 3501". The tool will figure out the rest for you.

See the official demo, to get a feeling of how much does it help.


提交答案