我有一张大约有100万个电话号码的表格,还有一张大约有3000个ISD码(国家代码)的表格。现在我想把电话号码和所有这些ISD码进行最长前缀匹配。
在ISD表中,我可以有例如:

1     US
1808  US (Hawaii)

如果现在的电话号码是1223244223,它应该返回我们,但如果是1808322353,它应该返回我们(夏威夷)。
在最佳性能下实现这一目标的最佳方法是什么?
这是我目前所拥有的。不幸的是,我对表演不满意,我想回避以下功能:
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `isd`(telnum varchar(32)) RETURNS int(4)
BEGIN
    RETURN (SELECT if(locate(isd, telnum)=1, (locate(isd, telnum)*length(isd)), 0) as score FROM tbl_ref_isd_v1 having score>0 order by score desc limit 1);
END

此外,我有一个不同的功能,似乎更快:
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `isd_new`(telnum varchar(32)) RETURNS int(4)
BEGIN
    RETURN (
        select isd
from test.tbl_ref_isd_v1
where telnum like CONCAT(isd, '%')
order by length desc LIMIT 1
    );
END

最佳答案

下面是一个执行您所需操作的查询:

select pn.*, max(ic.code)
from (select pn.*, ic.code, len(ic.code)
      from PhoneNumbers pn join
           ISDCodes ic
           on pn.phonenumber like concat(ic.code, '%')
     ) pni
group by pn.phonenumber;

注意,对于初始字符串,max()起作用是因为1808大于1,依此类推。
我应该补充一点。这就完成了任务。它不一定是性能最好的,但这取决于许多因素,StackOverflow可能不是解决此类问题的最佳位置。
编辑:
ISDCodes(code)上建立索引。那么下面的方法应该很有效:
select pn.*, coalesce(ic5.code, ic4.code, ic3.code, ic2.code, ic1.code) as code
from PhoneNumbers pn left outer join
     ISDCodes ic1
     on left(pn.phonenumber, 1) = ic1.code left outer join
     ISDCodes ic2
     on left(pn.phonenumber, 2) = ic2.code left outer join
     ISDCodes ic3
     on left(pn.phonenumber, 3) = ic3.code left outer join
     ISDCodes ic4
     on left(pn.phonenumber, 4) = ic4.code left outer join
     ISDCodes ic5
     on left(pn.phonenumber, 5) = ic5.code;

您需要连接到更长的ic.code

10-06 12:53