小小程知识库
xxcheng
2023-07-31
目录

CRUD 接口

使用 MySQL 进行学习。

# 1. 准备

# 下载依赖

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql
1
2

# 约定

使用 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
}
1
2
3
4
5
6
7
8

对应表结构

+-------------+-----------------+------+-----+---------+----------------+
| 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    |                |
+-------------+-----------------+------+-----+---------+----------------+
1
2
3
4
5
6
7
8
9
10

# 连接

基本配置格式：

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)
}
1
2
3
4
5
6

返回一个 DB 用于操作。

# 2. 创建

使用模型

type User struct {
	ID       uint
	Name     string
	IsBan    bool
	Birthday *time.Time
	Area     string `gorm:"column:user_area"`
}
1
2
3
4
5
6
7
+-----------+-----------------+------+-----+---------+----------------+
| 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)
1
2
3
4
5
6
7
8
9
10

使用 AutoMigrate 方法自动迁移，如果没有对应数据表，会自动创建。

db.AutoMigrate(&User{})
1

# 插入单条记录

使用 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)
}
1
2
3
4
5
6
7
8
9
10
11
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1
1
2
+----+---------+--------+-------------------------+--------------+
| id | name    | is_ban | birthday                | user_area    |
+----+---------+--------+-------------------------+--------------+
|  1 | xxcheng |      0 | 2023-07-29 19:52:25.571 | 黄冈师范学院 |
+----+---------+--------+-------------------------+--------------+
1 row in set (0.0014 sec)
1
2
3
4
5
6

# 插入多条记录

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)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：2
1
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)
1
2
3
4
5
6
7
8

# 限定可插入字段

使用 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)
}
1
2
3
4
5
6
7
8
9
10
11
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)
1
2
3
4
5
6
7

# 限定忽略字段

使用 Omit 方法忽略指定字段

t := time.Now()
u := User{
    Name:     "xxcheng001",
    IsBan:    false,
    Birthday: &t,
    Area:     "黄冈师范学院",
}
result := db.Omit("Birthday").Create(&u)
1
2
3
4
5
6
7
8
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)
1
2
3
4
5
6
7

# 3. 删除

gorm 使用主键和内联条件来删除记录。

Delete() 方法进行删除，主键可以字符串形式输入

func (db *DB) Delete(value interface{}, conds ...interface{}) (tx *DB)
1

Where 方法附加内联条件

# 删除单个

result := db.Delete(&User{}, 1)
if result.Error == nil {
    fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}
1
2
3
4
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1
1
2

再执行一次

PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：0
1
2

# 删除多个

# 连续传入多个主键参数删除

result := db.Delete(&User{}, 2, 3, 4)
if result.Error == nil {
    fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}
1
2
3
4
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：3
1
2

再执行一次

PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：0
1
2

# 传入一个主键切片参数删除

删除之前没有主键为 4 的记录

result := db.Delete(&User{}, &[]int{4, 5, 6})
if result.Error == nil {
    fmt.Printf("创建成功，影响行数：%d\n", result.RowsAffected)
}
1
2
3
4
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：2
1
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)
}
1
2
3
4
5
6

执行删除之前的记录

| 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 | 中国      |
+----+------+--------+-------------------------+-----------+
1
2
3
4
5
6
7
8
PS D:\resource\study-resource\fast-gorm> go run main.go
创建成功，影响行数：1
1
2

# 删除操作的钩子函数

提供 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
}
1
2
3
4
5
6
7
8
PS D:\resource\study-resource\fast-gorm> go run main.go
我是BeforeDelete Hook &{0  false <nil> }
我是AfterDelete Hook &{0  false <nil> }
创建成功，影响行数：0
1
2
3
4

# 回写删除操作

数据库好像不支持，测试不出来

var users []User
result := db.Clauses(clause.Returning{}).Where("name = ?", "www").Delete(&users)
fmt.Println(users)
1
2
3
PS D:\resource\study-resource\fast-gorm> go run main.go
[]
1
2

# 软删除

只要包含 gorm.DeletedAt 就自动获取软删除能力。

type Job struct {
	ID        uint
	Name      string
	DeletedAt gorm.DeletedAt
}
1
2
3
4
5
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name       | longtext        | YES  |     | NULL    |                |
| deleted_at | datetime(3)     | YES  |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+
1
2
3
4
5
6
7
+----+----------------+------------+
| id | name           | deleted_at |
+----+----------------+------------+
|  1 | 前端开发工程师 | NULL       |
|  2 | 后端开发工程师 | NULL       |
|  3 | 全栈开发工程师 | NULL       |
+----+----------------+------------+
1
2
3
4
5
6
7
result := db.Delete(&Job{}, 1)
1

删除后

+----+----------------+-------------------------+
| id | name           | deleted_at              |
+----+----------------+-------------------------+
|  1 | 前端开发工程师 | 2023-07-29 21:03:53.900 |
|  2 | 后端开发工程师 | NULL                    |
|  3 | 全栈开发工程师 | NULL                    |
+----+----------------+-------------------------+
1
2
3
4
5
6
7

# 永久删除

在删除前调用 Unscoped 方法即可。

result := db.Unscoped().Delete(&Job{}, 2)
1

删除后

+----+----------------+-------------------------+
| id | name           | deleted_at              |
+----+----------------+-------------------------+
|  1 | 前端开发工程师 | 2023-07-29 21:03:53.900 |
|  3 | 全栈开发工程师 | NULL                    |
+----+----------------+-------------------------+
1
2
3
4
5
6

# 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"`
}
1
2
3
4
5
6
7
+----+-----------+----------+------------+--------+
| id | leader_id | name     | location   | salary |
+----+-----------+----------+------------+--------+
|  1 |         1 | 研发一部 | 上海浦东   |  12000 |
|  2 |        99 | UI设计部 | 杭州滨江区 |  12000 |
|  3 |         8 | 销售部   | 北京       | 5000.5 |
+----+-----------+----------+------------+--------+
1
2
3
4
5
6
7

# 更新全部字段

全部会更新，即使值为默认零值也会被更新

dp := Department{
    ID:       3,
    Name:     "销售部",
    Location: "天津",
}
result := db.Save(&dp)
1
2
3
4
5
6
+----+-----------+----------+------------+--------+
| id | leader_id | name     | location   | salary |
+----+-----------+----------+------------+--------+
|  1 |         1 | 研发一部 | 上海浦东   |  12000 |
|  2 |        99 | UI设计部 | 杭州滨江区 |  12000 |
|  3 |         0 | 销售部   | 天津       |      0 |
+----+-----------+----------+------------+--------+
1
2
3
4
5
6
7

dp 定义时，leader_id 和 salary 默认初始值为 0。

# 不指定主键将创建新记录

dp := Department{
    Name:     "销售部2",
    Location: "天津",
}
result := db.Save(&dp)
1
2
3
4
5
+----+-----------+----------+------------+--------+
| id | leader_id | name     | location   | salary |
+----+-----------+----------+------------+--------+
|  1 |         1 | 研发一部 | 上海浦东   |  12000 |
|  2 |        99 | UI设计部 | 杭州滨江区 |  12000 |
|  3 |         0 | 销售部   | 天津       |      0 |
|  4 |         0 | 销售部2  | 天津       |  12000 |
+----+-----------+----------+------------+--------+
1
2
3
4
5
6
7
8

创建出来一条主键 ID 为 4 的记录。

# 更新指定某个字段

dp := Department{
    ID: 4,
}
result := db.Model(&dp).Update("leader_id", 666)
1
2
3
4
+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |         0 | 销售部2 | 天津     |  12000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

更新后

+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       666 | 销售部2 | 天津     |  12000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

# 更新指定多个字段

dp := Department{
    ID: 4,
}
result := db.Model(&dp).Updates(&Department{
    Salary:   9000,
    Location: "重庆",
})
1
2
3
4
5
6
7

更新后

+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       666 | 销售部2 | 重庆     |   9000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

# 忽略零值

当使用struct 作为更新数据，更新的数据为对应数据类型的零值会被忽略更新。

dp := Department{
    ID: 4,
}
result := db.Model(&dp).Updates(&Department{
    Salary:   8000,
    Location: "",
})
1
2
3
4
5
6
7
+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       666 | 销售部2 | 重庆     |   8000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

location 为字符串类型的零值，忽略更新。

使用 map 作为数据源可以成功更新

dp := Department{
    ID: 4,
}
result := db.Model(&dp).Updates(map[string]any{
    "salary":   3000,
    "location": "",
})
1
2
3
4
5
6
7
+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       666 | 销售部2 |          |   3000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

# 限定更新字段

使用 Select 指定哪些字段可以更新，Omit 忽略哪些字段的更新。

测试 Select

dp := Department{
    ID: 4,
}
result := db.Model(&dp).Select("Leader").Updates(&Department{
    Salary:   6000,
    Location: "武汉",
    Leader:   888,
})
1
2
3
4
5
6
7
8
+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       888 | 销售部2 |          |   3000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

只成功更新了 leader_id

测试 Omit

result := db.Model(&dp).Omit("Leader").Updates(&Department{
    Salary:   6000,
    Location: "武汉",
    Leader:   999,
})
1
2
3
4
5
+----+-----------+---------+----------+--------+
| id | leader_id | name    | location | salary |
+----+-----------+---------+----------+--------+
|  4 |       888 | 销售部2 | 武汉     |   6000 |
+----+-----------+---------+----------+--------+
1
2
3
4
5

location 和 salary 字段成功更新。

# 无 Hooks 更新

type Grade struct {
	*gorm.Model
	ID     uint
	Name   string
	Weight uint
}
1
2
3
4
5
6
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})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+----+-------------------------+-------------------------+------------+----------+--------+
| 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 |
+----+-------------------------+-------------------------+------------+----------+--------+
1
2
3
4
5
6
7
8

Update 和 Updates 方法更新时会自动调用 model 的 BeforeUpdate、 AfterUpdate 方法 ，修改 UpdatedAt，如果不想调用，可以使用 UpdateColumn、 UpdateColumns 方法

# 自动更新 UpdatedAt

g := Grade{
    ID: 4,
}
result := db.Model(&g).Update("weight", 2)
1
2
3
4
+----+-------------------------+-------------------------+------------+----------+--------+
| 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 |
+----+-------------------------+-------------------------+------------+----------+--------+
1
2
3
4
5

# 不自动更新 UpdatedAt

g := Grade{
    ID: 4,
}
result := db.Model(&g).UpdateColumn("weight", 3)
1
2
3
4
+----+-------------------------+-------------------------+------------+----------+--------+
| 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 |
+----+-------------------------+-------------------------+------------+----------+--------+
1
2
3
4
5

# 批量更新

必须设置 Where

result := db.Model(Grade{}).Where("weight>?", 10).Updates(map[string]any{
    "weight": 666,
})
1
2
3
+----+-------------------------+-------------------------+------------+----------+--------+
| 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 |
+----+-------------------------+-------------------------+------------+----------+--------+
1
2
3
4
5
6
7
8

# 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"`
}
1
2
3
4
5
6
7
8
9
10
+----------------+------------------+------+-----+---------+----------------+
| 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    |                |
+----------------+------------------+------+-----+---------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12

# 获取一条记录

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ID:1,Name:华为
ID:1,Name:华为
ID:99,Name:百度
ID:2,Name:腾讯
ID:3,Name:阿里巴巴
1
2
3
4
5

# 获取多条记录

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)
}
1
2
3
4
5
6
7
8
9
10
11
12
[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:百度
1
2
3
4
5
6
7
8
9
#GORM
上次更新: 2023/12/12, 11:52:58
数组、切片、字符串
配置

← 配置→

最近更新
01
Go：GMP模型深入理解
01-10
02
rpc学习：进阶到gRPC
01-04
03
配置
12-12
更多文章>
Theme by Vdoing | Copyright © 2019-2024 xxcheng | 浙ICP备19024050号