在mysql里0和 '' 比较是相等的
mysql 手写sql int类型字段判空 if(0 is not null and 0!='', bindtype=0,1=1) 查到了很多bindtype不为0的数据,有问题。
select * from account where 1=1
and if(0 is not NULL AND 0!='', bindtype=0, 1=1) ;---会查出bindtype不为0的数据; 0就等于'',所以前面是false
select * from account where 1=1
and if(0 is not null, bindtype=0, 1=1) ;---只查bindtype=0的
select * from account where 1=1
and if(1 is not null AND 1!='', bindtype=1, 1=1) ;---只查bindtype=1的
select * from account where 1=1
and if(0 is not null AND 0!='', bindtype=0, 1=1) ;
mysql jpa sql中 int ,long等可能为0数字类型的,以及boolean类型后面也不能加 ?1!='' , 如下是正确的:
@Query(value = "select * from sys_dept "
+ "where if(?1 is not null, dept_id=?1, 1=1) "
+ "and if(?2 is not null, pid=?2, 1=1) "
+ "and if(?3 is not null and ?3!='', name like concat('%',?3,'%'), 1=1) "
+ "and if(?4 is not null, enabled=?4, 1=1) "
, nativeQuery = true)
List<Dept> queryList(Long deptId, Long pid, String name, Boolean enabled);