在MySQL中使用InnoDB进行全文搜索

I have a MySQL database (version 5.5.28) with a table like this:

我有一个MySQL数据库(版本5.5.28),其表格如下:

products (InnoDB)
-------------------------------------------------
search_id     title               description
1             Levi Blue Jeans     Some cool jeans 
2             Gucci Handbag       Great accessory
3             Prada Dress         Beautiful dress

I want to do something like this in MySQL:

我想在MySQL中做这样的事情:

SELECT MATCH(title) AGAINST ('Jeans') AS score, search_id, FROM search WHERE MATCH(title) AGAINST ('Jeans' IN BOOLEAN MODE)

As far as I know you can only do that in MyISAM. Is it possible to do a similar search with InnoDB without resorting to things like:

据我所知,你只能在MyISAM中做到这一点。是否可以使用InnoDB进行类似的搜索,而无需采取以下措施:

Sphinx
Elasticsearch
Getting MySQL 5.6.4 which seems to not be stable yet

获得MySQL 5.6.4似乎还不稳定

For my application (around 1000 - 2000 records) Sphinx etc. seems like overkill. But getting every record into an array and search through it with PHP also seems too much. But maybe you disagree.

对于我的应用程序(大约1000 - 2000条记录),狮身人面像等似乎有点矫枉过正。但是将每个记录放入一个数组并用PHP搜索它似乎也太多了。但也许你不同意。

PS. LIKE doesn't seem to work well for me. The results are usually less accurate and you cannot get a score as you can with MATCH.

PS。 LIKE对我来说似乎不太好用。结果通常不太准确,你不能用MATCH获得分数。

1 个解决方案

#1

Duplicate the text column from products to a new MyISAM table. Establish a 1-1 relationship beween the two, and, in order to ensure of the ACID'ity provided by InnoDB, make sure that you always access the MyISAM table together with products.

将文本列从产品复制到新的MyISAM表。在两者之间建立1-1关系,并且,为了确保InnoDB提供的ACID,请确保始终与产品一起访问MyISAM表。

You may want to add triggers on products to maintain the bijection. You could also create a view so that the rework is minimal in your application when you upgrade to MySQL v5.6 (and drop this convoluted workaround).

您可能希望在产品上添加触发器以维持双射。您还可以创建一个视图,以便在升级到MySQL v5.6时,在应用程序中返工最少(并删除这个复杂的解决方法)。

Here is the full monty.

这是完整的monty。

Instead of copying the text column, you could move it altogether (delete it from products, that is). This would be more efficient, but it would also make it a bit more complicated to switch to a InnoDB-only solution when you feel like upgrading.

您可以完全移动文本列,而不是复制文本列(将其从产品中删除)。这样会更有效率,但是当您想要升级时,它也会使切换到仅支持InnoDB的解决方案变得更加复杂。

#1