MySQL软删除、唯一键和外键约束

Question

假设我有两张表，user和comment它们的表定义如下：

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

CREATE TABLE `comment` (
  `id`      INTEGER NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER NOT NULL,
  `comment` TEXT,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_comment_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

这对于强制数据完整性等方面非常好，但我希望能够“删除”一个用户并保留其所有评论（供参考）。

为此，我添加了“deleted”以便我可以在记录上设置“deleted = 1”。通过默认列出所有具有“deleted = 0”的内容，我可以隐藏所有已删除的记录直到需要它们时。

目前为止还不错。

问题出现在以下情况：

- 用户使用用户名注册（比如说，“Sam”）， - 我软删除该用户（由于与其无关的原因），并且 - 其他人来注册一个名为 Sam 的账户，这时我们就违反了 user 上的唯一约束条件。

我希望用户能够编辑自己的用户名，因此不应将“username”作为主键，当删除用户时仍会遇到相同的问题。

有什么想法吗？

编辑以澄清：我关注的是“已删除”的用户和评论不可见于公众，而只对管理员可见或用于计算统计信息的情况。

本问题是个思维实验，用户和评论表只是示例。然而，选择“username”并不是最好的，RedFilter提出了关于用户身份的合理观点，特别是当记录在公共环境中呈现时。关于“为什么不把用户名设为主键？”：这只是一个例子，但如果将其应用于真实问题，则需要在假定存在代理主键的现有系统约束内进行工作。

- Rob Howard

Answer 1

在字段(username, deleted)上添加唯一约束

将 'deleted' 字段类型更改为 INTEGER。

在删除操作期间（可以在触发器中执行，或者在需要实际删除用户的代码部分中执行），将 id 字段的值复制到 deleted 字段。

这种方法允许您：

• 保留活跃用户的唯一名称（deleted = 0）
• 允许多次删除具有相同用户名的用户

'Deleted' 字段不能只有两个值，因为以下场景将无法正常工作：

1. 你创建了用户'Sam'
2. User Sam被删除了
3. 你创建了一个新用户，用户名为'Sam'
4. 您尝试删除用户名为'Sam'的用户 - 失败。 您已经拥有记录userName = 'Sam'和deleted ='1'

- Michael Pakhantsov

---

将 deleted 字段类型更改为 timestamp 可以让你在那里添加更多已删除的行。如果你想获取现有（未删除）记录，请检查此字段中是否为空值。 - Taha Paksu

如果允许在“deleted”中出现空值，您将失去唯一键的价值。 - erstaples

@eric_s 这种方法需要设计成“除了1以外的所有内容都将被视为‘未删除’”。 - Taha Paksu

这个想法可以通过用户名和删除时间戳的一对键来实现。 - Sajjad Shirazy

@TahaPaksu 不支持MySQL，因为它会忽略null。 - Nikhil

Answer 2

只需在username上保留唯一索引或约束即可。您不希望新用户能够使用已删除的名称，因为这不仅会导致身份混淆，而且如果您仍然显示已删除用户的旧帖子，则它们将被错误地认为是由具有相同名称的新用户发布。

当新用户注册时，通常会检查该名称是否已被使用，然后才允许完成注册，因此这里不应该存在冲突。

.

- D'Arcy Rittich

---

@RedFilter，这不是一个完整的解决方案 - 你将无法标记两个“Sam”为已删除。在删除操作期间，最好将ID复制到已删除字段中。这样可以保持只有一个人处于活动状态，并且具有相同名称的多个已删除用户（通过使用唯一约束（名称，已删除）） - Michael Pakhantsov

@Michael：我不同意两个不同的用户应该能够使用相同的用户名。 - D'Arcy Rittich

@Michael：第一反应是“这是个hack”。第二反应是，“哇，这实际上相当聪明”。你的意思是将约束设置为UNIQUE KEY (username, deleted)。 - Rob Howard

@RedFilter：我关心的情况是，“已删除”的用户和评论对公众不可见，只有管理员才能看到，或者仅用于计算统计信息。很抱歉不同意，但我认为这是一个值得考虑的有效情况。 - Rob Howard

@Rob：从可用性和社区角度来看，允许多个非并发用户使用相同的用户名是一个不好的想法，即使你提供了旧用户信息将被隐藏的新信息。这并不能从其他用户的记忆中抹去他们，可能会导致混淆。 - D'Arcy Rittich

Answer 3

我们正在使用MySQL生成的列（https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html）来解决这个问题。

ALTER TABLE `dummy` ADD `not_deleted` tinyint AS (if(`deleted_at` is null,1,NULL)) STORED;
CREATE UNIQUE INDEX `IDX_4b8d12e176ac3d69d21ebebd5a` ON `dummy` (`code`, `not_deleted`);

- dream83619

Answer 4

我的软删除实用解决方案是通过创建一个新表进行归档，该表具有以下列：original_id、table_name、payload（以及可选的主键id）。

其中，original_id是已删除记录的原始ID，table_name是已删除记录的表名（在你的情况下为"user"），payload是来自已删除记录的所有列的JSON字符串化字符串。

我还建议在original_id列上建立索引，以便后续检索数据。

通过这种方式归档数据，您将获得以下优点：

• 跟踪所有历史数据
• 只需要一个地方就可以归档来自任何表的记录，而不管已删除记录的表结构如何
• 不必担心原始表中的唯一索引
• 不必担心检查原始表中的外键索引

这里已经有一个讨论here，解释为什么软删除在实践中不是一个好主意。软删除会在未来带来一些潜在的麻烦，比如计算记录数...

- transang

Answer 5

你可以做以下事情：

 CREATE UNIQUE INDEX YourIndexName 
 ON YourTableName (Column1, Column2, Column3)
 WHERE IsDeleted = 0

您可以在这里找到完整的答案：

输入链接描述

- MarchalPT

---

MySQL 支持吗？ - xjlin0

我认为他们改变了问题标题。 - MarchalPT

或者我在这里放置了SQL Server的示例，但忘记提到如何操作。 - MarchalPT