mysql - 触发器的实际使用场景, 可能也就是这个了, 一起讨论还有没有别的

Question

触发器的实际使用场景大体说来就是帮你方便的迁移数据, 不过最好不要和业务紧密结合, 因为一个事务的的一部分在java那边, 另另一部分在触发器中是无法很方便的调试/排查/维护的, 唯有一个场景, 就是不想物理删数据的时候

很久以前有个不成文的规定就是, 不要物理删除数据, 所有表都要加上is_delete这个字段来标识某行数据是否被物理删除, 但是当遇到有唯一索引的时候, 这个规则就歇菜了, 因为, 比如name是唯一索引, 当用户添加xiaoming后, 然后删除xiaoming, 这时is_delete = Y,但是再次重新添加xiaoming就不可以了, 因为违反了唯一约束

因此, 这种情况, 就不要更新is_delete了, 而是利用 after delete 类型的触发器将数据迁移到另外的一张表, 比如 user_del 中, 他的字段与user表一致, 只不过多了个记录插入数据时间的字段

大家还有没有其他使用场景呢???????

Answer 1

First of all, regarding triggers, many large companies prohibit their use. First, they have poor portability, and second, they affect performance. This is what I strongly advocate.

Then let’s focus on the situation of non-physical deletion. It's really painful when you encounter a table with unique key constraints and a column like is_delete. This is how I handle it in the project. Assume that the user table user has these columns:

• id (primary key)

• username (unique key)

• ...

• is_delete

When inserting, if the unique key conflicts, check whether there is a deleted user with the same name:

SELECT id FROM user WHERE username = ? AND is_delete = 1

If you have the same name (and got the id), do an UPDATE operation and it will be treated as a deletion:

UPDATE user SET ..., is_delete = 0 WHERE id = ?

Then the painful problem comes. Since users have the need to delete (to be honest, this kind of need is rare), there is also the need to change the user name. Changing the user name encounters a primary key conflict, and the existing user has been logically deleted. So should you let him change it or not?

The expedient is to make the unique key "non-physical". Check it every time before creating a user. If you find a user with the same name that has not been deleted, you will allow the creation. The same goes for renaming. However, this operation may need to be turned into a transaction, because in the case of high concurrency, it is entirely possible that the SELECT does not have the same name, but the INSERT has the same name.

Answer 2

Question upstairs
"When changing the user name, a primary key conflict occurs, and the existing user has been logically deleted, so do you let him change it or not?"
There is a way to do it in a small project before I have used it before, but I don’t know if it is feasible for large projects.
Each table uses an auto-incrementing ID, and the primary key constraint uses the primary key + "is_delete" constraint.