MySQL 1064语法错误：执行多表LEFT JOIN关联查询报错如何解决

问题原因 #

你遇到的语法报错是因为你为cities.longitude字段设置的别名long属于MariaDB的保留关键字，无法直接作为标识符使用，数据库解析SQL语句时会将它识别为数值类型关键字，导致解析逻辑中断，抛出1064语法错误，报错提示里的错误位置对应long附近也符合这个特征。

解决方案 #

你可以任选以下任意一种方式修改：

• 更换别名，将long替换为非保留字即可，比如lng，修改后的语句如下：

SELECT cities.latitude as lat, cities.longitude as lng, cities.name as ctn, states.name as stn, countries.name as cn FROM cities
LEFT JOIN states ON cities.state_id = states.id
LEFT JOIN countries ON states.country_id = countries.id
WHERE cities.id = :id

• 若必须使用long作为别名，用反引号将别名包裹，告诉数据库将其识别为普通标识符：

SELECT cities.latitude as lat, cities.longitude as `long`, cities.name as ctn, states.name as stn, countries.name as cn FROM cities
LEFT JOIN states ON cities.state_id = states.id
LEFT JOIN countries ON states.country_id = countries.id
WHERE cities.id = :id

内容的提问来源于stack exchange，提问作者Shubham Jha