【MySQL】软删除和主键

0.注意

• 先删除再插入，若主键相同，那就是UPDATE。不要犯蠢先DELETE再INSERT!!

1. 软删除

在使用软删除机制时，通常会为表增加一个表示记录是否被删除的字段，比如is_deleted。通过给该字段置1，表明该字段已删除，而不是直接物理删除。
好处：记录被软删除，仍然留在表中，便于后面对数据的审计/分析/管理。
缺点：导致表记录越来越多，查询耗时增加。

实现

建表：

ALTER TABLE users ADD COLUMN is_deleted TINYINT(1) DEFAULT 0;

在is_deleted字段上建立索引，优化查询

UPDATE users SET is_deleted = 0 WHERE id = 1

插入记录：

INSERT INTO users (name, email， is_deleted) VALUES （'张三'，‘zhangsan@example.com'， 0)

删除记录：

UPDATE users SET is_deleted = 1 WHERE id = 1;

查询未删除记录：

INSERT INTO users (name, email， is_deleted) VALUES（'张三'，‘zhangsan@example.com'， 0)

恢复已删除记录：

UPDATE users SET is_deleted = 0 WHERE id = 1;

2. 新纪录的主键和软删除的记录的主键重复

当尝试插入一条新记录时，如果新记录的主键值与之前被软删除的记录的主键值相同，就会导致主键冲突。

• 在插入前检查主键是否被软删除的记录占用
  在插入新记录之前，先查询数据库中是否存在主键相同的记录，并且这些记录的 is_deleted 字段为已删除状态（如 is_deleted = 1）。
  如果存在这样的记录，可以考虑更新这条软删除记录的主键，或者生成一个新的主键值来插入新记录。
  例如，可以使用如下 SQL 语句来检查：
  SELECT * FROM test_table WHERE id = 新记录主键值 AND is_deleted = 1;
  如果查询到结果，就需要对主键进行处理后再插入新记录。
• 修改主键生成策略
  如果是自增主键，确保插入新记录时能够正常生成新的自增主键值。例如，在 MySQL 中，使用 auto_increment 的主键字段会在插入新记录时自动增加主键值。如果由于数据恢复等原因导致主键出现空缺，可以通过修改表结构来重新设置自增主键的起始值。假设表名为 test_table，可以通过如下 SQL 语句来更新自增主键的起始值：

ALTER TABLE test_table AUTO_INCREMENT = 新的起始值;

其中，“新的起始值” 应该大于表中当前已有的最大主键值。