一对多分页查询mysql编写_一对多分页的SQL到底应该怎么写？

缺乏、安全感 2022-11-01 05:42 149阅读 0赞

![26331dd0b5207dddceeaafaf44520009.png][]

1. 前言

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

2. 问题分析

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

![ccb9b9a6d6d43faaf349d57355ce66cf.png][]

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

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

![b37efe18684c0a6dfc0e8b0c940ef68b.png][]

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

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\}

当我按照预想传入了(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配置：

SELECT PRODUCT\_ID, PROD\_NAME

FROM PRODUCT\_INFO

LIMIT \#\{current\},\#\{size\}

SELECT IMAGE\_URL

FROM PRODUCT\_IMAGE

WHERE PRODUCT\_ID = \#\{productId\}

4. 总结

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

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

[26331dd0b5207dddceeaafaf44520009.png]: /images/20221024/0598747730c546f890f863e51e98cd99.png
[ccb9b9a6d6d43faaf349d57355ce66cf.png]: /images/20221024/0c907dc3307e414f9a127fefc3affd06.png
[b37efe18684c0a6dfc0e8b0c940ef68b.png]: /images/20221024/4bc8ce1787a14aed9f057ea80a5fc82a.png