php - 我的 SQL 查询有什么问题？

Question

我试图将另一个表添加到一个已经工作的查询中，但由于某种原因我收到了一个错误。我不确定查询是否足以继续，因为它正在由另一个函数运行，但我认为这可能只是我的查询。

这是有效的原始查询:

$listing_sql = "select " . $select_column_list . " p.products_id, p.products_model,   

p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, 
s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 
s.specials_new_products_price, p.products_price) as final_price 

from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p 
left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id 
left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id,
" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c

where p.products_status = '1' and p.products_id = p2c.products_id and 
pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and 
p2c.categories_id = '" . (int)$current_category_id . "'";

下面是添加了表和 where 子句的新查询(TABLE_PRODUCTS_ATTRIBUTES 是新表 pa):

$listing_sql = "select " . $select_column_list . " p.products_id, p.products_model, 
p.manufacturers_id, p.products_price, pa.products_values_id, p.products_tax_class_id, 
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
IF(s.status, s.specials_new_products_price, p.products_price) as final_price 

from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p, " . 

TABLE_PRODUCTS_ATTRIBUTES . " pa
left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id 
left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id,
" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c

where p.products_status = '1' and p.products_id = p2c.products_id and p.products_id = 
pa.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . 
(int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

我的查询有什么问题吗？

Answer 1

问题是您将逗号样式的连接语法与 JOIN 关键字混合在一起。因为它们具有不同的优先级，所以连接不会按照您期望的顺序执行(您可能期望从左到右的顺序)。

MySQL manual特别警告混合两种类型的连接:

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

要修复错误，您可以进行一个简单的更改，即调换 TABLE_PRODUCTS_ATTRIBUTES 和 TABLE_PRODUCTS 的顺序:

from " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_ATTRIBUTES . " pa, "
. TABLE_PRODUCTS . " p

然而，这并没有解决真正的问题——您的查询无法维护。更好的办法是将所有逗号式连接更改为使用 JOIN 关键字。这将需要从头开始重写整个查询，但这将使将来修改起来容易得多。