两个MySQL表之间的最长前缀

Question

我有一个MySQL数据库，有两个表：

表A:

• 数字
• 位置

表B:

• 呼叫代码
• 区号
• 位置

最初，我在表A中有大约60000个条目，表A开头的Location列为空。在表B中，我有大约250000多个条目，其中有很多区号、呼叫码(1011(以及它们在世界上的各自位置。我想要的是一种快速的方法，用数字的位置填充表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:

号码位置17324765600空01134933638950空0114008203800空…目前有60万张唱片。。

样品表B:

调用代码位置1 7324765美国011 34933西班牙011 400820中国…目前有250000多张唱片

处理后的预期输出：表A:

号码位置17324765600美元01134933638950西班牙0114008203800中国

我提出的最好的更新声明如下：

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

当然，在这里，我不确定它是否总是返回代码的最长前缀，例如，如果在上表中有另一个代码以73247XX开头，那么假设代码是针对Iowa的(只是一个例子(。。我不确定查询是否总是返回最长的代码，所以在这里我也需要帮助。

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

数据库结构的.SQL：下载

更新2:

我正在考虑以下方式：

在将数据插入表A之前，我想将表B导出到CSV中，并按区域代码进行排序，这样我就可以有两个指针，一个用于表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) ;

Answer 2

您有一个只有250000条记录的联接，压力不大。您应该为搜索列和fine tune your mysql server建立适当的索引。设置good indexing & server variables well将很容易解决您的问题。很好地优化您的查询。一般来说，当我们有很多连接&多字符串比较。

我想你需要这样的查询-

UPDATE a SET a.location = ( 
                            SELECT location from b 
                            WHERE a.number LIKE CONCAT(b.calling_code, b.area_code, '%') 
                            ORDER BY LENGTH(CONCAT(b.calling_code, b.area_code, '%')) desc 
                            limit 1
                          );

Answer 3

我决定采取以下方法，因为我没有收到任何明确的回应：

在此过程之前，我准备了两个新表，一个是国家代码表，另一个是州代码表(因为如果号码在美国境内，我还需要知道州(。两个表都有：country，state，calling_code，code…

至于这两个表，我用前缀分解了所有数字，并按区号对其进行了分组，因此，我没有用完整的6个数字来识别一个国家/州，而是用前3个数字对其进行分组，如果代码是否在美国境内，则为2个表。

通过这些修改，我能够将250000行以上的表分解为大约300行(每个表(。

在此之后，我将遵循以下步骤：

1. 我得到电话号码列表
2. 我首先执行一个与我发布的查询非常相似的查询，以更新属于country_code表的所有数字
3. 然后，我更新那些仍然没有用state_code表分配位置的行
4. 为了避免有大量的手机，我不得不每隔x段时间就放一个cron

这可能不是最好的方法，但对于目前已经到位的50k个数字，我可以(手动执行一个又一个查询，并进行更多的抛光(将其减少到大约10秒，每x个时间执行一次(这将允许将该过程执行到小于10k个数字(将使这一过程顺利进行。

我会把这个标记为答案，但如果其他人神奇地想出了更好的答案，我会确保更新这个。

分而治之！