怎样在MySQL中设计支持逻辑删除且唯一索引不冲突的表-mysql教程-PHP中文网

唯一索引需与is_deleted组合为联合索引，如(username, is_deleted)，使已删与未删记录视为不同项；若历史数据存在多条同名已删记录，须先去重；也可改用deleted_at或主键id实现更灵活删除，但需配套查询条件和复合索引。

怎样在mysql中设计支持逻辑删除且唯一索引不冲突的表

唯一索引和 `is_deleted` 字段直接组合会出错

直接在 username 上建 UNIQUE INDEX，再加一个 is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 字段，看似合理，但插入同名新用户时必然报 [1062] duplicate entry 'xxx' for key 'idx_username'。原因很直白：MySQL 的唯一索引不区分 is_deleted 值，只要 username 相同，哪怕旧记录 is_deleted = 1，新记录仍被拦住。

用 `(username, is_deleted)` 建联合唯一索引是主流解法

这是最轻量、最可控、也最符合 MySQL 索引机制的做法。核心在于让“已删”和“未删”变成两个不同的索引项：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

执行 ALTER TABLE user DROP INDEX idx_username, ADD UNIQUE INDEX idx_username_deleted (username, is_deleted);
此时 (username='alice', is_deleted=0) 和 (username='alice', is_deleted=1) 被视为不同值，互不冲突
业务语义依然成立：一个用户名最多只允许一条 is_deleted = 0 的活跃记录
注意：如果表里已有历史数据，且存在多条同名但 is_deleted = 1 的记录，这条 ALTER 语句会失败（因联合索引要求整行唯一），需先去重或清掉冗余已删记录

把 `is_deleted` 改成时间戳或主键 ID 更灵活但有代价

当业务允许“同一用户名被反复删除/注册多次”，又不想手动清理历史已删记录，可以放弃二值标记，改用可变值：

将字段改为 deleted_at DATETIME NULL，逻辑删除时设为 NOW()；再建联合索引 UNIQUE(username, deleted_at) —— 因为 NULL 不参与唯一校验，未删记录天然满足唯一性
或者用 MyBatis-Plus 风格：字段类型仍是 TINYINT 或 BIGINT，但删除时写入主键 ID（如 UPDATE user SET is_deleted = id WHERE id = 123），再建 UNIQUE(username, is_deleted) —— 每次删除都产生新组合值
缺点：查询条件得写成 WHERE is_deleted = 0 或 WHERE deleted_at IS NULL，不能依赖 ORM 自动注入；索引体积略大；恢复删除时要小心覆盖原 deleted_at 值

别漏掉查询和索引的配套动作

光改索引不够，逻辑删除真正生效靠的是每一处读操作都带上过滤条件：

所有 SELECT 必须显式写 WHERE is_deleted = 0，不能只靠应用层拦截——统计脚本、后台导出、DBA 直连查表，全可能漏
is_deleted 单独建索引没意义，但高频查询如 WHERE is_deleted = 0 AND status = 'active'，建议建复合索引 INDEX(is_deleted, status)，且把 is_deleted 放左边
外键关联表若也走逻辑删除，关联查询时两边都要加 is_deleted = 0，否则 JOIN 出来可能是“已删用户 + 未删订单”这种无效组合