数据是无价的，当我们删除数据时，多数情况下我们会采用设置数据不可见的方法来替代真正意义上的物理删除，这种删除方法就叫做“软删除”。软删除可以保留数据的历史记录，便于今后数据分析以及一些故障情况下的恢复，但软删除也随之带来一些问题：数据表唯一索引失效。

# 唯一索引为什么会失效

很好理解，当我们使用软删除方法时，实际上是将删除delete from table 变为了 update table，虽然对外来说数据变为不可见了，但是数据库中的数据仍然保留，当插入同样的数据情况下，唯一索引就会发生冲突。

# 为逻辑删除字段添加联合索引

解决软删除索引问题的其中一个办法是用业务逻辑来保证数据的合法性，但是那样要大规模的修改插入逻辑，对系统影响较大。

另一个办法就是为我们的唯一字段和逻辑删除字段创建联合索引，这样就要求逻辑删除字段必须是一个动态值，可以是一个token、timestamp或是其他符合自己业务的值。

为什么逻辑删除字段是一个动态值？

考虑以下数据库

create table test{
	`username` varchar(20) not null,
	`deleted` int(1) not null default 0,	/* 1代表删除 */
	unique key `unique` (`username`, `deleted_at`) 
}
1
2
3
4
5

执行以下语句

insert into test (username) values ("test1");
// 此时插入一条数据
insert into test (username) values ("test1");
// 再次插入相同数据，会因为 (test1-0) 存在禁止插入
update test set deleted = 1 where username="test1";
// 逻辑删除刚才插入的数据
insert into test (username) values ("test1");
// 再次插入相同的数据，由于是联合索引，不存在 (test1-0)，插入是成功的
update test set deleted = 1 where username="test1";
// ！注意此时删除会报错，因为已经存在了 (test1-1) 的索引
1
2
3
4
5
6
7
8
9
10

可以看出，当只用一个固定值来代表逻辑删除状态时，联合索引会产生冲突，删除失败

有一个需要注意的坑是，MySQL中的联合索引是不能包含 NULL值的。例如:

create table test{
	`username` varchar(20) not null,
	`deleted_at` timestamp null,
	unique key `unique` (`username`, `deleted_at`) 
}
/* deleted_at 值默认是null */
insert into test (username) values ("test1");
insert into test (username) values ("test1");
insert into test (username) values ("test1");
## 多次插入是成功的，因为 null 不能做为索引
1
2
3
4
5
6
7
8
9
10
11
12

在MySQL的官方文档对唯一索引的描述是这样的：

UNIQUE

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

描述的最后写着，唯一索引会允许多个 NULL 值的添加。

因此当我们将逻辑删除字段加入唯一索引时，我们需要为其指定一个0值代表未删除的数据，而不能简单的使用NULL代替。

# Timestamp 0值设置与MySQL严格模式

一般情况下，我们会将逻辑删除字段设置为 timestamp 类型，当发生删除时，直接将字段设置为当前时间，这样既能实现删除字段值的动态变化，也能记录数据删除的时间，便于进行其他数据检索工作。

前面我们提到，逻辑删除字段需要指定一个0值，timestamp也有默认的0值。

timestamp 规定的数据大小是从1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999。其他任何不符合这个时间段的数据都会被变换为0插入到数据库中，这个数据在数据库中的表现为0000-00-00 00:00:00。

MySQL默认是禁止进行以上的转换的，如果直接部署一个MySQL服务并建立默认为0值的timestamp字段，或是插入一个0值的timestamp类型数据都会出错，这种模式被称为 MySQL 严格模式 (strict mode)。

默认的MySQL包含了6种模式（以MySQL 8.0 为准）：

名称 意义
ONLY_FULL_GROUP_BY 对于 GROUP BY操作，如果在 SELECT 中的列没有出现在GROUP BY中出现，则拒绝
STRICT_TRANS_TABLE 如果操作中的值不能插入事务表，则拒绝该操作
NO_ZERO_IN_DATE 不允许日期和月份为0
NO_ZERO_DATE 不允许插入0日期
ERROR_FOR_DIVISION_BY_ZERO 如果数据被0除，则报错
NO_ENGINE_SUBTITUTION 如果储存引擎被禁用或未编译，则报错

我们可以通过以下命令查看当前SQL模式:

select @@GLOBAL.sql_mode;
select @@SESSION.sql_mode;
1
2

修改MySQL模式有两种方式:

  1. 修改 my.cnf 文件，在[mysqld]下加入：

    [mysqld]
#set the SQL mode to strict
#sql-mode="modes..." 
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    1
    2
    3
    4

  2. 使用 sql 语句修改 server:

    SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';
    1
    2

# JAVA LocalDateTime 0值转换

当我们修改完数据库实现0值插入后，还需要对Java做一定的修改。

public Class Test{
  private String username;
  private LocalDateTime deletedAt;
}
1
2
3
4

当我们直接进行数据映射时会发生以下错误

com.mysql.cj.exceptions.DataReadException: Zero date value prohibited
1

我们需要在mysql连接时添加zeroDateTimeBehavior=convertToNull，告知驱动对0值的处理方法，以 Spring 为例：

spring.datasource.druid.url = jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull
1