文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-12-11 20:26

关注

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

2. 问题分析

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

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

 

  1. SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL 
  2. FROM PRODUCT_INFO P 
  3.          LEFT JOIN PRODUCT_IMAGE PI 
  4.                    ON P.PRODUCT_ID = PI.PRODUCT_ID 

 

 

 

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

  1. "ProductDTO" type="cn.felord.mybatis.entity.ProductDTO"
  2.     "productId" column="product_id"/> 
  3.     "prodName" column="prod_name"/> 
  4.     "imageUrls"  ofType="string"
  5.         column="image_url"/> 
  6.      
  7.  
  8.  
  9. <select id="page" resultMap="ProductDTO"
  10.     SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL 
  11.     FROM PRODUCT_INFO P 
  12.              LEFT JOIN PRODUCT_IMAGE PI 
  13.                        ON P.PRODUCT_ID = PI.PRODUCT_ID 
  14.     LIMIT #{current},#{size
  15. select>     

当我按照预想传入了(0,2)想拿到前两个产品的数据,结果并不是我期望的:

 

  1. 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 ?,?  
  2. 2020-06-21 23:35:54.541 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==> Parameters: 0(Long), 2(Long) 
  3. 2020-06-21 23:35:54.565 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : <==      Total: 2 
  4. page = [ProductDTO{productId=1, prodName='杯子', imageUrls=[https://www.528045.com/file/upload/202412/11/iw5fc5t0lt3.png, https://www.528045.com/file/upload/202412/11/mla3r23fmxj.png]}] 

我期望的两条数据是杯子和笔记本,但是结果却只有一条。原来当一对多映射时结果集会按照多的一侧进行输出(期望4条数据,实际上会有7条),而前两条展示的只会是杯子的数据(如上图),合并后就只有一条结果了,这样分页就对不上了。那么如何才能达到我们期望的分页效果呢?

3. 正确的方式

正确的思路是应该先对主表进行分页,再关联从表进行查询。

抛开框架,我们的SQL应该先对产品表进行分页查询然后再左关联图片表进行查询:

 

  1. SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL 
  2. FROM (SELECT PRODUCT_ID, PROD_NAME 
  3.       FROM PRODUCT_INFO 
  4.       LIMIT #{current},#{size}) P 
  5.          LEFT JOIN PRODUCT_IMAGE PI 
  6.                    ON P.PRODUCT_ID = PI.PRODUCT_ID 

这种写法的好处就是通用性强一些。但是MyBatis提供了一个相对优雅的路子,思路依然是开头所说的思路。只不过我们需要改造上面的Mybatis XML配置:

  1. "ProductDTO" type="cn.felord.mybatis.entity.ProductDTO"
  2.     "productId" column="product_id"/> 
  3.     "prodName" column="prod_name"/> 
  4.      -- 利用 collection 标签提供的 select 特性 和 column   --> 
  5.     "imageUrls" ofType="string" select="selectImagesByProductId" column="product_id"/> 
  6.  
  7. -- 先查询主表的分页数据    --> 
  8. <select id="page" resultMap="ProductDTO"
  9.     SELECT PRODUCT_ID, PROD_NAME 
  10.     FROM PRODUCT_INFO 
  11.     LIMIT #{current},#{size
  12. select
  13. --根据productId 查询对应的图片--> 
  14. <select id="selectImagesByProductId" resultType="string"
  15.     SELECT IMAGE_URL 
  16.     FROM PRODUCT_IMAGE 
  17.     WHERE PRODUCT_ID = #{productId} 
  18. select

4. 总结

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

来源:segmentfault.com内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯