出现该报错的原因比较多,
1.较常见的就是前端传参参数名与sql中的字段名对不上;
2.今天发现一个较为少见的原因,提示标识符无效,但是字段名、参数名完全一致;
原因:两张表连接查询时,A left join B,涉及多个字段,如果在<if></if>动态标签中传参没有写清楚是哪张表的字段,同样会出现这样的报错;
哪怕只是B表中独有的字段,也会报错,举例:
select a.name as name, a.age as age, a.height as height, b.chengshi as city
from person_info a
left join city_info b
on a.cityId = b.eid
<where>
<if test = "city != null">
and city = #{city}
</where>
上述代码同样报标识符无效;
虽然city字段是将b表中的chenshi字段的别名,但是仍然无法匹配;
正确写法:
select a.name as name, a.age as age, a.height as height, b.chengshi as city
from person_info a
left join city_info b
on a.cityId = b.eid
<where>
<if test = "city != null">
and b.chengshi = #{city}
</where>
如此就不会报错;
总结:
为了避免上述报错,写动态sql时尽量做到:
1.检查仔细;
2使用 表名.字段名 的形式 在if标签中进行判断;