说明
本文用示例介绍MyBatis-Plus如何解决逻辑删除与唯一索引的问题。
物理删除与逻辑删除
数据是很重要的,数据库里的数据在删除时一般不会用DELETE语句直接物理删除。
通常的做法是使用逻辑删除,也就是:新加一个标记是否删除的字段,在删除时不是真的删除,而是使用UPDATE语句将某个字段设置为删除状态。例如:“deleted_flag”,0表示未删除,其他表示已删除。
MyBatis-Plus****默认的逻辑删除
MyBatis-Plus里可以将某个字段标记为逻辑删除字段,方法是:在字段上加@TableLogic注解:
@TableLogic
private Long deletedFlag;
这样一来,操作数据时会自动操作:
其默认的全局配置为:
mybatis-plus:
global-config:
db-config:
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
MyBatis-Plus****默认逻辑删除的问题
假设有一张用户表,有user_name和deleted_flag字段,想给user_name加唯一索引(提高查询速度、确保用户名不重复),这时有如下两种方案:
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0modelVersion>
<parent>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-parentartifactId>
<version>2.3.1.RELEASEversion>
<relativePath/>
parent>
<groupId>com.examplegroupId>
<artifactId>MyBatis-Plus_SimpleartifactId>
<version>0.0.1-SNAPSHOTversion>
<name>MyBatis-Plus_Simplename>
<description>Demo project for Spring Bootdescription>
<properties>
<java.version>1.8java.version>
properties>
<dependencies>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-webartifactId>
dependency>
<dependency>
<groupId>com.baomidougroupId>
<artifactId>mybatis-plus-boot-starterartifactId>
<version>3.3.2version>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-testartifactId>
<scope>testscope>
dependency>
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
dependency>
<dependency>
<groupId>org.projectlombokgroupId>
<artifactId>lombokartifactId>
dependency>
<dependency>
<groupId>com.github.xiaoymingroupId>
<artifactId>knife4j-spring-boot-starterartifactId>
<version>3.0.3version>
dependency>
dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-maven-pluginartifactId>
plugin>
plugins>
build>
project>
DROP DATABASE IF EXISTS mp;
CREATE DATABASE mp DEFAULT CHARACTER SET utf8;
USE mp;
DROP TABLE IF EXISTS `t_user`;
SET NAMES utf8mb4;
CREATE TABLE `t_user`
(
`id` BIGINT(0) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(64) NOT NULL COMMENT '用户名(不能重复)',
`nick_name` VARCHAR(64) NULL COMMENT '昵称(可以重复)',
`email` VARCHAR(64) COMMENT '邮箱',
`create_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted_flag` BIGINT(0) NOT NULL DEFAULT 0 COMMENT '0:未删除 其他:已删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `index_user_name_deleted_flag` (`user_name`, `deleted_flag`),
KEY `index_create_time`(`create_time`)
) ENGINE = InnoDB COMMENT = '用户';
INSERT INTO `t_user` VALUES (1, 'knife', '刀刃', 'abc@qq.com', '2021-01-23 09:33:36', '2021-01-23 09:33:36', 0);
INSERT INTO `t_user` VALUES (2, 'sky', '天蓝', '123@qq.com', '2021-01-24 18:12:21', '2021-01-24 18:12:21', 0);
Entity
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName(value = "t_user")
public class User {
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 用户名(不能重复)
*/
private String userName;
/**
* 昵称(可以重复)
*/
private String nickName;
/**
* 邮箱
*/
private String email;
/**
* 创建时间
*/
private LocalDateTime createTime;
/**
* 修改时间
*/
private LocalDateTime updateTime;
/**
* 0:未删除 其他:已删除
*/
@TableLogic
private Long deletedFlag;
}
Service
接口
package com.example.demo.user.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.user.entity.User;
public interface UserService extends IService<User> {
}
实现类
package com.example.demo.user.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.user.entity.User;
import com.example.demo.user.mapper.UserMapper;
import com.example.demo.user.service.UserService;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@Api(tags = "测试")
@RestController
@RequestMapping("test")
public class TestController {
@Autowired
private UserService userService;
@ApiOperation("删除")
@PostMapping("delete")
public String delete(Long id) {
userService.removeById(id);
return "remove success";
}
@ApiOperation("添加")
@PostMapping("add")
public User add() {
User user = new User();
user.setUserName("sky");
userService.save(user);
return user;
}
}
打开测试页面:http://localhost:8080/doc.html

1. 查看原始数据

2. 删除user_name为“sky”的数据

数据库结果(成功删除)

后端结果
==> Preparing: UPDATE t_user SET deleted_flag=1 WHERE id=? AND deleted_flag=0
==> Parameters: 2(Long)
<== Updates: 1
3. 添加user_name为“sky”的数据

数据库结果(添加成功)

后端结果(添加成功)
==> Preparing: INSERT INTO t_user ( user_name ) VALUES ( ? )
==> Parameters: sky(String)
<== Updates: 1
4. 删除user_name为“sky”的数据

数据库结果(删除失败)

后端结果(删除失败)
2022-03-20 16:46:09.895 ERROR 15904 --- [nio-8080-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException:
### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'sky-1' for key 't_user.index_user_name_deleted_flag'
### The error may exist in com/example/demo/user/mapper/UserMapper.java (best guess)
### The error may involve com.example.demo.user.mapper.UserMapper.deleteById-Inline
### The error occurred while setting parameters
### SQL: UPDATE t_user SET deleted_flag=1 WHERE id=? AND deleted_flag=0
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'sky-1' for key 't_user.index_user_name_deleted_flag'
; Duplicate entry 'sky-1' for key 't_user.index_user_name_deleted_flag'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'sky-1' for key 't_user.index_user_name_deleted_flag'] with root cause
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'sky-1' for key 't_user.index_user_name_deleted_flag'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.20.jar:8.0.20]
@TableLogic注解的源码如下:
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//
package com.baomidou.mybatisplus.annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface TableLogic {
String value() default "";
String delval() default "";
}
所以方法就是:将delval设置为一个不会冲突的值就可以了,比如:
说明
delval所指定的字符串,将会直接作为UPDATE语句的SET值,例如,我将delval指定为“abc”,那么删除时的语句就是:UPDATE SET deleted_flag = abc WHERE …
方案简介
在删除时会将逻辑删除字段设置为id的值。
DROP DATABASE IF EXISTS mp;
CREATE DATABASE mp DEFAULT CHARACTER SET utf8;
USE mp;
DROP TABLE IF EXISTS `t_user`;
SET NAMES utf8mb4;
CREATE TABLE `t_user`
(
`id` BIGINT(0) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(64) NOT NULL COMMENT '用户名(不能重复)',
`nick_name` VARCHAR(64) NULL COMMENT '昵称(可以重复)',
`email` VARCHAR(64) COMMENT '邮箱',
`create_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted_flag` BIGINT(0) NOT NULL DEFAULT 0 COMMENT '0:未删除 其他:已删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `index_user_name_deleted_flag` (`user_name`, `deleted_flag`),
KEY `index_create_time`(`create_time`)
) ENGINE = InnoDB COMMENT = '用户';
INSERT INTO `t_user` VALUES (1, 'knife', '刀刃', 'abc@qq.com', '2021-01-23 09:33:36', '2021-01-23 09:33:36', 0);
INSERT INTO `t_user` VALUES (2, 'sky', '天蓝', '123@qq.com', '2021-01-24 18:12:21', '2021-01-24 18:12:21', 0);
package com.example.demo.user.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName(value = "t_user")
public class User {
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 用户名(不能重复)
*/
private String userName;
/**
* 昵称(可以重复)
*/
private String nickName;
/**
* 邮箱
*/
private String email;
/**
* 创建时间
*/
private LocalDateTime createTime;
/**
* 修改时间
*/
private LocalDateTime updateTime;
/**
* 0:未删除 其他:已删除
*/
@TableLogic(delval = "id")
private Long deletedFlag;
}
打开测试页面:http://localhost:8080/doc.html

1. 查看原始数据

2. 删除user_name为“sky”的数据

数据库结果(成功删除)

后端结果(成功删除)
==> Preparing: UPDATE t_user SET deleted_flag=id WHERE id=? AND deleted_flag=0
==> Parameters: 2(Long)
<== Updates: 1
3. 添加user_name为“sky”的数据

数据库结果(添加成功)

后端结果(添加成功)
==> Preparing: INSERT INTO t_user ( user_name ) VALUES ( ? )
==> Parameters: sky(String)
<== Updates: 1
4. 删除user_name为“sky”的数据

数据库结果(删除成功)

后端结果(删除成功)
==> Preparing: UPDATE t_user SET deleted_flag=id WHERE id=? AND deleted_flag=0
==> Parameters: 3(Long)
<== Updates: 1
方案简介
在删除时会将逻辑删除字段设置为NOW()函数的值。
DROP DATABASE IF EXISTS mp;
CREATE DATABASE mp DEFAULT CHARACTER SET utf8;
USE mp;
DROP TABLE IF EXISTS `t_user`;
SET NAMES utf8mb4;
CREATE TABLE `t_user`
(
`id` BIGINT(0) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(64) NOT NULL COMMENT '用户名(不能重复)',
`nick_name` VARCHAR(64) NULL COMMENT '昵称(可以重复)',
`email` VARCHAR(64) COMMENT '邮箱',
`create_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted_flag` DATETIME(0) NULL DEFAULT NULL COMMENT 'null:未删除 其他:已删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `index_user_name_deleted_flag` (`user_name`, `deleted_flag`),
KEY `index_create_time`(`create_time`)
) ENGINE = InnoDB COMMENT = '用户';
INSERT INTO `t_user` VALUES (1, 'knife', '刀刃', 'abc@qq.com', '2021-01-23 09:33:36', '2021-01-23 09:33:36', null);
INSERT INTO `t_user` VALUES (2, 'sky', '天蓝', '123@qq.com', '2021-01-24 18:12:21', '2021-01-24 18:12:21', null);
package com.example.demo.user.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName(value = "t_user")
public class User {
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 用户名(不能重复)
*/
private String userName;
/**
* 昵称(可以重复)
*/
private String nickName;
/**
* 邮箱
*/
private String email;
/**
* 创建时间
*/
private LocalDateTime createTime;
/**
* 修改时间
*/
private LocalDateTime updateTime;
/**
* NULL:未删除 其他:已删除
*/
@TableLogic(value = "NULL", delval = "NOW()")
private LocalDateTime deletedFlag;
}
打开测试页面:http://localhost:8080/doc.html

1. 查看原始数据

2. 删除user_name为“sky”的数据

数据库结果(成功删除)

后端结果(成功删除)
==> Preparing: UPDATE t_user SET deleted_flag=NOW() WHERE id=? AND deleted_flag IS NULL
==> Parameters: 2(Long)
<== Updates: 1
3. 添加user_name为“sky”的数据

数据库结果(添加成功)

后端结果(添加成功)
==> Preparing: INSERT INTO t_user ( user_name ) VALUES ( ? )
==> Parameters: sky(String)
<== Updates: 1
4. 删除user_name为“sky”的数据

数据库结果(删除成功)

后端结果(删除成功)
==> Preparing: UPDATE t_user SET deleted_flag=NOW() WHERE id=? AND deleted_flag IS NULL
==> Parameters: 3(Long)
<== Updates: 1