Go语言中实现MySQL分页查询的正确姿势

// 设置 GET /subnets 路由，支持 page 和 limit 参数
ws.Route(ws.GET("/subnets").
To(sc.ListSubnet).
Doc("List subnets authorized to the login user.").
Param(ws.QueryParameter(query.ParameterPage, "page").Required(false).DataFormat("page=%d").DefaultValue("page=1")).
Param(ws.QueryParameter(query.ParameterLimit, "limit").Required(false)).
Returns(http.StatusOK, api.StatusOK, api.ListResult{}))

// 处理 subnet 列表查询请求
func (v *SubController) ListSubnet(request *restful.Request, response *restful.Response) {
    // 1. 获取用户名等身份信息
    username := request.Attribute(filters.UserName).(string)

    // 2. 实例化 Service
    subnetService := &service.SubnetService{}
    
    // 3. 解析 Page 和 Limit 参数
    query := query.ParseQueryParameter(request)
    
    // 4. 调用 Service 层执行逻辑
    result, err := subnetService.ListSubnet(query, v.Db, username)
    if err != nil {
        api.HandleError(response, request, err)
        return
    }
    
    // 5. 写入响应
    response.WriteEntity(result)
}

// 服务层：委托 Mapper 执行实际查询
func (ss *SubnetService) ListSubnet(query *query.Query, db *sql.DB, userName string) (*api.ListResult, error) {
    sm := mapper.NewSubnetMapper(db)
    // 调用 Mapper 获取分页数据
    sb, err := sm.SearchAllByUserName(query, userName)
    return sb, err
}

// Mapper实现：带总数统计的分页查询
func (s *SubnetMapper) SearchAllByUserName(query *query.Query, userName string) (*api.ListResult, error) {

    // 1. 查询总记录数
    totalRow, err := s.Db.Query("SELECT COUNT(*) from t_subnet ,t_vpc where t_vpc.id = t_subnet.vpcid and username = ?", userName)
    if err != nil {
        klog.Error("query orders count error", err)
        return nil, err
    }
    
    var total int
    for totalRow.Next() {
        err := totalRow.Scan(&total)
        if err != nil {
            klog.Error("scan count error", err)
            continue
        }
    }
    totalRow.Close()

    // 2. 查询当前页数据 (LIMIT offset, limit)
    // 注意：此处应确保 SearchAllByUserNameSql 包含正确的 WHERE 和 JOIN 条件
    rows, err := s.Db.Query(SearchAllByUserNameSql, userName, query.Pagination.Limit, query.Pagination.Offset)
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()
    
    if err != nil {
        klog.Error("query subnet error", err)
        return nil, err
    }

    // 3. 扫描结果集
    items := make([]interface{}, 0)
    for rows.Next() {
        ss := new(vpc.SubnetItem)
        // 确保 Scan 的参数顺序与 SELECT 列的顺序一致
        err = rows.Scan(&ss.VpcName, &ss.VpcNetwork, &ss.SubnetName, &ss.SubnetNetwork, &ss.CreateTime)
        if err != nil {
            klog.Error("scan row error", err)
            break
        }
        items = append(items, *ss)
    }

    // 4. 组装返回结构
    return &api.ListResult{
        TotalItems: total,
        Items:      items,
    }, nil
}