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>      

清單中的資料也不再重複或丢失：