我有以下表格数据:
+----------------------+----------------------------------------------------------+--------------+
| subscriber_fields_id | name | field_type |
+----------------------+----------------------------------------------------------+--------------+
| 143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi | Job Location |
| 146 | Karachi | Job Location |
| 147 | Lahore and Karachi | Job Location |
| 149 | Karachi, Mirpur Khas, Sukkur, Layyah, Gilgit, Charsaddah | Job Location |
| 152 | Islamabad or Lahore | Job Location |
| 155 | Islamabad | Job Location |
| 157 | 7 Districts of Sindh and Karachi | Job Location |
+----------------------+----------------------------------------------------------+--------------+
我的查询是:
select * from subscriberfields
where name like '%Khairpur,Islamabad,Karachi%';
结果:
+----------------------+-----------------------------------------------+--------------+
| subscriber_fields_id | name | field_type |
+----------------------+-----------------------------------------------+--------------+
| 143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi | Job Location |
| 152 | Islamabad or Lahore | Job Location |
| 155 | Islamabad | Job Location |
+----------------------+-----------------------------------------------+--------------+
它应该返回所有名称中包含伊斯兰堡、开普尔或卡拉奇的行,但它没有。
WHERE name LIKE '%Khairpur%' OR name LIKE ...
进行查询。然而,这种方式不是可搜索的,因此需要进行全表扫描——随着表格大小的增长,效率将变得非常低下。你应该考虑使用全文搜索代替。 - eggyalSIMILAR TO
吗? - benscabbiaSIMILAR TO
,它是一个完全无意义的语句 - 总有更好的选项。我添加了一个答案。 - Erwin Brandstetter