小小程日记-笔记分享GORM - CRUD 接口（入门篇）
使用 MySQL 进行学习。
1. 准备
下载依赖
go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql约定
使用 ID 作为主键，结构体名的复数（蛇形复数）作为表名（比如，User 表名为 users），字段名使用 蛇形 作为表名（比如，RegTime 字段名为 reg_time），使用 CreatedAt、UpdatedAt 字段追踪创建、更新时间。
支持 链式调用
可以使用标签进行自定义。
type Admin struct {
ID uint
Username string
Password string
Level uint `gorm:"column:admin_level"`
CreatedAt time.Time
UpdatedAt time.Time
}对应表结构
+-------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| username | longtext | YES | | NULL | |
| password | longtext | YES | | NULL | |
| admin_level | bigint unsigned | YES | | NULL | |
| created_at | datetime(3) | YES | | NULL | |
| updated_at | datetime(3) | YES | | NULL | |
+-------------+-----------------+------+-----+---------+----------------+连接
基本配置格式：
dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
fmt.Println("数据库连接失败~", err)
panic(err)
}返回一个 DB 用于操作。
2. 创建
使用模型
type User struct {
ID uint
Name string
IsBan bool
Birthday *time.Time
Area string `gorm:"column:user_area"`
}+-----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | longtext | YES | | NULL | |
| is_ban | tinyint(1) | YES | | NULL | |
| birthday | datetime(3) | YES | | NULL | |
| user_area | longtext | YES | | NULL | |
+-----------+-----------------+------+-----+---------+----------------+
5 rows in set (0.0015 sec)使用 AutoMigrate 方法自动迁移，如果没有对应数据表，会自动创建。
db.AutoMigrate(&User{})插入单条记录
使用 Create 方法插入记录
t := time.Now()
u := User{
Name: "xxcheng",
IsBan: false,
Birthday: &t,
Area: "黄冈师范学院",
}
result := db.Create(&u)
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1+----+---------+--------+-------------------------+--------------+
| id | name | is_ban | birthday | user_area |
+----+---------+--------+-------------------------+--------------+
| 1 | xxcheng | 0 | 2023-07-29 19:52:25.571 | 黄冈师范学院 |
+----+---------+--------+-------------------------+--------------+
1 row in set (0.0014 sec)插入多条记录
t := time.Now()
us := []User{
User{
Name: "www",
IsBan: false,
Birthday: &t,
Area: "浙江",
},
User{
Name: "jpc",
IsBan: false,
Birthday: &t,
Area: "中国",
},
}
result := db.Create(&us)
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：2+----+---------+--------+-------------------------+--------------+
| id | name | is_ban | birthday | user_area |
+----+---------+--------+-------------------------+--------------+
| 1 | xxcheng | 0 | 2023-07-29 19:52:25.571 | 黄冈师范学院 |
| 2 | www | 0 | 2023-07-29 19:57:26.104 | 浙江 |
| 3 | jpc | 0 | 2023-07-29 19:57:26.104 | 中国 |
+----+---------+--------+-------------------------+--------------+
3 rows in set (0.0010 sec)限定可插入字段
使用 Select 方法可以指定给哪些字段插入值
t := time.Now()
u := User{
Name: "xxcheng123",
IsBan: false,
Birthday: &t,
Area: "黄冈师范学院",
}
result := db.Select("Name", "IsBan", "Birthday").Create(&u)
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}SQL > SELECT * FROM users ORDER BY id DESC LIMIT 1;
+----+------------+--------+-------------------------+-----------+
| id | name | is_ban | birthday | user_area |
+----+------------+--------+-------------------------+-----------+
| 4 | xxcheng123 | 0 | 2023-07-29 20:00:39.294 | NULL |
+----+------------+--------+-------------------------+-----------+
1 row in set (0.0007 sec)限定忽略字段
使用 Omit 方法忽略指定字段
t := time.Now()
u := User{
Name: "xxcheng001",
IsBan: false,
Birthday: &t,
Area: "黄冈师范学院",
}
result := db.Omit("Birthday").Create(&u)SQL > SELECT * FROM users ORDER BY id DESC LIMIT 1;
+----+------------+--------+----------+--------------+
| id | name | is_ban | birthday | user_area |
+----+------------+--------+----------+--------------+
| 5 | xxcheng001 | 0 | NULL | 黄冈师范学院 |
+----+------------+--------+----------+--------------+
1 row in set (0.0007 sec)3. 删除
gorm 使用主键和内联条件来删除记录。
Delete() 方法进行删除，主键可以字符串形式输入
func (db *DB) Delete(value interface{}, conds ...interface{}) (tx *DB)Where 方法附加内联条件
删除单个
result := db.Delete(&User{}, 1)
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1再执行一次
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：0删除多个
连续传入多个主键参数删除
result := db.Delete(&User{}, 2, 3, 4)
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：3再执行一次
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：0传入一个主键切片参数删除
删除之前没有主键为 4 的记录
result := db.Delete(&User{}, &[]int{4, 5, 6})
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：2附加内联条件
result := db.Where("name=?", "www").Delete(&User{}, &[]int{7, 8, 9})
if result.Error == nil {
fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
} else {
fmt.Println(result.Error)
}执行删除之前的记录
| id | name | is_ban | birthday | user_area |
+----+------+--------+-------------------------+-----------+
| 7 | jpc | 0 | 2023-07-29 20:17:34.079 | 中国 |
| 8 | www | 0 | 2023-07-29 20:17:36.512 | 浙江 |
| 9 | jpc | 0 | 2023-07-29 20:17:36.512 | 中国 |
| 10 | www | 0 | 2023-07-29 20:17:38.815 | 浙江 |
| 11 | jpc | 0 | 2023-07-29 20:17:38.815 | 中国 |
+----+------+--------+-------------------------+-----------+PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1删除操作的钩子函数
提供 BeforeDelete、AfterDelete 钩子在删除之前、删除之后调用。
func (u *User) BeforeDelete(tx *gorm.DB) error {
fmt.Println("我是BeforeDelete Hook", u)
return nil
}
func (u *User) AfterDelete(tx *gorm.DB) error {
fmt.Println("我是AfterDelete Hook", u)
return nil
}PS D:\resource\study-resource\fast-gorm> go run main.go
我是BeforeDelete Hook &{0 false <nil> }
我是AfterDelete Hook &{0 false <nil> }
创建成功，影响行数：0回写删除操作
数据库好像不支持，测试不出来
var users []User
result := db.Clauses(clause.Returning{}).Where("name = ?", "www").Delete(&users)
fmt.Println(users)PS D:\resource\study-resource\fast-gorm> go run main.go
[]软删除
只要包含 gorm.DeletedAt 就自动获取软删除能力。
type Job struct {
ID uint
Name string
DeletedAt gorm.DeletedAt
}+------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | longtext | YES | | NULL | |
| deleted_at | datetime(3) | YES | | NULL | |
+------------+-----------------+------+-----+---------+----------------++----+----------------+------------+
| id | name | deleted_at |
+----+----------------+------------+
| 1 | 前端开发工程师 | NULL |
| 2 | 后端开发工程师 | NULL |
| 3 | 全栈开发工程师 | NULL |
+----+----------------+------------+result := db.Delete(&Job{}, 1)删除后
+----+----------------+-------------------------+
| id | name | deleted_at |
+----+----------------+-------------------------+
| 1 | 前端开发工程师 | 2023-07-29 21:03:53.900 |
| 2 | 后端开发工程师 | NULL |
| 3 | 全栈开发工程师 | NULL |
+----+----------------+-------------------------+永久删除
在删除前调用 Unscoped 方法即可。
result := db.Unscoped().Delete(&Job{}, 2)删除后
+----+----------------+-------------------------+
| id | name | deleted_at |
+----+----------------+-------------------------+
| 1 | 前端开发工程师 | 2023-07-29 21:03:53.900 |
| 3 | 全栈开发工程师 | NULL |
+----+----------------+-------------------------+4. 更新
使用 Save、Update 和 Updates 方法实现更新，同时如果没有提供主键，则执行 Create 方法，插入新的记录。
type Department struct {
ID uint
Leader uint `gorm:"column:leader_id"`
Name string
Location string
Salary float32 `gorm:"default:12000"`
}+----+-----------+----------+------------+--------+
| id | leader_id | name | location | salary |
+----+-----------+----------+------------+--------+
| 1 | 1 | 研发一部 | 上海浦东 | 12000 |
| 2 | 99 | UI设计部 | 杭州滨江区 | 12000 |
| 3 | 8 | 销售部 | 北京 | 5000.5 |
+----+-----------+----------+------------+--------+更新全部字段
全部会更新，即使值为默认零值也会被更新
dp := Department{
ID: 3,
Name: "销售部",
Location: "天津",
}
result := db.Save(&dp)+----+-----------+----------+------------+--------+
| id | leader_id | name | location | salary |
+----+-----------+----------+------------+--------+
| 1 | 1 | 研发一部 | 上海浦东 | 12000 |
| 2 | 99 | UI设计部 | 杭州滨江区 | 12000 |
| 3 | 0 | 销售部 | 天津 | 0 |
+----+-----------+----------+------------+--------+dp 定义时，leader_id 和 salary 默认初始值为 0。
不指定主键将创建新记录
dp := Department{
Name: "销售部2",
Location: "天津",
}
result := db.Save(&dp)+----+-----------+----------+------------+--------+
| id | leader_id | name | location | salary |
+----+-----------+----------+------------+--------+
| 1 | 1 | 研发一部 | 上海浦东 | 12000 |
| 2 | 99 | UI设计部 | 杭州滨江区 | 12000 |
| 3 | 0 | 销售部 | 天津 | 0 |
| 4 | 0 | 销售部2 | 天津 | 12000 |
+----+-----------+----------+------------+--------+创建出来一条主键 ID 为 4 的记录。
更新指定某个字段
dp := Department{
ID: 4,
}
result := db.Model(&dp).Update("leader_id", 666)+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 0 | 销售部2 | 天津 | 12000 |
+----+-----------+---------+----------+--------+更新后
+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 666 | 销售部2 | 天津 | 12000 |
+----+-----------+---------+----------+--------+更新指定多个字段
dp := Department{
ID: 4,
}
result := db.Model(&dp).Updates(&Department{
Salary: 9000,
Location: "重庆",
})更新后
+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 666 | 销售部2 | 重庆 | 9000 |
+----+-----------+---------+----------+--------+忽略零值
当使用struct 作为更新数据，更新的数据为对应数据类型的零值会被忽略更新。
dp := Department{
ID: 4,
}
result := db.Model(&dp).Updates(&Department{
Salary: 8000,
Location: "",
})+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 666 | 销售部2 | 重庆 | 8000 |
+----+-----------+---------+----------+--------+location 为字符串类型的零值，忽略更新。
使用 map 作为数据源可以成功更新
dp := Department{
ID: 4,
}
result := db.Model(&dp).Updates(map[string]any{
"salary": 3000,
"location": "",
})+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 666 | 销售部2 | | 3000 |
+----+-----------+---------+----------+--------+限定更新字段
使用 Select 指定哪些字段可以更新，Omit 忽略哪些字段的更新。
测试 Select
dp := Department{
ID: 4,
}
result := db.Model(&dp).Select("Leader").Updates(&Department{
Salary: 6000,
Location: "武汉",
Leader: 888,
})+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 888 | 销售部2 | | 3000 |
+----+-----------+---------+----------+--------+只成功更新了 leader_id
测试 Omit
result := db.Model(&dp).Omit("Leader").Updates(&Department{
Salary: 6000,
Location: "武汉",
Leader: 999,
})+----+-----------+---------+----------+--------+
| id | leader_id | name | location | salary |
+----+-----------+---------+----------+--------+
| 4 | 888 | 销售部2 | 武汉 | 6000 |
+----+-----------+---------+----------+--------+location 和 salary 字段成功更新。
无 Hooks 更新
type Grade struct {
*gorm.Model
ID uint
Name string
Weight uint
}g := Grade{
Name: "董事长",
Weight: 9999,
}
g2 := Grade{
Name: "总经理",
Weight: 999,
}
g3 := Grade{
Name: "总裁",
Weight: 99,
}
g4 := Grade{
Name: "普通员工",
Weight: 1,
}
result := db.Create(&[]Grade{g, g2, g3, g4})+----+-------------------------+-------------------------+------------+----------+--------+
| id | created_at | updated_at | deleted_at | name | weight |
+----+-------------------------+-------------------------+------------+----------+--------+
| 1 | 2023-07-30 08:31:01.678 | 2023-07-30 08:31:01.678 | NULL | 董事长 | 9999 |
| 2 | 2023-07-30 08:31:01.678 | 2023-07-30 08:31:01.678 | NULL | 总经理 | 999 |
| 3 | 2023-07-30 08:31:01.678 | 2023-07-30 08:31:01.678 | NULL | 总裁 | 99 |
| 4 | 2023-07-30 08:31:01.678 | 2023-07-30 08:31:01.678 | NULL | 普通员工 | 1 |
+----+-------------------------+-------------------------+------------+----------+--------+Update 和 Updates 方法更新时会自动调用 model 的 BeforeUpdate、 AfterUpdate 方法 ，修改 UpdatedAt，如果不想调用，可以使用 UpdateColumn、 UpdateColumns 方法
自动更新 UpdatedAt
g := Grade{
ID: 4,
}
result := db.Model(&g).Update("weight", 2)+----+-------------------------+-------------------------+------------+----------+--------+
| id | created_at | updated_at | deleted_at | name | weight |
+----+-------------------------+-------------------------+------------+----------+--------+
| 4 | 2023-07-30 08:31:01.678 | 2023-07-30 08:58:50.760 | NULL | 普通员工 | 2 |
+----+-------------------------+-------------------------+------------+----------+--------+不自动更新 UpdatedAt
g := Grade{
ID: 4,
}
result := db.Model(&g).UpdateColumn("weight", 3)+----+-------------------------+-------------------------+------------+----------+--------+
| id | created_at | updated_at | deleted_at | name | weight |
+----+-------------------------+-------------------------+------------+----------+--------+
| 4 | 2023-07-30 08:31:01.678 | 2023-07-30 08:58:50.760 | NULL | 普通员工 | 3 |
+----+-------------------------+-------------------------+------------+----------+--------+批量更新
必须设置 Where
result := db.Model(Grade{}).Where("weight>?", 10).Updates(map[string]any{
"weight": 666,
})+----+-------------------------+-------------------------+------------+----------+--------+
| id | created_at | updated_at | deleted_at | name | weight |
+----+-------------------------+-------------------------+------------+----------+--------+
| 1 | 2023-07-30 08:31:01.678 | 2023-07-30 09:10:59.235 | NULL | 董事长 | 666 |
| 2 | 2023-07-30 08:31:01.678 | 2023-07-30 09:10:59.235 | NULL | 总经理 | 666 |
| 3 | 2023-07-30 08:31:01.678 | 2023-07-30 09:10:59.235 | NULL | 总裁 | 666 |
| 4 | 2023-07-30 08:31:01.678 | 2023-07-30 08:58:50.760 | NULL | 普通员工 | 3 |
+----+-------------------------+-------------------------+------------+----------+--------+5. 查询
type Company struct {
CompanyID uint `gorm:"column:reg_company_id;index;primaryKey;autoIncrement;"`
Name string
Grade uint8 `gorm:"column:company_grade;default:1""`
Balance float32
Desc string
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| reg_company_id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | longtext | YES | | NULL | |
| company_grade | tinyint unsigned | YES | | 1 | |
| balance | float | YES | | NULL | |
| desc | longtext | YES | | NULL | |
| created_at | datetime(3) | YES | | NULL | |
| updated_at | datetime(3) | YES | | NULL | |
| deleted_at | datetime(3) | YES | MUL | NULL | |
+----------------+------------------+------+-----+---------+----------------+获取一条记录
var c1, c2, c3 Company
// SELECT * FROM companies ORDER BY reg_company_id LIMIT 1;
db.First(&c1)
// SELECT * FROM companies LIMIT 1;
db.Take(&c2)
// SELECT * FROM companies ORDER BY reg_company_id DESC LIMIT 1;
db.Last(&c3)
// SELECT * FROM companies WHERE reg_company_id=2;
db.First(&c4, 2)
// SELECT * FROM companies WHERE name = 阿里巴巴
db.First(&c5, "name =?", "阿里巴巴")
fmt.Printf("ID:%d,Name:%s\n", c1.CompanyID, c1.Name)
fmt.Printf("ID:%d,Name:%s\n", c2.CompanyID, c2.Name)
fmt.Printf("ID:%d,Name:%s\n", c3.CompanyID, c3.Name)
fmt.Printf("ID:%d,Name:%s\n", c4.CompanyID, c4.Name)
fmt.Printf("ID:%d,Name:%s\n", c5.CompanyID, c5.Name)ID:1,Name:华为
ID:1,Name:华为
ID:99,Name:百度
ID:2,Name:腾讯
ID:3,Name:阿里巴巴获取多条记录
var cs1, cs2 []Company
// SELECT * FROM companies;
db.Find(&cs1)
db.Find(&cs2, []int{1, 2, 9, 99})
for _, row := range cs1 {
fmt.Printf("[cs1]--ID:%d,Name:%s\n", row.CompanyID, row.Name)
}
fmt.Println("----")
for _, row := range cs2 {
fmt.Printf("[cs2]--ID:%d,Name:%s\n", row.CompanyID, row.Name)
}[cs1]--ID:1,Name:华为
[cs1]--ID:2,Name:腾讯
[cs1]--ID:3,Name:阿里巴巴
[cs1]--ID:4,Name:百度
[cs1]--ID:99,Name:百度
----
[cs2]--ID:1,Name:华为
[cs2]--ID:2,Name:腾讯
[cs2]--ID:99,Name:百度本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
开学教教我！
欢迎大佬~
这图片发我一张，哈哈哈
from Internet.
6666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
llliang怎么还不更新？
你都不看，我更新又有何意义~
不更新怎么看