怎样用SQL安全实现逻辑软删除并保证唯一索引有效-SQL-PHP中文网

结论：不能靠 update set deleted=1 + 普通唯一索引解决，否则必然触发 duplicate entry 错误；真正安全的做法是利用 mysql 唯一索引对 null 的特殊处理，将逻辑删除设为 null 并配合联合唯一索引或生成列实现。

怎样用sql安全实现逻辑软删除并保证唯一索引有效

直接说结论：不能靠 UPDATE SET deleted=1 + 普通唯一索引解决，否则必然触发 Duplicate entry 错误。真正安全的做法，是让已删除记录“退出”唯一约束的覆盖范围。

MySQL 的唯一索引允许任意数量的 NULL 值共存——这是解题关键。只要把逻辑删除动作映射为“将标记字段设为 NULL”，就能天然绕过唯一性校验。

建表时，删除字段必须声明为 NULL（不能是 NOT NULL DEFAULT 0）
插入新记录时，deleted_at 或 del_flag 显式写为 NULL 或留空（依赖默认值）
逻辑删除时，执行 UPDATE ... SET del_flag = NULL WHERE id = ?，不是设为 1
查询活跃数据时，条件必须是 WHERE del_flag IS NULL，不是 = 0
联合唯一索引要包含该字段，例如 UNIQUE INDEX uk_username_del (username, del_flag) —— 因为 (username='a', del_flag=NULL) 和 (username='a', del_flag=NULL) 不冲突

MyBatis-Plus 默认不支持把逻辑删除值设为 NULL，它的 logic-delete-value 和 logic-not-delete-value 都要求是可比较的非空值。硬配 "null" 字符串会生成 deleted = 'null'，不是 IS NULL。

必须自定义全局逻辑删除 SQL：重写 UPDATE 语句，把 SET deleted = #{logicDeleteValue} 改为 SET deleted = NULL
查询拦截器也要改：原生的 AND deleted = 0 要替换为 AND deleted IS NULL
实体类字段类型建议用 Integer 或 LocalDateTime，避免基本类型（如 int）无法赋 null
配置里 logic-delete-value 可留空或设为占位符（如 "__DELETED__"），实际不参与拼 SQL，全靠自定义方法控制

如果你不能改删除字段的 NOT NULL 约束，又不想动 MyBatis-Plus 底层，可以用 MySQL 的计算列 + 唯一索引组合，完全不动业务逻辑。

新增一个虚拟列：ALTER TABLE user ADD COLUMN username_unique VARCHAR(255) AS (IF(deleted = 0, username, NULL)) STORED;
在该列上建唯一索引：CREATE UNIQUE INDEX uk_username_active ON user(username_unique);
原理：当 deleted = 0 时，username_unique 等于真实值，受唯一约束；当 deleted = 1 时，它为 NULL，不参与唯一校验
注意：必须用 STORED（不是 VIRTUAL），否则无法建索引；且该列不能有默认值或显式插入

相比 NULL 方案，用 deleted_at DATETIME NULL 更直观，也更容易被团队理解，且兼容 MyBatis-Plus 的 logic-delete-value: "now()" 配置。

字段定义：deleted_at DATETIME NULL DEFAULT NULL
联合唯一索引：UNIQUE KEY uk_username_deleted_at (username, deleted_at)
插入新用户：INSERT INTO user (username, email, deleted_at) VALUES ('alice', 'a@b.c', NULL)
逻辑删除：UPDATE user SET deleted_at = NOW() WHERE id = 123
问题：如果同一用户名被删了又删（两次都设了时间戳），仍可能冲突。所以生产环境建议加应用层防重或用 ON DUPLICATE KEY UPDATE 降级处理

最容易被忽略的是查询条件与索引的一致性：你用了 deleted_at IS NULL 查询，索引就必须是 (username, deleted_at)；若错写成 (username, deleted)，哪怕字段名只差一个下划线，索引就失效，性能会断崖下跌。