MySQL分页查询自己写

MySQL分页查询关键要点

分页查询关键limit、offset，总页数pages、当前页page、每页多少条数据size，总记录数total

limit后面的数据为每页多少条数据size；

offset为前面所有页数据之和 (page - 1)*size

示例MyBatis Mapper

查询当前页数据

<select id="listByQueryVideoResourceReq4Third" parameterType="string" resultType="ResourceJoinExecuteMachineVO">
        select
        tr.NAME as name,
        tr.ID as id,
        tr.CREATE_NAME as createName,
        tr.USER_DATA as userData,
        tr.FILE_NAME as fileName,
        tr.FILE_URL as fileUrl,
        tr.FILE_SIZE as fileSize,
        tr.GUID as guid,
        tr.PARAMETER as parameter,
        tr.RESOURCE_TYPE as resourceType,
        tr.STREAM_TYPE as streamType,
        tr.UPDATE_TIME as updateTime,
        temr.STATUS as status,
        temr.MACHINE_ID as machineId
        from table_a tr
        left join table_b temr
        on tr.id = temr.RESOURCE_ID
        where
        tr.CREATE_NAME = #{userName}
       limit #{offset},#{pageSize}
</select>

或者Mapper

<select id="listByQueryVideoResourceReq4Third" parameterType="string" resultType="ResourceJoinExecuteMachineVO">
        select
        tr.NAME as name,
        tr.ID as id,
        tr.CREATE_NAME as createName,
        tr.USER_DATA as userData,
        tr.FILE_NAME as fileName,
        tr.FILE_URL as fileUrl,
        tr.FILE_SIZE as fileSize,
        tr.GUID as guid,
        tr.PARAMETER as parameter,
        tr.RESOURCE_TYPE as resourceType,
        tr.STREAM_TYPE as streamType,
        tr.UPDATE_TIME as updateTime,
        temr.STATUS as status,
        temr.MACHINE_ID as machineId
        from table_a tr
        left join table_b temr
        on tr.id = temr.RESOURCE_ID
        where
        tr.CREATE_NAME = #{userName}
       limit #{pageSize} offset #{offset}
</select>

总记录数

<select id="listByQueryVideoResourceReq4Third" resultType="long">
        select
        count(1)
        from TB_RESOURCE tr
        left join tb_execute_machine_resource temr
        on tr.id = temr.RESOURCE_ID
        where
        tr.CREATE_NAME = #{userName}
      
</select>

 

 select * from order_detail order by order_detail_id limit 8,2

 

 select * from order_detail order by order_detail_id limit 2 offset 8