php - 两个 MySQL 表之间的最长前缀

Question

我有一个包含 2 个表的 MySQL 数据库:

表A:

• 人数
• 地点

表 B:

• 调用代码
• 区号
• 地点

最初，我在表 A 中有大约 60,000 个条目，其位置列在开头是空的。在表 B 中，我有大约 250,000 多个条目，其中包含大量区号、调用代码 (1、011) 以及它们在世界上各自的位置。我想要的是一种用数字位置填充表 A 的位置列的快速方法。

例如，如果表 A 中的第一个条目是 (17324765600, null)，我想通过表 B 读取并获取该数字的位置。现在我通过这个查询得到了一个数字的位置:

SELECT b.location
FROM 
  tableB b
  LEFT JOIN tableA a
     ON a.number LIKE CONCAT(b.calling_code, b.code, '%')
ORDER BY CHAR_LENGTH(b.code) DESC
LIMIT 1;

这给了我正确的位置(尽管我怀疑它会失败......)。问题是在性能方面这种方法是行不通的。如果我遍历所有 50k 数字

更新 1

请允许我将一些示例数据与预期输出放在一起:示例表 A:

number  location17324765600 NULL01134933638950  NULL0114008203800   NULL…60k Records + at the moment..

Sample Table B:

calling_code    code    location1   7324765 US-NJ011 34933   Spain011 400820  China…250,000+ records at the moment

Expected output after the processing:Table A:

number  location17324765600 US-NJ01134933638950  Spain0114008203800   China

The best I’ve come up with is the following update statement:

UPDATE tableA a JOIN tableB b ON a.location LIKE CONCAT(b.calling_code, b.code, '%') SET a.location = b.location

当然在这里我不确定它是否总是返回代码的最长前缀，例如，如果在上表中有另一个以 73247XX 开头的代码，假设该代码用于爱荷华州(仅作为示例)。 .我不确定查询是否总是返回最长的代码，所以在这里我也需要帮助。

如果样本有帮助，请告诉我。

.SQL为数据库结构: Download

更新 2:

我正在考虑按以下方式执行此操作:

在表 A 中插入数据之前，我正在考虑将表 B 导出到 CSV 中并按区号对其进行排序，这样我就可以有 2 个指针，一个用于表 A 的条目数组，一个用于 csv，两者都已排序通过区号，这样我可以进行一种并行搜索并在 PHP 上填充条目的位置，而不必在 MySQL 中执行此操作。

如果这种方法看起来是更好的选择，请告诉我，如果是的话，我会对其进行测试并发布答案。

Answer 1

如果您想要所有位置，则需要删除 LIMIT

SELECT b.location
FROM 
  tableB b
  LEFT JOIN tableA a
     ON a.number LIKE CONCAT(b.calling_code, b.code, '%')
ORDER BY CHAR_LENGTH(b.code);

如果您希望相同的位置名称不应该出现两次，那么您需要使用GROUP BY

SELECT b.location
FROM 
  tableB b
  LEFT JOIN tableA a
     ON a.number LIKE CONCAT(b.calling_code, b.code, '%')
GROUP BY b.location  ORDER BY CHAR_LENGTH(b.code) ;