mysql分页

发表于

mysql使用limit，offset实现分页。
limt 3 offset 5 效果同 limit 5,3
都表示偏移5，从第6条开始读，读3条记录。示例如下:

1
2
3
4
5
6
7
8
9
10
select
date_format(createTime, "%Y-%m-%d %T") as time,buyerId,commission
from
partner_commission_record
where
date_format(createTime, "%Y-%m-%d") between #{date_from} and #{date_to}
and agencyId = #{agencyId} and (rebateLV=1 or rebateLV=2)  and isCancel=0
order by
createTime desc
limit #{limit} offset #{offset};

ORM使用Mybatis则建议使用Mybatis的动态sql语句，通过标签实现不同情形的sql复用。示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<select id="addCardPlayerRecord" parameterType="HashMap" resultType="HashMap">
      select
      playerID,amount,date_format(time, "%Y-%m-%d %T") as time
      from
      add_player_card_log
      where
      agency = #{agencyID}
      <if test="null!=playerID">
          and playerID = #{playerID}
      </if>
      <if test="null!=date_from and null!=date_to">
          and date_format(time, "%Y-%m-%d") between #{date_from} and #{date_to}
      </if>
	  order by
	  add_player_card_log.time desc
	  <if test="null!=limit and null!=offset">
		  limit #{limit} offset #{offset};
   	  </if>
</select>