文章目录
一、gorm简介
1、简介
GORM 是 Go 语言中最受欢迎的 ORM 库之一,它提供了强大的功能和简洁的 API,让数据库操作变得更加简单和易维护。
GORM 官方支持的数据库类型有:MySQL、PostgreSQL、SQLite、SQL Server 和 TiDB
2、特性
● 全功能 ORM
● 关联 (Has One,Has Many,Belongs To,Many To Many,多态,单表继承)
● Create,Save,Update,Delete,Find 中钩子方法
● 支持 Preload、Joins 的预加载
● 事务,嵌套事务,Save Point,Rollback To Saved Point
● Context、预编译模式、DryRun 模式
● 批量插入,FindInBatches,Find/Create with Map,使用 SQL 表达式、Context Valuer 进行 CRUD
● SQL 构建器,Upsert,数据库锁,Optimizer/Index/Comment Hint,命名参数,子查询
● 复合主键,索引,约束
● Auto Migration
● 自定义 Logger
● 灵活的可扩展插件 API:Database Resolver(多数据库,读写分离)、Prometheus…
● 每个特性都经过了测试的重重考验
● 开发者友好
gorm源码:https://github.com/go-gorm/gorm
gorm官方文档:https://gorm.io/zh_CN/docs/index.html
二、入门使用
1、声明模型
GORM 使用模型(Model)来映射一张数据库表,模型是标准的 Go struct。
// 定义模型结构体
type User struct {
// gorm内置的model模型字段,可不使用
gorm.Model
Name string `gorm:"column:name"`
Email *string `gorm:"column:email"`
Age uint8 `gorm:"column:age"`
Birthday *time.Time `gorm:"column:birthday"`
// 在不指定 column 字段标签情况下,GORM 默认使用字段名的 snake_case 作为列名
MemberNumber sql.NullString `gorm:"column:member_number"`
ActivatedAt sql.NullTime `gorm:"column:activated_at"`
}
// 自定义表名,可忽略
// 若不指定TableName方法,GORM 默认使用结构体名的 snake_cases 作为表名
func (u *User) TableName() string {
return "user"
}
// gorm内置的model模型字段
type Model struct {
ID uint `gorm:"primarykey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt DeletedAt `gorm:"index"`
}
2、连接数据库
const MysqlConfig = "username:password@tcp(10.98.32.35:6315)/kms?charset=utf8mb4&parseTime=True&loc=Local"
// 自定义配置
func configGorm() *gorm.Config {
return &gorm.Config{
SkipDefaultTransaction: true,
DisableForeignKeyConstraintWhenMigrating: true,
PrepareStmt: true,
// 打印sql
Logger: logger.Default.LogMode(logger.Info),
}
}
// mysql.Open 返回 gorm.Dialector 对象
// gorm.Open 返回 *gorm.DB 对象
db, err := gorm.Open(mysql.Open(MysqlConfig), configGorm())
if err != nil {
fmt.Println(err)
panic("failed to connect!")
}
fmt.Println(db)
3、CRUD
// 插入数据
email := "u1@jianghushinian.com"
user := User{Name: "user1", Email: &email, Age: 18, Birthday: &now, MemberNumber:"1"}
// INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`) VALUES ('2023-05-22 22:14:47.814','2023-05-22 22:14:47.814',NULL,'user1','u1@jianghushinian.com',18,'2023-05-22 22:14:47.812',NULL,NULL)
result := db.Create(&user) // 通过数据的指针来创建
// 查询数据
var users []User
// SELECT * FROM `user` WHERE name = 'xiaoli'
result := db.Where("name = ?", "xiaoli").Find(&users)
// 更新数据
user.Name = "John"
user.Age = 20
// UPDATE `user` SET `name`='Jianghushinian',`updated_at`='2023-05-22 22:24:34.215' WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result := db.Model(&user).Update("name", "Jianghushinian")
// 删除数据
var user User
// UPDATE `user` SET `deleted_at`='2023-05-22 22:46:45.086' WHERE name = 'JiangHu' AND `user`.`deleted_at` IS NULL
result := db.Where("name = ?", "JiangHu").Delete(&user)
// result,依然是一个 *gorm.DB 对象;
// result.RowsAffected 可以拿到此次操作影响行数;
// result.Error 可以知道执行 SQL 是否出错
三、gorm原理剖析
以find()为例,了解内部实现原理
gorm.Open()
// 数据库连接,返回*gorm.DB
db, err := gorm.Open(mysql.Open(MysqlConfig), configGorm())
// 初始化db
func Open(dialector Dialector, opts ...Option) (db *DB, err error){
// 1.初始化配置,通过opts 来设置可变参数
config := &Config{}
// 2.配置进行应用
if d, ok := dialector.(interface{ Apply(*Config) error }); ok {
if err = d.Apply(config); err != nil {
return
}
}
// 3.new一个gorm.DB对象,默认clone=1
db = &DB{Config: config, clone: 1}
// 初始化执行函数
db.callbacks = initializeCallbacks(db)
...
initializeCallbacks()
// 这个对应的processor是 gorm.DB,也就是执行DB的Execute
// 但是这里只是创建callbacks结构体,其实是nil
func initializeCallbacks(db *DB) *callbacks {
return &callbacks{
processors: map[string]*processor{
"create": {db: db},
"query": {db: db},
"update": {db: db},
"delete": {db: db},
"row": {db: db},
"raw": {db: db},
},
}
}
gorm.Open()
func Open(dialector Dialector, opts ...Option) (db *DB, err error){
// 接上
...
if config.Dialector != nil {
// mysql驱动初始化
err = config.Dialector.Initialize(db)
if err != nil {
if db, err := db.DB(); err == nil {
_ = db.Close()
}
}
}
...
}
Dialector.Initialize(db)
func (dialector Dialector) Initialize(db *gorm.DB) (err error) {
// 默认为mysql
if dialector.DriverName == "" {
dialector.DriverName = "mysql"
}
if dialector.DefaultDatetimePrecision == nil {
dialector.DefaultDatetimePrecision = &defaultDatetimePrecision
}
// mysql连接池信息
if dialector.Conn != nil {
db.ConnPool = dialector.Conn
} else {
db.ConnPool, err = sql.Open(dialector.DriverName, dialector.DSN)
if err != nil {
return err
}
}
// 如果没有设置跳过初始化版本,就去获取版本
if !dialector.Config.SkipInitializeWithVersion {
err = db.ConnPool.QueryRowContext(context.Background(), "SELECT VERSION()").Scan(&dialector.ServerVersion)
if err != nil {
return err
}
...
// 根据不同的版本设置不同的配置
if strings.Contains(dialector.ServerVersion, "MariaDB") {
dialector.Config.DontSupportRenameIndex = true
dialector.Config.DontSupportRenameColumn = true
dialector.Config.DontSupportForShareClause = true
dialector.Config.DontSupportNullAsDefaultValue = true
withReturning = checkVersion(dialector.ServerVersion, "10.5")
} else if strings.HasPrefix(dialector.ServerVersion, "5.6.") {
dialector.Config.DontSupportRenameIndex = true
dialector.Config.DontSupportRenameColumn = true
dialector.Config.DontSupportForShareClause = true
} else if strings.HasPrefix(dialector.ServerVersion, "5.7.") {
dialector.Config.DontSupportRenameColumn = true
dialector.Config.DontSupportForShareClause = true
} else if strings.HasPrefix(dialector.ServerVersion, "5.") {
dialector.Config.DisableDatetimePrecision = true
dialector.Config.DontSupportRenameIndex = true
dialector.Config.DontSupportRenameColumn = true
dialector.Config.DontSupportForShareClause = true
}
if strings.Contains(dialector.ServerVersion, "TiDB") {
dialector.Config.DontSupportRenameColumnUnique = true
}
}
// register callbacks
// 注册固定的子句
callbackConfig := &callbacks.Config{
CreateClauses: CreateClauses,
QueryClauses: QueryClauses,
UpdateClauses: UpdateClauses,
DeleteClauses: DeleteClauses,
}
...
}
默认Clauses子句
// 注册mysql语法的子句
// CreateClauses create clauses
CreateClauses = []string{"INSERT", "VALUES", "ON CONFLICT"}
// QueryClauses query clauses
QueryClauses = []string{}
// UpdateClauses update clauses
UpdateClauses = []string{"UPDATE", "SET", "WHERE", "ORDER BY", "LIMIT"}
// DeleteClauses delete clauses
DeleteClauses = []string{"DELETE", "FROM", "WHERE", "ORDER BY", "LIMIT"}
Dialector.Initialize(db)
func (dialector Dialector) Initialize(db *gorm.DB) (err error) {
// 接上
...
//注册默认的callbacks
callbacks.RegisterDefaultCallbacks(db, callbackConfig)
...
}
RegisterDefaultCallbacks()
func RegisterDefaultCallbacks(db *gorm.DB, config *Config) {
...
// 获取插入数据的callbacks
createCallback := db.Callback().Create()
createCallback.Match(enableTransaction).Register("gorm:begin_transaction", BeginTransaction)
createCallback.Register("gorm:before_create", BeforeCreate)
createCallback.Register("gorm:save_before_associations", SaveBeforeAssociations(true))
createCallback.Register("gorm:create", Create(config))
createCallback.Register("gorm:save_after_associations", SaveAfterAssociations(true))
createCallback.Register("gorm:after_create", AfterCreate)
createCallback.Match(enableTransaction).Register("gorm:commit_or_rollback_transaction", CommitOrRollbackTransaction)
createCallback.Clauses = config.CreateClauses
// 获取查询数据的callbacks
queryCallback := db.Callback().Query()
queryCallback.Register("gorm:query", Query)
queryCallback.Register("gorm:preload", Preload)
queryCallback.Register("gorm:after_query", AfterQuery)
queryCallback.Clauses = config.QueryClauses
// 获取删除数据的callbacks
deleteCallback := db.Callback().Delete()
deleteCallback.Match(enableTransaction).Register("gorm:begin_transaction", BeginTransaction)
deleteCallback.Register("gorm:before_delete", BeforeDelete)
deleteCallback.Register("gorm:delete_before_associations", DeleteBeforeAssociations)
deleteCallback.Register("gorm:delete", Delete(config))
deleteCallback.Register("gorm:after_delete", AfterDelete)
deleteCallback.Match(enableTransaction).Register("gorm:commit_or_rollback_transaction", CommitOrRollbackTransaction)
deleteCallback.Clauses = config.DeleteClauses
// 获取更新数据的callbacks
updateCallback := db.Callback().Update()
updateCallback.Match(enableTransaction).Register("gorm:begin_transaction", BeginTransaction)
updateCallback.Register("gorm:setup_reflect_value", SetupUpdateReflectValue)
updateCallback.Register("gorm:before_update", BeforeUpdate)
updateCallback.Register("gorm:save_before_associations", SaveBeforeAssociations(false))
updateCallback.Register("gorm:update", Update(config))
updateCallback.Register("gorm:save_after_associations", SaveAfterAssociations(false))
updateCallback.Register("gorm:after_update", AfterUpdate)
updateCallback.Match(enableTransaction).Register("gorm:commit_or_rollback_transaction", CommitOrRollbackTransaction)
updateCallback.Clauses = config.UpdateClauses
// 获取更新数据的callbacks
rowCallback := db.Callback().Row()
rowCallback.Register("gorm:row", RowQuery)
rowCallback.Clauses = config.QueryClauses
// 支持原生sql查询
rawCallback := db.Callback().Raw()
rawCallback.Register("gorm:raw", RawExec)
rawCallback.Clauses = config.QueryClauses
}
最终的callbacks结果
where条件
func (db *DB) Where(query interface{}, args ...interface{}) (tx *DB) {
//获取db实例
tx = db.getInstance()
...
}
getInstance()
func (db *DB) getInstance() *DB {
//通过判断clone的值来决定是否清空查询条件
if db.clone > 0 {
// 返回的tx是新的db,新的db中clone=0(没有对clone赋值,默认是零值,int的零值就是0)
tx := &DB{Config: db.Config, Error: db.Error}
if db.clone == 1 {
// clone with new statement
// statement 中,连接池和上下文延用之前的,把条件和变量置为空;
tx.Statement = &Statement{
DB: tx,
ConnPool: db.Statement.ConnPool,
Context: db.Statement.Context,
// 清空查询条件
Clauses: map[string]clause.Clause{},
Vars: make([]interface{}, 0, 8),
}
} else {
// with clone statement
tx.Statement = db.Statement.clone()
tx.Statement.DB = tx
}
return tx
}
return db
}
小结:
- 获取的 db 跟原 db 的 clone 属性直接相关:
a. clone = 0,获取当前 db 实例,即不做处理;
b. clone = 1,返回一个新的 db,并清空查询条件;
c. clone = 2,返回一个新的 db,不清空查询条件;
where条件
func (db *DB) Where(query interface{}, args ...interface{}) (tx *DB) {
tx = db.getInstance()
...
// 接上
// 构建where条件(phone = ?)及数值,返回 []clause.Expression
if conds := tx.Statement.BuildCondition(query, args...); len(conds) > 0 {
// 将上面的表达式依次加入到tx.Statement的Clauses子查询中
tx.Statement.AddClause(clause.Where{Exprs: conds})
}
return
}
find()
// Find finds all records matching given conditions conds
func (db *DB) Find(dest interface{}, conds ...interface{}) (tx *DB) {
// 此时clone =0,就直接返回当前db,不做任何处理
tx = db.getInstance()
// conds是find内传入的条件,没传所以是nil,直接跳过
if len(conds) > 0 {
if exprs := tx.Statement.BuildCondition(conds[0], conds[1:]...); len(exprs) > 0 {
tx.Statement.AddClause(clause.Where{Exprs: exprs})
}
}
// dest是我们返回的结果
tx.Statement.Dest = dest
return tx.callbacks.Query().Execute(tx)
}
tx.callbacks.Query()
func (cs *callbacks) Query() *processor {
// 取出query对应的子句和callbacks
return cs.processors["query"]
}
Execute(tx)
func (p *processor) Execute(db *DB) *DB {
...
// 关联模型--表
if stmt.Model == nil {
stmt.Model = stmt.Dest
} else if stmt.Dest == nil {
stmt.Dest = stmt.Model
}
// parse model values
if stmt.Model != nil {
//stmt.Parse利用反射开始读取Model的结构,获取表名
// 如果结构体绑定了TableName(),则直接用返回值;
// 如果没有绑定,就会遍历结构体UserInfo,返回user_info作为表名
if err := stmt.Parse(stmt.Model); err != nil && (!errors.Is(err, schema.ErrUnsupportedDataType) || (stmt.Table == "" && stmt.TableExpr == nil && stmt.SQL.Len() == 0)) {
if errors.Is(err, schema.ErrUnsupportedDataType) && stmt.Table == "" && stmt.TableExpr == nil {
db.AddError(fmt.Errorf("%w: Table not set, please set it like: db.Model(&user) or db.Table(\"users\")", err))
} else {
db.AddError(err)
}
}
}
...
// 循环callbacks,执行查询
for _, f := range p.fns {
f(db)
}
...
return db
}
callbacks-Query
func Query(db *gorm.DB) {
if db.Error == nil {
// 构建查询sql
// 将各个Clause子句组装生成 SQL及Vars参数
BuildQuerySQL(db)
if !db.DryRun && db.Error == nil {
// 执行查询,返回的rows是*sql.Rows
rows, err := db.Statement.ConnPool.QueryContext(db.Statement.Context, db.Statement.SQL.String(), db.Statement.Vars...)
if err != nil {
db.AddError(err)
return
}
defer func() {
db.AddError(rows.Close())
}()
// Scan将结果快速方便地将数据存储到指定数据类型中
gorm.Scan(rows, db, 0)
}
}
}
BuildQuerySQL(db)生成的SQL截图
callbacks-Preload
联表查询时才需要使用
callbacks-AfterQuery
暂未使用
四、避坑指南
1、GORM非零字段查询
当使用struct进行查询时,GORM将只使用非零字段进行查询,这意味着如果您的字段的值为0、‘’、false或其他零值,则不会用于构建查询条件,所以要在查询条件中包括零值,可以使用map,该映射将包括所有键值作为查询条件,例如:
// struct会忽略Age=0这个条件
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";
// map不会忽略Age=0这个条件
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
2、关于gorm使用first自动加主键排序的坑
当使用First查询时,会自动排序带上ORDER BY user_info
.id
和LIMIT 1两个条件,所以所以不想要排序时使用Take()
// First()
tx := db.Model(&model.UserInfo{}).Select("phone,sum(age) as total").Where("phone LIKE ?", "%18888888888%").Group("phone").First(&result)
// sql:SELECT phone,sum(age) as total FROM `user_info` WHERE phone LIKE '%18888888888%' AND `user_info`.`deleted_at` IS NULL GROUP BY `phone` ORDER BY `user_info`.`id` LIMIT 1
// Take()
tx := db.Model(&model.UserInfo{}).Select("phone,sum(age) as total").Where("phone LIKE ?", "%18888888888%").Group("phone").Take(&result)
// sql:SELECT phone,sum(age) as total FROM `user_info` WHERE phone LIKE '280_xTIBz/qro13Ezh3EQzOPhA==' AND `user_info`.`deleted_at` IS NULL GROUP BY `phone` LIMIT 1
3、结构体接收查询结果时字段名要大写
结构体内的字段小写是私有的,转换的时候没权限访问,所以导致返回空字符
// 错误写法
type Result struct {
name, email string
}
var results []Result
tx := db.Table("user u").Select("u.name,u.email").Joins("JOIN user_role ur ON ur.user_id = u.id").Where("u.name = ?", "280_jiMAnAfWWlsvE2qa27hhKA==").Find(&results)
t.Log(tx.RowsAffected)
for _, userInfo := range results {
t.Log("查询结果为:", userInfo)
}
// 输出结果:查询结果为: { }
// 正确写法
type Result struct {
Name, Email string
}
var results []Result
tx := db.Table("user u").Select("u.name,u.email").Joins("JOIN user_role ur ON ur.user_id = u.id").Where("u.name = ?", "280_jiMAnAfWWlsvE2qa27hhKA==").Find(&results)
t.Log(tx.RowsAffected)
for _, userInfo := range results {
t.Log("查询结果为:", userInfo)
}
// 输出结果:查询结果为: {xiaoling asdqwe@163.com}
4、Find和Scan区别
Find和Scan都是用于多值查询,但是Find具有解析功能
// Find查询
// Find(): 具有解析功能,如果ret在数据库中有对应的表,就可以直接赋值
tx := db.Select("phone", "email").Find(&userInfos)
// Scan查询---须指定表名Table("user_info")
// Scan(): 不具有解析功能,所以无法获取ret对应的数据表
tx := db.Select("phone", "email").Table("user_info").Scan(&userInfos)