首页 / 知识

关于字符串：在mysql中搜索电话号码

2023-04-14 00:34:00

Searching for phone numbers in mysql

我有一张桌子，上面满是任意格式的电话号码，例如

1
2
3
4

027 123 5644
021 393-5593
(07) 123 456
042123456

我需要搜索类似任意格式的电话号码(例如07123456应该找到条目(07) 123 456

我将以普通的编程语言执行此操作的方法是将所有非数字字符从"针"中去除，然后遍历大海捞针中的每个数字，将所有非数字字符从中去除，然后与针进行比较，例如(ruby)

1
2
3
4

digits_only = lambda{ |n| n.gsub /[^\\d]/, '' }

needle = digits_only[input_phone_number]
haystack.map(&digits_only).include?(needle)

问题是，我需要在MySQL中执行此操作。它具有许多字符串函数，似乎没有一个真正可以满足我的要求。

目前我可以想到2个"解决方案"

一起整理CONCAT和SUBSTR的弗兰肯查询
在针的每个字符之间插入一个%(就像这样：%0%7%1%2%3%4%5%6%)

但是，这两个都不是特别优雅的解决方案。
希望有人可以提供帮助，或者我可能被迫使用%%%%%%%解决方案

更新：这是对一组相对固定的数据进行操作，可能有几百行。我只是不想做一些可笑的坏事，以致将来的程序员会哭泣。

如果数据集增加，我将采用" phoneStripped"方法。感谢您的所有反馈！

could you use a"replace" function to strip out any instances of"(","-" and"",

我不担心结果是数字。
我需要考虑的主要字符是+，-，(，)和space
那么该解决方案看起来像这样吗？

1
2
3
4

SELECT * FROM people
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+')
LIKE '123456'

那不是很慢吗？

这从一开始就是一个问题。您进行的任何类型的搜索都需要进行表扫描，我们都知道这很不好。

在除去所有格式字符后，如何添加带有当前电话号码哈希的列。然后，您至少可以为哈希值建立索引，并避免进行完整的表扫描。

还是数据量很小并且预计增长不会很大？
然后也许只是将所有数字吸入客户端并在其中进行搜索。

我知道这是古老的历史，但是我在寻找类似解决方案时发现了它。

一个简单的REGEXP可能起作用：

1	select * from phone_table where phone1 REGEXP"07[^0-9]123[^0-9]456"

这将与phonenumber列匹配，带有或不带有任何分隔字符。

这是MySQL的问题-regex函数可以匹配，但不能替换。请参阅此帖子以获取可能的解决方案。

一个开箱即用的想法，但是您是否可以使用"替换"功能删除"("，"-"和""的任何实例，然后使用" isnumeric"功能测试是否结果字符串是数字吗？

然后，您可以对要搜索的电话号码字符串执行相同的操作，并将它们比较为整数。

当然，这不适用于1800-MATT-ROCKS之类的数字。：)

我的解决方案将类似于约翰·代尔(John Dyer)所说的。我要添加第二列(例如phoneStripped)，该列会在插入和更新时被剥离。为该列建立索引并对其进行搜索(当然，在删除搜索词之后)。

您也可以添加触发器以自动更新列，尽管我没有使用触发器。但是就像您说的那样，编写MySQL代码来剥离字符串确实很困难，因此仅在客户端代码中执行操作可能会更容易。

(我知道这很晚了，但是我才开始四处看看：)

我建议使用php函数，而不要使用mysql模式，因此您将具有以下代码：

1
2
3
4
5
6

$tmp_phone = '';
for ($i=0; $i < strlen($phone); $i++)
if (is_numeric($phone[$i]))
$tmp_phone .= '%'.$phone[$i];
$tmp_phone .= '%';
$search_condition .=" and phone LIKE '" . $tmp_phone ."'";

请参阅

http://www.mfs-erp.org/community/blog/find-phone-number-in-database-format-independent

因为只有mysql能"看到"它，所以正则表达式在视觉上不会令人吃惊。请注意，您应在正则表达式中使用" *"，而不是""(在OP中以[\\\\ D]开头)。

一些用户担心性能(非索引搜索)，但是在有100000个客户的表中，从用户界面发出此查询后，立即返回，没有明显的延迟。

是否可以运行查询以重新格式化数据以匹配所需格式，然后仅运行简单查询？这样，即使最初的重新格式化很慢，您也没关系。

可能的解决方案可以在以下网址找到：//udf-regexp.php-baustelle.de/trac/

需要安装其他软件包，然后可以使用REGEXP_REPLACE

正如John Dyer所说，您应该考虑将数据固定在数据库中，并且仅存储数字。但是，如果您面临与我相同的情况(我无法运行更新查询)，则发现的解决方法是合并两个查询。

"内部"查询将检索所有电话号码，并将其格式化以除去非数字字符。

1	SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name

其结果将是所有没有任何特殊字符的电话号码。之后，"外部"查询仅需要获取您要查找的条目。
这两个查询将是：

1
2
3

SELECT phone_formatted FROM (
SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name
) AS result WHERE phone_formatted = 9999999999

重要提示：不使用AS结果，但应该在该位置以避免错误。

创建用户定义的函数以动态创建正则表达式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DELIMITER //

CREATE FUNCTION udfn_GetPhoneRegex
(
var_Input VARCHAR(25)
)
RETURNS VARCHAR(200)

BEGIN
DECLARE iterator INT DEFAULT 1;
DECLARE phoneregex VARCHAR(200) DEFAULT '';

DECLARE output VARCHAR(25) DEFAULT '';

WHILE iterator < (LENGTH(var_Input) + 1) DO
IF SUBSTRING(var_Input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
SET output = CONCAT(output, SUBSTRING(var_Input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
SET output = RIGHT(output,10);
SET iterator = 1;
WHILE iterator < (LENGTH(output) + 1) DO
SET phoneregex = CONCAT(phoneregex,'[^0-9]*',SUBSTRING(output, iterator, 1));
SET iterator = iterator + 1;
END WHILE;
SET phoneregex = CONCAT(phoneregex,'$');
RETURN phoneregex;
END//
DELIMITER ;

在存储过程中调用该用户定义函数。

1
2
3

DECLARE var_PhoneNumberRegex VARCHAR(200);
SET var_PhoneNumberRegex = udfn_GetPhoneRegex('+ 123 555 7890');
SELECT * FROM Customer WHERE phonenumber REGEXP var_PhoneNumberRegex;

我是祸。我最终这样做：

1
2
3