GORM踩坑之路

参考文档: GORM指南

版本: gorm.io/gorm v1.24.2

关于查询

根据主键索引

• int相关类型主键

db.First(&user, 10)  
// SELECT * FROM users WHERE id = 10;  
  
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);

• string类型主键

db.First(&user, "257b218c-5031-45a1-8061-209180499a93")  
// SELECT * FROM `users` WHERE 257b218c-5031-45a1-8061-209180499a93 ORDER BY `users`.`id` LIMIT 1;
  
db.Find(&users, []int{"257b218c-5031-45a1-8061-209180499a93"})  
// SELECT * FROM `users` WHERE `users`.`id` = '257b218c-5031-45a1-8061-209180499a93';

大家注意观察就会发现, 当主键类型是string类型时候, 直接在First后面跟参数, 生成的sql是错误的

查询会有这种问题, 那么删除呢? 会不会也存在同样的问题!!!

关于删除

• int相关类型主键

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);

• string类型主键

db.Delete(&User{}, "257b218c-5031-45a1-8061-209180499a93")  
// DELETE FROM `users` WHERE 257b218c-5031-45a1-8061-209180499a93; 
  
db.Delete(&users, []string{"257b218c-5031-45a1-8061-209180499a93"})  
// DELETE FROM `users` WHERE `users`.`id` = '257b218c-5031-45a1-8061-209180499a93';

可以看到, 在删除中, 如果主键不是int相关的类型, 直接通过第二参数给主键值, sql也是错误的

如何避免

1. 尽量使用Where传递条件
2. 不使用Where情况下, 使用[]string{}字符串或者对应类型的切片传递

测试代码

package test

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

type BaseModel struct {
   CreatedAt int64          `gorm:"autoCreateTime:nano"`
   UpdatedAt int64          `gorm:"autoUpdateTime:nano"`
   DeletedAt gorm.DeletedAt `gorm:"index"`
}

type UUIDModel struct {
   BaseModel
   ID string `gorm:"primarykey;type:varchar(36);not null;uniqueIndex;comment:主键ID;colum:id"`
}

type User struct {
   UUIDModel
   Username string `gorm:"type:varchar(32);not null;unique;comment:用户名"`
   Password string `gorm:"type:varchar(255);not null;comment:密码"`
   Email    string `gorm:"type:varchar(64);not null;unique;comment:邮箱"`
   Phone    string `gorm:"type:varchar(16);not null;unique;comment:手机号"`
   Avatar   string `gorm:"type:varchar(255);not null;comment:头像"`
   Nickname string `gorm:"type:varchar(32);not null;comment:昵称"`
}

const dsn = "root:<password>@tcp(<ip>:<port>)/<db_name>?charset=utf8mb4&parseTime=True&loc=Local"

var db *gorm.DB

func init() {
   conn, err := gorm.Open(mysql.Open(dsn))
   if err != nil {
      panic(err)
   }
   db = conn

   // 初始化表
   if err = db.AutoMigrate(&User{}); err != nil {
      panic(err)
   }
}

func TestGormP(t *testing.T) {
   uuidPK := "257b218c-5031-45a1-8061-209180499a93"
   var user User
   var sql string
   sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
      return tx.Model(&user).First(&user, uuidPK)
   })
   t.Log(sql)

   sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
      return tx.Model(&user).Find([]*User{}, []string{uuidPK})
   })

   t.Log(sql)

   sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
      return tx.Model(&user).Delete(&user, uuidPK)
   })

   t.Log(sql)

   sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
      return tx.Model(&user).Delete(&user, []string{uuidPK})
   })

   t.Log(sql)
}