如何创建包含匹配记录的良好搜索

Question

我不知道如何在标题中解释这一点，当我搜索诸如 united Kingdom 之类的典型查询并且记录包含具有 < code>united states 我不需要将其包含在查询结果中，即使在搜索 new york 时，我也得到了从 york 返回的一些记录（在英国）。

另外，当搜索 maidstone 时，查询不会返回任何记录，但在数据库中它存在。

我需要解决这个查询的问题，它应该搜索与输入的查询匹配的行，并删除与选项匹配的行——当我说选项时，我的意思是 rec.column_name = etc... 。

搜索 united Kingdom 时：

WHERE  ( MATCH (rec.street_name, rec.city, rec.state, rec.country) AGAINST ('united kingdom' IN BOOLEAN MODE) 
         OR ( rec.street_name = 'united kingdom' 
              OR rec.city = 'united kingdom' 
              OR rec.state = 'united kingdom'
              OR rec.country = 'united kingdom'
            )
       ) AND ( rec.visible_listing = 1 AND rec.marked_delete = 0 AND rec.is_archive = 0 ) 

搜索 maidstone 时：

WHERE  ( MATCH (rec.street_name, rec.city, rec.state, rec.country) AGAINST ('maidstone' IN BOOLEAN MODE) 
         OR ( rec.street_name = 'maidstone' 
              OR rec.city = 'maidstone' 
              OR rec.state = 'maidstone'
              OR rec.country = 'maidstone'
            )
       ) AND ( rec.visible_listing = 1 AND rec.marked_delete = 0 AND rec.is_archive = 0 )

这是表中的总体记录：

+--------------------+---------------+----------------+----------------+-----------------+---------------+------------+
| street_name        | city          | state          | country        | visible_listing | marked_delete | is_archive |
+--------------------+---------------+----------------+----------------+-----------------+---------------+------------+
| Mill Hill          | Dover         | Kent           | United Kingdom |               1 |             0 |          0 |
| Penmaes            | Rhayader      | Powys          | United Kingdom |               1 |             0 |          0 |
| Essex St           | Jersey City   | Hudson         | United States  |               1 |             0 |          0 |
| Vesey St           | New York      | New York       | United States  |               1 |             0 |          0 |
| E Broadway         | Manhattan     | New York       | United States  |               1 |             0 |          0 |
| Cowdray Square     | Dover         | Kent           | United Kingdom |               1 |             0 |          1 |
| Falsgrave Crescent | York          | England        | United Kingdom |               1 |             0 |          0 |
| Tait Ave           | Sanger        | California     | United States  |               1 |             0 |          0 |
| Morton Ave         | Parsons       | Kansas         | United States  |               1 |             0 |          0 |
| N Washington St    | Clinton       | Missouri       | United States  |               1 |             0 |          0 |
| Lower Barngoose    | Carn Brea     | Cornwall       | United Kingdom |               1 |             0 |          0 |
| Moorwell Dr        | Shepherdswell | Kent           | United Kingdom |               1 |             0 |          0 |
| Elm Grove          | Maidstone     | Kent           | United Kingdom |               1 |             0 |          0 |
| Manse Rd           | Killin        | Stirling       | United Kingdom |               1 |             0 |          0 |
| Muirkirk Dr        | Glasgow       | Glasgow City   | United Kingdom |               1 |             0 |          0 |
| Alveston Ave       | Harrow        | Greater London | United Kingdom |               1 |             0 |          0 |
+--------------------+---------------+----------------+----------------+-----------------+---------------+------------+

如何做出返回非虚假数据的良好搜索查询？

Answer 1

将搜索字符串放在双引号内：'"united Kingdom"' 以实现完全匹配。

如果您想搜索两个单词，但不一定彼此相邻，可以使用 + 运算符：'+united +kingdom'。

请注意，如果您对搜索列使用二进制排序规则，则搜索将区分大小写。