gorm实现删除功能
准备工作
建立数据库连接
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
)
var db *gorm.DB
func OpenDB() {
dsn := "root:adss123@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True&loc=Local"
res, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
db = res
if err != nil {
log.Fatal(err)
}
fmt.Printf("成功:%v\n", db)
}
建立模型
type TestTb2 struct {
Username string
Password string
TestTb2User1ID uint
gorm.Model
}
数据表预存数据
删除一条记录
删除一条记录时,删除对象需要指定主键,否则会触发 批量 Delete,例如:
// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;
// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";
实验案例如下
func DeleteByQuery(Any any) {
OpenDB()
db.Where("username=?", "ylj").First(Any)
tx := db.Delete(Any)
if tx.Error != nil {
fmt.Printf("删除失败,err:%v\n", tx.Error)
} else {
fmt.Printf("删除成功,删除的记录信息为:%v\n", Any)
}
}
根据主键删除
GORM 允许通过主键(可以是复合主键)和内联条件来删除对象。
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);
当传入的模型指针内含主键,则会自动将主键当作条件,例如:
user:=&User{ID:10}
db.Delete(&user)
// DELETE FROM users WHERE id = 10;
批量删除
如果指定的值不包括主键,那么 GORM 会执行批量删除,它将删除所有匹配的记录
db.Where("email LIKE ?", "%jinzhu%").Delete(&Email{})
// DELETE from emails where email LIKE "%jinzhu%";
db.Delete(&Email{}, "email LIKE ?", "%jinzhu%")
// DELETE from emails where email LIKE "%jinzhu%";
实验案例如下:
func DeleteManyRows(Any any) {
OpenDB()
tx := db.Where("username like ?", "%c%").Delete(Any)
if tx.Error != nil {
fmt.Printf("删除失败,err:%v\n", tx.Error)
} else {
fmt.Printf("删除成功,删除的记录数量为:%v\n", tx.RowsAffected)
}
}
阻止全局删除
同全局修改一样,在没有条件下,存在阻止全局删除机制,
对此,你必须加一些条件,或者使用原生 SQL,或者启用 AllowGlobalUpdate 模式,例如:
db.Delete(&User{}).Error // gorm.ErrMissingWhereClause
db.Where("1 = 1").Delete(&User{})
// DELETE FROM `users` WHERE 1=1
db.Exec("DELETE FROM users")
// DELETE FROM users
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})
// DELETE FROM users
软删除
如果您的模型包含了一个 gorm.deletedat 字段(gorm.Model 已经包含了该字段),它将自动获得软删除的能力!
拥有软删除能力的模型调用 Delete 时,记录不会从数据库中被真正删除。但 GORM 会将 DeletedAt 置为当前时间, 并且你不能再通过普通的查询方法找到该记录。
// user 的 ID 是 `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;
// 批量删除
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;
// 在查询时会忽略被软删除的记录
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;
如果您不想引入 gorm.Model,可以定义一个gorm.DeletedAt字段,例如:
type User struct {
ID int
Deleted gorm.DeletedAt
Name string
}
查找被软删除的记录
可以使用 Unscoped 找到被软删除的记录
db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;
实验案例如下
id为4的记录以被删除,deleteat字段不为空:
func QueryOneRowById(Any any, id int) {
OpenDB()
take := db.Take(Any, id)
if take.Error != nil {
fmt.Printf("take查询失败,err:%v\n", take.Error)
} else {
fmt.Printf("take查询成功,结果为:%v\n", Any)
}
}
在方法里添加Unscoped 后
func QueryOneRowById(Any any, id int) {
OpenDB()
take := db.Unscoped().Take(Any, id)
if take.Error != nil {
fmt.Printf("take查询失败,err:%v\n", take.Error)
} else {
fmt.Printf("take查询成功,结果为:%v\n", Any)
}
}
永久删除
可以使用 Unscoped 永久删除匹配的记录
db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;
删除标签
利用gorm:"softDelete:milli"
标签可以实现对软删除功能的修改,常见的是修改删除时间单位,与设置0/1删除flag。
指定使用 或 秒 作为值,例如:milli nano
type User struct {
ID uint
Name string
DeletedAt soft_delete.DeletedAt `gorm:"softDelete:milli"`
// DeletedAt soft_delete.DeletedAt `gorm:"softDelete:nano"`
}
// Query
SELECT * FROM users WHERE deleted_at = 0;
// Delete
UPDATE users SET deleted_at = /* current unix milli second or nano second */ WHERE ID = 1;
设置0/1删除flag
import "gorm.io/plugin/soft_delete"
type User struct {
ID uint
Name string
IsDel soft_delete.DeletedAt `gorm:"softDelete:flag"`
}
// Query
SELECT * FROM users WHERE is_del = 0;
// Delete
UPDATE users SET is_del = 1 WHERE ID = 1;
最好将删除记录字段设置默认值0.
实验案例如下
模型:
type TestTb3 struct {
Id int `gorm:"primaryKey"`
Username string
Password string
Flag soft_delete.DeletedAt `gorm:"softDelete:flag;default:0"`
}
表结构
删除操作与查询操作
func main() {
user := &TestTb3{}
DeleteInfo(user, 2)
user1 := &TestTb3{}
QueryByPrimarykey(user1, 2)
}