MySQL存储函数与触发器创建方法及实战案例

1、存储函数

存储函数是一种有返回值的特殊存储过程。其核心特征在于参数仅限 IN 类型，且必须在头部明确指定 RETURNS type。在调用时，可直接在 SELECT 语句中使用。

第1步：了解函数特性声明

由于 MySQL 8.0 版本默认开启 Binlog（二进制日志），为了保证数据的一致性校验，定义存储函数时必须添加以下特性说明之一，否则将会报错：

• DETERMINISTIC：表示相同的输入参数总是产生相同的结果（确定性）。
• NO SQL：表示函数体内不包含任何 SQL 语句。
• READS SQL DATA：表示包含读取数据的语句，但不包含写入操作。

第2步：编写循环累加案例

以下案例演示了创建一个名为 fun1 的函数，用于计算从 1 到传入参数 n 的累加值。注意代码中的参数声明、变量初始化以及循环退出条件。

CREATE FUNCTION fun1(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT DEFAULT 0;
    WHILE n > 0 DO
        SET total := total + n;
        SET n := n - 1;
    END WHILE;
    RETURN total;
END;

调用测试：

SELECT fun1(50);

2、触发器 (Trigger)

触发器是与表紧密关联的数据库对象。当对目标表执行 INSERT、UPDATE 或 DELETE 操作时，若满足指定条件（如 AFTER 或 BEFORE），系统将自动触发执行预定义的 SQL 语句集合。触发器仅支持行级触发，不支持语句级触发。

第1步：掌握 NEW 和 OLD 伪对象

在触发器内部，可以使用别名来引用发生变化的记录内容，这在不同数据库中具有通用性：

• INSERT 触发器：NEW 表示即将新增的数据行。
• UPDATE 触发器：OLD 表示修改前的数据，NEW 表示修改后的数据。
• DELETE 触发器：OLD 表示即将被删除的数据。

第2步：基础语法结构

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 强制行级触发
BEGIN
    -- 触发动作语句
END;

管理命令：

• 查看所有触发器：SHOW TRIGGERS;
• 删除触发器：DROP TRIGGER [schema_name.]trigger_name;

3、实战：利用触发器记录数据变更日志

为了确保数据操作的审计追踪，我们将建立一个日志表 user_logs，并通过三个触发器分别捕获 tb_user 表的增加、修改和删除动作，将所有变更记录入日志表。

第1步：准备日志表结构

首先创建用于存放操作记录的表 user_logs：

CREATE TABLE user_logs (
    id INT(11) NOT NULL AUTO_INCREMENT,
    operation VARCHAR(20) NOT NULL COMMENT '操作类型 (insert/update/delete)',
    operate_time DATETIME NOT NULL COMMENT '操作时间',
    operate_id INT(11) NOT NULL COMMENT '被操作的记录ID',
    operate_params VARCHAR(500) COMMENT '操作详细参数内容',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第2步：创建 INSERT 触发器

在用户表新增数据后，自动记录插入的信息：

DELIMITER //
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user 
FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (
        NULL, 
        'insert', 
        NOW(), 
        NEW.id, 
        CONCAT('插入数据: id=', NEW.id, ', name=', NEW.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession)
    );
END //
DELIMITER ;

测试方法：

-- 插入一条测试数据
INSERT INTO tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (26, '三皇子', '18809091212', 'erhuangzi@163.com', '软件工程', 23, '1', '1', NOW());

第3步：创建 UPDATE 触发器

在数据更新时，同时记录更新前 (OLD) 和更新后 (NEW) 的状态对比：

DELIMITER //
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON tb_user 
FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (
        NULL, 
        'update', 
        NOW(), 
        NEW.id, 
        CONCAT(
            '原数据: id=', OLD.id, ', name=', OLD.name, ', profession=', OLD.profession,
            ' | ',
            '新数据: id=', NEW.id, ', name=', NEW.name, ', profession=', NEW.profession
        )
    );
END //
DELIMITER ;

测试方法：

UPDATE tb_user SET profession = '会计' WHERE id = 23;

第4步：创建 DELETE 触发器

在数据删除后，将被删除的数据快照存入日志：

DELIMITER //
CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user 
FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (
        NULL, 
        'delete', 
        NOW(), 
        OLD.id, 
        CONCAT('删除数据: id=', OLD.id, ', name=', OLD.name, ', phone=', OLD.phone)
    );
END //
DELIMITER ;

测试方法：

DELETE FROM tb_user WHERE id = 26;

总结

通过以上步骤，我们完成了 MySQL 存储函数与触发器的创建与部署。存储函数解决了复杂计算的复用问题，而触发器则为业务逻辑增加了强大的自动化约束能力。在实际生产环境中，虽然触发器能简化应用层的日志记录工作，但也需注意其对性能的影响，建议定期清理日志表或建立归档机制。

以上就是 MySQL 开发中存储函数与触发器使用示例的详细内容，更多关于 MySQL 存储函数与触发器内容的资料请关注本站其它相关文章！