前言
在上一篇文章中,我们对Gorm
进行了介绍,而在这一篇文章中我们主要介绍GORM
的单表查询与Hook函数,在进行今天的内容之前我们先事先说明一下,下面我们对单表进行操作的表结构如下:
type Student struct { ID uint `gorm:"size:3"` Name string `gorm:"size:8"` Age int `gorm:"size:3"` Sex string `gorm:"size:3"` Email *string `gorm:"size:32"` }
好了,话不多说,开始我们今天的内容
表的初始化
首先我们确定一下我们已经将要演示的数据库内相关内容清空掉了,然后我们就可以开始连接数据库并创建students
表了:
package main import ( "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" ) type Student struct { ID uint `gorm:"size:3"` Name string `gorm:"size:8"` Age int `gorm:"size:3"` Sex string `gorm:"size:3"` Email *string `gorm:"size:32"` } var myDB *gorm.DB func init() { user := "root" password := "aaaa" dbname := "gorm" ip := "127.0.0.1" port := "3306" dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", user, password, ip, port, dbname) db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { fmt.Println("数据库连接失败,err:", err) return } fmt.Println("数据库连接成功") myDB = db } func main() { err := myDB.AutoMigrate(&Student{}) if err != nil { fmt.Println("students表创建失败,err:", err) return } fmt.Println("students表创建成功") }
单表操作
创建并插入数据
插入单条数据
func SingleInsert(student Student) { res := myDB.Create(&student) if res.Error != nil { fmt.Println("插入数据失败,err:", res.Error) return } fmt.Println("插入数据成功") } func main() { err := myDB.AutoMigrate(&Student{}) if err != nil { fmt.Println("students表创建失败,err:", err) return } fmt.Println("students表创建成功") email := "fengxu@163.com" student := Student{ Name: "fengxu", Age: 18, Sex: "男", Email: &email, } SingleInsert(student) }
运行成功,查找数据库:
这样我们就成功将一条记录插入数据库了
批量插入数据
func Insert(StudentList []Student) { var StudentList []Student for i := 0; i < 10; i++ { email := fmt.Sprintf("No.%d@163.com", i) student := Student{ Name: fmt.Sprintf("No.%d", i), Age: 18 + i, Sex: "男", Email: &email, } StudentList = append(StudentList, student) } res := myDB.Create(&StudentList) if res.Error != nil { fmt.Println("插入数据失败,err:", res.Error) return } fmt.Println("插入数据成功") }
这显示我们成功向students
表中批量插入数据。
插入数据的细节
- 这里的
email
我为了表示它可以为空,将它的类型设置成了指针,所以我们在传值的时候也要传指针 - 我们在使用
Create
函数时,传递的是指针,而不是具体值 - 由于我们传入到Create函数的是student的指针,所以student在此之后就会出现该记录的其他消息了,比如下面这样:
func SingleInsert(student Student) { //插入单条数据 email := "fengxu@163.com" student := Student{ Name: "luoyu", Age: 18, Sex: "男", Email: &email, } SingleInsert(student) res := myDB.Create(&student) if res.Error != nil { fmt.Println("插入数据失败,err:", res.Error) return } fmt.Println("插入数据成功") fmt.Println(student) }
打印的结果为:
{13 luoyu 18 男 0xc0001e0830}
单表插入的完整代码:
package main import ( "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" ) type Student struct { ID uint `gorm:"size:3"` Name string `gorm:"size:8"` Age int `gorm:"size:3"` Sex string `gorm:"size:3"` Email *string `gorm:"size:32"` } var myDB *gorm.DB func init() { user := "root" password := "ba161754" dbname := "gorm" ip := "127.0.0.1" port := "3306" dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", user, password, ip, port, dbname) db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { fmt.Println("数据库连接失败,err:", err) return } fmt.Println("数据库连接成功") myDB = db } func SingleInsert(student Student) { res := myDB.Create(&student) if res.Error != nil { fmt.Println("插入数据失败,err:", res.Error) return } fmt.Println("插入数据成功") fmt.Println(student) } func Insert(StudentList []Student) { res := myDB.Create(&StudentList) if res.Error != nil { fmt.Println("插入数据失败,err:", res.Error) return } fmt.Println("插入数据成功") } func main() { err := myDB.AutoMigrate(&Student{}) if err != nil { fmt.Println("students表创建失败,err:", err) return } fmt.Println("students表创建成功") //插入单条数据 email := "fengxu@163.com" student := Student{ Name: "luoyu", Age: 18, Sex: "男", Email: &email, } SingleInsert(student) //批量插入数据 var StudentList []Student for i := 0; i < 10; i++ { email := fmt.Sprintf("No.%d@163.com", i) student := Student{ Name: fmt.Sprintf("No.%d", i), Age: 18 + i, Sex: "男", Email: &email, } StudentList = append(StudentList, student) } Insert(StudentList) }
单表查询
前言
在讲解单表查询之前,我们先来看一个很简单的单表查询代码
package main import ( "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/logger" "log" "os" "time" ) type Student struct { ID uint `gorm:"size:3"` Name string `gorm:"size:8"` Age int `gorm:"size:3"` Sex string `gorm:"size:3"` Email *string `gorm:"size:32"` } var myDB *gorm.DB var mysqllogger logger.Interface func init() { user := "root" password := "ba161754" dbname := "gorm" ip := "127.0.0.1" port := "3306" dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", user, password, ip, port, dbname) db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { fmt.Println("数据库连接失败,err:", err) return } fmt.Println("数据库连接成功") myDB = db } func initLogger() { var mysqlLogger logger.Interface mysqlLogger = logger.Default.LogMode(logger.Info) //设置日志打印级别 mysqlLogger = logger.New( log.New(os.Stdout, "\r\n", log.LstdFlags), // (日志输出的目标,前缀和日志包含的内容) logger.Config{ SlowThreshold: time.Second, // 慢 SQL 阈值 LogLevel: logger.Info, // 日志级别 IgnoreRecordNotFoundError: true, // 忽略ErrRecordNotFound(记录未找到)错误 Colorful: true, // 使用彩色打印 }, ) myDB.Logger = mysqlLogger } func main() { var student Student initLogger() myDB.Session(&gorm.Session{ Logger: mysqllogger, }) myDB.Take(&student) //默认查找第一条数据 fmt.Println(student) student = Student{} myDB.First(&student) //查找第一条数据 fmt.Println(student) student = Student{} myDB.Last(&student) //查找最后一条数据 fmt.Println(student) }
输出结果为:
这里我在打印出查询结果的同时也打印出了它们原生的sql语句,我们可以看到相对于原生sql语句,GORM的语句相对比较简单。
当然和我们平时利用各种各样的筛选条件来进行查询,我们在GORM也可以利用各种各样的条件来完成查询,,最后我们介绍一下下面我们可能会使用的函数:
Take
:用于从数据库中检索符合条件的第一条记录,并将其填充到指定的结构体中。如果没有指定特定的条件,它会默认返回第一条记录。Find
:用于从数据库中检索符合条件的所有记录,并将它们填充到指定的结构体切片中。你可以使用Where函数来添加条件限制,以便只检索满足特定条件的记录。
单条记录查询
- 根据主键查询
myDB.Take(&student, 1) fmt.Println(student) myDB.Take(&student, "1") fmt.Println(student)
注意:这里指定主键时可以是数字也可以是字符串
- 根据其他条件来查询
myDB.Take(&student, "name = ?", "fengxu") fmt.Println(student)
这里我们用?
来作为占位符,这样可以有效的防止sql注入
- 根据struct来查询
myDB.Where(&Student{Name: "fengxu"}).Find(&student) fmt.Println(student) student = Student{}
- 获取查询结果
var studentList []Student err := myDB.Find(&studentList).Error switch { case errors.Is(err, gorm.ErrRecordNotFound): fmt.Println("没有找到数据") default: fmt.Println("sql语句出现问题") } count := myDB.Find(&studentList).RowsAffected //获取查询结果条数 fmt.Println(count) }
完整代码:
func SingleSerach() { var student Student var studentList []Student //根据主键查询 myDB.Take(&student, 1) fmt.Println(student) student = Student{} myDB.Take(&student, "3") fmt.Println(student) student = Student{} //根据字段查询 myDB.Take(&student, "name = ?", "fengxu") fmt.Println(student) student = Student{} //根据struct来查询 //student.ID = 4 myDB.Where(&Student{Name: "fengxu"}).Find(&student) fmt.Println(student) student = Student{} err := myDB.Find(&studentList).Error switch { case errors.Is(err, gorm.ErrRecordNotFound): fmt.Println("没有找到数据") default: fmt.Println("sql语句出现问题") } count := myDB.Find(&studentList).RowsAffected fmt.Println(count) }
多条记录查询
- 简单示例:
func MultipleSearch() { var studentList []Student myDB.Find(&studentList) for _, v := range studentList { fmt.Println(v) } //上面我们打印的email是地址,我们需要对它进行序列化 for _, v := range studentList { data, _ := json.Marshal(v) fmt.Println(string(data)) } }
- 按照主键来查询
studentList1 := []Student{} myDB.Find(&studentList1, 1, 2, 3, 4, 5, 6) for _, v := range studentList1 { data, _ := json.Marshal(v) fmt.Println(string(data)) } studentList2 := []Student{} myDB.Find(&studentList2, []int{1, 2, 3, 4, 5, 6}) for _, v := range studentList2 { data, _ := json.Marshal(v) fmt.Println(string(data)) }
注意:这里其实用不用前片都可以,但是测试结果是切片速度明显较快,建议使用切片
- 按照其他条件来查询
//根据字段查询 studentList := []Student{} myDB.Where("name in ?", []string{"fengxu", "luoyu"}).Find(&studentList) for _, v := range studentList { data, _ := json.Marshal(v) fmt.Println(string(data)) }
全部代码:
func MultipleSearch() { var studentList []Student myDB.Find(&studentList) for _, v := range studentList { fmt.Println(v) } //按主键查询 studentList1 := []Student{} myDB.Find(&studentList1, 1, 2, 3, 4, 5, 6) for _, v := range studentList1 { data, _ := json.Marshal(v) fmt.Println(string(data)) } studentList2 := []Student{} myDB.Find(&studentList2, []int{1, 2, 3, 4, 5, 6}) for _, v := range studentList2 { data, _ := json.Marshal(v) fmt.Println(string(data)) } //根据字段查询 studentList = []Student{} myDB.Where("name in ?", []string{"fengxu", "luoyu"}).Find(&studentList) for _, v := range studentList { data, _ := json.Marshal(v) fmt.Println(string(data)) } }
单表数据的更新
示例:
func Update() { var student Student myDB.Take(&student, 1) student.Name = "三玖" student.Sex = "女" myDB.Save(&student) fmt.Println(student) }
注意:
Save
会保留所有的字段,哪怕我们将字段的值设为0
Save
函数其实是一个组合操作,如果当前不存在该字段,则是执行Create
否则执行Update
- 不要将
Save
和Model
一同使用, 这是 未定义的行为
单列更新
更新单列指定字段
//更新指定字段 student = Student{} myDB.Take(&student) student.Age = 19 myDB.Select("age").Save(&student) fmt.Println(student)
批量更新
//同时更新单个指定字段 studentList := []Student{} myDB.Where("name like ?", "No.%").Find(&studentList).Update("age", 19) for _, v := range studentList { fmt.Println(v.Name, v.Age) } //同时更新多个指定字段 Email := "sanjiu@163.com" new_student := Student{ Age: 20, Sex: "女", Email: &Email, } myDB.Model(&student).Where("name=?", "三玖").Updates(new_student) //方法一:struct myDB.Model(&student).Where("name=?", "三玖").Updates(map[string]interface{}{ "age": 20, "sex": "女", "email": "sanjiu@163.com", })// 方法二:map
注意:Updates
函数在struct方法更新字段时会自动忽略零值,如果想避免建议使用map
或使用Select
函数说明一下要更新的字段,示例如下:
DB.Model(&Student{}).Where("age = ?", 21).Select("gender", "email").Updates(Student{ Email: &email, Gender: false, })
单表删除
func Delete() { var student Student var studentlist []Student myDB.Take(&student, 1) myDB.Delete(&student) //单行删除 myDB.Take(&studentlist, []int{1, 2, 3}) myDB.Delete(&studentlist) //批量删除 }
代码汇总
以上有关单表操作的全部代码:
package main import ( "encoding/json" "errors" "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/logger" "log" "os" "time" ) type Student struct { ID uint `gorm:"size:3"` Name string `gorm:"size:8"` Age int `gorm:"size:3"` Sex string `gorm:"size:3"` Email *string `gorm:"size:32"` } var myDB *gorm.DB var mysqllogger logger.Interface func init() { user := "root" password := "nicai" dbname := "gorm" ip := "127.0.0.1" port := "3306" dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", user, password, ip, port, dbname) db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { fmt.Println("数据库连接失败,err:", err) return } fmt.Println("数据库连接成功") myDB = db } func initLogger() { var mysqlLogger logger.Interface mysqlLogger = logger.Default.LogMode(logger.Info) //设置日志打印级别 mysqlLogger = logger.New( log.New(os.Stdout, "\r\n", log.LstdFlags), // (日志输出的目标,前缀和日志包含的内容) logger.Config{ SlowThreshold: time.Second, // 慢 SQL 阈值 LogLevel: logger.Info, // 日志级别 IgnoreRecordNotFoundError: true, // 忽略ErrRecordNotFound(记录未找到)错误 Colorful: true, // 使用彩色打印 }, ) myDB.Logger = mysqlLogger } func SingleSerach() { var student Student var studentList []Student //根据主键查询 myDB.Take(&student, 1) fmt.Println(student) student = Student{} myDB.Take(&student, "3") fmt.Println(student) student = Student{} //根据字段查询 myDB.Take(&student, "name = ?", "fengxu") fmt.Println(student) student = Student{} //根据struct来查询 //student.ID = 4 myDB.Where(&Student{Name: "fengxu"}).Find(&student) fmt.Println(student) student = Student{} err := myDB.Find(&studentList).Error switch { case errors.Is(err, gorm.ErrRecordNotFound): fmt.Println("没有找到数据") default: fmt.Println("sql语句出现问题") } count := myDB.Find(&studentList).RowsAffected fmt.Println(count) } func MultipleSearch() { var studentList []Student myDB.Find(&studentList) for _, v := range studentList { fmt.Println(v) } //按主键查询 studentList1 := []Student{} myDB.Find(&studentList1, 1, 2, 3, 4, 5, 6) for _, v := range studentList1 { data, _ := json.Marshal(v) fmt.Println(string(data)) } studentList2 := []Student{} myDB.Find(&studentList2, []int{1, 2, 3, 4, 5, 6}) for _, v := range studentList2 { data, _ := json.Marshal(v) fmt.Println(string(data)) } //根据字段查询 studentList = []Student{} myDB.Where("name in ?", []string{"fengxu", "luoyu"}).Find(&studentList) for _, v := range studentList { data, _ := json.Marshal(v) fmt.Println(string(data)) } } func Update() { //update操作示例 var student Student myDB.Take(&student, 1) student.Name = "三玖" student.Sex = "女" myDB.Save(&student) fmt.Println(student) //更新指定字段 student = Student{} myDB.Take(&student) student.Age = 19 myDB.Select("age").Save(&student) fmt.Println(student) //同时更新多列的指定字段 studentList := []Student{} myDB.Where("name like ?", "No.%").Find(&studentList).Update("age", 19) for _, v := range studentList { fmt.Println(v.Name, v.Age) } //同时更新多列 Email := "sanjiu@163.com" new_student := Student{ Age: 20, Sex: "女", Email: &Email, } myDB.Model(&student).Where("name=?", "三玖").Updates(new_student) //方法一:struct myDB.Model(&student).Where("name=?", "三玖").Updates(map[string]interface{}{ "age": 20, "sex": "女", "email": "sanjiu@163.com", }) // 方法二:map } func Delete() { var student Student var studentlist []Student myDB.Take(&student, 1) myDB.Delete(&student) //单行删除 myDB.Take(&studentlist, []int{1, 2, 3}) myDB.Delete(&studentlist) //批量删除 } func main() { //var student Student initLogger() myDB.Session(&gorm.Session{ Logger: mysqllogger, }) //myDB.Take(&student) //默认查找第一条数据 //fmt.Println(student) //student = Student{} // //myDB.First(&student) //查找第一条数据 //fmt.Println(student) //student = Student{} // //myDB.Last(&student) //查找最后一条数据 //fmt.Println(student) Delete() }
结语
上面仅仅是我基于Gorm
框架学习的一些笔记,详细可以参考: