gorm-V2-07-删除
2022-02-17 本文已影响0人
玄德公笔记
1. 按主键删除
1.1 使用
- 说明
如果指定的不是主键，则会触发批量删除，但是没有Where的批量删除被gorm禁止。 - 样例
db.Delete(&User{ID: 2})
或写做
db.Delete(&User{ID: 2},2)
1.2 完整示例
删除
id=2的一行
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"time"
)
type User struct {
ID int64
Age int64
Name string
Email string
Company string
}
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
db.Delete(&User{ID: 2})
}
func connect() (db *gorm.DB,sqlDB *sql.DB,err error) {
dsn := "root:40010355@tcp(127.0.0.1:3306)/crow?charset=utf8&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
sqlDB,_ = db.DB()
if err != nil {
fmt.Printf(err.Error())
defer sqlDB.Close()
}else {
fmt.Printf("OK\n")
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
}
return
}
2. 非主键删除
2.1 使用
db.Where("name LIKE ?", "%Guan%").Delete(&User{})
或写作
db.Delete(User{},"name LIKE ?","%Guan%")
2.2 示例
- 表中原有数据
mysql> select * from users ;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | LiuBei | 28 |
| 2 | GuanYu | 22 |
| 3 | ZhangFei | 20 |
| 4 | ZhaoYun | 18 |
| 5 | ZhuGeLiang | 20 |
| 6 | GuanPing | 3 |
+----+------------+------+
6 rows in set (0.00 sec)
- 代码
上例main函数修改如下：
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
result := db.Where("name LIKE ?", "%Guan%").Delete(&User{}).RowsAffected
fmt.Println(result)
}
- 表修改后
mysql> select * from users ;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | LiuBei | 28 |
| 3 | ZhangFei | 20 |
| 4 | ZhaoYun | 18 |
| 5 | ZhuGeLiang | 20 |
+----+------------+------+
4 rows in set (0.00 sec)
如上，关家将都被删除
3. 全局删除
gorm禁止全局删除，如果要全局删除，可以
- 指定一些条件
db.Where("1 = 1").Delete(&User{})
- 使用原生sql
db.Exec("DELETE FROM users")
4. 软删除
4.1 使用
- 说明
实际是给deleted列加了一个删除时间
deleted列必须存在，模型中必须有成员Deleted且类型为gorm.DeletedAt，否则直接删除。 - 定义模型
模型中必须有成员 Deleted
type User struct {
ID int64
Age int64
Name string
Email string
Company string
Deleted gorm.DeletedAt
}
- 删除的代码
删除的代码和普通删除没有差别
db.Delete(&User{ID: 2})
4.2 完整示例
- 原有表
mysql> select * from users;
+----+----------+------+--------------------+---------+---------+
| id | name | age | email | company | deleted |
+----+----------+------+--------------------+---------+---------+
| 1 | LiuBei | 28 | liubei@xishu.com | shu | NULL |
| 2 | GuanYu | 22 | guanyu@xishu.com | shu | NULL |
| 3 | ZhangFei | 20 | zhangfei@xishu.com | shu | NULL |
+----+----------+------+--------------------+---------+---------+
3 rows in set (0.00 sec)
- 代码
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"time"
)
type User struct {
ID int64
Age int64
Name string
Email string
Company string
Deleted gorm.DeletedAt
}
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
db.Delete(&User{ID: 1})
}
func connect() (db *gorm.DB,sqlDB *sql.DB,err error) {
dsn := "root:40010355@tcp(127.0.0.1:3306)/crow?charset=utf8&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
sqlDB,_ = db.DB()
if err != nil {
fmt.Printf(err.Error())
defer sqlDB.Close()
}else {
fmt.Printf("OK\n")
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
}
return
}
- 表结果
mysql> select * from users;
+----+----------+------+--------------------+---------+---------------------+
| id | name | age | email | company | deleted |
+----+----------+------+--------------------+---------+---------------------+
| 1 | LiuBei | 28 | liubei@xishu.com | shu | 2022-02-13 19:22:31 |
| 2 | GuanYu | 22 | guanyu@xishu.com | shu | NULL |
| 3 | ZhangFei | 20 | zhangfei@xishu.com | shu | NULL |
+----+----------+------+--------------------+---------+---------------------+
3 rows in set (0.00 sec)
4.3 软删除查询
4.2.1 说明
- 软删除的行，使用gorm查询时是查不到的
- 需要用Unscoped()查询软删除数据
db.Unscoped().Find(&users)
- 彻底删除软删除数据
db.Unscoped().Delete(&User{ID: 1})
4.3.2 示例
- gorm 直接查询查不到软删除
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
db.Find(&users)
fmt.Println(users)
}
输出
OK
[{2 22 GuanYu guanyu@xishu.com shu {0001-01-01 00:00:00 +0000 UTC false}} {3 20 ZhangFei zhangfei@xishu.com shu {0001-01-01 00:00:00 +0000 UTC false}}]
- 使用Unscoped()查询
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
db.Unscoped().Find(&users)
fmt.Println(users)
}
结果
如下：可以查到软删除的刘备信息
OK
[{1 28 LiuBei liubei@xishu.com shu {2022-02-13 19:22:31 +0800 CST true}} {2 22 GuanYu guanyu@xishu.com shu {0001-01-01 00:00:00 +0000 UTC false}} {3 20 ZhangFei zhangfei@xishu.com shu {0001-01-01 00:00:00 +0000 UTC false}}]
- 彻底删除被软删除数据
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
db.Unscoped().Delete(&User{ID: 1})
fmt.Println(users)
}
