07-删除数据

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

posted on 2023-06-28 13:50  运维开发玄德公  阅读(21)  评论(0)  编辑  收藏  举报

导航