mysql - 关于数据逻辑删除的问题？

Question

我在表中逻辑删除了一条数据，表中存在唯一索引的字段，逻辑删除后，我有要insert一条一样的数据，这样肯定不行，如何解决这个问题？

Answer 1

1. As 1L said, cancel the unique constraint and rely on program control;
2. If it is mysql, you can add on duplicate key update status = ?
3. It is also mysql after the insert statement. In this case, you can also use replace into instead of insert into;

There is actually a lot of room for thinking about this issue. The most flexible method is 1, but it also faces the risk of generating dirty data (I won’t go into details about the impact of dirty data on program code. Anyone who has dealt with it will understand. ); 2 and 3 can theoretically only save the most recent logically deleted record

Which method to use depends on the business requirements for tombstone data storage. If you want to retain historical data and do not want to use method 1, you can use 2 and 3 to store tombstone data in real time (code level) or regularly. Migrate to history table.

The above are the methods I know. If there is a better method, I hope you can give me some advice.

Answer 2

The simplest way is to cancel the unique index and use program control to ensure that the field is unique.

Answer 3

If the data is exactly the same (note that it is exactly the same, the modification time is also the same), you can just undelete it

If the data is not exactly the same, either change the ID and insert it again, or add a column for modification history