MySQL - 分页查询PaginationInnerInterceptor:autoCountSql，虽然不影响，但是看起来很难受

        在排查问题的时候，发现在日志打印的控制台输出，有一堆下面的日志输出，虽然没有报错啥的，但是看起来很难受，于是决定一探究竟；

[04-07 13:51:58.869] [ WARN] [租户1_OYeFuitGXiPr] [XNIO-1 task-1] c.b.m.e.p.i.PaginationInnerInterceptor:autoCountSql:351 : optimize this sql to a count sql has exception, sql:"SELECT  id,activity_type,name,poster_attach_uuid,poster_attach_url,theme_color,rule,start_time,end_time,share_poster_attach_uuid,share_poster_attach_url,avatar_config,nick_name_config,app_title,app_cover_image_uuid,app_cover_image_url,limit_switch,max_num,daily_limit_switch,daily_max_num,is_close AS close,is_enabled AS enabled,is_deleted AS deleted,creator_id,updater_id,create_time,update_time  FROM table_activity 
 
 WHERE (activity_type = ? AND is_enabled = ? AND is_deleted = ?) ORDER BY create_time DESC", exception:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "close" "CLOSE"
    at line 1, column 302.

Was expecting one of:

    "ACTION"
    "ACTIVE"
    "ALGORITHM"
    "ARCHIVE"
    "ARRAY"
    "AT"
    "BYTE"
    "CASCADE"
    "CASE"
    "CAST"
    "CHANGE"
    "CHAR"
    "CHARACTER"
    "CHECKPOINT"
    "COLUMN"
    "COLUMNS"
    "COMMENT"
    "COMMIT"
    "COSTS"
    "CYCLE"
    "DBA_RECYCLEBIN"
    "DESC"
    "DESCRIBE"
    "DISABLE"
    "DISCONNECT"
    "DIV"
    "DO"
    "DUMP"
    "DUPLICATE"
    "ENABLE"
    "END"
    "EXCLUDE"
    "EXTRACT"
    "FALSE"
    "FILTER"
    "FIRST"
    "FLUSH"
    "FN"
    "FOLLOWING"
    "FORMAT"
    "FULLTEXT"
    "HISTORY"
    "INDEX"
    "INSERT"
    "INTERVAL"
    "ISNULL"
    "JSON"
    "KEY"
    "LAST"
    "LEADING"
    "LINK"
    "LOCAL"
    "LOG"
    "MATERIALIZED"
    "NO"
    "NOLOCK"
    "NULLS"
    "OF"
    "OPEN"
    "OVER"
    "PARALLEL"
    "PARTITION"
    "PATH"
    "PERCENT"
    "PRECISION"
    "PRIMARY"
    "PRIOR"
    "QUERY"
    "QUIESCE"
    "RANGE"
    "READ"
    "RECYCLEBIN"
    "REGISTER"
    "REPLACE"
    "RESTRICTED"
    "RESUME"
    "ROW"
    "ROWS"
    "SCHEMA"
    "SEPARATOR"
    "SEQUENCE"
    "SESSION"
    "SHUTDOWN"
    "SIBLINGS"
    "SIGNED"
    "SIZE"
    "SKIP"
    "SUSPEND"
    "SWITCH"
    "SYNONYM"
    "SYSTEM"
    "TABLE"
    "TABLESPACE"
    "TEMP"
    "TEMPORARY"
    "TIMEOUT"
    "TO"
    "TOP"
    "TRUE"
    "TRUNCATE"
    "TRY_CAST"
    "TYPE"
    "UNQIESCE"
    "UNSIGNED"
    "USER"
    "VALIDATE"
    "VALUE"
    "VALUES"
    "VIEW"
    "XML"
    "ZONE"
    <K_DATETIMELITERAL>
    <K_DATE_LITERAL>
    <K_NEXTVAL>
    <K_STRING_FUNCTION_NAME>
    <S_CHAR_LITERAL>
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

[04-07 13:51:58.877] [DEBUG] [租户1_OYeFuitGXiPr] [XNIO-1 task-1] c.s.s.c.m.S.selectPage_mpCount:debug:137 : ==>  Preparing: SELECT COUNT(*) FROM (SELECT id,activity_type,name,poster_attach_uuid,poster_attach_url,theme_color,rule,start_time,end_time,share_poster_attach_uuid,share_poster_attach_url,avatar_config,nick_name_config,app_title,app_cover_image_uuid,app_cover_image_url,limit_switch,max_num,daily_limit_switch,daily_max_num,is_close AS close,is_enabled AS enabled,is_deleted AS deleted,creator_id,updater_id,create_time,update_time FROM table_activity WHERE (activity_type = ? AND is_enabled = ? AND is_deleted = ?) ORDER BY create_time DESC) TOTAL
[04-07 13:51:58.878] [DEBUG] [租户1_OYeFuitGXiPr] [XNIO-1 task-1] c.s.s.c.m.S.selectPage_mpCount:debug:137 : ==> Parameters: 2(Integer), false(Boolean), false(Boolean)
[04-07 13:51:58.881] [DEBUG] [租户1_OYeFuitGXiPr] [XNIO-1 task-1] c.s.s.c.m.S.selectPage_mpCount:debug:137 : <==      Total: 1

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "close" "CLOSE"
    at line 1, column 302.

        这个异常提示，其实已经明确的告诉了我们，跟这个close有关，上面日志打印的就是MySQL使用的关键字，其中就包括‘close’；

        查询的SQL语句中，的确是有 ... ...,is_close AS close,... ...；

         查了下代码中对应的实体类，如下，将数据库中的“is_close”映射了对象中的“close”；

@TableField(value = "is_close")
private boolean close;

        这里是进行数据分页查询的地方： 

Page<TableActivity> page = tableActivityService.page(request.getPage(), lambdaQueryWrapper);

        其中，分页的类继承自mybatis的分页，其中有一个这个参数，如下： 

//自动优化COUNT SQL
protected boolean optimizeCountSql = true;

         IDEA中全局搜索了下报错信息中的提示类（c.b.m.e.p.i.PaginationInnerInterceptor:autoCountSql:351）；

protected String autoCountSql(IPage<?> page, String sql) {
        if (!page.optimizeCountSql()) {
            return lowLevelCountSql(sql);
        }
        try {
            //... ....
        } catch (JSQLParserException e) {
            // 无法优化使用原 SQL
            logger.warn("optimize this sql to a count sql has exception, sql:\"" + sql + "\", exception:\n" + e.getCause());
        } catch (Exception e) {
            logger.warn("optimize this sql to a count sql has error, sql:\"" + sql + "\", exception:\n" + e);
        }
        return lowLevelCountSql(sql);
    }

        catch里面的内容，不就跟我们的报错信息对应起来了嘛，是因为默认对我们的分页查询进行了COUNT的SQL进行了优化，但是我们的sql中又包含了MySQL的关键字，导致这个自动优化失败了，退化成了lowLevelCountSql(sql)，因此查询是正常的，同时又有提示出来；

        解决方案：

                1. 在分页查询的时候 ，直接禁止它优化查询 setOptimizeCountSql(false)

Page<TableActivity> page = tableActivityService.page(request.getPage().setOptimizeCountSql(false), lambdaQueryWrapper);

                2. 修改实体类中的参数名，使其与MySQL中的关键字不产生冲突，如：close --> closed

@TableField(value = "is_close")
private boolean closed;