Как написать SQL для пейджинга «один ко многим»?

Spring Boot SQL

1. Введение

MySQLПостраничное разбиение данных «один ко многим» является очень распространенным требованием.Например, мы хотим запросить информацию о продукте и изображении продукта. Но многие люди столкнутся здесь с непониманием нумерации страниц и получат неправильные результаты. Давайте проанализируем и решим эту проблему сегодня.

2. Анализ проблемы

Сначала мы создаем простую таблицу продуктов и соответствующую таблицу отношений изображений продуктов, которая представляет собой отношение «один ко многим» между ними:

一对多关系

Затем я написал несколько продуктов и изображения, соответствующие этим продуктам.С помощью следующего запроса на левое соединение мы видим, что между ними существует очевидная связь «один ко многим»:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM PRODUCT_INFO P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

所有的一对多结果

Согласно традиционному мышлению, наш оператор пейджинга будет записан так:

    <resultMap id="ProductDTO" type="cn.felord.mybatis.entity.ProductDTO">
        <id property="productId" column="product_id"/>
        <result property="prodName" column="prod_name"/>
        <collection property="imageUrls"  ofType="string">
            <result column="image_url"/>
        </collection>
    </resultMap>

    <select id="page" resultMap="ProductDTO">
        SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL
        FROM PRODUCT_INFO P
                 LEFT JOIN PRODUCT_IMAGE PI
                           ON P.PRODUCT_ID = PI.PRODUCT_ID
        LIMIT #{current},#{size}
    </select>

Когда я прошел, как и ожидалось(0，2)Хотите получить данные первых двух продуктов, результат не тот, что я ожидал:

2020-06-21 23:35:54.515 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==>  Preparing: SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID limit ?,? 
2020-06-21 23:35:54.541 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==> Parameters: 0(Long), 2(Long)
2020-06-21 23:35:54.565 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : <==      Total: 2
page = [ProductDTO{productId=1, prodName='杯子', imageUrls=[http://asset.felord.cn/cup1.png, http://asset.felord.cn/cup2.png]}]

Две части данных, которые я ожидал, были чашки и тетради, но оказалось, что это только одна. Получается, что при отображении один-ко-многим результирующий набор будет выводиться согласно стороне многих (ожидая 4 куска данных, на самом деле будет 7 кусков), а первые два куска данных будут только показать данные стакана (как показано на рисунке выше) Результат только один, поэтому пагинация не совпадет. Итак, как мы можем добиться ожидаемого эффекта пейджинга?

3. Правильный путь

Правильная идея состоит в том, чтобы сначала разбить первичную таблицу на страницы, а затем связать вторичную таблицу для запроса.

рамки в сторону, нашиSQLСначала вы должны выполнить запрос на разбивку на страницы в таблице продуктов, а затем запросить левую связанную таблицу изображений:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM (SELECT PRODUCT_ID, PROD_NAME
      FROM PRODUCT_INFO
      LIMIT #{current},#{size}) P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

Преимущество этого способа письма в том, что он более универсален. ноMyBatisЭто обеспечивает относительно элегантный способ, и идея по-прежнему остается идеей, упомянутой в начале. Просто нам нужно изменить вышеуказанноеMybatis XMLКонфигурация:

<resultMap id="ProductDTO" type="cn.felord.mybatis.entity.ProductDTO">
    <id property="productId" column="product_id"/>
    <result property="prodName" column="prod_name"/>
     <!-- 利用 collection 标签提供的 select 特性 和 column   -->
    <collection property="imageUrls" ofType="string" select="selectImagesByProductId" column="product_id"/>
</resultMap>
<!-- 先查询主表的分页数据    -->
<select id="page" resultMap="ProductDTO">
    SELECT PRODUCT_ID, PROD_NAME
    FROM PRODUCT_INFO
    LIMIT #{current},#{size}
</select>
<!--根据productId 查询对应的图片-->
<select id="selectImagesByProductId" resultType="string">
    SELECT IMAGE_URL
    FROM PRODUCT_IMAGE
    WHERE PRODUCT_ID = #{productId}
</select>

4. Резюме

Пейджинг в большинстве случаев прост, но у «один ко многим» есть несколько мелких ловушек. Как только мы понимаем механизм, решить его несложно. Конечно, если у вас есть лучшее решение, вы можете оставить сообщение для обсуждения и мозгового штурма. Уделять больше внимания:Код Фермер Маленький Толстый Брат, чтобы получить дополнительные советы по разработке.

关注公众号：Felordcn获取更多资讯

Личный блог: https://felord.cn

Категории