一. 初始化连接
1 package model 2 3 import ( 4 "fmt" 5 6 "gorm.io/driver/mysql" 7 "gorm.io/gorm" 8 ) 9 10 /*** 11 Navicat 数据库可视化工具: https://www.navicat.com/en/ 12 GORM 操作数据库的Golang库: https://gorm.io/zh_CN/docs/index.html 13 ***/ 14 15 var ( 16 DB *gorm.DB 17 ) 18 19 func init() { 20 var err error 21 // dsn := "user:pass@tcp(192.168.0.106:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local" 22 dsn := "root:123456@tcp(192.168.0.106:3306)/gin?charset=utf8mb4&parseTime=True&loc=Local" 23 24 DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{ 25 SkipDefaultTransaction: false, // true为禁用事务,官方说可以提升30%的性能 26 QueryFields: true, // 显示执行的SQL 27 28 }) 29 if err != nil { 30 fmt.Println("Mysql 数据库连接失败!") 31 } else { 32 fmt.Println("Mysql 数据库连接成功!") 33 } 34 }
二. 单表增删改查
1 package model 2 3 import "fmt" 4 5 /*** 6 注意事项: 7 1. 结构体的名称必须首字母大写,并和数据库表名称对应. 8 例如: 表名user,结构体名User 9 2. 结构体中字段名称首字母必须大写,并和数据库中字段一一对应. 10 例如: 结构体 数据库 11 Id id 12 Username username 13 AddTime add_time 驼峰命名在数据库中会以下划线分隔 14 3. 默认情况表名是结构体名称的复数形式. 修改表明,定义TableName函数返回表明就可以 15 例如: 结构体名称User 默认对应表明users 16 17 ***/ 18 19 type User struct { // 默认表名是users, 可以通过TableName函数返回来设定 20 Id int 21 Username string 22 Age int 23 Email string 24 AddTime int 25 } 26 27 // 事务 28 func UserTransaction() { 29 // 开启事务 30 tx := DB.Begin() 31 32 // 捕获异常 33 defer func() { 34 if r := recover(); r != nil { 35 // 遇到错误回滚 36 tx.Rollback() 37 } 38 }() 39 40 // 在事务中执行一些业务 41 user := User{ 42 Username: "chad", 43 Age: 29, 44 Email: "773254968@qq.com", 45 AddTime: int(GetUnix()), 46 } 47 u1 := User{} 48 49 DB.Create(&user).Find(&u1) 50 fmt.Println("新增成功", u1) 51 52 // 提交事务 53 tx.Commit() 54 } 55 56 // 默认表名是users, 可以通过TableName函数返回来设定 57 func (User) TableName() string { 58 return "user" 59 } 60 61 // 执行原生SQL 这样就可以进行group统计了 62 func UserExecSql() { 63 userlist := []User{} 64 // Raw是执行查询数据 65 DB.Raw("select username, age, email from user where id > ? and username like '%ll%' order by username desc ", 0).Scan(&userlist) 66 fmt.Println("ExecSql:", userlist) 67 68 // Exec是执行更新数据 69 result := DB.Exec("delete from user where id=8;") 70 fmt.Println("Exec:", result.RowsAffected) 71 72 // 获取统计行数 73 var count int64 74 DB.Raw("select * from user;").Count(&count) 75 fmt.Println("Count:", count) 76 } 77 78 // Order排序,Limit, Offset 79 func UserOtherOpt() { 80 userlist := []User{} 81 82 // 排序 83 DB.Order("id desc").Order("username asc").Find(&userlist) 84 fmt.Println("Order:", userlist) 85 86 // 分页: 每页显示三条记录Limit(3),显示第二页Offset(2) 87 DB.Order("id desc").Order("username asc").Offset(2).Limit(3).Find(&userlist) 88 fmt.Println("Offset:", userlist) 89 90 // 计数 91 var count int64 92 DB.Order("id desc").Order("username asc").Find(&userlist).Count(&count) 93 fmt.Println("Count:", count) 94 95 } 96 97 // 删除 98 func UserDelete() { 99 100 // Delete from user where id=1; 101 u := &User{Id: 1} 102 DB.Delete(u) 103 fmt.Println("1:", u) // 1: &{1 0 0} 104 // Delete from user where id=2; 105 u = &User{} 106 DB.Where("id = ?", 2).Delete(u) 107 fmt.Println("2:", u) // 2: &{0 0 0} 108 109 // Delete from user where id=3; 110 ul := []User{} 111 DB.Delete(&User{}, 3).Find(&ul) // 按主键删除的 112 fmt.Println("3:", ul) // 3: [] 113 // Delete from user where id=4; 114 ul = []User{} 115 DB.Delete(&User{}, "4").Find(&ul) // 按主键删除的 116 fmt.Println("4:", ul) // 4: [] 117 // Delete from user where id in (5,6,7); 118 ul = []User{} 119 DB.Delete(&User{}, []int{5, 6, 7}).Find(&ul) // 按主键删除的 120 fmt.Println("5,6,7:", ul) // 5,6,7: [] 121 122 } 123 124 // 修改 125 func UserEdit() { 126 user := User{Id: 1} 127 128 // 单条数据更新方式一 129 DB.Find(&user) 130 fmt.Println(user) 131 user.Age = 20 132 DB.Save(user) 133 134 // 单字段更新方式 135 DB.Find(&User{Id: 2}).Update("Age", 18) 136 137 // 单字段更新方式三 138 user1 := User{} 139 DB.Model(&user1).Where("age = ?", 19).Update("Age", 10) 140 fmt.Println(user1) 141 142 // 更新多列 143 /*** 144 根据struct更新属性,只会更新非零的字段值 145 update user set username='hello', age=18 where id=3 146 ***/ 147 DB.Model(&User{Id: 3}).Updates(User{Username: "hello", Age: 18}) 148 149 /*** 150 根据struct更新属性,只会更新非零的字段值 151 update user set username='hello', age=18 where id=3 152 ***/ 153 DB.Model(&User{Id: 4}).Updates(map[string]interface{}{"username": "hello1", "age": 25}) 154 155 } 156 157 // 新增 158 func UserInsert() { 159 user := User{ 160 Username: "chad", 161 Age: 29, 162 Email: "773254968@qq.com", 163 AddTime: int(GetUnix()), 164 } 165 u1 := User{} 166 167 DB.Create(&user).Find(&u1) 168 fmt.Println("新增成功", u1) 169 } 170 171 // 查询 172 func UserQuery() { 173 userList := []User{} 174 user := User{} 175 176 /*** 177 查询所有 178 select * from user; 179 ***/ 180 DB.Find(&userList) 181 fmt.Println("查询所有:", userList) 182 183 /*** 184 获取第一条匹配的记录 185 select * from user name='zhangsan' order by id limit 1; 186 ***/ 187 DB.Where("username = ?", "zhangsan").First(&user) 188 fmt.Println("获取第一条匹配的记录:", user) 189 190 /*** 191 获取匹配的所有记录 192 select * from user name='zhangsan' order by id ; 193 ***/ 194 DB.Where("username = ?", "zhangsan").Find(&userList) 195 fmt.Println("获取匹配的所有记录:", userList) 196 197 /*** 198 in 199 select * from user name in ('zhangsan', 'lisi') order by id ; 200 ***/ 201 DB.Where("username in ?", []string{"zhangsan", "lisi"}).Find(&userList) 202 fmt.Println("in:", userList) 203 204 /*** 205 in 206 select * from user name in ('zhangsan', 'lisi') order by id ; 207 ***/ 208 DB.Where("username in ?", []string{"zhangsan", "lisi"}).Find(&userList) 209 fmt.Println("in:", userList) 210 211 /*** 212 like 213 select * from user name like '%ang%' order by id ; 214 ***/ 215 DB.Where("username like ?", "%ang%").Find(&userList) 216 fmt.Println("like:", userList) 217 218 /*** 219 and 220 select * from user name ='zhangsan' and age=18 order by id ; 221 ***/ 222 DB.Where("username = ? and age >= ?", "zhangsan", 18).Find(&userList) 223 fmt.Println("and:", userList) 224 225 }
三. 多表关联查询
1 package model 2 3 import ( 4 "fmt" 5 6 "gorm.io/gorm" 7 ) 8 9 type Student struct { // 默认表名是users, 可以通过TableName函数返回来设定 10 Id int 11 Username string 12 Age int 13 Email string 14 AddTime int 15 /*** 16 One2many关联表Artical, 通过反射标签来设定Artical表中与之关联的Many2one字段. 这样就构成了一对主从表. 17 foreignKey 设置的外健, references设置的主键.主键默认Id 18 ***/ 19 Artical []Artical `gorm:"foreignKey:AuthorId;references:Id"` 20 21 /*** 22 Many2many 关联表查询 23 ***/ 24 Lesson []Lesson `gorm:"many2many:lesson_student"` 25 } 26 27 type Artical struct { 28 Title string 29 Content string 30 31 // Many2one关联表Student, 默认外健 AuthorID 或者可以通过反射标签定义外健名 32 Author Student `gorm:"foreignKey:AuthorId"` 33 AuthorId int 34 } 35 36 type Lesson struct { 37 Id int 38 Name string 39 Student []Student `gorm:"many2many:lesson_student"` 40 } 41 42 type LessonStudent struct { 43 LessonID int 44 StudentID int 45 } 46 47 // 关联查询 48 func ArticalSelect() { 49 // Many2one 查询 50 fmt.Println("Many2one 查询==========================") 51 articalList := []Artical{} 52 DB.Preload("Author").Find(&articalList) 53 fmt.Println("articalList:", articalList) 54 55 // One2many 查询 56 fmt.Println("\n\nOne2many 查询==========================") 57 userlist := []Student{} 58 DB.Preload("Artical").Find(&userlist) 59 fmt.Println("userlist:", userlist) 60 61 // Many2many 查询 62 fmt.Println("\n\nMany2many 查询==========================") 63 lessonlist := []Lesson{} 64 DB.Preload("Student").Offset(1).Limit(2).Order("id desc").Find(&lessonlist) 65 fmt.Println("lessonlist:", lessonlist) 66 // Many2many 预加载查询方式一 67 DB.Preload("Student", "username = ?", "zhangsan").Find(&lessonlist) 68 fmt.Println("lessonlist:", lessonlist) 69 // Many2many 预加载查询方式二 70 DB.Where("name like ?", "%inux%").Preload("Student", "username = ?", "zhangsan").Find(&lessonlist) 71 fmt.Println("lessonlist:", lessonlist) 72 // Many2many 预加载查询方式三 这样就可以根据关联字段值进行排序了 73 DB.Preload("Student", func(db *gorm.DB) *gorm.DB { 74 return db.Order("user.id asc") 75 }).Find(&lessonlist) 76 fmt.Println("lessonlist:", lessonlist) 77 // 多个Preload就是多表关联DB.Preload("Student").Preload("Class") 78 DB.Preload("Student", func(db *gorm.DB) *gorm.DB { 79 return db.Where("id > ?", 20).Preload("Lesson").Order("user.id asc") 80 }).Find(&lessonlist) 81 fmt.Println("lessonlist:", lessonlist) 82 83 } 84 85 func (Artical) TableName() string { 86 return "artical" 87 } 88 89 func (Student) TableName() string { 90 return "user" 91 } 92 93 func (Lesson) TableName() string { 94 return "lesson" 95 } 96 97 func (LessonStudent) TableName() string { 98 return "lesson_student" 99 }