gorm

GORM 指南 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

ORM全称是:Object Relational Mapping(对象关系映射),其主要作用是在编程中,把面向对象的概念跟数据库中表的概念对应起来。举例来说就是,我定义一个对象,那就对应着一张表,这个对象的实例,就对应着表中的一条记录。

gorm是Golang语言中一款性能极好的ORM库

gorm连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package main

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

func main() {
// 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情

dsn := "root:123456@tcp(127.0.0.1:3306)/gorm_test?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil{
panic(err)
}

}

设置Logger

设置全局logger,会在我们执行每哥sql语句的时候打印出每一行sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//设置全局logger,会在我们执行每哥sql语句的时候打印出每一行sql
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer(日志输出的目标,前缀和日志包含的内容——译者注)
logger.Config{
SlowThreshold: time.Second, // 慢 SQL 阈值
LogLevel: logger.Info, // 日志级别
IgnoreRecordNotFoundError: true, // 忽略ErrRecordNotFound(记录未找到)错误
Colorful: true, // 彩色打印
},
)

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
Logger: newLogger,
})

基本使用

1
2
3
4
5
6
7
8
type Product struct {
gorm.Model
Code string
Price uint
}

//定义一个表结构,将表结构直接生成对应的表 - migrations
_ = db.AutoMigrate(&Product{})

日志输出:

1
2
3
4
5
6
7
8
9
10
11
12
2022/03/05 14:55:56 E:/Project/Go/pkg/mod/gorm.io/driver/mysql@v1.3.2/migrator.go:228
[0.511ms] [rows:-] SELECT DATABASE()

2022/03/05 14:55:56 E:/Project/Go/pkg/mod/gorm.io/driver/mysql@v1.3.2/migrator.go:231
[1.344ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'gorm_test%' ORDER BY SCHEMA_NAME='gorm_test' DESC limit 1

2022/03/05 14:55:56 E:/Project/gorm/t1/main.go:42
[1.354ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'gorm_test' AND table_name = 'products' AND table_type = 'BASE TABLE'

2022/03/05 14:55:56 E:/Project/gorm/t1/main.go:42
[13.375ms] [rows:0] CREATE TABLE `products` (`id` bigint unsigned AUTO_INCREMENT,`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`code` longtext,`price` bigint unsigned,PRIMARY KEY (`id`),INDEX idx_products_deleted_at (`deleted_at`))

CRUD

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Create
db.Create(&Product{Code: "D42", Price: 100})

// Read
var product Product
db.First(&product, 1) // 根据整型主键查找
db.First(&product, "code = ?", "D42") // 查找 code 字段值为 D42 的记录

// Update - 将 product 的 price 更新为 200
db.Model(&product).Update("Price", 200)
// Update - 更新多个字段
db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // 仅更新非零值字段
db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})

// Delete - 删除 product 逻辑删除
db.Delete(&product, 1)

日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:45
[17.892ms] [rows:1] INSERT INTO `products` (`created_at`,`updated_at`,`deleted_at`,`code`,`price`) VALUES ('2022-03-05 15:10:20.517','2022-03-05 15:10:20.517',NULL,'D42',100)

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:49
[0.597ms] [rows:1] SELECT * FROM `products` WHERE `products`.`id` = 1 AND `products`.`deleted_at` IS NULL ORDER BY `products`.`id` LIMIT 1

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:50
[0.577ms] [rows:1] SELECT * FROM `products` WHERE code = 'D42' AND `products`.`deleted_at` IS NULL AND `products`.`id` = 1 ORDER BY `products`.`id` LIMIT 1

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:53
[1.882ms] [rows:1] UPDATE `products` SET `price`=200,`updated_at`='2022-03-05 15:10:20.537' WHERE `products`.`deleted_at` IS NULL AND `id` = 1

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:55
[1.437ms] [rows:1] UPDATE `products` SET `updated_at`='2022-03-05 15:10:20.539',`code`='F42',`price`=200 WHERE `products`.`deleted_at` IS NULL AND `id` = 1

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:56
[1.211ms] [rows:1] UPDATE `products` SET `code`='F42',`price`=200,`updated_at`='2022-03-05 15:10:20.54' WHERE `products`.`deleted_at` IS NULL AND `id` = 1

2022/03/05 15:10:20 E:/Project/gorm/t1/main.go:59
[1.800ms] [rows:1] UPDATE `products` SET `deleted_at`='2022-03-05 15:10:20.542' WHERE `products`.`id` = 1 AND `products`.`id` = 1 AND `products`.`deleted_at` IS NULL

更新零值的情况

通过结构体变量更新字段值, gorm库会忽略零值字段。就是字段值等于0, nil, “”, false这些值会被忽略掉,不会更新。

1
2
//这种情况下会被忽略
db.Model(&product).Updates(Product{Price: 200, Code: "")

方法一:

使用map代替结构体

1
db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": ""})

方法二:

通过更改类型sql.NullString

1
2
3
4
5
6
7
8
9
type Product struct {
gorm.Model
Code sql.NullString
Price uint
}
// Create
db.Create(&Product{Code: sql.NullString{"D42", true}, Price: 100})
// Update - 更新多个字段
db.Model(&product).Updates(Product{Price: 200, Code: sql.NullString{"", true}})

方法三:

使用指针类型

1
2
3
type User struct {
Email *string
}
1
2
empty := ""
db.Model(&User{ID:1}).Updates(User{Email: &empty})

自定义Model

gorm的默认model如下

1
2
3
4
5
6
7
// gorm.Model 的定义
type Model struct {
ID uint `gorm:"primaryKey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}

我们也可以自定义model

1
2
3
4
type User struct {
UserID uint `gorm:"primaryKey"`
Name string `gorm:"column:user_name;type:varchar(50)"`
}

gorm tag:

字段 介绍
column 指定 db 列名
type 列数据类型,推荐使用兼容性好的通用类型,例如:所有数据库都支持 bool、int、uint、float、string、time、bytes 并且可以和其他标签一起使用,例如:not nullsize, autoIncrement… 像 varbinary(8) 这样指定数据库数据类型也是支持的。在使用指定数据库数据类型时,它需要是完整的数据库数据类型,如:MEDIUMINT UNSIGNED not NULL AUTO_INCREMENT
size 指定列大小,例如:size:256
primaryKey 指定列为主键
unique 指定列为唯一
default 指定列的默认值
precision 指定列的精度
scale 指定列大小
not null 指定列为 NOT NULL
autoIncrement 指定列为自动增长
autoIncrementIncrement 自动步长,控制连续记录之间的间隔
embedded 嵌套字段
embeddedPrefix 嵌入字段的列名前缀
autoCreateTime 创建时追踪当前时间,对于 int 字段,它会追踪秒级时间戳,您可以使用 nano/milli 来追踪纳秒、毫秒时间戳,例如:autoCreateTime:nano
autoUpdateTime 创建/更新时追踪当前时间,对于 int 字段,它会追踪秒级时间戳,您可以使用 nano/milli 来追踪纳秒、毫秒时间戳,例如:autoUpdateTime:milli
index 根据参数创建索引,多个字段使用相同的名称则创建复合索引,查看 索引 获取详情
uniqueIndex index 相同,但创建的是唯一索引
check 创建检查约束,例如 check:age > 13,查看 约束 获取详情
<- 设置字段写入的权限, <-:create 只创建、<-:update 只更新、<-:false 无写入权限、<- 创建和更新权限
-> 设置字段读的权限,->:false 无读权限
- 忽略该字段,- 无读写权限
comment

创建

创建 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

1
2
3
4
5
6
7
8
9
10
11
type User struct {
ID uint
Name string
Email *string
Age uint8
Birthday *time.Time
MemberNumber sql.NullString
ActivatedAt sql.NullTime
CreatedAt time.Time
UpdatedAt time.Time
}

基础插入

1
db.Create(&User{Name:"Bobby"})
1
2
3
4
5
6
7
8
user := User{
Name : "Bobby",
}
result := db.Create(&user)

fmt.Println(user.ID) // 返回插入数据的主键 // 1
fmt.Println(result.Error) // 返回 error //<nil>
fmt.Println(result.RowsAffected) // 返回插入记录的条数 //1

批量插入

  • 一次性插入所有sql
1
2
3
4
5
6
var users = []User{{Name: "x1"}, {Name: "x2"}, {Name: "x3"}}
db.Create(&users)

for _, user := range users {
fmt.Println(user.ID) // 1,2,3
}
  • 将sql分批次插入
1
db.CreateInBatches(users, 100)

使用map

1
2
3
4
5
6
7
8
9
db.Model(&User{}).Create(map[string]interface{}{
"Name": "x", "Age": 18,
})

// batch insert from `[]map[string]interface{}{}`
db.Model(&User{}).Create([]map[string]interface{}{
{"Name": "x1", "Age": 18},
{"Name": "x2", "Age": 20},
})

查询

查询 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

基础查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
var user User

// 获取第一条记录(主键升序)
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录,没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录(主键降序)
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result返回值

1
2
3
4
5
6
7
8
9
10
11
var user User
result := db.First(&user)
fmt.Println(result.RowsAffected) // 返回找到的记录数
fmt.Println(result.Error) // returns error or nil
// 检查 ErrRecordNotFound 错误
fmt.Println(errors.Is(result.Error, gorm.ErrRecordNotFound))

[17.517ms] [rows:1] SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
1
<nil>
false

通过主键查询

1
2
3
4
5
6
7
//通过主键查询
db.First(&user, 2)
//SELECT * FROM `users` WHERE `users`.`id` = 2 ORDER BY `users`.`id` LIMIT 1

db.First(&user, []int{1,2,3})
//SELECT * FROM `users` WHERE `users`.`id` IN (1,2,3) ORDER BY `users`.`id` LIMIT 1

检索全部对象

1
2
3
4
5
6
//检索全部对象
var users []User
db.Find(&users)
for _, user := range users{
fmt.Println(user.ID)
}

条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
var user User

//属性名要和数据库的字段对应
db.Where("name=?", "bobby").First(&user)
//只需与结构体字段对应
db.Where(&User{Name: "bobby"}).First(&user)

// 获取全部匹配的记录
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';


// Struct
//不会查询零值!!若要查询零值可以使用Map
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// 主键切片条件
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

更新

更新 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

save

1
2
3
4
5
//save方法是集create和update于一体的操作
var user User
db.First(&user)
user.Age = 10;
db.Save(&user)

条件更新

1
2
3
4
5
6
7
8
9
10
11
12
// 条件更新
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;

// User 的 ID 是 `111`
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// 根据条件和 model 的值进行更新
db.Model(&user).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;

更新多列

1
2
3
4
5
6
7
8
// 根据 `struct` 更新属性,只会更新非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 根据 `map` 更新属性
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

更新选定字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 使用 Map 进行 Select
// User's ID is `111`:
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello' WHERE id=111;

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

// 使用 Struct 进行 Select(会 select 零值的字段)
db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
// UPDATE users SET name='new_name', age=0 WHERE id=111;

// Select 所有字段(查询包括零值字段的所有字段)
db.Model(&user).Select("*").Update(User{Name: "jinzhu", Role: "admin", Age: 0})

// Select 除 Role 外的所有字段(包括零值字段的所有字段)
db.Model(&user).Select("*").Omit("Role").Update(User{Name: "jinzhu", Role: "admin", Age: 0})

删除

删除 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

1
2
3
4
5
6
7
8
// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

根据主键删除

1
2
3
4
5
6
7
8
9
db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;

db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);

批量删除

1
2
3
4
5
6
db.Where("email LIKE ?", "%jinzhu%").Delete(&Email{})
// DELETE from emails where email LIKE "%jinzhu%";

db.Delete(&Email{}, "email LIKE ?", "%jinzhu%")
// DELETE from emails where email LIKE "%jinzhu%";

软删除

如果您的模型包含了一个 gorm.deletedat 字段(gorm.Model 已经包含了该字段),它将自动获得软删除的能力!

拥有软删除能力的模型调用 Delete 时,记录不会被数据库。但 GORM 会将 DeletedAt 置为当前时间, 并且你不能再通过普通的查询方法找到该记录。

1
2
3
4
5
6
7
8
9
10
11
// user 的 ID 是 `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 批量删除
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// 在查询时会忽略被软删除的记录
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

如果不想引入 gorm.Model,可以这样启用软删除特性:

1
2
3
4
5
type User struct {
ID int
Deleted gorm.DeletedAt
Name string
}