一 点睛
1 物理删除
真实删除,将对应数据从数据库中删除,之后查询不到此条被删除数据。
2 逻辑删除
假删除,将对应数据中代表是否被删除字段状态修改为“被删除状态”,之后在数据库中仍旧能看到此条数据记录。
3 逻辑删除的使用场景
可以进行数据恢复
有关联数据,不便删除
二 实现流程图解
三 实战
1 数据表结构
CREATE TABLE `user` (
`id` bigint(20) NOT NULL DEFAULT '0' COMMENT '主键ID',
`NAME` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`deleted` int(11) DEFAULT NULL, # 这一项是关键
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2 数据表中的数据
mysql> select * from user;
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| id | NAME | age | email | create_time | update_time | version | deleted |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| 0 | auto | 20 | 1243@qq.com | NULL | NULL | NULL | NULL |
| 2 | Jack | 20 | test2@baomidou.com | NULL | NULL | NULL | NULL |
| 3 | Tom | 28 | test3@baomidou.com | NULL | NULL | NULL | NULL |
| 1443158688033337346 | lucymaryupup | 20 | 1243@qq.com | NULL | 2021-09-30 08:51:12 | NULL | NULL |
| 1443378040145903617 | ASSIGN_ID | 20 | 1243@qq.com | 2021-09-30 08:51:56 | 2021-09-30 08:51:56 | NULL | NULL |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
5 rows in set (0.00 sec)
3 实体类
@Data
public class User {
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private String name;
private Integer age;
private String email;
@TableField(fill = FieldFill.INSERT)
private Date createTime; // create_time
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime; // update_time
@Version
@TableField(fill = FieldFill.INSERT)
private Integer version;
// 这一项是关键
@TableLogic
@TableField(fill = FieldFill.INSERT)
private Integer deleted;
}
4 插入时,设置默认值为0
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
// mp执行添加操作,这个方法执行
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("createTime",new Date(),metaObject);
this.setFieldValByName("updateTime",new Date(),metaObject);
this.setFieldValByName("version",1,metaObject);
this.setFieldValByName("deleted",0,metaObject); // 这一项是关键
}
// mp执行修改操作,这个方法执行
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime",new Date(),metaObject);
}
}
5 逻辑删除的配置(可选)
application.properties 加入以下配置,此为默认值,如果你的默认值和mp默认的一样,该配置可无。
# 配置逻辑删除时的值
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
6 添加一条数据
// 添加
@Test
public void testAdd() {
User user = new User();
user.setName("luojishanch");
user.setAge(20);
user.setEmail("1243@qq.com");
int insert = userMapper.insert(user);
System.out.println(insert);
}
7 测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6ae7deac] was not registered for synchronization because synchronization is not active
2021-10-02 16:56:05.206 INFO 17280 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-10-02 16:56:06.956 INFO 17280 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1891673045 wrapping com.mysql.cj.jdbc.ConnectionImpl@5c8e67b9] will not be managed by Spring
==> Preparing: INSERT INTO user ( id, name, age, email, create_time, update_time, version, deleted ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 1444224656633389058(Long), luojishanch(String), 20(Integer), 1243@qq.com(String), 2021-10-02 16:56:05.198(Timestamp), 2021-10-02 16:56:05.198(Timestamp), 1(Integer), 0(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6ae7deac]
1
8 测试后的数据库如下
注意:被删除前,数据的 deleted 字段的值必须是 0,才能被选取出来执行逻辑删除的操作
mysql> select * from user;
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| id | NAME | age | email | create_time | update_time | version | deleted |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| 0 | auto | 20 | 1243@qq.com | NULL | NULL | NULL | NULL |
| 2 | Jack | 20 | test2@baomidou.com | NULL | NULL | NULL | NULL |
| 3 | Tom | 28 | test3@baomidou.com | NULL | NULL | NULL | NULL |
| 1443158688033337346 | lucymaryupup | 20 | 1243@qq.com | NULL | 2021-09-30 08:51:12 | NULL | NULL |
| 1443378040145903617 | ASSIGN_ID | 20 | 1243@qq.com | 2021-09-30 08:51:56 | 2021-09-30 08:51:56 | NULL | NULL |
| 1444224656633389058 | luojishanch | 20 | 1243@qq.com | 2021-10-02 16:56:05 | 2021-10-02 16:56:05 | 1 | 0 |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
6 rows in set (0.00 sec)
9 删除测试
// 根据 id 删除
@Test
public void testDeleteId() {
int rows = userMapper.deleteById(1444224656633389058L);
System.out.println(rows);
}
10 测试结果
测试后分析打印的 sql 语句,是一条 update
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1931d99] was not registered for synchronization because synchronization is not active
2021-10-02 16:59:13.182 INFO 16668 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-10-02 16:59:14.949 INFO 16668 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1979325411 wrapping com.mysql.cj.jdbc.ConnectionImpl@7a389761] will not be managed by Spring
==> Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0
==> Parameters: 1444224656633389058(Long)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1931d99]
1
11 删除后的数据库
测试后发现,数据并没有被删除,deleted 字段的值由 0 变成了 1
mysql> select * from user;
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| id | NAME | age | email | create_time | update_time | version | deleted |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| 0 | auto | 20 | 1243@qq.com | NULL | NULL | NULL | NULL |
| 2 | Jack | 20 | test2@baomidou.com | NULL | NULL | NULL | NULL |
| 3 | Tom | 28 | test3@baomidou.com | NULL | NULL | NULL | NULL |
| 1443158688033337346 | lucymaryupup | 20 | 1243@qq.com | NULL | 2021-09-30 08:51:12 | NULL | NULL |
| 1443378040145903617 | ASSIGN_ID | 20 | 1243@qq.com | 2021-09-30 08:51:56 | 2021-09-30 08:51:56 | NULL | NULL |
| 1444224656633389058 | luojishanch | 20 | 1243@qq.com | 2021-10-02 16:56:05 | 2021-10-02 16:56:05 | 1 | 1 |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
6 rows in set (0.00 sec)
12 手动改一下 deleted 值
mysql> select * from user;
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| id | NAME | age | email | create_time | update_time | version | deleted |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
| 0 | auto | 20 | 1243@qq.com | NULL | NULL | NULL | 1 |
| 2 | Jack | 20 | test2@baomidou.com | NULL | NULL | NULL | 0 |
| 3 | Tom | 28 | test3@baomidou.com | NULL | NULL | NULL | 1 |
| 1443158688033337346 | lucymaryupup | 20 | 1243@qq.com | NULL | 2021-09-30 08:51:12 | NULL | 0 |
| 1443378040145903617 | ASSIGN_ID | 20 | 1243@qq.com | 2021-09-30 08:51:56 | 2021-09-30 08:51:56 | NULL | 1 |
| 1444224656633389058 | luojishanch | 20 | 1243@qq.com | 2021-10-02 16:56:05 | 2021-10-02 16:56:05 | 1 | 1 |
+---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+
6 rows in set (0.00 sec)
13 测试一下查询
@Test
public void findAll() {
List<User> users = userMapper.selectList(null);
System.out.println(users);
}
14 测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f829c76] was not registered for synchronization because synchronization is not active
2021-10-02 17:02:03.070 INFO 18420 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-10-02 17:02:04.978 INFO 18420 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@668929853 wrapping com.mysql.cj.jdbc.ConnectionImpl@c35af2a] will not be managed by Spring
==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0
==> Parameters:
<== Columns: id, name, age, email, create_time, update_time, version, deleted
<== Row: 2, Jack, 20, test2@baomidou.com, null, null, null, 0
<== Row: 1443158688033337346, lucymaryupup, 20, 1243@qq.com, null, 2021-09-30 08:51:12, null, 0
<== Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f829c76]
[User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null, version=null, deleted=0), User(id=1443158688033337346, name=lucymaryupup, age=20, email=1243@qq.com, createTime=null, updateTime=Thu Sep 30 08:51:12 CST 2021, version=null, deleted=0)]