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 | [email protected] | shu | NULL |
| 2 | GuanYu | 22 | [email protected] | shu | NULL |
| 3 | ZhangFei | 20 | [email protected] | 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 | [email protected] | shu | 2022-02-13 19:22:31 |
| 2 | GuanYu | 22 | [email protected] | shu | NULL |
| 3 | ZhangFei | 20 | [email protected] | 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 [email protected] shu {0001-01-01 00:00:00 +0000 UTC false}} {3 20 ZhangFei [email protected] 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 [email protected] shu {2022-02-13 19:22:31 +0800 CST true}} {2 22 GuanYu [email protected] shu {0001-01-01 00:00:00 +0000 UTC false}} {3 20 ZhangFei [email protected] 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)
}
标签:11,gormV2,07,删除,db,sqlDB,User,gorm,users From: https://www.cnblogs.com/crow1840/p/17511213.html