参考GORM摘取

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

GORM

一、GORM基本概念

1.约定

结构体与表名的对应，字段与列名的对应，以及GORM框架中自带的字段并能自动处理
Primary Key: GORM uses a field named ID as the default primary key for each model.

Table Names: By default, GORM converts struct names to snake_case and pluralizes them for table names. For instance, a User struct becomes users in the database.

Column Names: GORM automatically converts struct field names to snake_case for column names in the database.

Timestamp Fields: GORM uses fields named CreatedAt and UpdatedAt to automatically track the creation and update times of records.

2.gorm.Model

// gorm.Model 的定义
type Model struct {
  ID        uint           `gorm:"primaryKey"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
}
Fields Included:
ID: A unique identifier for each record (primary key).
CreatedAt: Automatically set to the current time when a record is created.
UpdatedAt: Automatically updated to the current time whenever a record is updated.
DeletedAt: Used for soft deletes (marking records as deleted without actually removing them from the database).

建议用法：将Model嵌入到自定义的结构体中

3.字段级权限控制

注意： 使用 GORM Migrator 创建表时，不会创建被忽略的字段

type User struct {
  Name string `gorm:"<-:create"` // 允许读和创建
  Name string `gorm:"<-:update"` // 允许读和更新
  Name string `gorm:"<-"`        // 允许读和写（创建和更新）
  Name string `gorm:"<-:false"`  // 允许读，禁止写
  Name string `gorm:"->"`        // 只读（除非有自定义配置，否则禁止写）
  Name string `gorm:"->;<-:create"` // 允许读和写
  Name string `gorm:"->:false;<-:create"` // 仅创建（禁止从 db 读）
  Name string `gorm:"-"`  // 通过 struct 读写会**忽略**该字段
  Name string `gorm:"-:all"`        // 通过 **struct 读写、迁移会忽略该字段**
  Name string `gorm:"-:migration"`  // 通过 **struct 迁移会忽略该字段**
}

4.自动更新时间戳

GORM 约定使用 CreatedAt、UpdatedAt 追踪创建/更新时间。如果您定义了这种字段，GORM 在创建、更新时会自动填充 当前时间
要使用不同名称的字段，您可以配置 autoCreateTime、autoUpdateTime 标签。
如果您想要保存 UNIX（毫/纳）秒时间戳，而不是 time，您只需简单地将 time.Time 修改为 int 即可

type User struct {
  CreatedAt time.Time // 在创建时，如果该字段值为零值，则使用当前时间填充
  UpdatedAt int       // 在创建时该字段值为零值或者在更新时，使用当前时间戳秒数填充
  Updated   int64 `gorm:"autoUpdateTime:nano"` // 使用时间戳纳秒数填充更新时间
  Updated   int64 `gorm:"autoUpdateTime:milli"` // 使用时间戳毫秒数填充更新时间
  **Created   int64 `gorm:"autoCreateTime"`      // 使用时间戳秒数填充创建时间**
}

注：默认使用model中的字段名或者自定义字段+标签方式，能够实现自动更新时间戳

5.字段嵌入

对于正常的结构体字段，你也可以**通过标签 embedded 将其嵌入**

type Author struct {
    Name  string
    Email string
}

type Blog struct {
  ID      int
  Author  Author `gorm:"embedded"`
  Upvotes int32
}

等效：
type Blog struct {
  ID    int64
  Name  string
  **Email string**
  Upvotes  int32
}

使用标签 embeddedPrefix 来为 db 中的字段名添加前缀，例如：

type Blog struct {
  ID      int
  Author  Author `gorm:"embedded;embeddedPrefix:author_"`
  Upvotes int32
}
// 等效于
type Blog struct {
  ID          int64
  AuthorName string
  **AuthorEmail string**   // 添加了前缀Author
  Upvotes     int32
}

6.字段标签

tag大小写不敏感，建议驼峰形式
支持标签： https://gorm.io/zh_CN/docs/models.html

数据库

1. 数据连接

2. 参数设置–连接池

GORM 使用 database/sql 来维护连接池

sqlDB, err := db.DB()
// SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections to the database.
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
sqlDB.SetConnMaxLifetime(time.Hour)

CRUD

创建

创建单个记录

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
 **`注：传入对象指针`**
result := db.Create(**&user**) // 通过数据的指针来创建       

user.ID             // 返回插入数据的主键
result.Error        // 返回 error
result.RowsAffected // 返回插入记录的条数

创建多项记录：

users := []*User{
    User{Name: "Jinzhu", Age: 18, Birthday: time.Now()},
    User{Name: "Jackson", Age: 19, Birthday: time.Now()},
}

**`注：传入对象指针的切片`**
result := db.Create(users) // 传递切片以插入多行数据         

result.Error        // 返回 error
result.RowsAffected // 返回插入记录的条数

创建指定字段

用指定的字段创建记录
创建记录并为指定字段赋值。

**`db.Select`**("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")
创建记录并忽略传递给 ‘Omit’ 的字段值

**`db.Omit`**("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`birthday`,`updated_at`) VALUES ("2020-01-01 00:00:00.000", "2020-07-04 11:05:21.775")

批量插入

要高效地插入大量记录，请将切片传递给Create方法。 GORM 将生成一条 SQL 来插入所有数据，以返回所有主键值，并触发 Hook 方法 It will begin a transaction when records can be split into multiple batches.

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)

// 返回每个对象的ID
for _, user := range users {
  user.ID // 1,2,3
}

指定批量插入时的最大size

var users = []User{{Name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}
// batch size 100
**db.CreateInBatches(users, 100)**

初始化时指定批量插入的size

db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
  CreateBatchSize: 1000,
})

db := db.Session(&gorm.Session{CreateBatchSize: 1000})

users = [5000]User{{Name: "jinzhu", Pets: []Pet{pet1, pet2, pet3}}...}
// 分成5次插入
db.Create(&users)

创建钩子

GORM allows user defined hooks to be implemented for BeforeSave, BeforeCreate, AfterSave, AfterCreate. These hook method will be called when creating a record, refer Hooks for details on the lifecycle

对象实现自定义钩子函数

func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
  u.UUID = uuid.New()

    if u.Role == "admin" {
        return errors.New("invalid role")
    }
    return
}

// 可通过标记跳过执行钩子函数
If you want to skip Hooks methods, you can use the SkipHooks session mode, for example:
DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)
DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)
DB.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(users, 100)

根据map创建

GORM supports create from map[string]interface{} and []map[string]interface{}{}, e.g:

db.Model(&User{}).Create(map[string]interface{}{
  "Name": "jinzhu", "Age": 18,
})

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

使用map创建时，不会调用hook函数，同时关联不会保存，主键不会回填
NOTE When creating from map, hooks won’t be invoked, associations won’t be saved and primary key values won’t be back filled

使用 SQL 表达式、Context Valuer 创建记录

注：直接使用结构类型时，需要结构体类型实现相关的方法

// Create from map
db.Model(User{}).Create(map[string]interface{}{
  "Name": "jinzhu",
  "Location": clause.Expr{SQL: "ST_PointFromText(?)", Vars: []interface{}{"POINT(100 100)"}},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"));

// Create from customized data type
type Location struct {
    X, Y int
}

// Scan implements the sql.Scanner interface
func (loc *Location) Scan(v interface{}) error {
  // Scan a value into struct from database driver
}

func (loc Location) GormDataType() string {
  return "geometry"
}

func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
  return clause.Expr{
    SQL:  "ST_PointFromText(?)",
    Vars: []interface{}{fmt.Sprintf("POINT(%d %d)", loc.X, loc.Y)},
  }
}

type User struct {
  Name     string
  Location Location
}

db.Create(&User{
  Name:     "jinzhu",
  Location: Location{X: 100, Y: 100},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"))

关联创建

When creating some data with associations, if its associations value is not zero-value, those associations will be upserted, and its Hooks methods will be invoked.

type CreditCard struct {
  gorm.Model
  Number   string
  UserID   uint
}

type User struct {
  gorm.Model
  Name       string
  CreditCard CreditCard
}

db.Create(&User{
  Name: "jinzhu",
  CreditCard: CreditCard{Number: "411111111111"}
})

// 插入两条数据
// INSERT INTO `users` ...
// INSERT INTO `credit_cards` ...

忽略关联对象更新
You can skip saving associations with Select, Omit, for example:

db.Omit("CreditCard").Create(&user)    // 忽略指定的关联对象

// skip all associations
db.Omit(clause.Associations).Create(&user)    // 忽略所有关联对象

定义默认值

默认值
You can define default values for fields with tag default, for example:

type User struct {
ID int64
Name string gorm:"default:galeone" // 缺省时的默认值
Age int64 gorm:"default:18"
}

注： 零值(0,空字符串，false)会被默认值代替， 但可定义为指针类型或者Scanner/Valuer 避免使用默认值
Then the default value will be used when inserting into the database for zero-value fields

NOTE Any zero value like 0, ‘’, false won’t be saved into the database for those fields defined default value, you might want to use pointer type or Scanner/Valuer to avoid this, for example:

type User struct {
gorm.Model
Name string
Age *int gorm:"default:18"
Active sql.NullBool gorm:"default:true"
}

NOTE You have to setup the default tag for fields having default or virtual/generated value in database, if you want to skip a default value definition when migrating, you could use default:(-), for example:

type User struct {
ID string gorm:"default:uuid_generate_v3()" // db func
FirstName string
LastName string
Age uint8
FullName string gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"
}
When using virtual/generated value, you might need to disable its creating/updating permission, check out Field-Level Permission

Upsert 及冲突

GORM provides compatible Upsert support for different databases

import "gorm.io/gorm/clause"

// Do nothing on conflict
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)

// Update columns to default value on `id` conflict
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET ***; SQL Server
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE ***; MySQL

// Use SQL expression
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.Assignments(map[string]interface{}{"count": gorm.Expr("GREATEST(count, VALUES(count))")}),
}).Create(&users)
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `count`=GREATEST(count, VALUES(count));

// Update columns to new value on `id` conflict
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age`=VALUES(age); MySQL

// Update all columns to new value on conflict except primary keys and those columns having default values from sql func
db.Clauses(clause.OnConflict{
  UpdateAll: true,
}).Create(&users)
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age`=VALUES(age), ...; MySQL

查询

检索单个对象
GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误

// 获取第一条记录（主键升序） 指定顺序
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 := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)

如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。

Find方法是搜索全部对象
对单个对象使用Find而不带limit，db.Find(&user)将会查询整个表并且只返回第一个对象，这是性能不高并且不确定的。

如果对象设置了主键，条件查询将不会覆盖主键的值，而是用 And 连接条件。 例如：

var user = User{ID: 10}
db.Where("id = ?", 20).First(&user)
// SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1

这个查询将会给出record not found错误 所以，在你想要使用例如 user 这样的变量从数据库中获取新值前，需要将例如 id 这样的主键设置为nil。

查询条件为零值：对象传入查询参数时，零值会被忽略，此时需要map方式传入入参

 When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";
To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:

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

内联条件

Query conditions can be inlined into methods like First and Find in a similar way to Where.

// Get by primary key if it were a non-integer type
db.First(&user, "id = ?", "string_primary_key")
// SELECT * FROM users WHERE id = 'string_primary_key';

// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";

db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

// Struct
db.Find(&users, User{Age: 20})
// SELECT * FROM users WHERE age = 20;

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

API用法

1. Assign 与Attr

FirstOrInit ： 不会修改数据库记录

FirstOrInit	查询到记录	未查询到记录
Attr	返回数据库记录	返回新建对象，基于 查询条件值 和 指定值
Assign	修改查询数据为指定值	返回新建对象，基于 查询条件值 和 指定值

FirstOrCreate：存在修改数据库记录

FirstOrCreate	查询到记录	未查询到记录
Attr	不修改数据库为指定值	新建对象，基于 查询条件值 和 指定值
Assign	修改查询数据为指定值	新建对象，基于 查询条件值 和 指定值