MySQL 跨多个表的全文搜索 - 快速/长解决方案?

发布于 2024-09-02 17:59:26 字数 2003 浏览 11 评论 0原文

我一直在对全文搜索进行一些研究,因为我们意识到一系列 LIKE 语句很糟糕。我的第一个发现是 MySQL 全文搜索。我尝试实现这个,它在一个表上工作,当我尝试连接多个表时失败,所以我查阅了stackoverflow的文章(查看末尾的我去过的列表)

我没有看到任何能清楚回答我的问题的东西。我试图在一两个小时内完成这项工作(快速解决方案),但我也想做一个更好的长期解决方案。这是我的查询:

SELECT 
    a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, 
    b.`price`, c.`image`, c.`swatch`, e.`name` AS industry 
FROM `products` AS a 
LEFT JOIN `website_products` AS b ON (a.`product_id` = b.`product_id`)
LEFT JOIN 
    ( SELECT `product_id`, `image`, `swatch` FROM `product_images` 
      WHERE `sequence` = 0) AS c ON (a.`product_id` = c.`product_id`) 
LEFT JOIN `brands` AS d ON (a.`brand_id` = d.`brand_id`) 
INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) 
WHERE 
    b.`website_id` = 96 
    AND b.`status` = 1 
    AND b.`active` = 1 
    AND MATCH( a.`name`, a.`sku`, a.`description`, d.`name` ) AGAINST ( 'ashley sofa' ) 
GROUP BY a.`product_id` 
ORDER BY b.`sequence` LIMIT 0, 9

我得到的错误是: MATCH 的参数不正确

如果我从 MATCH 语句中删除 d.name ,它就会起作用。我有该列的全文索引。

我看到一篇文章说对此表使用 OR MATCH ,但这不会失去能够将它们排列在一起或正确匹配它们的有效性吗?

其他地方据说使用 UNION ,但我不知道如何正确执行。

任何建议将不胜感激。


从长期解决方案的角度来看,Sphinx 或 Lucene 似乎是最好的。现在我绝不是 MySQL 专家,而且我听说 Lucene 的设置有点复杂,任何建议或指导都会很棒。

文章:

跨多个表的 MySQL 全文搜索 跨 1 个表进行 MySQL 全文搜索 MySQL:如何进行多表全文搜索 全文搜索引擎的比较- Lucene、Sphinx、Postgresql、MySQL? 跨多个表搜索(最佳实践)

I have been doing a bit of research on full-text searches as we realized a series of LIKE statements are terrible. My first find was MySQL full-text searches. I tried to implement this and it worked on one table, failed when I was trying to join multiple tables, and so I consulted stackoverflow's articles (look at the end for a list of the ones I've been to)

I didn't see anything that clearly answered my questions. I'm trying to get this done literally in an hour or two (quick solution) but I also want to do a better long term solution. Here is my query:

SELECT 
    a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, 
    b.`price`, c.`image`, c.`swatch`, e.`name` AS industry 
FROM `products` AS a 
LEFT JOIN `website_products` AS b ON (a.`product_id` = b.`product_id`)
LEFT JOIN 
    ( SELECT `product_id`, `image`, `swatch` FROM `product_images` 
      WHERE `sequence` = 0) AS c ON (a.`product_id` = c.`product_id`) 
LEFT JOIN `brands` AS d ON (a.`brand_id` = d.`brand_id`) 
INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) 
WHERE 
    b.`website_id` = 96 
    AND b.`status` = 1 
    AND b.`active` = 1 
    AND MATCH( a.`name`, a.`sku`, a.`description`, d.`name` ) AGAINST ( 'ashley sofa' ) 
GROUP BY a.`product_id` 
ORDER BY b.`sequence` LIMIT 0, 9

The error I get is: Incorrect arguments to MATCH

If I remove d.name from the MATCH statement it works. I have a full-text index on that column.

I saw one of the articles say to use an OR MATCH for this table, but won't that lose the effectiveness of being able to rank them together or match them properly?

Other places said to use UNIONs but I don't know how to do that properly.

Any advice would be greatly appreciated.


In the idea of a long term solution it seems that either Sphinx or Lucene is best. Now by no means and I a MySQL guru, and I heard that Lucene is a bit more complicated to setup, any recommendations or directions would be great.

Articles:

MySQL full text search across multiple tables
MySQL FULLTEXT Search Across >1 Table
MySQL: how to make multiple table fulltext search
Comparison of full text search engine - Lucene, Sphinx, Postgresql, MySQL?
Searching across multiple tables (best practices)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

暖心男生 2024-09-09 17:59:26

对于短期解决方案,我建议创建一个仅包含全文值的表,如 这个问题

对于长期解决方案,请查看 Solr。它比 Lucene 更容易安装,并且提供了它的大部分功能。我也听说过 Sphinx 的好消息,但没有亲自使用过它。

For the short-term solution, I suggest creating a table of just the full-text values, as in this question.

For the long-term solution, please take a look at Solr. It is much easier to install than Lucene, and yet gives you most of its functionality. I have also heard good things about Sphinx, but have not personally ever used it.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文