MySQL/Mybatis多个AND和OR混用注意事项

来源：互联网发布：怎么联系网络服务商编辑：程序博客网时间：2024/05/02 06:11

mysql中AND的优先级高于OR，所以在查询时，会优先执行AND条件，除非使用（）来将一个AND和OR括起来，这样才能使得OR得以按照语句的顺序执行。

如下图所示：

java测试代码

@Test    public void TestMutil(){        Species species = new Species();        ArrayList<String> arrayList = new ArrayList<String>();        arrayList.add("长喙蚤");        arrayList.add("尤氏");        List<Species> querySpeciesesListByMutilCondition = this.speciesMapper.querySpeciesesListByMutilCondition(arrayList, species.getEnglishName(), species.getHost(), species.getPosition(), species.getLocation(), species.getPhylum(), species.getClassName(), species.getOrder(), species.getFamily(), species.getJenus());                for (Species s : querySpeciesesListByMutilCondition) {            System.out.println(s);        }        System.out.println(querySpeciesesListByMutilCondition.size());            }

Mapper文件中没有使用（）放在语句中执行情况

<select id="querySpeciesesListByMutilCondition" resultType="Species">SELECT * FROM t_json_species<where><if test="englisName != null and englisName != ''">AND englishName like CONCAT('%',#{englishName},'%') OR sameName like CONCAT('%',#{englishName},'%')</if><if test="host != null and host != ''">AND host like CONCAT('%',#{host},'%')</if><if test="position != null and position != ''">AND position like CONCAT('%',#{position},'%')</if><if test="location != null and location != ''">AND location like CONCAT('%',#{location},'%')</if><if test="phylumName != null and phylumName != ''">AND phylumName = #{phylumName}</if><if test="className != null and className != ''">AND className = #{className}</if><if test="orderName != null and orderName != ''">AND orderName = #{orderName}</if><if test="familyName != null and familyName != ''">AND familyName = #{familyName}</if><if test="jenusName != null and jenusName != ''">AND jenusName = #{jenusName}</if><if test="nameList != null and nameList != ''"><foreach collection="nameList" item="name" >AND name like CONCAT('%',#{name},'%') OR sameName like CONCAT('%',#{name},'%')</foreach></if></where></select>

Mapper文件中使用（）放在语句中执行情况

<select id="querySpeciesesListByMutilCondition" resultType="Species">SELECT * FROM t_json_species<where><if test="englisName != null and englisName != ''">AND englishName like CONCAT('%',#{englishName},'%') OR sameName like CONCAT('%',#{englishName},'%')</if><if test="host != null and host != ''">AND host like CONCAT('%',#{host},'%')</if><if test="position != null and position != ''">AND position like CONCAT('%',#{position},'%')</if><if test="location != null and location != ''">AND location like CONCAT('%',#{location},'%')</if><if test="phylumName != null and phylumName != ''">AND phylumName = #{phylumName}</if><if test="className != null and className != ''">AND className = #{className}</if><if test="orderName != null and orderName != ''">AND orderName = #{orderName}</if><if test="familyName != null and familyName != ''">AND familyName = #{familyName}</if><if test="jenusName != null and jenusName != ''">AND jenusName = #{jenusName}</if><if test="nameList != null and nameList != ''"><foreach collection="nameList" item="name" >AND (name like CONCAT('%',#{name},'%') OR sameName like CONCAT('%',#{name},'%'))</foreach></if></where></select>

--------------------------------------------------------------------------------------

补充：

如果这里使用多个%来解决上述的含有多个OR和AND情况，那么所实现功能会有问题，因为多个关键词有%来连接，会有一个次序问题。具体效果见下图

0 0