Golang gorm 常用查找方法

测试数据准备

package main

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

func getConnect() *gorm.DB {
	dsn := "root:Xrx@1994113@tcp(127.0.0.1:3306)/proxy?charset=utf8mb4&parseTime=True&loc=Local"
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 日志级别
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  false,       // 禁用彩色打印
		},
	)
	open, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: true,
		Logger:                                   newLogger,
	})
	if err != nil {
		panic(err)
	}
	return open
}

func CreateTable(connect *gorm.DB, table interface{}) {
	err := connect.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET UTF8").AutoMigrate(table)
	if err != nil {
		panic(err)
	}
}

type User struct {
	gorm.Model
	Name    string `gorm:"column:name;type:varchar(20);default:xie;comment:名称"`
	Age     uint   `gorm:"column:age;type:int unsigned;default:18;comment:年龄"`
	Company Company
}

func (u User) TableName() string {
	return "user"
}

type Company struct {
	gorm.Model
	UserId int    `gorm:"type:int not null"`
	Name   string `gorm:"type:varchar(30) not null"`
}

func (u Company) TableName() string {
	return "company"
}

func main() {
	connect := getConnect()
	CreateTable(connect, &User{})
	CreateTable(connect, &Company{})

	//插入测试数据
	users := []User{
		{Name: "xie", Age: 27, Company: Company{
			Name: "company_1",
		}},
		{Name: "rui", Age: 28, Company: Company{
			Name: "company_2",
		}},
		{Name: "xiang", Age: 29, Company: Company{
			Name: "company_3",
		}},
	}

	db := connect.Create(&users)
	fmt.Println(db.RowsAffected)
}

单条查找

使用First，Take，Last 进行单条查找，First根据主键正序，Last 根据主键倒序

func main() {
	connect := getConnect()

	// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` LIMIT 1
	// First 方法自带主键正序排
	user := User{}
	connect.First(&user)
	fmt.Println(user.Age)

	//SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL LIMIT 1
	user = User{}
	connect.Take(&user)
	fmt.Println(user.Age)

	// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` DESC LIMIT 1
	// 按主键倒序
	user = User{}
	connect.Last(&user)
	fmt.Println(user.Age)
}

多条查找

使用Find进行数据查询，结果以[]struct来接收

func main() {
	connect := getConnect()

	users := []User{}

	//SELECT * FROM `user` WHERE `user`.`name` = 'xie' AND `user`.`age` = 27 AND `user`.`deleted_at` IS NULL
	connect.Where(User{}).Find(&users)

	for _, item := range users {
		fmt.Println(item.Name)
	}
}

按主键查找

Find,First,Last,Take 第二个参数都支持传入int,[]int,string

	connect := getConnect()
	users := []User{}

	//传入int 即 id =
	//SELECT * FROM `user` WHERE `user`.`id` = 3 AND `user`.`deleted_at` IS NULL
	connect.Find(&users, 3)
	fmt.Println(users[0].Name)

	//传入[]int id in
	//SELECT * FROM `user` WHERE `user`.`id` IN (1,3) AND `user`.`deleted_at` IS NULL
	connect.Find(&users, []int{1, 3})
	for _, item := range users {
		fmt.Println(item.Name)
	}

不建议传入string，或者换一种方式传入string

	connect := getConnect()

	user := User{}

	// 不推荐传string，有sql注入风险
	// SELECT * FROM `user` WHERE (66 or 1 = 1) AND `user`.`deleted_at` IS NULL
	connect.Find(&user, "66 or 1 = 1")

	//要传入string，可以这么传
	//SELECT * FROM `user` WHERE id = '66 or 1 = 1' AND `user`.`deleted_at` IS NULL AND `user`.`id` = 1
	connect.Find(&user, "id = ?", "66 or 1 = 1")

返回的响应值

这里将查找返回分为两部分，第一部分为响应值，即调用查找返回的grom.DB。第二部分为数据结果集，通过&struct写入到结构体中。

func main() {
	connect := getConnect()

	user := User{}
	result := connect.First(&user)

	//判断影响条数
	fmt.Println(result.RowsAffected)
	//查询是否发生错误
	fmt.Println(result.Error)
	//如果查询不到数据，那么返回的错误应该是 gorm.ErrRecordNotFound
	fmt.Println(errors.Is(result.Error, gorm.ErrRecordNotFound))
}

以结构体作为查询条件

可以将struct作为查询。局限性在于只能精确值查询，and查询。
优点在于不用关心数据表中的具体名称

func main() {
	connect := getConnect()

	user := User{}

	//SELECT * FROM `user` WHERE `user`.`name` = 'xie' AND `user`.`age` = 27 AND `user`.`deleted_at` IS NULL
	connect.Where(User{
		Name: "xie",
		Age:  29,
	}).Find(&user)
}

string 条件

和pdo的预编译有点像

	connect := getConnect()

	user := User{}

	// SELECT * FROM `user` WHERE (name = 'xie' and age = 27) AND `user`.`deleted_at` IS NULL
    connect.Where("name = ? and age = ?","xie",27).Find(&user)

限制查询字段

通过seelct设置查询哪些字段

	connect := getConnect()

	user := User{}

	// SELECT id,name,age FROM `user` WHERE `user`.`name` = 'xie' AND `user`.`age` = 27 AND `user`.`deleted_at` IS NULL
    connect.Select("id,name,age").Where(User{
		Name:  "xie",
		Age:   27,
	}).Find(&user)

排序

通过order进行排序

	connect := getConnect()

	user := User{}

	// SELECT id,name,age FROM `user` WHERE `user`.`name` = 'xie' AND `user`.`age` = 27 AND `user`.`deleted_at` IS NULL ORDER BY age desc

    connect.Select("id,name,age").Where(User{
		Name:  "xie",
		Age:   27,
	}).Order("age desc").Find(&user)

or

	connect := getConnect()

	user := User{}
	
    // SELECT * FROM `user` WHERE (`user`.`name` = 'xie' OR `user`.`name` = 'rui') AND `user`.`deleted_at` IS NULL
    connect.Where(User{Name:"xie"}).Or(User{Name:"rui"}).Find(&user)

分页

使用 limit + offset 分页

	connect := getConnect()

	user := User{}

    // SELECT * FROM `user` WHERE (`user`.`name` = 'xie' OR `user`.`name` = 'rui') AND `user`.`deleted_at` IS NULL LIMIT 1 OFFSET 10
    connect.Where(User{Name:"xie"}).Or(User{Name:"rui"}).Limit(1).Offset(10).Find(&user)

group by & having

	connect := getConnect()

	user := User{}

    //  SELECT `name` FROM `user` WHERE `user`.`name` = 'xie' AND `user`.`deleted_at` IS NULL GROUP BY `name` HAVING count(*) > 1
    connect.Select("name").Where(User{Name:"xie"}).Group("name").Having("count(*) > 1").Find(&user)

distinct

	connect := getConnect()

	user := User{}
	//  SELECT DISTINCT `name` FROM `user` WHERE `user`.`deleted_at` IS NULL
	connect.Distinct("name").Find(&user)

join

	connect := getConnect()

	user := User{}
	//SELECT user.name,user2.name as user2_name FROM `user` left join user as user2 on user.id = user2.id WHERE `user`.`deleted_at` IS NULL
	connect.Select("user.name,user2.name as user2_name").Joins("left join user as user2 on user.id = user2.id").Find(&user)
	

关联预加载

joins(“关系”),关系是在结构体中定义的

	connect := getConnect()
	user := []User{}
	// SELECT `user`.`id`,`user`.`created_at`,`user`.`updated_at`,`user`.`deleted_at`,`user`.`name`,`user`.`age`,`Company`.`id` AS `Company__id`,`Company`.`created_at` AS `Company__created_at`,`Company`.`updated_at` AS `Company__updated_at`,`Company`.`deleted_at` AS `Company__deleted_at`,`Company`.`user_id` AS `Company__user_id`,`Company`.`name` AS `Company__name` FROM `user` LEFT JOIN `company` `Company` ON `user`.`id` = `Company`.`user_id` AND `Company`.`deleted_at` IS NULL WHERE `user`.`deleted_at` IS NULL
	connect.Joins("Company").Find(&user)

scan

将不通过model调用的sql语句结果写入struct中

	connect := getConnect()
	user := []User{}
	//select * from user
	connect.Raw("select * from user").Scan(&user)
	fmt.Println(user[0].Name)
	//SELECT * FROM `user`
	connect.Table("user").Scan(&user)
	fmt.Println(user[0].Name)

查询包含软删除的数据

	connect := getConnect()
	user := []User{}
	// SELECT * FROM `user`
	connect.Unscoped().Find(&user)