MySQL InnoDB 文本搜索选项
完全了解我的 InnoDB 表不支持全文搜索，我想知道在表中搜索文本的替代方案是什么？使用 LIKE 时性能那么很差吗？
我看到很多建议说在 MYISAM 表中制作相关 InnoDB 表的副本，然后对该表运行查询并匹配两者之间的键，我只是不知道这是一个很好的解决方案。
我并不反对使用某些第三方解决方案，但我并不是它的忠实粉丝。我想更多地探索 MySQL 本身可以做什么。
想法？
Knowing full well that my InnoDB tables don't support FULLTEXT searches, I'm wondering what my alternatives are for searching text in tables ? Is the performance that bad when using LIKE ?
I see a lot of suggestions saying to make a copy of the InnoDB table in question in a MYISAM table, and then run queries against THAT table and match keys between the two and I just don't know that that's a pretty solution.
I'm not opposed to using some 3rd party solution, I'm not a huge fan of that though. I'd like to explore more of what MySQL can do on its own.
Thoughts ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用 LIKE 只能在没有前导 % 的情况下使用索引。在大表上执行 LIKE '%foo%' 将会对性能造成巨大影响。如果我是你，我会考虑使用 sphinx。它能够通过使用您提供的查询从 MySQL 中获取数据来构建索引。它非常简单，旨在解决您的具体问题。
还有 solr，它是 lucene 的 http 包装器，但我发现 sphinx 更简单一些。
Using LIKE can only use an index when there is no leading %. It will be a huge performance hit to do LIKE '%foo%' on a large table. If I were you, I'd look into using sphinx. It has the ability to build its index by slurping data out of MySQL using a query that you provide. It's pretty straightforward and was designed to solve your exact problem.
There's also solr which is an http wrapper around lucene, but I find sphinx to be a little more straightforward.
我和其他人一样强烈建议使用 Lucene、Sphinx 或 Solr。
但是，如果这些都已完成并且您的要求很简单，我已使用此处 在过去的一些项目上构建简单搜索功能。
该链接适用于 Symfony/PHP，但您可以将这些概念应用于任何语言和应用程序结构，前提是存在可用的词干算法实现。但是，如果您不使用可以在更新记录时挂钩来更新索引的数据访问模式，则不太容易实现。
还有一些缺点是，如果您想要单个索引表但需要对多个表建立索引，您要么必须在 DAL 中模拟引用完整性，要么为要索引的每个不同表添加一个 fk 列。我不确定你想做什么，这样可能会完全排除它。
I as others have i would urge use of Lucene, Sphinx or Solr.
However if these are out and your requirements are simple I've used the steps here to build simple search capability on a number projects in the past.
That link is for Symfony/PHP but you can apply the concepts to any language and application structure assuming there is an implementation of a stemming algorithm available. However, if you dont use a data access pattern where you can hook in to update the index when a record is updated its not as easily doable.
Also a couple downsides are that if you want a single index table but need to index multiple tables you either have to emulate referential integrity in your DAL, or add a fk column for each different table you want to index. Im not sure what youre trying to do so that may rule it out entirely.
如果您想正确执行此操作，您可能应该使用 Lucene 或 Sphinx 从一开始就使用。
Lucene 和 Sphinx 的扩展性都非常好（Lucene 为 Wikipedia 提供支持，而 Digg / Sphinx 为 Slashdot 提供支持）
If you want to do it right you probably should go with Lucene or Sphinx from the very start.
Both Lucene and Sphinx scale amazingly well (Lucene powers Wikipedia and Digg / Sphinx powers Slashdot)