一个升级Mysql版本后出现的SQL错误

nbxmedia

前段时间升级了Mysql到5.0.16

1. select distinct a.* from order_main a,order_details b
   
2. left join order_history c on c.client_id=a.client_id
   
3. where a.id=b.order_id
   
4. order by a.id

复制代码

mysql返回错误

#1054 - Unknown column 'a.client_id' in 'on clause'

表设计并没有任何问题,因为我们原来是用4.0的,并没有出现过这个错误





后来解决的方法是改成两个left join,问题解决

SELECT DISTINCT a . *
FROM order_main a
LEFT JOIN order_history c ON c.client_id = a.client_id
LEFT JOIN order_details b ON a.id = b.order_id
ORDER BY a.id

求解这个错误的原因

厄,如果我火星,请BS我吧

helbreathszw

MySQL Bugs: #13551: #1054 - Unknown column 'xxxx' in 'on clause'-
Description: LEFT JOIN gives the "#1054 - Unknown column 'xxxx' in 'on clause'" error on MySQL 5, but it's working fine in MySQL 4.1 How to repeat: CREATE TABLE IF NOT EXISTS `t1` ( `id` int(10) unsigned NOT NULL auto_increment, ...

yejr

这个是因为新版本中left join和 ( 等运算符的优先级别有关系，记得用括号啥的就可以了
试试看

1. 
   
2. select distinct a.* from order_main a,order_details b
   
3. left join order_history c on (c.client_id=a.client_id)
   
4. where a.id=b.order_id
   
5. order by a.id
   

复制代码