在mysql数据库中最有效地搜索关键字

问题描述：

I am working on a project where I have a database, that contains a summary field, which is filled in by a web form that visitors to the site enter on.

When the user completes entering the summary field, I want to perform a lookup using the words that were entered by the the user on the page for similar records in the database that contain the same keywords that they've filled in on the page.

I was thinking I could split the summary string that is submitted and then loop through the array and build up a query so the query would end up something like:

SELECT * 
FROM my_table 
WHERE summary LIKE '%keyword1%' 
OR summary LIKE '%keyword2' 
OR summary LIKE '%keyword3%';

However, this seems massively inefficient, and as the database could grow quite big, could potentially become quite a slow query to run.

I then found the MySQL IN clause, but this only seems to work with multiple values where a field can only contain 1 of these values in a row.

Is there a way I can use the IN function, or is there a better MySQL function that I can use to do what I want, or is my first idea the only way round it?

An example of what I am trying to achieve is a bit like on Stack Overflow. When you lose focus of the title field, it pops up similar questions based on the title you've provided.

我正在开发一个项目，我有一个数据库，其中包含一个汇总字段，由一个填写网站访问者进入的网络表单。 p>

当用户完成输入摘要字段时，我想使用页面上用户输入的单词对包含相同关键字的数据库中的类似记录执行查找他们填写了页面。 p>

我以为我可以拆分提交的摘要字符串，然后循环遍历数组并构建查询，以便查询最终结果如下： p> \ n

  SELECT * 
FROM my_table 
WHERE summary LIKE'％keyword1％'
OR summary LIKE'％keyword2'
OR summary LIKE'％keyword3％'; 
  code>  pre> \  n 
 然而，这看起来效率非常低，并且由于数据库可能会变得非常大，因此可能会成为一个非常慢的查询运行。   p> 
 
 
然后我找到了MySQL  IN  code>子句，但这似乎只适用于多个值，其中一个字段只能连续包含其中一个值。   p> 
 
 
有没有办法可以使用IN功能，还是有更好的MySQL功能可以用来做我想要的，或者是我的第一个想法绕过它的唯一方法？   p> 
 
 

我想要实现的一个例子有点像Stack Overflow。 当您失去标题字段的焦点时，它会根据您提供的标题弹出类似的问题。   p> 
  div>

答

I would recommend reading this manual page InnoDB FULLTEXT Indexes and the one on Full-Text Restrictions. New functionality of full text has been incorporated in recent releases of mysql, augmenting the use of it with INNODB tables.

Concerning the inability to upgrade a mysql version, there is no reason why one cannot mix and match MyISAM and INNODB tables in the same db. As such, one would keep textual information in MyISAM (where historically FTS index power was available), and doing joins to INNODB tables when needed. This avoids the "must upgrade to version 5.6" argument.

Legend: FTS=Full Text Search

在mysql数据库中最有效地搜索关键字

相关推荐