GORM学习｜ 青训营笔记

Pluston
71 阅读4分钟

这是我参与「第五届青训营 」笔记创作活动的第4天

Gorm

教程：gorm.cn/docs/#Insta…

  1. 安装

go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

  1. 基本使用

  1. 定义gorm model

type User struct {
  ID           uint
  Name         string
  Email        *string
  Age          uint8
  Birthday     *time.Time
  MemberNumber sql.NullString
  ActivedAt    sql.NullTime
  CreatedAt    time.Time
  UpdatedAt    time.Time
}

约定：

  • 使用ID作为主键

  • 将结构名称复数化为snake_cases（蛇形负数）作为表名

  • 字段名snake_case作为列名

  • 使用CreatedAt，UpdatedAt跟踪创建/更新时间

  1. 数据库连接

GORM正式支持数据库MySQL、PostgreSQL、SQlite、SQL Server

mysql连接

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

func main() {
  // refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
  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{})
}

连接池

sqlDB，err := db.DB()

// SetMaxIdleConns设置空闲连接池中的最大连接数。
sqlDB.SetMaxIdle康奈斯（10）

// SetMaxOpenConns设置与数据库的最大开放连接数量。
sqlDB.SetMaxOpenConns（100）

// SetConnMaxLifetime设置连接可以重用的最大时间。
sqlDB.SetConnMaxLifetime（time.Hour）

  1. CRUD

  2. Create

创建一条

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

result := db.Create(&user) // pass pointer of data to Create

user.ID             // returns inserted data's primary key
result.Error        // returns error
result.RowsAffected // returns inserted records count

创建多条

将切片数据传递给方法Create，GORM将生成单个SQL语句来插入所有数据并回填主键值，也会调用钩子方法。

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

for _, user := range users {
  user.ID // 1,2,3
}

默认值

可以为default标签的字段定义默认值

type User struct {
  ID         int64
  Name       string `gorm:"default:galeone"`
  Age        int64  `gorm:"default:18"`
  uuid.UUID  UUID   `gorm:"type:uuid;default:gen_random_uuid()"` // db func
}

插入/冲突

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

// 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
  1. Query
  1. 检索单个对象

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;

// 获取最后记录，按主键 desc 排序
db.Last（&user）
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录计数
result.Error // 返回错误

// 检查错误 ErrRecordNotFound
errors.Is(result.Error, gorm.ErrRecordNotFound)
  1. 条件
// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

// Get all matched records
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';
  1. 排序
db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// Multiple orders
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;
  1. 选择特定字段
db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;
  1. Update

Updates支持使用struct或map[string]interface{}更新，使用struct更新时，默认情况下只会更新非零字段，需要更新非零字段时可以使用Map或Select

// Update attributes with `struct`, will only update non-zero fields
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;

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

如果想在更新时更新选定的字段或忽略某些字段，您可以使用Select，Omit

// Select with Map
// User's ID is `111`:
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
// UPDATE users SET name='hello' WHERE id=111;

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

// Select with Struct (select zero value fields)
DB.Model(&result).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
// UPDATE users SET name='new_name', age=0 WHERE id=111;
  1. Delete
  1. 物理删除
// Email's ID is `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// Delete with additional conditions
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

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. 软删除

如果模型包含gorm.DeletedAt字段（包含在gorm.Model中），将自动获得软删除功能！

type User struct {
  ID      int
  Deleted gorm.DeletedAt
  Name    string
}

调用Delete时，记录不会从数据库中删除，但GORM会将DeletedAt的值设置为当前时间，并且不再使用正常的查询方法找到数据。

// user's ID is `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

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

// Soft deleted records will be ignored when querying
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

查找软删除的数据

db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;
avatar
文章
阅读
粉丝