mysql - 前缀最匹配 MySQL

Question

这就是我的场景。我有 4 个表:记录、提供者、routing_domain、域。

• 域:id、名称(类似于“example.com”)
• 提供者:id、名称(类似于“TLC”)
• 记录:phone_number (varchar), provider_id (外键提供商)
• routing_domain:provider_id(提供商的外键)，domain_id(域的外键)和前缀 (varchar)。

表格示例:

mysql> select id,name from domains;
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | e164.arpa             |
|  3 | example.com           |
|  0 | localhost.localdomain |
|  4 | luigi.it              |
|  2 | tim.it                |
+----+-----------------------+

mysql> select id,name from providers where id in (9,10);
+----+----------+
| id | name     |
+----+----------+
|  9 | TIM      |
| 10 | VODAFONE |
+----+----------+    

mysql> select * from routing_domain;
+----+--------+-----------+-------------+
| id | prefix | domain_id | provider_id |
+----+--------+-----------+-------------+
|  3 | 3932   |         4 |           9 |
|  1 | 39320  |         2 |           9 |
|  2 | 39321  |         3 |          10 |
+----+--------+-----------+-------------+

现在，

• 给定一个 phone_number '39320xxxxxxx' 和 provider_id 9，我需要得到domain_id=2;
• 给定一个 phone_number '39321xxxxxxx' 和 provider_id 9，我需要得到domain_id=4;

因此，给定一个 provider_id=9 的特定电话号码“3932xxxxxxxx”，我需要进行一些最佳匹配搜索。从 6 个字符开始搜索前缀，如果不匹配，尝试使用 5 个字符，依此类推，直到 3 个字符(393)。

我设法从 phone_number 搜索中获得了正确的域，仅从前缀到 5 个字符进行搜索。

类似于:

select * FROM records r
left join routing_domain rd on rd.prefix like SUBSTRING(r.phone_number,1,5) and r.provider_id = rd.provider_id 
left join providers p on p.id = rd.provider_id
left join domains d on d.id = rd.domain_id 
where r.name = 'xxxxxxxxxxxx';

做这个最佳匹配有什么建议吗？非常感谢!

Update

我试过这个:

select * FROM records r
left join routing_domain rd on on r.phone_number like concat(rd.prefix, '%') and r.provider_id = rd.provider_id 
left join providers p on p.id = rd.provider_id
left join domains d on d.id = rd.domain_id 
where r.name = 'xxxxxxxxxxxx';

现在，如果我搜索“39325xxxxxxx”，则会找到前缀为“3932”的匹配项，但是如果我搜索“39320xxxxxxx”，两个前缀都会匹配并且搜索返回 2 行。

Answer 1

一个选项是让子查询为您提供最长的前缀匹配 provider_id 和 prefix。像这样:

select domain_id from routing_domain
where
  provider_id = 9
  and '39321xxxxxxx' like concat(prefix, '%')
  and length(prefix) =
  (    select max(length(prefix))
       from routing_domain
       where 
         provider_id = 9
         and '39321xxxxxxx' like concat(prefix, '%')
  )

看我的 fiddle here .