介绍
上文 gorm基础05–CRUD 接口-更新 介绍了gorm 中常见的更新操作,本文继上文继续介绍gorm中常见的删除操作,具体包括 删除一条记录、根据主键删除、批量删除、返回删除行的数据、软硬删除 等。
案例
deleteOne(db) 删除一条记录
deleteByPrimaryKey(db) 根据主键删除
deleteBatch(db) 批量删除
deleteWithReturnData(db) 返回删除行的数据
deleteSoftHard(db) 软硬删除
源码:
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/clause"
"gorm.io/gorm/schema"
"time"
)
type User struct {
ID uint
Name string
Email string
Age uint8
Birthday time.Time
MemberNumber sql.NullString
ActivatedAt sql.NullTime
CreatedAt time.Time
UpdatedAt time.Time
Role string
Active bool `gorm:"default:1"`
Salary int64 `gorm:"default:10000"`
}
func getDb() *gorm.DB {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:111111@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // DSN data source name
DefaultStringSize: 256, // string 类型字段的默认长度
DisableDatetimePrecision: true, // 禁用 datetime 精度,MySQL 5.6 之前的数据库不支持
DontSupportRenameIndex: true, // 重命名索引时采用删除并新建的方式,MySQL 5.7 之前的数据库和 MariaDB 不支持重命名索引
DontSupportRenameColumn: true, // 用 `change` 重命名列,MySQL 8 之前的数据库和 MariaDB 不支持重命名列
SkipInitializeWithVersion: false, // 根据当前 MySQL 版本自动配置
}), &gorm.Config{NamingStrategy: schema.NamingStrategy{SingularTable: true}})
fmt.Printf("getDb db=%v, err=%v\n", db, err)
return db
}
func deleteOne(db *gorm.DB) {
var user User
// Email 的 ID 是 `10`
user.ID = 10
db.Delete(&user)
// DELETE from user where id = 10;
// 带额外条件的删除
user.ID = 11
db.Where("name = ?", "jinzhu").Delete(&user)
// DELETE from user where id = 11 AND name = "jinzhu";
}
func deleteByPrimaryKey(db *gorm.DB) {
var users []User
db.Delete(&User{}, 1)
// DELETE FROM users WHERE id = 1;
db.Delete(&User{}, "2")
// DELETE FROM users WHERE id = 2;
db.Delete(&users, []int{3, 4, 5})
// DELETE FROM users WHERE id IN (3,4,5);
}
func deleteBatch(db *gorm.DB) {
db.Where("name LIKE ?", "%jinzhu%").Delete(&User{})
// DELETE from User where name LIKE "%jinzhu%";
db.Delete(&User{}, "name LIKE ?", "%jinzhu%")
// DELETE from User where name LIKE "%jinzhu%";
}
func deleteWithReturnData(db *gorm.DB) {
// 返回被删除的数据,仅适用于支持 Returning 的数据库,mysql 暂不支持
// 返回所有列
var users []User
db.Clauses(clause.Returning{}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}
println("users1=%v", users)
// 返回指定的列
db.Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}
println("users2=%v", users)
}
func deleteSoftHard(db *gorm.DB) {
type User struct {
ID uint
Name string
Age uint8
DeletedAt gorm.DeletedAt
}
var user User
var users []User
// user 的 ID 是 `111`
user.ID = 10
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 10;
// 批量删除
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;
// 您可以使用 Unscoped 找到被软删除的记录
db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;
db.Unscoped().Delete(&user)
// DELETE FROM orders WHERE id=10;
}
func main() {
db := getDb()
deleteOne(db)
//deleteByPrimaryKey(db)
//deleteBatch(db)
//deleteWithReturnData(db)
deleteSoftHard(db)
}
测试:
此处为 deleteSoftHard 的截图:
user.id=10 的被硬删除了,user.id=13的为软删除(deleted_at 字段有最后删除的时间)
注意事项
- 软删除
当User结构体中包含 gorm.Model 或者 DeletedAt gorm.DeletedAt 的时候,使用 gorm 初始化表结构都会生成一个 deleted_at 字段,且为null;后续删除该数据的时候,该字段会被设置为指定的日期,实际数据没有删除;该操作在 gorm 中为软删除。
使用通常的查找操作是无法查到软删除的数据,如果需要将数据彻底删除,那么可以通过 db.Unscoped().具体方法 的方式来删除。