Magento Catalog Search Query Explanation

问题:

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

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`);

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

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!

回答1:

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