问题描述
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.persnamefrom bookings b, (customer cleft join grade g on b.chrggrade=g.grcode )left join person p on b.person=p.percodewhere complete='Y' and invoiced='N'and datemade between '2009-03-25' and '2009-03-31'and c.custcode=b.custorder 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-如何重写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！