使用mybatis查出数据与mysql查出来的不一致(order by)_2020-10-23解决的BUG

最新推荐文章于 2023-03-01 08:00:00 发布

qq_39408729

最新推荐文章于 2023-03-01 08:00:00 发布

阅读量473

点赞数

本文链接：https://blog.csdn.net/qq_39408729/article/details/109239692

版权

使用mybatis查出数据与mysql查出来的不一致(order by)_2020-10-23解决的BUG

错误：
xml拼接sql:
select consequence.* from consequence
<where>
    <include refid="getPageCondition"/>
</where>
<if test="page != null and page.orderBy != null and page.orderBy !=''">
    order by #{page.orderBy}
</if>
<include refid="order_by_sql"></include>
<include refid="pageLimit"/>

控制台输出的查询信息：
select consequence.* from consequence WHERE consequence.id < ? and consequence.task_id = ? order by ? limit ? ,?
Parameters: 170166(Long), 99(Long),  id desc (String), 0(Integer), 1(Integer)

在mysql中执行的sql:
SELECT
                consequence.* 
        FROM
                consequence 
        WHERE
                consequence.id < 170166
                AND consequence.task_id = 99
                order by 'id' 'desc'
                LIMIT 0, 1



正确：
xml拼接sql:
select consequence.* from consequence
<where>
    <include refid="getPageCondition"/>
</where>
<if test="page != null and page.orderBy != null and page.orderBy !=''">
    order by ${page.orderBy}
</if>
<include refid="order_by_sql"></include>
<include refid="pageLimit"/>

控制台输出的查询信息:
select consequence.* from consequence WHERE consequence.id < ? and consequence.task_id = ? order by id desc limit ? ,? 
Parameters: 170166(Long), 99(Long), 0(Integer), 1(Integer)

mysql中执行的sql：
SELECT
                consequence.* 
        FROM
                consequence 
        WHERE
                consequence.id < 170166
                AND consequence.task_id = 99
                order by id desc
                LIMIT 0, 1