MySQL 字段值修改检测方案

在数据库操作中，经常需要检测某个字段的值是否被修改过。这在审计、数据一致性检查以及触发某些业务逻辑时非常有用。本文将介绍一种基于MySQL的方案，通过在表中添加额外的字段来记录字段值的变更历史。

问题背景

假设我们有一个users表，其中包含用户的基本信息，如id、username和email。我们需要跟踪email字段的修改历史。

方案设计

1. 表结构设计

首先，我们需要在users表中添加两个额外的字段：email_modified和email_history。

  • email_modified：一个时间戳字段，用于记录email字段最后一次被修改的时间。
  • email_history：一个JSON字段，用于存储email字段的修改历史。
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    email_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    email_history JSON
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
2. 插入和更新数据

在插入新用户时，我们不需要手动设置email_modified和email_history字段，因为email_modified字段已经设置了默认值和ON UPDATE触发器，而email_history字段默认为NULL。

INSERT INTO users (username, email) VALUES ('John Doe', 'john@example.com');
  • 1.

在更新email字段时，我们需要同时更新email_modified字段和email_history字段。

UPDATE users
SET email = 'newemail@example.com',
    email_history = JSON_ARRAY_APPEND(email_history, '{"email": "' || email || '", "modified": "' || email_modified || '"}')
WHERE id = 1;
  • 1.
  • 2.
  • 3.
  • 4.
3. 查询修改历史

我们可以通过查询email_history字段来获取email字段的修改历史。

SELECT id, username, email, email_history
FROM users
WHERE id = 1;
  • 1.
  • 2.
  • 3.
4. 关系图

使用mermaid语法，我们可以绘制users表的关系图：

USER int id PK Primary Key string username Not Null string email Not Null timestamp email_modified Default Current Timestamp, On Update Current Timestamp json email_history

结论

通过在users表中添加email_modified和email_history字段，我们可以有效地跟踪email字段的修改历史。这种方法简单易行，不需要复杂的触发器或外部存储，可以很好地满足大多数业务场景的需求。

请注意，这种方法可能会增加数据库的存储需求，因为每次修改都会在email_history字段中添加新的记录。在实际应用中，可能需要根据业务需求和性能考虑，定期清理或归档旧的修改记录。