当前位置：首页 > news >正文

MySQL触发器和函数的详细示例

news 2025/5/1 11:32:47

概述

以下介绍MySQL触发器和函数的详细示例及说明
触发器是数据库的自动化规则，监听数据变化并自动干活。
函数是封装好的计算逻辑，随用随调，帮你省去重复写代码的麻烦。

一、MySQL 触发器示例

触发器（Trigger）是数据库在特定事件（INSERT/UPDATE/DELETE）发生时自动执行的一段代码。

1. 基本语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN-- 触发器逻辑
END;

2. 示例场景

场景1：自动填充创建时间

-- 在插入数据前自动设置 create_time 字段
CREATE TRIGGER before_insert_set_create_time
BEFORE INSERT ON orders
FOR EACH ROW
BEGINSET NEW.create_time = NOW();
END;

场景2：更新审计日志

-- 在更新用户表后记录变更日志
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGININSERT INTO audit_log (action, old_value, new_value, timestamp)VALUES ('UPDATE_USER',CONCAT('Old: ', OLD.email),CONCAT('New: ', NEW.email),NOW());
END;

场景3：数据校验

-- 插入数据前检查年龄是否合法
CREATE TRIGGER before_insert_check_age
BEFORE INSERT ON employees
FOR EACH ROW
BEGINIF NEW.age < 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能为负数';END IF;
END;

二、MySQL 函数示例

函数（Function）是一段可重复使用的代码，接收参数并返回一个值。

1. 基本语法

CREATE FUNCTION function_name(param1 type, ...)
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN-- 函数逻辑RETURN value;
END;

2. 示例场景

示例1：计算折扣后的价格

-- 输入原价和折扣率，返回折扣后的价格
CREATE FUNCTION calculate_discounted_price(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGINDECLARE discounted_price DECIMAL(10,2);SET discounted_price = original_price * (1 - discount_rate);RETURN discounted_price;
END;
-- 使用函数
SELECT calculate_discounted_price(100.00, 0.2); -- 返回 80.00

示例2：格式化电话号码

-- 将电话号码格式化为 xxx-xxxx-xxxx
CREATE FUNCTION format_phone_number(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGINRETURN CONCAT(SUBSTR(phone, 1, 3), '-',SUBSTR(phone, 4, 4), '-',SUBSTR(phone, 8, 4));
END;
-- 使用函数
SELECT format_phone_number('13812345678'); -- 返回 '138-1234-5678'

示例3：计算订单总价（含折扣）

-- 根据订单ID计算总价（考虑折扣）
CREATE FUNCTION calculate_order_total(order_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGINDECLARE total DECIMAL(10,2);SELECT SUM(quantity * price * (1 - discount))INTO totalFROM order_itemsWHERE order_id = order_id;RETURN total;
END;
-- 使用函数
SELECT calculate_order_total(1001);

三、注意事项

触发器
• 避免在触发器中执行耗时操作，可能影响性能。
• 使用 OLD 和 NEW 关键字访问旧数据和新数据。
• 确保触发器逻辑不会导致无限循环（例如在 AFTER UPDATE 触发器中更新同一张表）。
函数
• 函数必须返回一个值。
• 使用 DETERMINISTIC 声明确定性函数（相同输入固定输出），优化查询性能。
• 函数中可以使用条件语句（IF）、循环（WHILE）等逻辑。