一对多分页的SQL到底应该怎么写?

  1. 云栖社区>
  2. 博客>
  3. 正文

一对多分页的SQL到底应该怎么写?

云栖号资讯小哥 2020-06-23 14:11:28 浏览550
展开阅读全文

云栖号资讯:【点击查看更多行业资讯
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!


1. 前言

MySQL一对多的数据分页是非常常见的需求,比如我们要查询商品和商品的图片信息。但是很多人会在这里遇到分页的误区,得到不正确的结果。今天就来分析并解决这个问题。

2. 问题分析

我们先创建一个简单商品表和对应的商品图片关系表,它们之间是一对多的关系:

1

然后我分别写入了一些商品和这些商品对应的图片,通过下面的左连接查询可以看出它们之间具有明显的一对多关系:

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

2

按照传统的思维我们的分页语句会这么写:

    <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. 总结

大部分情况下分页是很容易的,但是一对多还是有一些小小的陷阱的。一旦我们了解了其中的机制,也并不难解决。当然如果你有更好的解决方案可以留言讨论,集思广益。

【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.aliyun.com/live

立即加入社群,与专家面对面,及时了解课程最新动态!
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK

原文发布时间:2020-06-23
本文作者:码农小胖哥
本文来自:“掘金”,了解相关信息可以关注“掘金”

网友评论

登录后评论
0/500
评论
云栖号资讯小哥
+ 关注