在数据库操作中，更新数据是常见的需求。然而，有时候我们在更新数据前，需要获取并保留更新前的记录，以便进行数据恢复或审计。MySQL提供了存储过程和触发器来实现这一功能。本文将介绍如何使用MySQL存储过程和触发器来返回更新前的记录，并提供多个实际案例。

1. 什么是存储过程和触发器？

  • 存储过程：存储过程是存储在数据库中的一组SQL语句，它们可以被调用执行。存储过程允许我们封装逻辑，使得代码更可重用和模块化。
  • 触发器：触发器是在特定数据库事件（如INSERT、UPDATE、DELETE）发生时自动执行的一组SQL语句。触发器通常用于数据验证和审计。

2. 准备工作

首先，确保你有一个MySQL数据库和相应的表。假设我们有一个简单的表 employees，结构如下：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(255),
    salary DECIMAL(10, 2)
);

我们将创建存储过程和触发器来返回和记录更新前的记录。

3. 创建存储过程

案例1：简单存储过程返回更新前记录

我们将创建一个存储过程 update_employee_salary，在更新员工薪资前返回更新前的记录。

DELIMITER //

CREATE PROCEDURE update_employee_salary(IN emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
    DECLARE old_name VARCHAR(255);
    DECLARE old_position VARCHAR(255);
    DECLARE old_salary DECIMAL(10,2);

    -- 获取更新前的记录
    SELECT name, position, salary INTO old_name, old_position, old_salary
    FROM employees
    WHERE id = emp_id;

    -- 返回更新前的记录
    SELECT old_name AS name, old_position AS position, old_salary AS salary;

    -- 更新记录
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
END //

DELIMITER ;

调用存储过程

CALL update_employee_salary(1, 60000.00);

此存储过程在更新员工的薪资前返回员工的原始信息。

4. 使用触发器记录更新前的记录

触发器是另一种在更新数据时捕获记录的有效方法。我们将创建一个触发器，在更新员工记录前将旧记录插入到另一个表 employees_audit 中。

创建审计表

CREATE TABLE employees_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    old_name VARCHAR(255),
    old_position VARCHAR(255),
    old_salary DECIMAL(10, 2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器

DELIMITER //

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 插入旧记录到审计表
    INSERT INTO employees_audit (emp_id, old_name, old_position, old_salary)
    VALUES (OLD.id, OLD.name, OLD.position, OLD.salary);
END //

DELIMITER ;

更新记录并查看审计表

更新员工记录时，触发器会自动将旧记录插入审计表。

UPDATE employees SET salary = 70000.00 WHERE id = 1;

SELECT * FROM employees_audit;

5. 使用存储过程结合触发器

为了更方便地管理和调用，我们可以结合存储过程和触发器。

案例2：使用存储过程和触发器组合

我们将创建一个存储过程 update_employee_info，在更新信息前调用触发器以记录旧记录。

DELIMITER //

CREATE PROCEDURE update_employee_info(IN emp_id INT, IN new_name VARCHAR(255), IN new_position VARCHAR(255), IN new_salary DECIMAL(10,2))
BEGIN
    -- 更新记录
    UPDATE employees
    SET name = new_name, position = new_position, salary = new_salary
    WHERE id = emp_id;
END //

DELIMITER ;

调用存储过程并查看审计记录

CALL update_employee_info(1, 'John Doe', 'Manager', 80000.00);

SELECT * FROM employees_audit;

结语

通过结合使用MySQL的存储过程和触发器，我们可以在更新数据前有效地获取和记录旧记录。这不仅有助于数据恢复和审计，还提高了数据库操作的安全性和可靠性。希望本文提供的代码案例能帮助你在实际项目中更好地实现这一功能。