Magento目录搜索查询说明 [英] Magento Catalog Search Query Explanation

问题描述

更新:已回答问题，请参见 Magento目录搜索查询我的后续问题的约束错误.

UPDATE: Question answered, see Magento Catalog Search Query Constraint Error for my follow-up question.

在Magento目录中进行某些搜索时，我遇到了外键约束错误.这是导致该问题的查询:

I'm getting foreign key constraint errors when making certain searches in the Magento catalog. Here's a query that cause that:

INSERT INTO `catalogsearch_result` 
(
    SELECT 
        '0', 
        `s`.`product_id`, 
        -(
            ( MATCH(`s`.`data_index`) AGAINST ('ip335') ) + 
            ( 5 * ( MATCH(`s`.`data_index_1`) AGAINST('ip335') ) ) + 
            ( 3 * ( MATCH(`s`.`data_index_2`) AGAINST('ip335') ) ) + 
            ( 2 * ( MATCH(`s`.`data_index_3`) AGAINST ('ip335') ) ) 
        ) 

    FROM `mikkelrickycatalogsearch_fulltext` AS `s` 

    INNER JOIN `catalog_product_entity` AS `e`
        ON `e`.`entity_id`=`s`.`product_id` 

    WHERE 
        (
            (`s`.`data_index` LIKE '%ip335%')
        ) 
        AND `s`.`store_id`='2'
) 
ON DUPLICATE 
    KEY UPDATE `relevance` = VALUES(`relevance`);

我们正在使用MikkelRicky_CatalogSearch扩展名，但即使是核心搜索代码也会产生类似的查询(INSERT/ON DUPLICATE KEY).

We're using the MikkelRicky_CatalogSearch extension, but even the core search code produces a similar query (INSERT/ON DUPLICATE KEY).

我正在尝试了解SQL中的这种查询类型.我只是不了解INSERT语句如何与DUPLICATE KEY UPDATEs一起工作.谁能提供一些实用的解释?谢谢！

I'm trying to understand this TYPE of query in SQL. I simply don't understand how INSERT statements work with DUPLICATE KEY UPDATEs. Can anyone provide some practical explanations? Thanks!

推荐答案

如果MySQL发现catalogsearch_result的键query_id和product_id是重复的，而不是MySQL引擎更新值relevance = VALUES('relevance');而不是insert

if MySQL sees that keys query_id and product_id of catalogsearch_result are duplicating than MySQL engine updates value relevance = VALUES('relevance'); instead of insert

这篇关于Magento目录搜索查询说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！