gorm 使用介绍

本文介绍go中grom的使用。ORM(Object Relation Mapping, 关系对象映射)可以把Golang Struct模型对象映射到关系型数据库结构中。

介绍

gorm 是使用 golang 语言开发的 ORM 库，源码地址：https://github.com/go-gorm/gorm

关系型数据库与Golang对象的对应关系如下：

关系型数据库	Golang
表	struct
列	struct attr
行数据	struct 对象
数据库操作	struct 对象方法的调用

gorm 的部分特性：

• 添加struct属性，支持自动 migrate 变化到数据库

• 删除struct属性，不会删除数据库的列

• db migrate 详细参考

• Declaring Models

type Abc struct {
  ID        uint           `gorm:"primaryKey;auto_increment"`
  Name      string         `gorm:"type:varchar(32); size:32; not null; unique; default:''"`
  CreatedAt time.Time      `gorm:"type:date"`
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
}

• 调整表名
• Many to Many

自定义类型

GORM 的自定义数据类型必须实现 Scanner/Valuer 接口

• Scanner 接口的 Scan 方法实现从数据库读取数据到 Go 变量时进行的解析处理
• Valuer 接口的 Value 方法实现将 Go 变量存到数据库时进行编码处理

type User struct {
	ID    uint `gorm:"primary_key"`
	Name  string
	Cards Card `gorm:"json"`
}

type Card struct { // 指定json的Tag。
	Type     int    `json:"type"`
	Account  string `json:"account"`
	Password string `json:"password"`
}

// Scan 解码json字符串
func (card *Card) Scan(val interface{}) error {
	b, _ := val.([]byte)
	return json.Unmarshal(b, card)
}

// Value 编码json
func (card Card) Value() (value driver.Value, err error) {
	return json.Marshal(card)
}
source repo / download raw

json 类型

sqlite, mysql, postgres supported，参考

# demo1
import "gorm.io/datatypes"

type UserWithJSON struct {
	gorm.Model
	Name       string
	Attributes datatypes.JSON
}

DB.Create(&User{
	Name:       "json-1",
	Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}

# demo2
import "gorm.io/datatypes"

type Tag struct {
	Name  string
	Score float64
}

type UserWithJSON struct {
	gorm.Model
	Name       string
	Tags       datatypes.JSONSlice[Tag]
}

var tags = []Tag{{Name: "tag1", Score: 0.1}, {Name: "tag2", Score: 0.2}}
var user = UserWithJSON{
	Name: "hello",
	Tags: datatypes.NewJSONSlice(tags),
}
source repo / download raw

示例

package main

import (
	"fmt"
	"log"
	"os"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type Product struct {
	gorm.Model
	Code  string
	Price uint
	CreatedAt ...
	UpdatedAt ...
}

func (p *Product) BeforeCreate(scope *gorm.Scope) error {
	scope.SetColumn("CreatedAt", time.Now().Unix())
	return nil
}

func (p *Product) BeforeUpdate(scope *gorm.Scope) error {
	scope.SetColumn("UpdatedAt", time.Now().Unix())
	return nil
}

func main() {
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // Slow SQL threshold
			LogLevel:                  logger.Info, // Log level
			IgnoreRecordNotFoundError: true,        // Ignore ErrRecordNotFound error for logger
			Colorful:                  false,       // Disable color
		},
	)

	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{Logger: newLogger})
	if err != nil {
		panic("failed to connect database")
	}

	// Migrate the schema
	db.AutoMigrate(&Product{})

	// Create
	db.Create(&Product{Code: "D42", Price: 100})

	// Read
	var product Product
	db.First(&product, 1)                 // find product with integer primary key
	db.First(&product, "code = ?", "D42") // find product with code D42

	fmt.Println(product)

	var p1 Product
	db.Last(&p1)
	fmt.Println(p1)
	var ps []Product
	db.Find(&ps, "code like ?", "D%")
	fmt.Println(ps)

	// Update - update product's price to 200
	db.Model(&product).Update("Price", 200)
	// Update - update multiple fields
	db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // non-zero fields
	db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})

	// Delete - delete product
	db.Delete(&product, 1)
}
source repo / download raw

F&Q

日志大量出现 record not found

解决方式一：

db.Callback().Query().Before("gorm:query").Register("disable_raise_record_not_found", func(d *gorm.DB) {
  d.Statement.RaiseErrorOnNotFound = false
})

解决方式二：参考

newLogger := logger.New(
  log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
  logger.Config{
    SlowThreshold:              time.Second,   // Slow SQL threshold
    LogLevel:                   logger.Silent, // Log level
    IgnoreRecordNotFoundError: true,           // Ignore ErrRecordNotFound error for logger
    ParameterizedQueries:      true,           // Don't include params in the SQL log
    Colorful:                  false,          // Disable color
  },
)

// Globally mode
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
  Logger: newLogger,
})

// Continuous session mode
tx := db.Session(&Session{Logger: newLogger})
tx.First(&user)
tx.Model(&user).Update("Age", 18)

Prepared statement contains too many placeholders

一次插入的数据量太大了（MySQL 默认占位符大小m*n<65535，m是行数，n是列数）

db.Table(<tableName>).CreateInBatches(xxx)