mysql - 全文判断匹配字段

Question

我有一个表设置，其中包含 ID、StreetNumber、StreetName、StreetType 的全文索引，用于实时表单搜索。示例数据如下所示:

       ID        StreetNumber   StreetName   StreetType
   -----------------------------------------------------
   | 141099  |        1411   |  Elm       |      ST    |
   -----------------------------------------------------
   | 141100  |        2901   |  Maple     |      LN    |
   -----------------------------------------------------

如果我查询使用:

SELECT ID, StreetName, StreetNumber, StreetType
FROM Locations
WHERE MATCH(ID, StreetName, StreetNumber, StreetType)
      AGAINST('1411*' IN BOOLEAN MODE)

它当然会返回两条记录...但是，我很难找到一种简单的方法来确定它匹配的字段。

我想将我的实时搜索结果格式化为:

ID: 141100 (2901 Maple Lane)
Address: 1411 Elm St

...按匹配项排序。用 PHP 格式化很容易，问题在于找到匹配的结果。但是，我无法找到一种方法来执行此操作而不必在 PHP 中对其进行检查(效率不高)。想法？

Answer 1

您可以使用全文索引高效地进行搜索，然后使用另一种解决方案来测试选择列表的列(它仅针对通过 WHERE 子句中的过滤器的行运行):

SELECT ID, StreetName, StreetNumber, StreetType,
  (StreetName like '%1411%') AS `StreetName_matches`, 
  (StreetNumber like '%1411%') AS `StreetNumber_matches`,
  (StreetType like '%1411%') AS `StreetType_matches`
FROM Locations 
WHERE MATCH(ID, StreetName, StreetNumber, StreetType) 
      AGAINST('1411*' IN BOOLEAN MODE)

或者您可以在列上创建四个单独的全文索引，然后依次搜索它们中的每一个:

SELECT ID, StreetName, StreetNumber, StreetType,
  MAX(`ID_Matches`) AS `ID_Matches`,
  MAX(`StreetName_Matches`) AS `StreetName_Matches`,
  MAX(`StreetNumber_Matches`) AS `StreetNumber_Matches`,
  MAX(`StreetType_Matches`) AS `StreetType_Matches`
FROM (
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    1 AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`
  FROM Locations 
  WHERE MATCH(ID) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    1 AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetName) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    1 AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetNumber) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    1 AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetTYPE) AGAINST('1411*' IN BOOLEAN MODE)
) AS t
GROUP BY t.ID;

您可能还想研究功能更全面的全文搜索技术，例如 Sphinx Search或 Apache Solr .

例如参见 How to return column that matched the query in Solr..? 的答案