软删除设计有哪些坑，唯一索引冲突该怎么解决？

学完这篇，你将学会如何正确设计软删除字段，避免因唯一索引冲突导致的数据写入失败，并掌握解决这类冲突的三种实战方案。

一、软删除设计的常见陷阱

这一步要理解软删除的核心逻辑是“标记”而非“物理移除”，这会带来两个主要问题：查询时容易漏掉过滤条件，以及唯一索引的冲突。

第一步：定义软删除字段

在数据库表中增加一个字段，例如 is_deleted（类型为 TINYINT 或 BOOLEAN），默认值为 0（未删除），1（已删除）。
* 操作入口：在数据库管理工具（如 Navicat、MySQL Workbench）中，选中对应表，点击“结构”或“Design”选项卡，添加新列。
* 具体参数：

    ALTER TABLE users ADD COLUMN is_deleted TINYINT(1) DEFAULT 0 COMMENT '0未删除 1已删除';
    

* 得到结果：表中每一行数据都多了一个标记位。

第二步：陷入查询歧义

这一步要展示如果不加过滤条件会发生什么。
* 具体操作：编写查询语句获取用户列表，但忘记加 WHERE is_deleted = 0。
* 代码示例：

    SELECT * FROM users WHERE username = 'test_user';
    

* 得到结果：如果之前有一个被软删除的同名用户，查询结果会同时返回两条记录，导致业务逻辑混乱（如显示重复数据）。
* > 注意：软删除后，旧数据依然占据索引空间。如果在软删除字段上建立唯一索引，旧数据依然会阻碍新数据的插入。

二、解决唯一索引冲突的实战方案

这一步要解决当尝试插入一个已软删除的记录时，数据库报“Duplicate entry”错误的难题。

第一步：使用 INSERT IGNORE 忽略错误

这一步要实现“如果存在就跳过，不存在就插入”的效果，不抛出异常。
* 具体操作：在 INSERT 语句前加上 IGNORE 关键字。
* 代码示例：

    INSERT IGNORE INTO users (username, email) VALUES ('test_user', 'test@example.com');
    

* 得到结果：如果数据库中发现 username 已存在（无论是已删除还是未删除），则直接忽略该语句，不执行插入操作，也不会报错。
* > 注意：这种方法无法区分是“已删除”还是“未删除”，也无法返回操作是否成功，仅适用于完全不需要重复数据的场景。

第二步：使用 ON DUPLICATE KEY UPDATE

这一步要实现“如果存在就更新，不存在就插入”，常用于修改昵称或更新资料。
* 具体操作：在 INSERT 语句后加上 ON DUPLICATE KEY UPDATE 子句。
* 代码示例：

    INSERT INTO users (username, email) 
    VALUES ('test_user', 'newemail@example.com') 
    ON DUPLICATE KEY UPDATE email = VALUES(email);
    

* 得到结果：系统会先检查唯一索引（如 username）。如果发现已存在该用户，则执行 UPDATE 语句更新邮箱；如果不存在，则执行 INSERT 新增用户。
* > 注意：此方案的前提是更新逻辑是安全的（例如更新时间戳、修改字段等），避免数据覆盖问题。

第三步：先物理删除旧记录，再插入新记录

这一步要彻底解决冲突，但要注意外键约束。
* 具体操作：在插入新数据前，先执行一条 DELETE 语句物理移除冲突的旧数据，然后再执行 INSERT。
* 代码示例：

    -- 先删除冲突的旧记录
    DELETE FROM users WHERE username = 'test_user' AND is_deleted = 1;
    -- 再插入新记录
    INSERT INTO users (username, email) VALUES ('test_user', 'newemail@example.com');
    

* 得到结果：数据库中只保留一条最新的数据，彻底避免了唯一索引冲突。
* > 注意：如果该表有外键关联，直接物理删除可能会报错。需要先解除外键关联（SET FOREIGN_KEY_CHECKS=0），操作完再恢复（SET FOREIGN_KEY_CHECKS=1）。

小结

1. 软删除的本质是标记数据，查询时必须始终带上 WHERE is_deleted = 0 条件，防止数据重复。
2. 唯一索引冲突通常发生在尝试插入已删除的重复数据时。
3. INSERT IGNORE 适合静默忽略重复，ON DUPLICATE KEY UPDATE 适合更新数据，先删后插 适合彻底清理冗余。