mysql分页出现重复数据-order by

好久没更新了。昨天项目测试报了个bug,分页列表中,第一页的数据,在第二页也存在,同时也丢失了一条数据。



于是我就上网开始扒拉扒拉。。。。昨天下午年会,于是今天周六加班搞!

原来是Sql 的问题。

原映射文件的sql语句如下:

<select id="queryList"
        resultType="com.rmkj.microcap.modules.user.entity.UserBean">
    SELECT
    user.id as id,
    user.open_id as openId,
    user.user_header as userHeader,
    user.chn_name as chnName,
    user.mobile as mobile,
    user.trade_password as tradePassword,
    user.money as money,
    `user`.ticket as ticket,
    `user`.trade_count as tradeCount,
    user.coupon_money as couponMoney,
    user.recharge_money as rechargeMoney,
    user.status as status,
    user.register_time as registerTime,
    user.last_login_time as lastLoginTime,
    user.last_login_ip as lastLoginIp,
    t.group_id AS groupId
    FROM
tb_user user LEFT JOIN tb_control_group_user t ON t.user_id = user.id AND t.group_id = '0'
    where user.mobile is not null
    <if test="id!=null">
        and user.id=#{id}
    </if>
    <if test="openId!=null">
        and user.open_id=#{openId}
    </if>
    <if test="userHeader!=null">
        and user.user_header=#{userHeader}
    </if>
    <if test="chnName!=null">
        and user.chn_name like '%' #{chnName} '%'
    </if>
    <if test="mobile!=null">
        and user.mobile = #{mobile}
    </if>
    <if test="tradePassword!=null">
        and user.trade_password=#{tradePassword}
    </if>
    <if test="money!=null">
        and user.money=#{money}
    </if>
    <if test="rechargeMoney!=null">
        and user.recharge_money=#{rechargeMoney}
    </if>
    <if test="status!=null">
        and user.status=#{status}
    </if>
    <if test="registerTime!=null">
        and user.register_time=#{registerTime}
    </if>
    <if test="lastLoginTime!=null">
        and user.last_login_time=#{lastLoginTime}
    </if>
    <if test="lastLoginIp!=null">
        and user.last_login_ip=#{lastLoginIp}
    </if>
    <if test="uMoneyMin != null">
        AND user.money &gt; #{uMoneyMin}
    </if>
    <if test="uMoneyMax != null">
        AND user.money &lt; #{uMoneyMax}
    </if>
    <if test="groupId != null">
        AND t.group_id = '0'
    </if>
    <if test="orderKey != null and orderValue !=null">
        ORDER BY ${orderKey} ${orderValue}
    </if>
</select>

于是我在数据库按照0到10排序,结果和页面第一页显示的一样。



百度了一下,原来是在select查询的时候, 如果不加order by ,mysql会以物理存储顺序显示,当进行增删改之后,存储顺序改变,可能会造成数据重复或丢失,解决办法就是加order by!

因为我是在页面中的下拉列表框进行选择,动态排序,所以在进页面时,传到后台的orderkey和ordervalue为空,就不会进行排序。而且我没有在sql中添加默认排序。

添加order by之后,映射文件的sql如下:

<select id="queryList"
        resultType="com.rmkj.microcap.modules.user.entity.UserBean">
    SELECT
    user.id as id,
    user.open_id as openId,
    user.user_header as userHeader,
    user.chn_name as chnName,
    user.mobile as mobile,
    user.trade_password as tradePassword,
    user.money as money,
    `user`.ticket as ticket,
    `user`.trade_count as tradeCount,
    user.coupon_money as couponMoney,
    user.recharge_money as rechargeMoney,
    user.status as status,
    user.register_time as registerTime,
    user.last_login_time as lastLoginTime,
    user.last_login_ip as lastLoginIp,
    t.group_id AS groupId
    FROM
tb_user user LEFT JOIN tb_control_group_user t ON t.user_id = user.id AND t.group_id = '0'
    where user.mobile is not null
    <if test="id!=null">
        and user.id=#{id}
    </if>
    <if test="openId!=null">
        and user.open_id=#{openId}
    </if>
    <if test="userHeader!=null">
        and user.user_header=#{userHeader}
    </if>
    <if test="chnName!=null">
        and user.chn_name like '%' #{chnName} '%'
    </if>
    <if test="mobile!=null">
        and user.mobile = #{mobile}
    </if>
    <if test="tradePassword!=null">
        and user.trade_password=#{tradePassword}
    </if>
    <if test="money!=null">
        and user.money=#{money}
    </if>
    <if test="rechargeMoney!=null">
        and user.recharge_money=#{rechargeMoney}
    </if>
    <if test="status!=null">
        and user.status=#{status}
    </if>
    <if test="registerTime!=null">
        and user.register_time=#{registerTime}
    </if>
    <if test="lastLoginTime!=null">
        and user.last_login_time=#{lastLoginTime}
    </if>
    <if test="lastLoginIp!=null">
        and user.last_login_ip=#{lastLoginIp}
    </if>
    <if test="uMoneyMin != null">
        AND user.money &gt; #{uMoneyMin}
    </if>
    <if test="uMoneyMax != null">
        AND user.money &lt; #{uMoneyMax}
    </if>
    <if test="groupId != null">
        AND t.group_id = '0'
    </if>
    <if test="orderKey == null and orderValue ==null">      
        order by user.register_time desc
    </if>
    <if test="orderKey != null and orderValue !=null">
        ORDER BY ${orderKey} ${orderValue}
    </if>
</select>
 列表中的数据也不再重复或丢失: 




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值