前言

hello大家好，我是喜欢玩基金的秀总，今天给大家带来一个mysql查询报错的问题

报错

### Error querying database.  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 'and t.opTime BETWEEN '2022-04-15 00:00:00.0' and '2022-04-20 00:00:00.0') tmp_co' at line 21
### The error may exist in file
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select count(0) from (select * from (             select nlog.id,             nlog.op_module opModule,             (case             when nlog.op_event = 0 then '增加'             when nlog.op_event = 1 then '删除'             when nlog.op_event = 2 then '修改'             when nlog.op_event = 4 then '导出'             when nlog.op_event = 5 then '导入'             end) opEvent,             nlog.op_time opTime,             su.nick opUser,             nlog.is_deleted isDeleted             from net_operation_log nlog             left join sys_user su on nlog.op_user = su.username             where nlog.is_deleted = 0         ORDER BY nlog.op_time DESC         ) t                                 and t.opTime BETWEEN ? and ?) tmp_count
### 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 'and t.opTime BETWEEN '2022-04-15 00:00:00.0' and '2022-04-20 00:00:00.0') tmp_co' at line 21
; 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 'and t.opTime BETWEEN '2022-04-15 00:00:00.0' and '2022-04-20 00:00:00.0') tmp_co' at line 21

原语句

select * from (
    select nlog.id,
    nlog.op_module opModule,
    (case
    when nlog.op_event = 0 then '增加'
    when nlog.op_event = 1 then '删除'
    when nlog.op_event = 2 then '修改'
    when nlog.op_event = 4 then '导出'
    when nlog.op_event = 5 then '导入'
    end) opEvent,
    nlog.op_time opTime,
    su.nick opUser,
    nlog.is_deleted isDeleted
    from net_operation_log nlog
    left join sys_user su on nlog.op_user = su.username
    where nlog.is_deleted = 0

ORDER BY nlog.op_time DESC
) t
    <if test="paramDTO.textValue != null and paramDTO.textValue != ''">
        and (
        t.opModule like  CONCAT('%',#{paramDTO.textValue},'%')
        or t.opEvent like  CONCAT('%',#{paramDTO.textValue},'%')
        or t.opUser like  CONCAT('%',#{paramDTO.textValue},'%')
        )
    </if>
    <if test="paramDTO.startDate != null and
    paramDTO.endDate != null ">
        and t.opTime BETWEEN #{paramDTO.startDate,jdbcType=TIMESTAMP} and #{paramDTO.endDate,jdbcType=TIMESTAMP}
    </if>

解决

凡事遇到报错不要慌，认证对待肯定没问题。

仔细看看上面这个报错你可以把sql复制到navcat执行即可解决，但是你不想这样，那么你仔细再看看下面有一行这样语句

and t.opTime BETWEEN '2022-04-15 00:00:00.0' and '2022-04-20 00:00:00.0') tmp_co' at line 21

这个什么意思呢？他说爆粗就在这个语句段旁边，于是再结合完整语句一看发现sql写错了，没有where，再结合报错提示就知道了，是内联sql外面临时表名t附近没有where关联，那肯定就出问题。

总结

写if语句时候一定要加上where字段。细心决定失败

本人开发的玩基金小工具

个人博客：
名称：纯洁的麦田
链接：[http://www.idearyou.cn/]
描述：争取哪一天做上架构师
公众号：纯洁的麦田

网址：[xiu.idearyou.cn]
谷歌插件搜：秀基宝
小程序：秀基宝
复制代码
复制代码

后语

如果本文对你哪怕有一丁点帮助，请帮忙点好看。你的好看是我坚持写作的动力。另外，关注免费学习。

Mysq报错（一）| check the manual that corresponds to your MySQL server version for t

前言

报错

原语句

解决

总结