SQL优化之解决一次翻页数据重复的问题

最近踩了一个SQL的坑；

💡

背景：

对于分页一直是采用：page、size、total在前端控制

后端接受page和size然后进行offset偏移取数据

对于这个SQL：


// 获取所有的 Post 和 User 信息，并计算相关的点赞、收藏、评论数以及用户行为
	postsResults := make([]response.PostResult, 0)
	err = query.Table("posts").
		Select(`
			posts.*,
			users.*,
			COUNT(DISTINCT likes.id) as like_num,
			COUNT(DISTINCT favorites.id) as favourite_num,
			COUNT(DISTINCT comments.id) as comment_num,
			COUNT(DISTINCT shares.id) as share_num,
			MAX(CASE WHEN likes.user_id = ? THEN 1 ELSE 0 END) as liked,
			MAX(CASE WHEN favorites.user_id = ? THEN 1 ELSE 0 END) as favourited
		`, userId, userId).
		Joins("JOIN users ON posts.user_id = users.user_id COLLATE utf8mb4_unicode_ci").
		Joins("LEFT JOIN likes ON posts.id = likes.post_id").
		Joins("LEFT JOIN favorites ON posts.id = favorites.post_id").
		Joins("LEFT JOIN comments ON posts.id = comments.post_id").
		Joins("LEFT JOIN shares ON posts.id = shares.post_id").
		Where("posts.user_id NOT IN (SELECT block_user_id FROM blocks WHERE blocks.user_id = ?)", userId).
		Group("posts.id, users.id").
		Order("posts.id DESC").
		Count(&total).
		Limit(int(size)).
		Offset(int(size * page)).
		Scan(&postsResults).Error

这个sql虽然有些复杂，但是逻辑也相对清晰，其实并不是sql本身出了问题，而是当系统里有用户新发布了帖子，那么上一页的数据有可能在下一页也出现，逻辑如下：

之前自己一直没有遇到这个问题是，表数据没有那么实时性，所以按照page和size一般是没有问题的；

那么如何解决这个问题？

其实也简单，我只要记号上次查询到了哪里，增加一个游标：


	query := global.App.DB

	var total int64
	userId := params.UserId
	page := params.Page //这是游标
	size := params.Size

	const FirstQueryCursor = -1

	if page == FirstQueryCursor {
		time.Sleep(1 * time.Second)
	}
	
	var postsResults []response.PostResult

	baseQuery := query.Table("posts").
		Select(`
            posts.*,
            users.*,
            COUNT(DISTINCT likes.id) as like_num,
            COUNT(DISTINCT favorites.id) as favourite_num,
            COUNT(DISTINCT comments.id) as comment_num,
            COUNT(DISTINCT shares.id) as share_num,
            MAX(CASE WHEN likes.user_id = ? THEN 1 ELSE 0 END) as liked,
            MAX(CASE WHEN favorites.user_id = ? THEN 1 ELSE 0 END) as favourited
        `, userId, userId).
		Joins("JOIN users ON posts.user_id = users.user_id COLLATE utf8mb4_unicode_ci").
		Joins("LEFT JOIN likes ON posts.id = likes.post_id").
		Joins("LEFT JOIN favorites ON posts.id = favorites.post_id").
		Joins("LEFT JOIN comments ON posts.id = comments.post_id").
		Joins("LEFT JOIN shares ON posts.id = shares.post_id").
		Count(&total).
		Where("posts.user_id NOT IN (SELECT block_user_id FROM blocks WHERE blocks.user_id = ?)", userId)

	if page != FirstQueryCursor {
		baseQuery = baseQuery.Where("posts.id < ?", page)
	}

	err = baseQuery.Group("posts.id, users.id").
		Order("posts.id DESC").
		Limit(int(size)).
		Scan(&postsResults).Error

SQL优化 之 解决一次翻页数据重复的问题

SQL优化之解决一次翻页数据重复的问题