因?yàn)闃I(yè)務(wù)需要一個(gè)分頁(yè)查詢的列表，但是對(duì)數(shù)據(jù)內(nèi)容有去重的需求，簡(jiǎn)單了解了JPA的@Query注解使用方式后就直接用了自定義sql來(lái)做，測(cè)試通過(guò)后上線測(cè)了兩筆也沒(méi)問(wèn)題就沒(méi)太在意。但是隔了一天運(yùn)營(yíng)就反應(yīng)說(shuō)功能報(bào)錯(cuò)了，而且我去看日志發(fā)現(xiàn)報(bào)錯(cuò)很奇怪，是sql語(yǔ)法錯(cuò)誤，如果是sql語(yǔ)法錯(cuò)誤那么是怎么測(cè)試通過(guò)的呢？報(bào)錯(cuò)如下：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from test a where a.id in (select max(b.id) from test' at line 1

但是我寫的sql在結(jié)果集那里是沒(méi)有括號(hào)的

select a.* from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

這個(gè)括號(hào)是如何生成的呢，經(jīng)過(guò)反復(fù)的測(cè)試樣本測(cè)試之后發(fā)現(xiàn)，這個(gè)是jpa在做分頁(yè)需要查詢總數(shù)量的時(shí)候自動(dòng)加了一個(gè)sql

select count(a.*) from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

這個(gè)語(yǔ)法在高版本的mysql中是不支持的，只能寫成

select count(*) from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

那么一開(kāi)始又是怎么測(cè)試通過(guò)的呢，為什么有的查詢（pageSize>count）jpa不會(huì)加上count(pageSize<count)而有的又會(huì)自動(dòng)count？
debug到j(luò)pa源碼之后，才知道jpa在執(zhí)行分頁(yè)查詢的時(shí)候有對(duì)記錄總數(shù)的獲取方式做優(yōu)化，具體實(shí)現(xiàn)在org.springframework.data.repository.support.PageableExecutionUtils的getPage方法，jpa就是通過(guò)這個(gè)方法將結(jié)果集封裝成page，而其中在計(jì)算total的時(shí)候會(huì)判斷當(dāng)前頁(yè)偏移量，當(dāng)前pageSize是否大于查詢條件的pageSize，如果能直接計(jì)算出來(lái)total就不會(huì)調(diào)用count去數(shù)據(jù)庫(kù)查詢記錄總數(shù)

/**
     * Constructs a {@link Page} based on the given {@code content}, {@link Pageable} and {@link Supplier} applying
     * optimizations. The construction of {@link Page} omits a count query if the total can be determined based on the
     * result size and {@link Pageable}.
     *
     * @param content must not be {@literal null}.
     * @param pageable must not be {@literal null}.
     * @param totalSupplier must not be {@literal null}.
     * @return the {@link Page}.
     */
    public static <T> Page<T> getPage(List<T> content, Pageable pageable, LongSupplier totalSupplier) {

        Assert.notNull(content, "Content must not be null!");
        Assert.notNull(pageable, "Pageable must not be null!");
        Assert.notNull(totalSupplier, "TotalSupplier must not be null!");

        if (pageable.isUnpaged() || pageable.getOffset() == 0) {

            if (pageable.isUnpaged() || pageable.getPageSize() > content.size()) {
                return new PageImpl<>(content, pageable, content.size());
            }

            return new PageImpl<>(content, pageable, totalSupplier.getAsLong());
        }

        if (content.size() != 0 && pageable.getPageSize() > content.size()) {
            return new PageImpl<>(content, pageable, pageable.getOffset() + content.size());
        }

        return new PageImpl<>(content, pageable, totalSupplier.getAsLong());
    }

jpa生成count語(yǔ)句的邏輯也非常簡(jiǎn)單，就是做了一個(gè)正則來(lái)替換查詢語(yǔ)句，對(duì)于我這個(gè)查詢，count語(yǔ)句就變成了上面那個(gè)樣子，jpa也考慮到這樣生成的sql可能是有問(wèn)題的，所以@Query注解提供了countQuery屬性，查詢語(yǔ)句比較復(fù)雜的時(shí)候可以自己寫一個(gè)count語(yǔ)句給jpa使用。