MySQL多表连接

在一次进行多表链接的时候,MySQL抛出错误,不是别表名称.如下:

1
2
select * from t_cms_ad_info ad ,t_cms_materials materials,t_cms_ads_materials adsmaterials LEFT JOIN t_cms_materials_ext ext
on materials.materials_id=ext.mid where ad.ad_id= adsmaterials.advertise_id and adsmaterials.material_id= materials.materials_id;

t_cms_ad_info 广告表
t_cms_materials 物料表
t_cms_ads_materials 广告物料关联表
t_cms_materials_ext 物料扩展信息表
上面的语句执行的时候抛出错误: 
Unknown column ‘materials.materials_id’ in ‘on clause’
当然这个不是表的小名问题
Unknown column ‘t_cms_materials.materials_id’ in ‘where clause’

问题产生的原因是: 在之前的MySQL版本中,逗号连接操作和JOIN 连接操作的优先级相同,现在JOIN的优先级高于逗号连接操作.在以前,在进行select 操作时,逗号连接的表是隐式分组,现在优先进行join操作,这个时候使用的表名称,有可能识别不了,这就是抛出错误的原因.解决的方法是吧前面的表用括号扩起来提高优先级,或者调整表的顺序.

1
2
3
4
5
6
7
8
9
-- 1
select * from (t_cms_ad_info ad ,t_cms_materials materials,t_cms_ads_materials adsmaterials )LEFT JOIN t_cms_materials_ext ext
on materials.materials_id=ext.mid where ad.ad_id= adsmaterials.advertise_id and adsmaterials.material_id= materials.materials_id;
-- 2
select * from t_cms_ad_info ad ,t_cms_ads_materials adsmaterials ,t_cms_materials materials
LEFT JOIN t_cms_materials_ext ext
on materials.materials_id=ext.mid
where ad.ad_id= adsmaterials.advertise_id
and adsmaterials.material_id= materials.materials_id;