SQL_CALC_FOUND_ROWS的使用

CBeann

发布于 2023-12-25 17:57:18

1660

发布于 2023-12-25 17:57:18

文章被收录于专栏：CBeann的博客CBeann的博客

需求

经常会有这么一种情况，让你根据条件分页查询学生的信息，最后还要总条数，

基本操作是两条SQL：

(1)select * from student where age = 18 limit 10,10 ;

(2) select count(*) from student where age = 18

现在通过一条SQL足矣

低配版本

select  * from student WHERE id < 1000 LIMIT 10,10 ;
select  count(id) from student WHERE id < 1000 ;

高配版本

select SQL_CALC_FOUND_ROWS 
    *
from student 
WHERE id < 1000
LIMIT 10,10;
SELECT FOUND_ROWS() as total_count;

MyBatis中的使用

<resultMap id="BaseResultMap" type="com.Student">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/> 
    </resultMap>

    <resultMap id="ExtCountResultMap" type="Integer">
        <result column="total_count" jdbcType="INTEGER" javaType="Integer"/>
    </resultMap>


    <select id="getStudentInfo2" resultMap="BaseResultMap,ExtCountResultMap">
        select
        SQL_CALC_FOUND_ROWS
        * from student where id in
        <foreach collection="ids" item="id" index="i" open="(" close=")" separator=",">
            #{id}
        </foreach>
        <if test="limit != null">
            <if test="offset != null">
                limit ${offset}, ${limit}
            </if>
        </if>
        ;SELECT FOUND_ROWS() as total_count;


    </select>

public interface TestExtDao {


  public List<?> getStudentInfo2(
      @Param("ids") List<Integer> ids,
      @Param("offset") Integer offset,
      @Param("limit") Integer limit);
}

List<?> result = testExtDao.getStudentInfo2(ids, offset, limit);
    List<Student> extResults = (List<Student>) result.get(0);
    Integer count = ((List<Integer>) result.get(1)).get(0);

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-03-07，如有侵权请联系 cloudcommunity@tencent.com 删除

sql