在数据库中存储标准化电话号码有标准吗？ [英] Is there a standard for storing normalized phone numbers in a database?

问题描述

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

What is a good data structure for storing phone numbers in database fields? I'm looking for something that is flexible enough to handle international numbers, and also something that allows the various parts of the number to be queried efficiently.

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

Just to clarify the use case here: I currently store numbers in a single varchar field, and I leave them just as the customer entered them. Then, when the number is needed by code, I normalize it. The problem is that if I want to query a few million rows to find matching phone numbers, it involves a function, like

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

这是非常低效的。

人们在这里提出了很多好的建议，谢谢！作为更新，这里是我现在正在做的：我仍然存储数字，正如他们输入，在varchar字段中，但不是标准化的事情在查询时，我有一个触发器，所有工作，因为记录插入或更新。所以我需要查询的任何部分的int或bigints，并且这些字段被索引以使查询运行更快。

People have made lots of good suggestions here, thanks! As an update, here is what I'm doing now: I still store numbers exactly as they were entered, in a varchar field, but instead of normalizing things at query time, I have a trigger that does all that work as records are inserted or updated. So I have ints or bigints for any parts that I need to query, and those fields are indexed to make queries run faster.

推荐答案

首先，除了国家代码，没有真正的标准。关于最好的，你可以做的是根据国家代码识别哪个国家的特定电话号码属于，并根据该国家的格式处理其余的号码。

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.

• C国家/地区代码1-10位数字（现在为4或更少，但可能会更改）


• 区号（省/州/地区）代码0-10个数字


• E Exchange（前缀或开关）代码0-10个数字


• L行号1 -10数字

• 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.

此外，在每个国家都有不同的标准。在美国，您可以始终依赖于（AAA）EEE-LLLL，但在第三世界国家，您可以在城市（AAA）EE-LLL中进行交换，而在农村地区（AAA）LLLL中只需要行号。你将不得不在某种形式的树的顶部开始，并且在你有信息时对它们进行格式化。例如，国家代码0对于其余号码具有已知的格式，但是对于国家代码5432，您可能需要在了解该号码的其余部分之前检查区号。

Further, inside each country there are differing standards. You can always depend on a (AAA) EEE-LLLL in the US, but in a 3rd world 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.

您可能还需要处理 vanity 数字，例如（800）Lucky-Guy 如果是美国号码，有一个太多的数字（您可能需要完全表示广告或其他目的），而在美国，字母映射到的数字与德国不同。

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.

这篇关于在数据库中存储标准化电话号码有标准吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！