MySQL触发器和存储过程

---

前言

在 MySQL 中，触发器（Trigger）和存储过程（Stored Procedure）都是重要的数据库编程工具，它们都用于执行自动化的数据库操作，但它们的用途和触发时机有所不同

---

一、触发器（Trigger）

1.介绍

MySQL 触发器（Trigger）是一种特殊的存储过程，它在对某个表执行 INSERT、UPDATE 或 DELETE 操作时自动触发并执行。触发器的作用通常是用于数据的验证、审计、日志记录、或自动更新等

2.语法及参数解释

CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
trigger_body;

1. trigger_name：触发器的名称
2. BEFORE 或 AFTER：定义触发器执行的时机，BEFORE 表示在触发的 SQL 语句执行之前，AFTER 表示在触发的 SQL 语句执行之后
3. INSERT、UPDATE、DELETE：定义触发器响应的事件类型
4. table_name：触发器关联的表
5. FOR EACH ROW：指示触发器针对每一行进行操作
6. trigger_body：触发器执行的实际 SQL 语句，类似于存储过程的代码

3.触发器示例

1.先创建员工信息employees 表，日志记录audit_log表

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50),
    table_name VARCHAR(50),
    record_id INT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.创建insert触发器

CREATE TRIGGER after_employee_insert # 触发器的名称
AFTER INSERT ON employees # 当 employees 表插入新记录时触发此触发器
FOR EACH ROW # 对每一行进行操作
BEGIN
# SQL 语句
    INSERT INTO audit_log (action, table_name, record_id)
    VALUES ('INSERT', 'employees', NEW.id); # NEW.id 表示插入的数据行的 id 值
END;

3.在employees 表写入一条数据

INSERT INTO employees(`name`, `salary`) VALUES ('test', 15000.00);

查看audit_log表

4.创建BEFORE UPDATE 触发器，在更新 employees 表时，记录某个员工的薪资变动

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
# OLD.salary 和 NEW.salary 分别表示更新前和更新后的 salary 值
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit_log (action, table_name, record_id)
        VALUES ('UPDATE', 'employees', OLD.id);
    END IF;
END;

执行更新SQL

UPDATE employees SET salary = 20000 WHERE id = 1;

查看audit_log表

4.触发器管理

1.查看触发器

# 查看数据库中所有的触发器
SHOW TRIGGERS;

2.删除触发器

DROP TRIGGER trigger_name;

二、存储过程（Stored Procedure）

1.定义

存储过程（Stored Procedure）是一组预定义的 SQL 语句，它封装了数据库操作，可以像一个普通的函数一样进行调用。存储过程用于实现复杂的业务逻辑和数据操作，它可以接收输入参数、执行 SQL 查询、控制事务、以及返回结果。存储过程是数据库中的一类重要的对象，能够提高代码重用性、维护性和执行效率

2.用途

1. 封装复杂的业务逻辑：存储过程可以将多条 SQL 语句封装成一个过程，简化应用层代码
2. 提高性能：存储过程在数据库中预编译，执行时效率高，不需要每次都重新编译
3. 减少网络流量：存储过程通过一次调用执行多条 SQL 操作，减少了客户端与数据库之间的交互
4. 可维护性强：业务逻辑封装在数据库中，避免了在应用程序中重复写相同的 SQL
5. 事务控制：存储过程可以包含事务控制（COMMIT、ROLLBACK），确保数据的一致性

3.语法

CREATE PROCEDURE procedure_name([parameters])
[characteristics]
routine_body;

procedure_name：存储过程的名称
[parameters]：输入、输出或输入输出参数
[characteristics]：一些可选的特性，如 DETERMINISTIC（确定性函数），NO SQL（不执行 SQL），CONTAINS SQL（包含 SQL）等
routine_body：存储过程的执行内容，可以包含 SQL 语句、条件判断、循环等

存储过程的参数类型

IN：输入参数，调用存储过程时传入。
OUT：输出参数，存储过程执行后返回。
INOUT：输入输出参数，既可以传入，也可以返回

4.存储过程示例

CREATE PROCEDURE AddEmployee(IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, salary)
    VALUES (emp_name, emp_salary);
END;

这个存储过程 AddEmployee 接收两个参数，分别是 emp_name（员工姓名）和 emp_salary（员工薪水），并将这些数据插入到 employees 表中

5.存储过程调用

CALL procedure_name([parameters]);
# 上面的示例可以使用下面语句调用
CALL AddEmployee('John Doe', 50000.00);

6.存储过程的常见功能

1. 条件判断：存储过程可以使用 IF、CASE 等语句进行条件判断

DELIMITER $$  -- 更改分隔符

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    
    SELECT salary INTO emp_salary
    FROM employees
    WHERE id = emp_id;
    
    IF emp_salary IS NULL THEN
        SELECT 'Employee not found' AS Message;
    ELSE
        SELECT emp_salary AS Salary;
    END IF;
END$$

DELIMITER ;  -- 恢复分隔符

调用示例

2. 循环和游标：存储过程可以使用 LOOP、WHILE、FOR 等循环语句进行重复操作。游标（Cursor）可以用于逐行遍历查询结果

DELIMITER $$

CREATE PROCEDURE GetAllEmployees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT emp_id, emp_name;
    END LOOP;
    
    CLOSE emp_cursor;
END$$

DELIMITER ;

调用示例

3. 事务控制：存储过程可以包含 COMMIT 和 ROLLBACK，用于控制事务

# 先创建表
CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户ID，主键
    balance DECIMAL(10, 2) NOT NULL            -- 账户余额
);
# 写入数据
INSERT INTO accounts (balance) VALUES (1000.00), (500.00);

DELIMITER $$

CREATE PROCEDURE TransferFunds(IN from_account INT, IN to_account INT, IN amount DECIMAL(10, 2))
BEGIN
    DECLARE sufficient_funds BOOLEAN;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 检查是否有足够的资金
    SELECT balance >= amount INTO sufficient_funds
    FROM accounts
    WHERE account_id = from_account;
    
    IF sufficient_funds THEN
        -- 扣款
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
        -- 存款
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
        -- 提交事务
        COMMIT;
        SELECT 'Transaction successful' AS Message;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SELECT 'Insufficient funds' AS Message;
    END IF;
END$$

DELIMITER ;

调用示例

7.存储过程管理

1. 查看存储过程

SHOW PROCEDURE STATUS WHERE Db = 'your_database';

2. 查看存储过程定义

SHOW CREATE PROCEDURE procedure_name;

3. 删除存储过程

DROP PROCEDURE procedure_name;

8.存储过程优缺点

1. 减少网络延迟：存储过程减少了客户端与数据库之间的交互次数，通过一次调用执行多个操作
2. 避免重复查询：存储过程避免了在应用层多次发送 SQL 查询，尤其是在处理复杂的业务逻辑时，可以减少不必要的查询
3. 优化 SQL 执行计划：存储过程在数据库中预编译，可以避免每次执行时的解析和优化，提升执行效率
4. 过度依赖存储过程的风险：将所有逻辑都放入存储过程中可能使得数据库变得复杂，难以维护

---

三.触发器与存储过程的比较

特性	触发器（Trigger）	存储过程（Stored Procedure）
触发时机	由事件（如 INSERT、UPDATE、DELETE）自动触发	由应用程序或用户手动调用
用途	自动化数据操作、审计、数据验证等	封装业务逻辑、批量处理、复杂查询等
执行频率	自动执行，无需显式调用	需要显式调用
操作粒度	每次表的操作（例如，每行数据的插入、更新或删除）	可以操作多个表、执行复杂的 SQL 逻辑
事务控制	不支持事务控制	可以控制事务（BEGIN、COMMIT、ROLLBACK）
性能影响	可能影响性能，特别是对于大数据量的表	可以减少应用程序与数据库之间的交互次数，提高效率
递归调用	触发器可能导致递归调用	存储过程通常不涉及递归调用

总结

触发器用于自动化某些操作，在数据库层面上响应特定的事件，它的执行是由数据库事件触发的

存储过程则用于封装复杂的 SQL 逻辑，并可以由应用程序显式调用，适用于需要传递参数和控制流程的场景

这两者各有特点，通常情况下可以根据具体的需求选择使用触发器还是存储过程

原文地址：https://blog.csdn.net/weixin_42695345/article/details/143802411

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！