Gorm框架-单表操作

单表操作

连接初始化

package main

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

var DB *gorm.DB
var mySqlLogger logger.Interface

func init() {
    username := "root"   //账号
    password := "123456" //密码
    host := "127.0.0.1"  //数据库地址，可以是Ip或者域名
    port := 3306         //数据库端口
    Dbname := "gorm"     //数据库名
    timeout := "10s"     //连接超时，10秒

    mySqlLogger = logger.Default.LogMode(logger.Info)

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local&timeout=%s",
       username, password, host, port, Dbname, timeout)
    //连接MYSQL, 获得DB类型实例，用于后面的数据库读写操作。
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
       // 日志配置
       Logger: mySqlLogger,
    })
    if err != nil {
       panic("连接数据库失败, error=" + err.Error())
    }
    // 连接成功
    DB = db
}

package main

type Students struct {
    ID     uint   `gorm:"size:3"`
    Name   string `gorm:"size:8"`
    Age    int    `gorm:"size:3"`
    Gender bool
    Email  *string `gorm:"size:32"`
}

func main() {
    Db.AutoMigrate(&Students{})
	// 这里进行具体操作...
}

添加操作

email := "223535324@qq.com"

s1 := Students{
    Name:   "张三",
    Age:    18,
    Gender: true,
    Email:  &email,
}
err := DB.Create(&s1).Error
fmt.Println(err)

有两个地方需要注意

指针类型是为了更好的存null类型，但是传值的时候，也记得传指针
Create接收的是一个指针，而不是值

批量插入操作

var studentsList []Students

for i := 0; i < 10; i++ {
    studentsList = append(studentsList, Students{
        Name:   fmt.Sprintf("张三%d", i+1),
        Age:    18 + i + 1,
        Gender: true,
        Email:  &email,
    })
}
err := DB.Create(&studentsList).Error
fmt.Println(err)

查询单条记录

var student Student
DB.Take(&student)
fmt.Println(student)

获取单条记录的方法很多，我们对比sql就很直观了

DB = DB.Session(&gorm.Session{Logger: Log})
var student Student
DB.Take(&student)  
// SELECT * FROM `students` LIMIT 1
DB.First(&student) 
// SELECT * FROM `students` ORDER BY `students`.`id` LIMIT 1
DB.Last(&student)  
// SELECT * FROM `students` ORDER BY `students`.`id` DESC LIMIT 1

根据主键查

var student Student
DB.Take(&student, 2) //第二个参数，默认会根据主键查询，可以是字符串，可以是数字
fmt.Println(student)

student = Student{} // 重新赋值
DB.Take(&student, "4")
fmt.Println(student)

自定义查询

var student Student
DB.Take(&student, "name = ?", "机器人27号") //使用?作为占位符，将查询的内容放入?
fmt.Println(student)

等价于

SELECT * FROM `students` WHERE name = '机器人27号' LIMIT 1

struct查询

var student Student
// 只能有一个主要值
student.ID = 2
//student.Name = "枫枫"
DB.Take(&student)
fmt.Println(student)

查询多条记录

var StudentList []Students
count := DB.Find(&StudentList).RowsAffected
fmt.Println(count)

data, _ := json.Marshal(StudentList)
fmt.Println(string(data))

更新操作

var Students Students
DB.Take(&Students, 11)
Students.Name = "张三"
DB.Save(&Students) // 全字段更新
// UPDATE `students` SET `name`='张三',`age`=0,`gender`=true,`email`='xxx@qq.com' WHERE `id` = 1

可以使用select选择要更新的字段

var student Student
DB.Take(&student)
student.Age = 21
// 全字段更新
DB.Select("age").Save(&student)
// UPDATE `students` SET `age`=21 WHERE `id` = 1

批量更新

var studentList []Students
DB.Find(&studentList, []int{12,13,14}).Update("gender", false)
// UPDATE `students` SET `gender`=false WHERE `students`.`id` IN (12,13,14) AND `id` IN (12,13,14)

更新多列

email := "xxx@qq.com"
DB.Model(&Student{}).Where("age = ?", 21).Updates(Student{
  Email:  &email,
  Gender: false,  // 这个不会更新,因为结构体默认不会更新零值
})
// UPDATE `students` SET `email`='xxx@qq.com' WHERE age = 21

如果想让他更新零值，用select就好

email := "xxx1@qq.com"
DB.Model(&Student{}).Where("age = ?", 21).Select("gender", "email").Updates(Student{
  Email:  &email,
  Gender: false,
})
// UPDATE `students` SET `gender`=false,`email`='xxx1@qq.com' WHERE age = 21

如果不想多写几行代码，则推荐使用map

DB.Model(&Student{}).Where("age = ?", 21).Updates(map[string]any{
  "email":  &email,
  "gender": false,
})

删除操作

var students Students
Db.Delete(&students, 14)
// DELETE FROM `student` WHERE `students`.`id` = 14