数据库的SQL到底应该怎么写？

前言

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=[http://asset.felord.cn/cup1.png, http://asset.felord.cn/cup2.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. 总结

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

数据库中的数据是为众多用户所 共享其信息而建立的，已经摆脱了具体 程序的限制和制约。不同的用户可以按各自的用法使用数据库中的数据；多个用户可以同时共享数据库中的数据资源，即不同的用户可以同时存取数据库中的同一个数据。数据共享性不仅满足了各用户对信息内容的要求，同时也满足了各用户之间信息通信的要求。