本文介绍 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 的部分特性：
type Abc struct {
ID uint `gorm:"primaryKey;auto_increment"`
Name string `gorm:"type:varchar(32); size:32; not null; unique; default:''"`
// Name string `gorm:"default:galeone"`
// Age int64 `gorm:"default:18"`
CreatedAt time.Time `gorm:"type:date"`
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}
删除
# 软删除
db.Delete(&user)
# 物理删除
db.Unscoped().Delete(&users)
自定义类型
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)
}
json 类型
- sqlite, mysql, postgres supported，参考、docs
# 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),
}
serializer
type User struct {
Name []byte `gorm:"serializer:json"`
Roles Roles `gorm:"serializer:json"`
Contracts map[string]interface{} `gorm:"serializer:json"`
JobInfo Job `gorm:"type:bytes;serializer:gob"`
CreatedTime int64 `gorm:"serializer:unixtime;type:time"` // store int as datetime into database
}
type Post struct {
Title string
Tags []string `gorm:"serializer:json"`
}
索引 index
type User struct {
Name string `gorm:"index"`
Name2 string `gorm:"index:idx_name,unique"`
Name3 string `gorm:"index:,sort:desc,collate:utf8,type:btree,length:10,where:name3 != 'jinzhu'"`
Name4 string `gorm:"uniqueIndex"`
Age int64 `gorm:"index:,class:FULLTEXT,comment:hello \\, world,where:age > 10"`
Age2 int64 `gorm:"index:,expression:ABS(age)"`
}
唯一索引 uniqueIndex
Some_key1 uint `gorm:"uniqueIndex:some_key;type:varchar(256)"
Some_key2 uint `gorm:"uniqueIndex:some_key;type:varchar(256)"
- 其他实现：通过数据库冗余字段实现，值为多个键值的 hash
hook
GORM 通过 HOOK 机制是在模型的生命周期中提供了钩子函数，使用场景：如自动填充创建时间、更新时间、软删除、数据加密等- BeforeSave
- BeforeCreate
- AfterCreate
- BeforeUpdate
- AfterUpdate
- BeforeDelete
- AfterDelete
- 创建时间
debug 信息
db.Debug().Save(&user)
集成 prometheus
x := prometheus.New(prometheus.Config{
DBName: "db1", // `DBName` as metrics label
RefreshInterval: 15, // refresh metrics interval (default 15 seconds)
PushAddr: "prometheus pusher address", // push metrics if `PushAddr` configured
StartServer: false, // start http server to expose metrics 不单独启动服务
HTTPServerPort: 8080, // configure http server port, default port 8080 (if you have configured multiple instances, only the first `HTTPServerPort` will be used to start server)
MetricsCollector: []prometheus.MetricsCollector{
&prometheus.MySQL{VariableNames: []string{"Threads_running"}},
},
Labels: map[string]string{
"instance": "127.0.0.1", // config custom labels if necessary
},
})
x.Ini...xx 即可将 conllector 注册好
示例
基本使用
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)
}
type User struct {
gorm.Model
UserId int64 `gorm:"index"` // 设置一个普通的索引，没有设置索引名，gorm会自动命名
Birtheday time.Time
Age int `gorm:"column:age"` // column:一个tag，可以设置列名称
Name string `gorm:"size:255;index:idx_name_add_id"` // size:设置长度大小，index:设置索引，这个就取了一个索引名
Num int `gorm:"AUTO_INCREMENT"`
Email string `gorm:"type:varchar(100);unique_index"` // type:定义字段类型和大小
AddressID sql.NullInt64 `gorm:"index:idx_name_add_id"`
IgnoreMe int `gorm:"_"`
Description string `gorm:"size:2019;comment:'用户描述字段'"` // comment：字段注释
Status string `gorm:"type:enum('published', 'pending', 'deleted');default:'pending'"`
}
type Tag struct {
Id uint `gorm:"column:id;type:int(11) unsigned;primary_key;AUTO_INCREMENT" json:"id"`
TagName string `gorm:"column:tag_name;type:varchar(20);comment:关键字;NOT NULL" json:"tag_name"`
CreatedAt time.Time `gorm:"column:created_at;type:datetime;comment:创建时间" json:"created_at"`
UpdatedAt time.Time `gorm:"column:updated_at;type:datetime;comment:更新时间" json:"updated_at"`
OnlineAt time.Time `gorm:"column:online_at;type:datetime;comment:上线时间" json:"online_at"`
}
// TableName -
func (m *Tag) TableName() string {
return "tag"
}
SelectOrUpdate
// 找到了 `name` = `jinzhu` 的 user，依然会根据 Assign 更新记录
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// UPDATE users SET age=20 WHERE id = 111;
// user -> User{ID: 111, Name: "jinzhu", Age: 20}
批量插入
大批量插入回出现错误：Prepared statement contains too many placeholders，mysql 默认占位符大小：m*n<65535，m 是行数，n 是列数
# 每次插入 1000 条
for i := 0; i < len(items); i += 1000 {
end := i + 1000
if end > len(items) {
end = len(items)
}
err := db.Table(tableName).CreateInBatches(items[i:end], len(items[i:end])).Error
if err != nil {
return err
}
}
Upsert / On Conflict
分页
func Paginate(r *http.Request) func(db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
q := r.URL.Query()
page, _ := strconv.Atoi(q.Get("page"))
if page <= 0 {
page = 1
}
pageSize, _ := strconv.Atoi(q.Get("page_size"))
switch {
case pageSize > 100:
pageSize = 100
case pageSize <= 0:
pageSize = 10
}
offset := (page - 1) * pageSize
return db.Offset(offset).Limit(pageSize)
}
}
db.Scopes(Paginate(r)).Find(&users)
db.Scopes(Paginate(r)).Find(&articles)
每天分表
// 摘自互联网
package database
import (
"time"
"gorm.io/gorm"
)
type Daily struct {
ID int64 `json:"id" gorm:"column:id;primaryKey"`
Name string `json:"name" gorm:"column:name"`
}
// TodayName 今天的表名
func (d Daily) TodayName() func(*gorm.DB) *gorm.DB {
return d.DateName(time.Now())
}
// TomorrowName 明天的表名
func (d Daily) TomorrowName() func(*gorm.DB) *gorm.DB {
date := time.Now().Add(24 * time.Hour)
return d.DateName(date)
}
// DateName 根据时间生成对应的表名，表名规则：daily_yyyy_MM_dd
func (Daily) DateName(date time.Time) func(*gorm.DB) *gorm.DB {
return func(tx *gorm.DB) *gorm.DB {
name := date.Format("daily_2006_01_02")
return tx.Table(name)
}
}
// 定时任务提前一天建好表
var daily1 Daily
db.Scopes(daily1.TomorrowName()).AutoMigrate(&daily1)
// 查询一条今日数据
var daily2 Daily
db.Scopes(daily2.TodayName()).First(&daily2)
枚举
type Role string
const (
Admin Role = "admin"
Moderator Role = "moderator"
User Role = "user"
)
type User struct {
ID uint
Role Role `gorm:"type:enum('admin', 'moderator', 'user')"`
}
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)
Failed to auto migrate, but got error Error 1170: BLOB/TEXT column ‘user_refer’ used in key specification without a key length
type CreditCard struct {
UserRefer string `gorm:"size:191"`
}