mysql查询问题-嵌套和/或语句

Question

我的查询运行良好:

SELECT count(distinct p.products_id) as total 
FROM (products p 
        LEFT JOIN manufacturers m 
        USING(manufacturers_id), products_description pd, 
              categories c, products_to_categories p2c ) 
LEFT JOIN meta_tags_products_description mtpd 
          ON mtpd.products_id= p2c.products_id 
          AND mtpd.language_id = 1 
WHERE (p.products_status = 1 AND p.products_id = pd.products_id 
       AND pd.language_id = 1 AND p.products_id = p2c.products_id 
       AND p2c.categories_id = c.categories_id 
       AND (
            (pd.products_name LIKE '%3220%' 
             OR p.products_model LIKE '%3220%' 
             OR m.manufacturers_name LIKE '%3220%' 
             OR (mtpd.metatags_keywords LIKE '%3220%' 
                 AND  mtpd.metatags_keywords !='') 
             OR 
             (mtpd.metatags_description LIKE '%3220%' 
              AND   mtpd.metatags_description !='') 
             OR 
              pd.products_description LIKE '%3220%'
            ) 
           ) 
      )

但我想搜索那些 WHERE 子句，或者像示例中那样给定一个数字关键字值，通过将其添加到先前的查询来搜索产品 ID:

 OR (p.products_id=3220)

但由于某种原因，添加卡住了 mysql 服务器。它只是不断地执行查询，并且永远不会结束。我必须手动重新启动服务器。我做错了什么？

Answer 1

当您将 OR 添加到末尾时(假设您在最后一个括号之后添加它)，您正在创建笛卡尔连接，因为您使放置在 WHERE 子句中的 JOIN 条件无效 (p .products_id = pd.products_id、p.products_id = p2c.products_id 和 p2c.categories_id = c.categories_id)。另外，为什么要将初始表(产品、制造商、products_description、类别、products_to_categories)放在括号内？相反，尝试更像这样的事情:

SELECT count(distinct p.products_id) as total 
FROM products p 
LEFT JOIN manufacturers m USING(manufacturers_id)
LEFT JOIN products_description pd using products_id
LEFT JOIN categories c USING (categories_id)
LEFT JOIN products_to_categories p2c USING (products_id)
LEFT JOIN meta_tags_products_description mtpd 
          ON mtpd.products_id= p2c.products_id 
          AND mtpd.language_id = 1 
WHERE (p.products_status = 1  
       AND pd.language_id = 1  
       AND (
            (pd.products_name LIKE '%3220%' 
             OR p.products_model LIKE '%3220%' 
             OR m.manufacturers_name LIKE '%3220%' 
             OR (mtpd.metatags_keywords LIKE '%3220%' 
                 AND  mtpd.metatags_keywords !='') 
             OR 
             (mtpd.metatags_description LIKE '%3220%' 
              AND   mtpd.metatags_description !='') 
             OR 
              pd.products_description LIKE '%3220%'
            ) 
           ) 
      )
      OR (p.products_id=3220)

我不明白 p.products_status = 1 AND pd.language_id = 1 在您的情况下是什么意思，因此您可能需要调整这些在 JOIN 中的包含方式。