5.0.12更改后的MySQL LEFT JOIN-如何重写查询 [英] MySQL LEFT JOIN after 5.0.12 changes - How to rewrite query

问题描述

5.0.12之后，MySQL更改了左联接的语法以匹配SQL2003标准.所以

After 5.0.12 MySQL changed the syntax for left joins to match SQL2003 standard. So

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

需要重写为

... FROM (t1 , t2) LEFT JOIN t3 ON (expr

否则它将被解析为... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

现在，我有一个古老的应用程序，即将从MySQL 3.23(eek！)移植到5.1，并且旧代码具有以下查询:

Now, I have an ancient app I'm porting from MySQL 3.23 (eek!) to 5.1, and the old code has this query:

select b.*, c.*, g.*, p.perfname, p.persname 
from bookings b, customer c 
left join grade g on b.chrggrade=g.grcode 
left join person p on b.person=p.percode 
where complete='Y' and invoiced='N' 
and datemade between '2009-03-25' and '2009-03-31' 
and c.custcode=b.cust 
order by cust, person, tsref, stdt 

此操作失败，并出现SQL错误1054，b.chrggrade中的未知列.这是因为它解析为

This fails with SQL error 1054, unknown column in b.chrggrade. This is because it's parsing as

选择b.，c.，g.*，p.perfname，p.persname 来自预订b，(客户c 在b.chrggrade = g.grcode上左联接等级g) 在b.person = p.percode上左加入人员p 其中complete ='Y'和发票='N' 和日期在"2009-03-25"和"2009-03-31"之间 和c.custcode = b.cust 按客户，人员，tsref，stdt的顺序

select b., c., g.*, p.perfname, p.persname from bookings b, (customer c left join grade g on b.chrggrade=g.grcode ) left join person p on b.person=p.percode where complete='Y' and invoiced='N' and datemade between '2009-03-25' and '2009-03-31' and c.custcode=b.cust order by cust, person, tsref, stdt

我想.

我确定正确放置的括号可以解决此问题，但是我很困惑. 我在 http://bugs.mysql.com/bug.php中找到了对此更改的引用?id = 13551 ，它显示了如何修复简单的左联接，但对于该查询，我仍然无法解决. 大卫

I'm sure correctly placed brackets can fix this but I'm stumped. I found reference to this change at http://bugs.mysql.com/bug.php?id=13551, which shows how to fix a simple left join, but I still can't work it out for this query. David

推荐答案

完全停止使用逗号语法，并在JOIN语句中明确显示.逗号语法会强制您将JOIN条件放在WHERE子句中，取决于条件的解析方式，该条件直到LEFT/RIGHT加入后才能执行.无论如何，使用显式JOINS可使查询更具可读性.

Stop using the comma syntax altogether and be explicit in your JOIN statements. The comma syntax forces you to put the JOIN condition in the WHERE clause, which may not get executed until after LEFT/RIGHT joins, depending on how it's parsed. Using explicit JOINS makes the query more readable anyway.

... FROM t1，t2左连接t3 ON(expr) 变成 ... FROM t1内接头t2 ON(expr)左接头t3 ON(expr)

...FROM t1, t2 LEFT JOIN t3 ON (expr) becomes ...FROM t1 INNER JOIN t2 ON (expr) LEFT JOIN t3 ON (expr)

这还将解决您所看到的错误.除非预订表中没有Chrggrade，否则没有任何东西可以解决该错误.

That will also fix the error you are seeing. Unless there is no chrggrade in the bookings table, then nothing will fix the error.

这篇关于5.0.12更改后的MySQL LEFT JOIN-如何重写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！