SQL学习笔记5

SQL SQL 触发器函数

发布时间 : 2026-07-04 14:30

字数:2.4k 阅读 :

SQL学习笔记五：触发器与自定义函数

上篇讲了存储过程，结尾留了个对比表——存储过程、函数、触发器三者各司其职。这篇就把触发器和函数补上。

11. 触发器（Trigger）

触发器是一种特殊的存储过程，不需要手动调用，当表上发生 INSERT、UPDATE、DELETE 时自动触发。最常见的用途：审计日志、数据校验、级联更新。

11.1 触发器的分类

分类维度	类型	说明
触发时机	`BEFORE` / `AFTER`	操作前触发 / 操作后触发
触发事件	`INSERT` / `UPDATE` / `DELETE`	对哪种操作响应
作用范围	`FOR EACH ROW`（行级） / `FOR EACH STATEMENT`（语句级）	每行触发一次 / 每条语句触发一次

💡 MySQL 只支持行级触发器（FOR EACH ROW），PostgreSQL 两者都支持。

11.2 基本语法

MySQL

DELIMITER $$

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

DELIMITER ;

PostgreSQL

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW;  -- 或 RETURN OLD（DELETE 时）
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    FOR EACH ROW
    EXECUTE FUNCTION trigger_function();

💡 PostgreSQL 把触发器逻辑写在函数里，再用 CREATE TRIGGER 绑定。好处是函数可以复用给多个触发器。

11.3 NEW 和 OLD 伪记录

在行级触发器中，可以通过 NEW 和 OLD 访问数据：

操作	`OLD`	`NEW`
`INSERT`	无（全是 NULL）	新插入的行
`DELETE`	被删除的行	无（全是 NULL）
`UPDATE`	修改前的行	修改后的行

-- 示例：自动记录修改前后的值
IF NEW.price <> OLD.price THEN
    INSERT INTO price_log(product_id, old_price, new_price, changed_at)
    VALUES (NEW.id, OLD.price, NEW.price, NOW());
END IF;

11.4 实战示例

示例一：自动更新”修改时间”字段

DELIMITER $$

CREATE TRIGGER trg_users_before_update
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END$$

DELIMITER ;

每次 UPDATE users 表时，updated_at 自动刷新，不用在应用代码里手动设置。

示例二：审计日志（记录谁在什么时候删了什么）

先建一张日志表：

CREATE TABLE audit_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    table_name  VARCHAR(50),
    operation   VARCHAR(10),
    record_id   INT,
    old_data    JSON,
    new_data    JSON,
    changed_at  DATETIME DEFAULT NOW(),
    changed_by  VARCHAR(50)
);

再创建触发器：

DELIMITER $$

CREATE TRIGGER trg_products_after_delete
    AFTER DELETE ON products
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, record_id, old_data, changed_at)
    VALUES (
        ''products'',
        ''DELETE'',
        OLD.id,
        JSON_OBJECT(''name'', OLD.name, ''price'', OLD.price, ''stock'', OLD.stock),
        NOW()
    );
END$$

DELIMITER ;

示例三：防止误删（阻止删除关键数据）

DELIMITER $$

CREATE TRIGGER trg_orders_before_delete
    BEFORE DELETE ON orders
    FOR EACH ROW
BEGIN
    IF OLD.status = ''已发货'' THEN
        SIGNAL SQLSTATE ''45000''
        SET MESSAGE_TEXT = ''不允许删除已发货的订单'';
    END IF;
END$$

DELIMITER ;

示例四：级联自动插入（一对多关系联动）

-- 新增用户时，自动创建一个默认钱包
DELIMITER $$

CREATE TRIGGER trg_users_after_insert
    AFTER INSERT ON users
    FOR EACH ROW
BEGIN
    INSERT INTO wallets (user_id, balance)
    VALUES (NEW.id, 0.00);
END$$

DELIMITER ;

11.5 触发器的注意事项

陷阱 🕳️

触发器链（级联触发）

触发器里修改了表 B，表 B 又有触发器修改表 C……形成链条。一旦出问题很难排查。
```
-- 预防：检查嵌套深度
SELECT @@max_sp_recursion_depth;  -- MySQL，默认 0（不允许递归）
```

性能问题

每行触发一次，批量操作时开销巨大：

-- 10000 行 → 触发器执行 10000 次！
UPDATE products SET price = price * 1.1;

批量操作时考虑临时禁用触发器：

DISABLE TRIGGER trg_products_before_update ON products;  -- PostgreSQL
-- 执行批量操作
ENABLE TRIGGER trg_products_before_update ON products;

调试困难

触发器在后台默默执行，出了问题没有显式的报错栈。建议在触发器中加日志记录。
BEFORE 触发器中修改 NEW 仅对行级有效

语句级触发器不能修改数据行。

最佳实践 ✅

建议	说明
一个触发器只做一件事	别把校验、日志、级联全写一起
触发器命名要规范	`trg_{表名}_{before/after}_{insert/update/delete}`
避免在触发器中调用外部服务	如发 HTTP 请求、发邮件——会拖慢整个事务
多行操作时评估性能	考虑用语句级触发器代替行级
保持逻辑简单	复杂业务放存储过程，触发器只做轻量校验

11.6 查看与管理触发器

-- MySQL 查看所有触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE ''users%'';

-- 查看触发器定义
SHOW CREATE TRIGGER trg_users_before_update;

-- 删除触发器
DROP TRIGGER IF EXISTS trg_users_before_update;

-- PostgreSQL 查看触发器
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers;

12. 自定义函数（User-Defined Function）

函数和存储过程很像，但有一个本质区别：函数必须有返回值，且可以在 SELECT 中直接调用。

12.1 函数 vs. 存储过程

比较维度	函数	存储过程
返回值	必须返回一个值（标量或表）	可选，通过 OUT 参数
在 SELECT 中使用	✅ `SELECT func()`	❌
事务控制	❌ 不能 COMMIT/ROLLBACK	✅
调用方式	`SELECT func(args)`	`CALL proc(args)`
修改数据	有限制（视数据库而定）	无限制
典型场景	计算、格式化、转换	批量处理、复杂业务

12.2 基本语法

MySQL

DELIMITER $$

CREATE FUNCTION func_name(param1 INT, param2 VARCHAR(50))
RETURNS INT                                    -- 返回值类型
DETERMINISTIC                                  -- 确定性：相同输入必有相同输出
READS SQL DATA                                 -- 只读数据
BEGIN
    DECLARE result INT;

    -- 函数逻辑
    SELECT COUNT(*) INTO result FROM table_name WHERE col = param1;

    RETURN result;
END$$

DELIMITER ;

MySQL 函数属性说明：

属性	含义
`DETERMINISTIC`	确定性函数，相同输入一定返回相同结果（用于优化和主从复制）
`NOT DETERMINISTIC`	非确定性（如 `NOW()`）
`READS SQL DATA`	只读数据，不修改
`MODIFIES SQL DATA`	会修改数据
`NO SQL`	不含 SQL 语句，纯计算

PostgreSQL

CREATE OR REPLACE FUNCTION calc_discount(price NUMERIC, level VARCHAR)
RETURNS NUMERIC AS $$
DECLARE
    rate NUMERIC;
BEGIN
    CASE level
        WHEN ''VIP''    THEN rate := 0.8;
        WHEN ''GOLD''   THEN rate := 0.85;
        WHEN ''SILVER'' THEN rate := 0.9;
        ELSE rate := 0.95;
    END CASE;

    RETURN price * rate;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

PostgreSQL 函数易变性标记：

标记	含义	示例
`IMMUTABLE`	相同参数永远返回相同结果	数学计算
`STABLE`	同一事务内相同参数返回相同结果	查表取值
`VOLATILE`	每次调用结果都可能不同（默认）	`random()`

12.3 实战示例

示例一：格式化日期

DELIMITER $$

CREATE FUNCTION format_date_cn(d DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    RETURN DATE_FORMAT(d, ''%Y年%m月%d日'');
END$$

DELIMITER ;

-- 使用
SELECT format_date_cn(''2026-07-04'');  -- 2026年07月04日
SELECT name, format_date_cn(created_at) AS 创建日期 FROM users;

示例二：提取字符串中的数字

DELIMITER $$

CREATE FUNCTION extract_number(str VARCHAR(255))
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    DECLARE result VARCHAR(255) DEFAULT '''';
    DECLARE i INT DEFAULT 1;
    DECLARE ch CHAR(1);

    WHILE i <= CHAR_LENGTH(str) DO
        SET ch = SUBSTRING(str, i, 1);
        IF ch BETWEEN ''0'' AND ''9'' THEN
            SET result = CONCAT(result, ch);
        END IF;
        SET i = i + 1;
    END WHILE;

    RETURN IF(result = '''', NULL, CAST(result AS INT));
END$$

DELIMITER ;

-- 使用
SELECT extract_number(''订单号: AB20260704001'');  -- 20260704001

示例三：计算两个日期之间的工作日数

DELIMITER $$

CREATE FUNCTION workdays_between(start_date DATE, end_date DATE)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    DECLARE days INT DEFAULT 0;
    DECLARE cur DATE;

    SET cur = start_date;

    WHILE cur <= end_date DO
        IF DAYOFWEEK(cur) NOT IN (1, 7) THEN  -- 排除周日(1)和周六(7)
            SET days = days + 1;
        END IF;
        SET cur = DATE_ADD(cur, INTERVAL 1 DAY);
    END WHILE;

    RETURN days;
END$$

DELIMITER ;

-- 使用
SELECT workdays_between(''2026-07-01'', ''2026-07-04'');  -- 3（7月1-4日，排除周六）

示例四：返回表类型（PostgreSQL）

PostgreSQL 函数可以返回整个表作为结果集：

CREATE OR REPLACE FUNCTION get_top_students(n INT)
RETURNS TABLE(name VARCHAR, avg_score NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT s.name, AVG(sc.score)::NUMERIC(5,2)
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name
    ORDER BY AVG(sc.score) DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;

-- 像查表一样调用
SELECT * FROM get_top_students(5);

12.4 注意事项

陷阱 🕳️

MySQL 函数中不能执行动态 SQL

-- ❌ MySQL 函数中不允许 PREPARE/EXECUTE
-- 需要用存储过程代替

函数中修改数据有风险

-- ❌ 在 SELECT 中调用会修改数据的函数，结果不可预测
SELECT id, update_counter(id) FROM users;  -- 副作用不确定

性能陷阱：函数在 SELECT 中对每行执行一次

-- ❌ 大表慢！对 users 每一行都调用一次函数
SELECT id, complex_calc(id) FROM users WHERE 1=1;  -- 100 万行 = 100 万次函数调用

-- ✅ 尽量把逻辑写到 WHERE/JOIN 里，减少函数调用

函数与 GROUP BY 混用要注意确定性

-- 确保函数在聚合层面计算，而非行级
SELECT department, SUM(salary) FROM employees GROUP BY department;
-- 而不是
SELECT department, calc_bonus(salary) FROM employees GROUP BY department;  -- ❌ 不可预测

四件套对比：存储过程 vs. 函数 vs. 触发器 vs. 视图

特性	存储过程	函数	触发器	视图
调用方式	`CALL`	`SELECT`	事件触发	`SELECT`
返回值	OUT 参数	必须返回	无	虚拟表
修改数据	✅	⚠️ 有限制	✅	❌
事务控制	✅	❌	跟随触发语句	❌
参数	IN/OUT/INOUT	IN	NEW/OLD	无
存储数据	❌	❌	❌	❌（只是查询封装）
典型场景	复杂业务流程	计算/格式化	审计/校验	简化复杂查询

💡 选择原则：能查询解决的用视图，要计算的用函数，要自动化响应用触发器，复杂多步操作用存储过程。

结语

触发器和函数是 SQL 进阶的两块重要拼图。触发器让数据库能”主动响应”数据变化，函数让复杂计算可以在 SQL 层面复用。到现在为止，存储过程、函数、触发器、视图这四件套已经齐全，配合之前的 JOIN、子查询、索引等基础，日常开发中的绝大多数 SQL 场景都能应对了。

下一篇计划讲窗口函数——这是做数据分析、排名、累计计算的利器，也是面试高频考点。

sunrtnj@163.com