SQL 如何跟踪数据表中的更改

在本文中，我们将介绍如何使用SQL来跟踪数据表中的更改。跟踪数据表的更改是数据库管理的重要任务之一，它可以帮助我们了解数据的历史变化以及进行数据的审计和恢复。我们将介绍以下几种常见的方法来实现这一目标：使用时间戳列、使用日志表和使用触发器。

阅读更多：SQL 教程

使用时间戳列

时间戳列是一种用来记录数据最后更改时间的特殊列。每当数据表中的一行发生更改时，时间戳列会自动更新为当前时间。通过查询时间戳列，我们可以获取到数据表中的更改历史。下面是一个使用时间戳列记录更改的示例：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入一行数据
INSERT INTO employees (id, name) VALUES (1, 'John Doe');

-- 更新数据
UPDATE employees SET name = 'Jane Smith' WHERE id = 1;

-- 查询更改历史
SELECT id, name, last_updated_at FROM employees;

上述示例中，我们创建了一个名为”employees”的数据表，并在其中添加了一个时间戳列”last_updated_at”。当我们插入或更新数据时，”last_updated_at”列会自动记录最后的更改时间。通过查询”employees”表，我们可以获取到每一行数据的更改历史。

使用日志表

另一种常见的跟踪数据表更改的方法是使用日志表。日志表是一个独立的表，用于记录数据表中的更改操作。每当对数据表进行插入、更新或删除操作时，都会在日志表中添加一条对应的记录。通过查询日志表，我们可以了解到数据表中的所有更改历史。下面是一个使用日志表记录更改的示例：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE employee_logs (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  action VARCHAR(10),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一行数据
INSERT INTO employees (id, name) VALUES (1, 'John Doe');

-- 插入一条日志记录
INSERT INTO employee_logs (employee_id, action) VALUES (1, 'INSERT');

-- 更新数据
UPDATE employees SET name = 'Jane Smith' WHERE id = 1;

-- 插入一条日志记录
INSERT INTO employee_logs (employee_id, action) VALUES (1, 'UPDATE');

-- 查询更改历史
SELECT el.log_id, e.id, e.name, el.action, el.changed_at 
FROM employee_logs el
JOIN employees e ON el.employee_id = e.id;

上述示例中，我们创建了一个名为”employees”的数据表和一个名为”employee_logs”的日志表。每当对”employees”表进行插入、更新或删除操作时，都会在”employee_logs”表中添加一条对应的记录。通过查询”employee_logs”表，我们可以获取到”employees”表的更改历史。

使用触发器

触发器是一种特殊的数据库对象，它可以在特定的数据表操作发生时自动触发一系列的动作。我们可以通过创建触发器来实现跟踪数据表更改的功能。下面是一个使用触发器记录更改的示例：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE employee_changes (
  change_id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  action VARCHAR(10),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER track_employee_changes
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_changes (employee_id, action) VALUES (NEW.id, 'INSERT');
END;

CREATE TRIGGER track_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_changes (employee_id, action) VALUES (NEW.id, 'UPDATE');
END;

-- 插入一行数据
INSERT INTO employees (id, name) VALUES (1, 'John Doe');

-- 更新数据
UPDATE employees SET name = 'Jane Smith' WHERE id = 1;

-- 查询更改历史
SELECT ec.change_id, e.id, e.name, ec.action, ec.changed_at 
FROM employee_changes ec
JOIN employees e ON ec.employee_id = e.id;

上述示例中，我们创建了一个名为”employees”的数据表和一个名为”employee_changes”的记录更改的表。然后，我们创建了两个触发器，分别用于在每次对”employees”表进行插入和更新操作时插入对应的记录到”employee_changes”表中。通过查询”employee_changes”表，我们可以获取到”employees”表的更改历史。