1. 前言
MySQL一对多的数据分页是非常常见的需求,比如我们要查询商品和商品的图片信息。但是很多人会在这里遇到分页的误区,得到不正确的结果。今天就来分析并解决这个问题。
2. 问题分析
我们先创建一个简单商品表和对应的商品图片关系表,它们之间是一对多的关系:
![ffbd63d0e97091eacc89ef8f1ae73745.png](https://i-blog.csdnimg.cn/blog_migrate/e59069bb44a1cf68aaa47b20d389b4b7.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
![d589ab4a3481adf980e0713005946e30.png](https://i-blog.csdnimg.cn/blog_migrate/23c58242b981e1aa0d12eac4913593f3.png)
按照传统的思维我们的分页语句会这么写:
<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 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>
<select id="selectImagesByProductId" resultType="string">
SELECT IMAGE_URL
FROM PRODUCT_IMAGE
WHERE PRODUCT_ID = #{productId}
select>
4. 总结
大部分情况下分页是很容易的,但是一对多还是有一些小小的陷阱的。一旦我们了解了其中的机制,也并不难解决。多多关注:码农小胖哥,获取更多开发技巧。
往期推荐:
JSON类库Jackson优雅序列化Java枚举类
2020-06-21
![4aa2e4002b2bb6c2d8cdd3c6a1fb5458.png](https://i-blog.csdnimg.cn/blog_migrate/d7d173ef8f7df4f1524f771c2e1ca5a6.jpeg)
刷题2个月,终于进了梦寐以求的大厂,数据结构和算法太TM重要了!
2020-06-19
![02ebc10a0623987c8af056fd71dcb408.png](https://i-blog.csdnimg.cn/blog_migrate/d932e5bc82e2adb4d99ec87519e07b51.jpeg)
利用Redis的Geo功能实现查找附近的位置
2020-06-18
![822c4d29dbf29a08595688be488e200b.png](https://i-blog.csdnimg.cn/blog_migrate/c084a7a10683f3463b9c28ed0a0a4676.jpeg)
![c06447e8c9f8af902119883cbf04aa04.gif](https://i-blog.csdnimg.cn/blog_migrate/81febf9f47528d6b18c1d87092678114.gif)