努力与电话号码的MySQL数据库 [英] Struggling with a MySQL database of phone numbers

问题描述

我的应用程序想要在mysql数据库中存储国际电话号码的列表。然后应用程序将需要查询数据库并搜索特定的数字。听起来很简单，但它实际上是一个巨大的问题。

My application wants to store a list of international phone number in a mysql database. Then the application would need to query the database and search for a specific number. Sounds simple but it's actually a huge problem.

由于用户可以使用不同的格式搜索该号码，因此我们必须每次都对数据库进行完整扫描。

Because users can search for that number in a different format we'll have to do a full scan to the database each time.

例如。我们可能将数字17162225555存储在数据库中（以及另外500万个条目）。现在用户来了，尝试使用7162225555搜索。另一个用户可能尝试用2225555等等搜索。因此，换句话说，数据库必须发出SQL查询使用like％number％，这将导致一个全扫描。

For example. We might have the number 17162225555 stored in the database (along with another 5 million entries). Now the user comes along and attempt to search using 7162225555. Another user might try to serach with 2225555. etc etc. So in other words, the database have to issue the SQL query using a "like %number%" which would result in a full scan.

我们应如何设计此应用程序？有什么办法打扰Mysql处理这个更好吗？或者我们不应该使用SQL吗？

How should we design this application? Is there some way to tweat the Mysql to handle this better? Or should we not use SQL at all?

PS。我们有数百万个条目，每秒搜索请求数量为10个。

PS. We have millions of entries, and 10s of these search request per second.

推荐答案

这很奇怪，这个问题我自己多次，在过去的15年中，通常会提出将区域代码，国家代码和数字分隔成单独的字段等的结构。但是，虽然阅读您的问题另一个解决方案只是弹出我的头，它需要一个单独的字段虽然这样可能不适合你。

This is very weird, I've struggled with this issue myself many times, over the last 15 years and generally come up with structures that separate area codes, country codes and number into separate fields etc. But whilst reading your question another solution just popped into my head, it does require a separate field though so may not be appropriate for you.

您可以有一个单独的字段名为reverse_phone_number，这是由数据库引擎自动填充，然后当人们搜索只是反向搜索字符串并使用索引的反向字段，在类似字符串的末尾只有一个％，从而允许使用索引。

You could have a separate field called reverse_phone_number, have this automatically populated by the DB engine then when people search simply reverse the search string and use the indexed reverse field with just a % at the end of the like string, thereby allowing the use of the index.

根据您的数据库引擎，您可能基于用户定义的函数创建一个索引，这样做可以避免需要额外的字段。

Dependant on your DB engine you may be able to create an index based on a user-defined function that does the reverse for you obviating the need for an additional field.

在某些国家/地区英国，您可能有前导零的问题。英国电话号码表示为（区号）（电话号码）。 01634 511098，当这被国际化时，去除区号的前导零，并且添加国际拨号代码（+或00）和国家代码（44）。这导致国际电话号码为+441634511098。任何搜索0163451109的用户如果以国际化格式输入，将找不到电话号码。您可以通过从搜索字符串中删除前导零来克服此问题。

In some countries, e.g. the UK, you may have an issue with leading zeros. A UK phone number is represented as (area code)(Phone Number) e.g. 01634 511098, when this is internationalised the leading zero of the area code is removed and the international dial code (+ or 00) and the country code (44) are added. This results in an international phone number of [phone]. Any user searching for 0163451109 would not find the phone number if it was entered in internationalised format. You can overcome this issue by removing leading zeros from the search string.

EDIT
根据Ollie Jones的建议，用户输入的数字，然后从反转前的数字中剥离前导零，标点符号和空格，并存储在反转字段中。然后，简单地使用相同的算法在反转之前去除搜索字符串，找到记录，然后将最初输入的数字显示给用户。

EDIT Based on suggestions from Ollie Jones you should store the number as entered by the user and then strip leading zeros, punctuation and white space from the number before reversing and storing in the reversed field. Then simply use the same algorithm to strip the search string before reversing, find the record and then display the originally entered number back to the user.

这篇关于努力与电话号码的MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！