mysql - 如何在匹配中使用 substring_index

Question

我需要一个sql查询来使用mysql匹配(index:fulltext)来提取所有相关的“网球礼物”产品，但是当使用substring_index仅从product_attributes获取类别部分以与Tennis Gifts字符串匹配时，我收到以下错误

product_attributes value = {"category":"网球 > 礼物 > 项链", "seasons":{"months":["一月","二月"]}}

select product_code
from products
where match(substring_index(substring_index(product_attributes, 'category', -1), '"', 1)) against ('Tennis Gifts' IN NATURAL LANGUAGE MODE) 

错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(substring_index(product_attributes, 'category', -1), '"', 1)) ' at line 3

我如何才能仅将类别值与搜索词相匹配，感谢任何帮助。提前致谢。

Answer 1

你可以试试这个

select product_code
    from products
    where match(substring_index(substring_index(product_attributes, 'category', -1), '\"', 1)) 
    against ('Tennis Gifts' IN NATURAL LANGUAGE MODE)