MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints MySQL使用软删除、唯一键和外键约束 Soft-deletion is a technique used to mark data as deleted instead of physically removing it from the database. This allows for easy recovery of deleted data if needed.
MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints MySQL使用软删除、唯一键和外键约束 Soft-deletion is a technique used to mark data as deleted instead of physically removing it from the database. This allows for easy recovery of deleted data if needed.
假设我有两个表，`user`和`comment`。它们的表定义如下所示：\n
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;
\n这样做可以很好地保证数据的完整性，但是我希望能够“删除”一个用户并保留其所有的评论（供参考）。\n为此，我添加了`deleted`字段，这样我就可以在记录上设置`deleted = 1`。通过默认只显示`deleted = 0`的所有记录，我可以将所有已删除的记录隐藏起来，直到需要它们为止。\n到目前为止还不错。\n问题出现在以下情况：\n
- \n
- 一个用户注册了一个用户名（比如\"Sam\"），
- 我软删除了该用户（出于其他原因），然后
- 另一个人过来注册了一个相同的用户名为Sam，突然我们违反了`user`表上的唯一约束条件。
\n
\n
\n
\n我希望用户能够编辑自己的用户名，所以我不能将`username`作为主键，并且当删除用户时仍然会遇到相同的问题。\n有什么想法吗？\n编辑以进行澄清：根据RedFilter的答案和下面的评论，我关注的是“已删除”的用户和评论对公众不可见，只对管理员可见，或者仅用于计算统计信息的目的。\n这个问题只是一个思想实验，并且用户和评论表只是示例。不过，`username`并不是最好的例子；RedFilter提出了关于用户身份的有效观点，特别是当记录在公共环境中呈现时。\n关于“为什么`username`不是主键？”：这只是一个例子，但如果我将其应用于实际问题，我将需要在现有系统的约束条件下工作，该系统假定存在一个替代主键。
MySQL中的软删除（Soft-Deletion）、唯一键（Unique Key）和外键约束（Foreign Key Constraints）问题的出现原因是为了解决软删除的实际问题。
为了实现软删除，我提出了一种实用的解决方案，即通过创建一个新表来实现归档，该表包含以下列：original_id、table_name、payload（和可选的主键id）。
其中，original_id是被删除记录的原始ID，table_name是被删除记录的表名（在你的情况下是"user"），payload是被删除记录的所有列的JSON字符串化字符串。
我还建议对original_id列创建索引，以便后续数据检索。
通过这种方式来归档数据，你将获得以下几个优点：
- 跟踪历史数据
- 只需在一个地方归档任何表的记录，而不用考虑被删除记录的表结构
- 不用担心原始表中的唯一索引
- 不用担心检查原始表中的外键索引
在这里已经有一个讨论（链接）解释了为什么软删除在实践中不是一个好主意。软删除引入了一些潜在的麻烦，例如计算记录数量等。
通过使用MySQL的软删除、唯一键和外键约束，可以通过归档表的方式解决软删除的问题，并获得一些优势。
MySQL中使用软删除、唯一键和外键约束的问题出现的原因是为了避免新用户使用已删除的用户名，以防止身份混淆和由此可能产生的错误。当新用户注册时，通常会检查该用户名是否已被使用，以避免冲突。
解决方法是保持对username的唯一索引或约束。在删除操作时，将Id复制到已删除字段中，这样可以保持只有一个活动用户，同时可以有几个具有相同名称的已删除用户（通过使用constaint unique(name, deleted)）。
有人认为允许两个不同的用户使用相同的用户名是一种“hack”，但也有人认为这是一种聪明的解决方法。可以将约束设置为UNIQUE KEY (username, deleted)。
还有人担心“删除”用户和评论对公众不可见，只对管理员可见，或者仅用于计算统计信息的目的。尽管有这些情况，但仍然有人认为允许多个非并发用户具有相同的用户名在可用性和社区角度来看是一个坏主意。这些已删除用户的信息虽然对其他用户不可见，但其他用户仍然记得他们，可能会导致混淆。
MySQL中的软删除、唯一键和外键约束的问题是如何解决在删除操作中出现的重复记录问题。解决方法是改变'deleted'字段的类型为整数，然后在删除操作中将id字段的值复制到deleted字段中。这种方法可以实现以下功能：保留活跃用户的唯一用户名，允许多次删除具有相同用户名的用户。
但是，这种方法有一个问题，即'deleted'字段不能只有两个值，因为这会导致以下情况无法正常工作：首先创建一个名为'Sam'的用户，然后删除该用户，再创建一个用户名为'Sam'的新用户，最后尝试删除用户名为'Sam'的用户时会失败。这是因为已经存在一个用户名为'Sam'且deleted=1的记录。
要解决这个问题，可以将'deleted'字段的类型改为时间戳。这样可以在其中添加更多的已删除行。如果要获取现有（未删除）记录，可以在该字段中检查null值。但是，如果允许deleted字段为空，将丢失唯一键的值。
至少在PostgreSQL中，可以使用部分索引来解决这两个要求的问题。然而，在MySQL中无法实现这种方法，因为MySQL忽略null值。
在MySQL中，可以通过在username和deletedAt字段上使用一对键来实现这个想法。但是，这种方法同样存在null值被忽略的问题。
MySQL中的软删除、唯一键和外键约束的问题是如何在删除操作中解决重复记录的问题。解决方法是将'deleted'字段的类型改为整数，并在删除操作中将id字段的值复制到deleted字段中。然而，这种方法存在唯一键值的丢失和null值被忽略的问题。在MySQL中无法使用部分索引来解决这个问题，并且通过在username和deletedAt字段上使用一对键也无法解决。