概述
- 以下介绍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
BEGIN
SET NEW.create_time = NOW();
END;
场景2：更新审计日志
-- 在更新用户表后记录变更日志
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT 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
BEGIN
IF NEW.age < 0 THEN
SIGNAL 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
BEGIN
DECLARE 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
BEGIN
RETURN 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
BEGIN
DECLARE total DECIMAL(10,2);
SELECT SUM(quantity * price * (1 - discount))
INTO total
FROM order_items
WHERE order_id = order_id;
RETURN total;
END;
-- 使用函数
SELECT calculate_order_total(1001);
三、注意事项
-
触发器 • 避免在触发器中执行耗时操作，可能影响性能。 • 使用
OLD和NEW关键字访问旧数据和新数据。 • 确保触发器逻辑不会导致无限循环（例如在AFTER UPDATE触发器中更新同一张表）。 -
函数 • 函数必须返回一个值。 • 使用
DETERMINISTIC声明确定性函数（相同输入固定输出），优化查询性能。 • 函数中可以使用条件语句（IF）、循环（WHILE）等逻辑。
四、总结
• 触发器适合处理数据一致性、审计日志、自动填充字段等场景。 • 函数适合封装复杂计算或格式化逻辑，提高代码复用性。
通过合理使用触发器和函数，可以简化应用程序逻辑并提高数据库的健壮性。
