如何在多个表格列中搜索多个术语? [英] How can I search for multiple terms in multiple table columns?

问题描述

我有一张表格，列出了人员及其所有联系信息.我希望用户能够通过简单地键入一些内容并返回结果(他们输入的每个术语至少与表中的一列匹配)来对表执行智能搜索.首先，我进行了

I have a table that lists people and all their contact info. I want for users to be able to perform an intelligent search on the table by simply typing in some stuff and getting back results where each term they entered matches at least one of the columns in the table. To start I have made a query like

SELECT * FROM contacts WHERE
    firstname LIKE '%Bob%'
 OR lastname LIKE '%Bob%'
 OR phone LIKE '%Bob%' OR
 ...

但是现在我意识到，对于像鲍勃·詹金斯"这样简单的东西，这将完全失败，因为它不够聪明，无法单独搜索姓氏和名字.我需要做的是分解搜索词并分别搜索它们，然后以某种方式将每个词的结果相交.至少这对我来说似乎是解决方案.但是最好的解决方法是什么?

But now I realize that that will completely fail on something as simple as 'Bob Jenkins' because it is not smart enough to search for the first an last name separately. What I need to do is split up the the search terms and search for them individually and then intersect the results from each term somehow. At least that seems like the solution to me. But what is the best way to go about it?

我听说过全文和MATCH()... AGAINST()，但这听起来像是一个模糊的搜索，而且我不知道要设置多少工作.我想要精确的是或否结果以及合理的性能.搜索需要在大约20列乘120,000行上进行.希望用户输入的术语不会超过两个或三个.

I have heard about fulltext and MATCH()...AGAINST() but that sounds like a rather fuzzy search and I don't know how much work it is to set up. I would like precise yes or no results with reasonable performance. The search needs to be done on about 20 columns by 120,000 rows. Hopefully users wouldn't type in more than two or three terms.

抱歉，我忘了提到我正在使用MySQL(和PHP).

Oh sorry, I forgot to mention I am using MySQL (and PHP).

我刚刚想出了全文搜索，这是一个很不错的选择(有没有办法调整它的严格程度?LIMIT只会将结果砍掉，无论其匹配程度如何).但这需要全文索引，而我的网站正在使用视图，您无法为视图建立索引吗?所以...

I just figured out fulltext search and it is a cool option to consider (is there a way to adjust how strict it is? LIMIT would just chop of the results regardless of how well it matched). But this requires a fulltext index and my website is using a view and you can't index a view right? So...

推荐答案

布尔模式

BOOLEAN MODE

SELECT * FROM contacts WHERE  
MATCH(firstname,lastname,email,webpage,country,city,street...)  
AGAINST('+bob +jenkins' IN BOOLEAN MODE)

布尔模式非常强大.它甚至可以满足我的所有需求.我将不得不做一些测试.通过将+放在搜索词的前面，这些词成为必需的. (该行必须匹配"bob"和"jenkins"，而不是"bob"或"jenkins").此模式甚至适用于非索引列，因此尽管它会更慢(这是我需要测试的速度)，但我可以在视图上使用它.我遇到的最后一个问题是，它与部分搜索词不匹配，因此"bob"将找不到"bobby".通常的%通配符不起作用，而是使用星号*.

Boolean mode is very powerful. It might even fulfil all my needs. I will have to do some testing. By placing + in front of the search terms those terms become required. (The row must match 'bob' AND 'jenkins' instead of 'bob' OR 'jenkins'). This mode even works on non-indexed columns, and thus I can use it on a view although it will be slower (that is what I need to test). One final problem I had was that it wasn't matching partial search terms, so 'bob' wouldn't find 'bobby' for example. The usual % wildcard doesn't work, instead you use an asterisk *.

这篇关于如何在多个表格列中搜索多个术语?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！