优雅解决MySQL逻辑删除导致的唯一索引冲突：

优雅解决MySQL逻辑删除导致的唯一索引冲突：

前情说明

该方法只适用于MySQL 8.0 之后的版本。 不适用于之前的版本，如 MySQL 5.7

问题背景

在实际业务中，我们通常使用逻辑删除来避免数据的物理删除，即通过 is_deleted 字段来标识数据是否被删除（0 代表未删除，1 代表已删除）。

然而，当 is_deleted 与 唯一索引 共同使用时，会导致数据插入失败的问题。

例如，在 user 表中，username 需要保持唯一性：

1. 业务层会检查 username 是否已存在。
2. 数据库使用唯一索引 UNIQUE(username) 兜底，避免并发问题导致的数据不一致。

假设该用户逻辑删除了（“张三”，is_deleted=1）数据，用户尝试创建同名的新数据时（“张三”，is_deleted=0），唯一索引仍然会导致插入失败，因为数据库仍然认为该 username 存在。

错误方案：

你可能会想到创建联合唯一索引 (username, is_deleted)，但这仍然无法解决问题：

• 逻辑删除后，如果再插入相同 username 的新数据，新数据也可能需要被逻辑删除，这时 username + is_deleted=1 组合会发生冲突。

那么，该如何解决这一问题呢？

解决方式

为什么选择虚拟生成列？

MySQL 8.0.13 及以上版本提供了虚拟生成列（Generated Column）+索引 的方式来解决此问题。相比其他方法，虚拟生成列方案具有以下优点：

1. 实现快速，无需改动业务代码，只需在数据库层面进行修改。
2. 索引更高效，可以直接作用于未删除的数据，避免无效索引浪费。
3. 数据自动维护，不会影响原表数据的插入和更新逻辑。

虚拟生成列介绍

什么是虚拟生成列？

虚拟生成列（Generated Column）是一种基于其他列计算得到的列，它不会实际存储在表中，而是在查询时动态计算。

MySQL 版本支持情况

• MySQL 5.7：支持存储生成列（Stored Generated Column），但不支持索引。
• MySQL 8.0.13+：支持虚拟生成列（Virtual Generated Column），并且可以在其上创建索引。

实践

1. 建立测试表

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2. 测试唯一索引冲突

INSERT INTO user (username, is_deleted, email) VALUES ('test_user', 1, 'test@example.com');
INSERT INTO user (username, is_deleted, email) VALUES ('test_user', 0, 'new@example.com');
-- 失败，违反唯一索引约束

3. 解决方案：创建虚拟生成列

生成列 SQL

ALTER TABLE user
  ADD COLUMN username_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, username, NULL)) VIRTUAL,
  ADD COLUMN email_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, email, NULL)) VIRTUAL;

CREATE UNIQUE INDEX idx_user_unique
  ON user(username_visible, email_visible);

4. 解决方案验证

插入数据

INSERT INTO user (username, is_deleted, email) VALUES ('test_user', 1, 'test@example.com');
INSERT INTO user (username, is_deleted, email) VALUES ('test_user', 0, 'new@example.com');
-- 成功，不再违反唯一索引

逻辑删除后可再次插入

UPDATE user SET is_deleted = 1 WHERE username = 'test_user' AND is_deleted = 0;
INSERT INTO user (username, is_deleted, email) VALUES ('test_user', 0, 'new@example.com');
-- 仍然成功

IF(is_deleted = 0, username, NULL) 的含义为：IF函数的结构是IF(条件, 值1, 值2)

结束

通过使用 虚拟生成列 + 索引，我们成功解决了唯一索引与逻辑删除冲突的问题。这种方法不仅高效，而且无需修改业务代码，是一种优雅的数据库层解决方案。