在MySQL中使用InnoDB进行全文搜索 [英] Fulltext search with InnoDB in MySQL

问题描述

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

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

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

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

I want to do something like this in MySQL:

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

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

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:

• Sphinx
• Elasticsearch
• 获得MySQL 5.6.4似乎尚未稳定

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

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

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.

PS. 好像对我来说效果不佳.结果通常不太准确，并且无法获得与MATCH一样的分数.

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.

推荐答案

将文本列从products复制到新的MyISAM表. 在两者之间建立1-1关系，并确保InnoDB提供的ACID，确保您始终将MyISAM表与products一起访问.

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.

您可能想在products上添加触发器以保持双射.您还可以创建一个视图，以便在升级到MySQL v5.6(并丢弃这种复杂的解决方法)时，减少应用程序中的返工.

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).

这里是全部款项.

您可以完全移动它(而不是从products删除它)，而不是复制文本列.这样会更有效，但是当您想要升级时，切换到仅使用InnoDB的解决方案也将变得更加复杂.

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.

这篇关于在MySQL中使用InnoDB进行全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！