golang gorm 增删改查以及使用原生SQL（以操作mysql为例）

gorm + mysql增删改查

model定义

package _caseimport "gorm.io/gorm"func init() {DB.Migrator().AutoMigrate(Teacher{}, Course{})
}
type Roles []stringtype Teacher struct {gorm.ModelName     string   `gorm:"size:256"`Email    string   `gorm:"size:256"`Salary   float64  `gorm:"scale:2;precision:7"`   // 指定小数部分宽度为2，列宽度为7. 列宽：【整数部分+小数部分的总长度】【不含小数点】Age      uint8    `gorm:"check:age>30"`Birthday int64    `gorm:"serializer:unixtime;type:time"`  // 反序列化方式 unixtime， 类型为timeRoles    Roles    `gorm:"serializer:json"`JobInfo  Job      `gorm:"embedded;embeddedPrefix:job_"`   // 嵌套字段， 嵌入字段的列名前缀job_JobInfo2 Job      `gorm:"type:bytes;serializer:gob"`      // 字节流类型，gob反序列化,go自己的序列化方法,跨语言项目的时候，不建议用
}type Job struct {Title    stringLocation string
}type Course struct {gorm.ModelName   string  `gorm:"size:256"`Price  float64 `gorm:"scale:2;precision:7"`UserID uint    `gorm:"type:int"`
}

数据库连接并设置连接池

package _caseimport ("gorm.io/driver/mysql""gorm.io/gorm""gorm.io/gorm/logger""log""time"
)var DB *gorm.DBvar dsn = "root:123456@tcp(10.74.18.61:3306)/mydb?charset=utf8mb4&parseTime=True&loc=Local"func init() {var err error// 每次调用返回的都是DB对象，这是支持链式调用的DB, err = gorm.Open(mysql.New(mysql.Config{DSN: dsn,DefaultStringSize: 256,}), &gorm.Config{Logger: logger.Default.LogMode(logger.Info),//开启预编译，提高后续调用速度//开启预编译的情况下，不支持嵌套事务PrepareStmt: true,})if err != nil {log.Println(err)return}setPool(DB)
}// 连接池设置
func setPool(db *gorm.DB) {sqlDB, err := db.DB()if err != nil {log.Println(err)return}// 连接存活最大时长sqlDB.SetConnMaxLifetime(time.Hour)// 最大空闲连接数sqlDB.SetMaxIdleConns(5)// 最大连接打开数sqlDB.SetMaxOpenConns(10)}

假数据定义

var teacherTemp = Teacher{Name: "kk",Age: 40,Salary: 1234.22,Email: "[e-mail]",Birthday: time.Now().Unix(),Roles: Roles{"普通用户", "讲师"},JobInfo: Job{Title: "教授",Location: "九龙湖",},JobInfo2: Job{Title: "教授",Location: "九龙湖",},
}

插入数据

• 插入一条/多条记录

t1 := teacherTemp 
res := DB.Create(&t) // 指针传入

• 正向选择, 选择某些字段插入 【gorm model里的不包含】

t1 := teacherTemp
res = DB.Select("name", "age").Create(&t1)

• 反向选择, 排除某些字段插入（忽略大小写

t2 := teacherTemp
res = DB.Omit("email", "Birthday").Create(&t2)

• 批量，按batchsize插入

var teachers = []Teacher{{Name: "qq", Age: 50}, {Name: "pp", Age: 60}, {Name: "gg", Age: 55}, {Name: "mm", Age: 56}}
DB.CreateInBatches(teachers, len(teachers))  // 或者直接DB.Create(teachers)

• 查找第一个匹配的记录并返回，若无匹配记录则插入

t3 := teacherTemp
t3.Name = "oo"
out := Teacher{}
res = DB.Attrs(t3).FirstOrCreate(&out, Teacher{Name: "oo"})

删除数据

gorm采用的是软删除，删除语句为update，去更新delete_at字段

• 根据ID删除

DB.Delete(&Teacher{Model: gorm.Model{ID: 9}})

gorm输出语句（软删除）

 UPDATE `teachers` SET `deleted_at`='2023-10-09 18:51:43.901' WHERE `teachers`.`id` = 9 AND `teachers`.`deleted_at` IS NULL

• 根据条件删除, 批量删除

DB.Where("name like ?", "yuan*").Delete(&Teacher{})
DB.Delete(&Teacher{}, []int{3,4,5,})

• 根据主键删除（主键不一定是gorm.Model的ID）

DB.Delete(&Teacher{}, 1)
DB.Delete(&Teacher{}, []int{3,4,5,})

更新数据

• 全部更新

teacher := Teacher{}
DB.First(&teacher)
// 更新
teacher.Age = 66
teacher.Name = "wang"
// 更新所有 save 会将所有字段保存到数据库, 无论字段有没有被改
DB.Save(teacher)

• 更新单行数据某个列

DB.Model(&Teacher{}).Where("id = ?", teacher.ID).Update("name", "yuan")

• 通过model指定条件更新, 只能指定ID

DB.Model(&Teacher{Model:gorm.Model{ID: teacher.ID}}).Update("name", "yuan")

• 更新单行多个列

// 更新单行多个列 结构体方式
DB.Model(&Teacher{}).Where("id = ?", teacher.ID).Updates(Teacher{Name: "updatayuan", Age: 40})
// 更新单行多个列 map方式
DB.Model(&Teacher{}).Where("id = ?", teacher.ID).Updates(map[string]interface{}{"name": "updateY", "age": 40})

• 批量更新 多行数据

DB.Model(&Teacher{}).Where("age > ?", 60).Updates(Teacher{Email: "[e-mail]"}) // 用map也行

• 选定/排除某些字段更新

// 选定更新某些字段
DB.Model(&Teacher{}).Where("id = ?", 5).Select("name", "age").Updates(teacher)
// 排除某些字段 更新
DB.Model(&Teacher{}).Where("id = ?", 5).Omit("name", "age").Updates(teacher)

查询

简单查询

• 单记录查询

func GetOnce() {t := Teacher{}// 查询一条// 获取主键排序第一条DB.First(&t)// 无排序规则 取第一条t = Teacher{}DB.Take(&t)// 主键排序最后一条t = Teacher{}DB.Last(&t)//查询结果填充到集合中result := map[string]interface{}{}// 可能会有特殊类型不好处理，无法完成类型转换，可以忽略一些字段DB.Model(&Teacher{}).Omit("Birthday", "Roles", "JobInfo2").First(&result)// 基于表名 查询记录result = map[string]interface{}{}DB.Table("teachers").Take(&result)}

• 根据字符串条件获取记录

func GetByStrCond() {t := Teacher{}var teaches []TeacherDB.Find(&teaches, "name IN ?", []string{"yuan", "oo", "gg"})DB.Where("name = ?", "gg").First(&t)DB.Where("name IN ?", []string{"yuan", "oo"}).Find(&teaches)
}

• 通过结构体或集合指定条件查询

func GetByStructOrMapCond() {var teachers []Teachert := Teacher{}//structDB.Find(&teachers, Teacher{Name: "oo", Age: 40})// mapDB.Find(&teachers, map[string]interface{}{"Name": "gg", "Age": 40})DB.Where(Teacher{Name: "yuan", Age: 40}).First(&t)DB.Where(map[string]interface{}{"Name": "qq", "Age": 40}).Find(&teachers)DB.Where([]int{10, 11, 12}).Find(&teachers)//指定查询条件使用的字段DB.Where(Teacher{Name: "cc"}, "name", "age").Find(&teachers)
}

复合查询

• 复杂条件查询

var teachers []Teacher
// 复合查询 offset为跳过几个记录,分页会用
DB.Where(DB.Where("name = ?", "yuan").Or("name = ?", "oo")).
Where("age > ?", 40).Order("id desc").Offset(1).Limit(10).Find(&teachers)// 查询返回行rows
rows, err := DB.Model(&Teacher{}).Select("id", "name").Where(DB.Where("name in ?", []string{"oo", "qq"})).
Order("id desc").Offset(1).Limit(10).Rows()if err != nil {log.Fatal(err)
}
defer rows.Close()
// 扫描row
for rows.Next() {id := 0name := ""err = rows.Scan(&id, &name)if err != nil {continue}fmt.Println(id, name)
}

• 分组聚合

// 分组聚合
type Result struct {Count intAge   int
}
list := []Result{}
DB.Model(&Teacher{}).Select("count(*) as count", "age").Not("name = ?", "yuan").
Group("age").Having("count > ?", 2).Rows()fmt.Println(list)
// 根据年龄分组，过滤掉name为yuan的数据，保留对应分组count数大于2的结果
rows2, err := DB.Model(&Teacher{}).Select("count(*) as count", "age").Not("name = ?", "yuan").
Group("age").Having("count > ?", 2).Rows()
if err != nil {log.Fatal(err)
}
defer rows2.Close()for rows2.Next() {count := 0age := 0err = rows2.Scan(&count, &age)if err != nil {continue}fmt.Println(count, age)
}

使用原生SQL查询

基本exec和raw就够用了

func NativeSql() {var teacher Teachervar list []Teacher//查询DB.Raw("select id,name,age from teachers where name = ?", "yuan").Scan(&teacher)fmt.Println(teacher)DB.Raw("select id,name,age from teachers where name = ?", "yuan").Scan(&list)fmt.Println(list)teacher = Teacher{}DB.Raw("select id,name,age from teachers where name = ?", "yuan").Find(&teacher)fmt.Println(teacher)//更新res := DB.Exec("update teachers set age = @age where name = @name", sql.Named("age", 22), sql.Named("name", "yuan"))fmt.Println(res.RowsAffected, res.Error)}