MySQL在Ruby on Rails中的全文搜索

I am trying to implement a basic full-text search with MySQL.

我正在尝试使用MySQL实现基本的全文搜索。

I wrote this migration:

我写了这个迁移：


def self.up
  execute 'ALTER TABLE photos ENGINE = MyISAM'
  execute 'CREATE FULLTEXT INDEX fulltext_photos ON photos (place, info)'
end

def self.down
  execute 'ALTER TABLE photos ENGINE = InnoDB'
  execute 'DROP INDEX fulltext_photos ON photos'
end

And here's my model:

这是我的模特：


def self.search(*args)
  options = args.extract_options!
  find_by_sql [ "SELECT * FROM photos WHERE MATCH (place, info) AGAINST (?)", options[:query] ]
end

The problem is that this code always returns an empty array.

问题是这段代码总是返回一个空数组。

For example:

例如：

% Photo.find(:first)
=> Photo id: 1, place: "Baceno", info: "Era immerso in erba alta." ... 

% Photo.search(:all, :query => 'baceno')
=> []

4 个解决方案

#1

I created a project (Rails 2.3.2, Ruby 1.9.1 MySQL 5.0) to emulate this. With one record in the database, I got the same results you did. When I added more records, the Photo.search command found the record.

我创建了一个项目（Rails 2.3.2，Ruby 1.9.1 MySQL 5.0）来模拟这个。在数据库中有一条记录，我得到了相同的结果。当我添加更多记录时，Photo.search命令找到了记录。

This could be because "words that are present in 50% or more of the rows are considered common and do not match". Ref.

这可能是因为“50％或更多行中出现的单词被认为是常见且不匹配”。参考。

The 50% threshold does not apply in binary mode. Ref.

50％阈值不适用于二进制模式。参考。

IN BINARY MODE belongs inside the parentheses: AGAINST ('baceno' IN BOOLEAN MODE)

IN BINARY MODE属于括号内：AGAINST（'baceno'IN BOOLEAN MODE）

#2

Check out the Thinking Sphinx gem. It's great.

查看Thinking Sphinx宝石。这很棒。

#3

The below code runs for my web page and gives correct result in ruby on rails.

下面的代码运行我的网页，并在rails上的ruby中给出正确的结果。

Adverse.find(:all, :conditions => ["match(related_company,client_name) against (? IN BOOLEAN MODE)",@chk])

#4

In my test (just in MySQL, not in Rails) when I add the option IN BOOLEAN MODE to the SELECT statement, it seems to return rows.

在我的测试中（仅在MySQL中，而不是在Rails中）当我将选项IN BOOLEAN MODE添加到SELECT语句时，它似乎返回行。

SELECT * FROM photos WHERE MATCH (place, info) AGAINST (?) IN BOOLEAN MODE

I would also recommend using a seperate search product such as Solr or Sphinx for searching.

我还建议使用单独的搜索产品，如Solr或Sphinx进行搜索。

#1