gorm查询条件中带有limit的同时查询count总条数

gorm查询条件中带有limit的同时查询count总条数

1、场景

假设我需要查询用户表中年龄是18岁的用户，且取第4-14条数据，则sql语句应为

SELECT * FROM `t_people` WHERE age = 18 ORDER BY username asc LIMIT 10 OFFSET 3

这时候我还需要拿一个没有limit和offset条件的数量，即

SELECT count(*) FROM `t_people` WHERE age = 18 ORDER BY username asc

这时候用gorm可以这么写

2、写法

①正常的查询代码

这样只能筛选出十条数据，而不能获取到总数据条数

err := db.Model(&TPeople{}).Where("age = ?", 18).
   Order("username asc").
   Limit(10).
   Offset(3).
   Scan(&peoples).
   Error

②通过给参数-1重置limit条件和offset条件，即可获取总数据条数

err := db.Model(&TPeople{}).Where("age = ?", 18).
   Order("username asc").
   Limit(10).
   Offset(3).
   Scan(&peoples).
   Limit(-1).
   Offset(-1).
   Count(&count).
   Error

完整代码请参考

创建表的sql语句

CREATE TABLE `t_people` (

`username` varchar(50) NOT NULL,

`sex` varbinary(100) NOT NULL,

`age` int NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

mysql.go文件

package mysqlManager

import (
   "fmt"
   "gorm.io/driver/mysql"
   "gorm.io/gorm"
   "gorm.io/gorm/logger"
)

type MysqlBase struct {
   //用于连接数据库
   Host     string `toml:"host"`
   Port     int    `toml:"port"`
   Db       string `toml:"db"`
   Username string `toml:"username"`
   Password string `toml:"password"`
   MaxConn  int    `toml:"maxConn"`
   MaxIdle  int    `toml:"maxIdle"`
   Timeout  int    `toml:"timeout"`
}

var ManagerMysql *mysqlManager

type mysqlManager struct {
   //初始化数据库,可初始化多个
   Database1 *gorm.DB
   Database2 *gorm.DB
   Database3 *gorm.DB
}

func makePool(obj MysqlBase) *gorm.DB {
   dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local&timeout=%ds",
      obj.Username, obj.Password, obj.Host, obj.Port, obj.Db, obj.Timeout)
   var db *gorm.DB
   var err error

   //打印SQL语句
   db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Info)})

   if err != nil {
      panic(fmt.Sprintf("connect mysql fail, error: %v", err))
   }

   sqlDB, err := db.DB()
   if err != nil {
      panic(fmt.Sprintf("make mysql pool fail, error:%v", err))
   }
   sqlDB.SetMaxOpenConns(obj.MaxConn)
   sqlDB.SetMaxIdleConns(obj.MaxIdle)
   return db
}

func init() {
   database1 := makePool(MysqlBase{
      Host:     "127.0.0.1",
      Port:     3306,
      Db:       "mydatabase",
      Username: "root",
      Password: "txh0916@TXH",
      MaxConn:  10,
      MaxIdle:  5,
      Timeout:  60,
   })
   ManagerMysql = &mysqlManager{
      Database1: database1,
   }

}

test.go文件

package main

import (
   "fmt"
   mysqlManager "rainbow-tan.com/m/v1"
)

type TPeople struct {
   Username string `gorm:"column:username" db:"username" json:"username" form:"username"`
   Sex      int64  `gorm:"column:sex" db:"sex" json:"sex" form:"sex"`
   Age      int64  `gorm:"column:age" db:"age" json:"age" form:"age"`
}

func (p TPeople) TableName() string {
   return "t_people"
}

func main() {
   var peoples []TPeople
   var count int64
   db := mysqlManager.ManagerMysql.Database1
   err := db.Model(&TPeople{}).Where("age = ?", 18).
      Order("username asc").
      Limit(10).
      Offset(3).
      Scan(&peoples).
      Limit(-1).
      Offset(-1).
      Count(&count).
      Error
   if err != nil {
      fmt.Println(fmt.Sprintf("错误信息:%s", err))
      return
   }
   fmt.Println(fmt.Sprintf("查询到的条数:%d, %+v", len(peoples), peoples))
   fmt.Println(fmt.Sprintf("总条数:%d", count))

}

参考 ： https://www.yuedun.wang/blogdetail/5e815979c9109f0f909b4cdd/