mybatisplus分页插件MybatisPlusInterceptor优化left join语句

问题

使用mybatisplus分页插件后，查询语句带有left join提示如下：

SQL: SELECT COUNT() FROM contract_material cm WHERE (1 = 1 AND cm.del_status = ?)
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM contract_material cm WHERE (1 = 1 AND cm.del_status = 1)' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM contract_material cm WHERE (1 = 1 AND cm.del_status = 1)' at line 1

使用mybatisplus分页插件后带left join的语句被自动优化，并且SELECT COUNT()执行报错。

我的配置文件如下：

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

解决办法

禁用mybatis-plus自动优化left join语句，可在调用IPage时调用setOptimizeCountSql方法

public IPage pageList(CriteriaQuery<?> wrapper) {
        PageParams page = wrapper.getPagerInfo();
        //禁用mybatis-plus自动优化
        page.setOptimizeCountSql(false);
        IPage list = this.baseMapper.pageList(page, wrapper);
        EntityMap.setEnumConvertInterceptor(null);
        return list;
    }

禁用后countSQL语句正确

2024-03-13 09:45:40.577  INFO 56112 --- [nio-9201-exec-1] p6spy   : 2024-03-13 09:45:40|0|14ms|statement|SELECT COUNT(*)FROM (select re.id,cm.id materialId,cm.material_name,con.purchase_material_type,cm.material_type,cm.purchase_quantity,cm.total_amount,con.contract_number,con.contract_name,con.supplier,cm.delivery_deadline,cm.manufacturer,re.acceptance_status,re.create_time acceptanceTime FROM contract_material cm left join contract con on cm.contract_id = con.id and con.del_status = 1 left join receipt re on re.material_id = cm.id and re.del_status = 1 WHERE (1 = 1 AND cm.del_status = 1)) TOTAL

<==    Columns: COUNT(*)
<==        Row: 12
<==      Total: 1

问题解决。

解决提示来源：MybatisPlus的分页插件自动优化LeftJoin语句导致参数不匹配_mybatis 分页省略left join-CSDN博客