如何获得MYSQL相关搜索结果 - How to get MYSQL relevance search result

How to get MYSQL relevance search result

本文关键字：搜索结果 MYSQL 何获得 | 更新日期: 2023-09-27

我是mysql的新用户，我想通过句子搜索或关键字搜索像谷歌搜索相关的搜索结果。

表名:belongate_products

id                      product_name
---                     ------------
1                       smartphone micromax 2gb ram
2                       smartphone samsung 2gb ram
3                       laptop sony ram
4                       lenovo a361 smartphone
5                       sony z1 dual core smartphone
6                       Sony HD Dual Sim Smartphone

我已经使用了一些SQL，像下面的

SELECT *,
    CASE
      WHEN product_name LIKE '%vbsdf 2gb ram%'  THEN 50
      WHEN product_name LIKE '%vbsdf%2gb%ram%'  THEN 40
      WHEN product_name LIKE '%vbsdf%' AND product_name LIKE '%2gb%' AND product_name LIKE '%ram%' THEN 30
      WHEN product_name LIKE '%vbsdf%'  THEN 20
      WHEN product_name LIKE '%2gb%'  THEN 15
      WHEN product_name LIKE '%ram%'  THEN 10
      ELSE 0
    End as Importance
    FROM `affiliate_products`
    WHERE `product_name` LIKE '%vbsdf%'
    OR `product_name` LIKE '%2gb%'
    OR `product_name` LIKE '%ram%'
    ORDER BY Importance DESC;

也用

Select `product_name` from `affiliate_products` WHERE match(`product_name`) against('+"vbsdf" +2gb' +'ram' in boolean mode)

，但是这个SQL查询不符合我的要求。

i want to get result by below

如果搜索关键词:Micromax Ram

    id                      product_name
    ---                     ------------
    1                       smartphone **micromax** 2gb **ram**
    2                       smartphone samsung 2gb **ram**
    3                       laptop sony **ram**

inside ** **关键字已匹配

如果搜索关键字:2gb智能手机

id                      product_name
---                     ------------
1                       **smartphone** micromax **2gb** ram
2                       **smartphone** samsung **2gb** ram
4                       lenovo a361 **smartphone**
5                       sony z1 dual core **smartphone**
6                       Sony HD Dual Sim **Smartphone**

inside ** **关键字已匹配

if搜索关键词:四核智能手机

id                      product_name
---                     ------------
5                       sony z1 dual **core** **smartphone**
1                       **smartphone** micromax 2gb ram
2                       **smartphone** samsung 2gb ram
4                       lenovo a361 **smartphone**
6                       Sony HD Dual Sim **Smartphone**

inside ** **关键字已匹配

如果搜索关键字:购买索尼四核ram智能手机

id                      product_name
---                     ------------
5                       **sony** z1 dual **core** **smartphone**
6                       **Sony** HD Dual Sim **Smartphone**
3                       laptop **sony** **ram**
1                       **smartphone** micromax 2gb ram
2                       **smartphone** samsung 2gb ram
4                       lenovo a361 **smartphone**

inside ** **关键字已匹配

请帮助我如何使这个SQL得到这种信息的结果。提前感谢。

感谢所有的答案提交者。我已经得到了答案，我已经实现了这一点，它工作良好，sql查询是

    SELECT *,
       CASE
           WHEN product_name LIKE '%smartphone a361%' THEN 6
           WHEN MATCH (product_name) AGAINST ('+smartphone +a361' IN BOOLEAN MODE) THEN 5
           WHEN product_name LIKE '%smartphone%a361%' THEN 4
           WHEN product_name LIKE '%smartphone%' THEN 2
           WHEN product_name LIKE '%a361%' THEN 1
           ELSE 0
       END AS Importance
FROM `affiliate_products`
WHERE status = 'Active'
  AND (product_name LIKE '%smartphone%'
       OR product_name LIKE '%a361%')
ORDER BY Importance DESC