mysql使用mybatis,一对多情况下,根据主表个数分页,一次查询出数据

1 业务详情

rt,两张表user,score,一对多,以user为主表,需要根据user来进行分页查询.想要获取男性,以pageNum=3分页的学生信息和分数信息

2 表

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `score` int(1) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

3 坑

需求: 查询男性,pageSize=3
在mybatis中,使用resultMap 进行映射,此处使用的是直接映射子表结果集,而不是使用select,防止出现n+1次查询问题.此查询问题,不懂的可以google


① 下面sql,为坑

select u.*,s.* from user u 
left join score s on u.id=s.user_id
where u.gender='男'
limit 0,3;

很明显,对于小白来说,很大可能这么写,但是这样写,是获取不到想要结果的…

② why
其实很简单,最终获取到的是1对多的集,limit是对最终的临时表进行分页,即对最终一对多形成的结果进行分页,而不是根据主表数据分页.

4 解决方案

sql如下:

select u.*,s.* from 
(select u1.* from user u1 where u1.gender='男' limit 0,3)  u 
left join score s on u.id=s.user_id

结果:

即,先对你想要分页的表进行分页和条件之后,再进行关联查询.这样的结果经过mybatis的resultMap映射后,即可获取到根据user分页的结果

5 注意

当你使用4的sql需要进行排序的时候,子查询需要排序,外面的查询也需要排序
错误sql:

select u.*,s.* from 
(select u1.* from user u1 where u1.gender='男' order by u1.id desc limit 0,3)  u 
left join score s on u.id=s.user_id

错误sql结果:

正确sql:

select u.*,s.* from 
(select u1.* from user u1 where u1.gender='男' order by u1.id desc limit 0,3)  u 
left join score s on u.id=s.user_id

order by u.id desc

正确sql结果: