GORM使用
mysql连接设置连接池数据
import (
"gorm.io/driver/clickhouse"
"gorm.io/gorm"
)
func main() {
dsn := "tcp://localhost:9000?database=gorm&username=gorm&password=gorm&read_timeout=10&write_timeout=20"
db, err := gorm.Open(clickhouse.Open(dsn), &gorm.Config{})
// 开启debug
db.Debug()
db.LogMode(true)
// 维护连接池信息
sqlDB, err := db.DB()
// Ping
sqlDB.Ping()
// Close
// sqlDB.Close()
// 返回数据库统计信息
sqlDB.Stats()
// SetMaxIdleConns 设置空闲连接池中连接的最大数量
sqlDB.SetMaxIdleConns(10)
// SetMaxOpenConns 设置打开数据库连接的最大数量。
sqlDB.SetMaxOpenConns(100)
// SetConnMaxLifetime 设置了连接可复用的最大时间。
sqlDB.SetConnMaxLifetime(time.Hour)
// Auto Migrate
db.AutoMigrate(&User{})
// Set table options
db.Set("gorm:table_options", "ENGINE=Distributed(cluster, default, hits)").AutoMigrate(&User{})
// 插入
db.Create(&user)
// 查询
db.Find(&user, "id = ?", 10)
// 批量插入
var users = []User{user1, user2, user3}
db.Create(&users)
// ...
}
gorm.Model嵌套
GORM 定义一个 gorm.Model 结构体，其包括字段 ID、CreatedAt、UpdatedAt、DeletedAt.
type User struct {
gorm.Model
Name string
}
字段权限控制
GORM 允许您用标签控制字段级别的权限。这样您就可以让一个字段的权限是只读、只写、只创建、只更新或者被忽略
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 读写会忽略该字段
}
创建/更新时间追踪
type User struct {
CreatedAt time.Time // 在创建时，如果该字段值为零值，则使用当前时间填充
UpdatedAt int // 在创建时该字段值为零值或者在更新时，使用当前时间戳秒数填充
Updated int64 `gorm:"autoUpdateTime:nano"` // 使用时间戳填纳秒数充更新时间
Updated int64 `gorm:"autoUpdateTime:milli"` // 使用时间戳毫秒数填充更新时间
Created int64 `gorm:"autoCreateTime"` // 使用时间戳秒数填充创建时间
}
创建
1.)创建指定字段
db.Select("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")
2.)忽略部分字段
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")
3.)批量插入
var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)
var users = []User{{name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}
// 数量为 100
db.CreateInBatches(users, 100)
// batch insert from `[]map[string]interface{}{}`
db.Model(&User{}).Create([]map[string]interface{}{
{"Name": "jinzhu_1", "Age": 18},
{"Name": "jinzhu_2", "Age": 20},
})
4.）使用默认值
type User struct {
ID string `gorm:"default:uuid_generate_v3()"` // db func
FirstName string `gorm:"default:jone"`
LastName string
Age uint8
FullName string `gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"`
}
创建钩子
GORM 允许用户定义的钩子有 BeforeSave, BeforeCreate, AfterSave, AfterCreate 创建记录时将调用这些钩子方法
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
u.UUID = uuid.New()
if u.Role == "admin" {
return errors.New("invalid role")
}
return
}
// 跳过钩子
DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)
查询
1.)查询单个对象
// 获取第一条记录（主键升序）
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;
db.Limit(1).Find(&user)
// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)
2.) 主键索引
db.First(&user, 10)
// SELECT * FROM users WHERE id = 10;
db.Find(&users, []int{1,2,3})
// SELECT * FROM users WHERE id IN (1,2,3);
3.)检索全部
// 获取全部记录
result := db.Find(&users)
// SELECT * FROM users;
4.)条件查询
// String 条件
// 获取第一条匹配的记录
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// 获取全部匹配的记录
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 条件
// Struct
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);
// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";
// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";
// 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;
5.)零值的使用
// 默认忽略零值
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";
// 正确使用：
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
6.）分页查询
// 通过 -1 消除 Limit 条件
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)
db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;
db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;
7.）排序分组
// 多个 order
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for rows.Next() {
...
}
type Result struct {
Date time.Time
Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
8.)多表联合查询
type result struct {
Name string
Email string
}
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
}
工具使用
原生SQL生成GORM结构体
https://sql2gorm.mccode.info/
https://github.com/cascax/sql2gorm
https://juejin.cn/post/6844903508823506958
https://segmentfault.com/a/1190000021441127
https://www.jianshu.com/p/288b9296e4b4