MySQL存储过程触发器函数实战：从SQL封装到自动化业务逻辑，3大核心组件吃透数据库编程

第一部分：存储过程——业务逻辑的封装工厂

1.1 概念：为什么需要存储过程？

存储过程是一组预编译的SQL语句，被存储在数据库中，可以被程序调用执行。它类似于编程语言中的函数或方法。

打个比方：存储过程就像厨房里的"预制菜"。客人点菜时，不需要从洗菜、切菜开始，而是直接端上已经准备好的成品。数据库调用存储过程时，服务器直接执行预编译的SQL，省去了网络传输和编译的损耗。

存储过程的核心优势

1.2 基本语法

// sql
— 语法结构
DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] param_name param_type)
BEGIN
    — SQL语句块
    — 支持变量声明、条件判断、循环控制
END //
DELIMITER ;

参数说明

管理命令

// sql
— 查看所有存储过程
SHOW PROCEDURE STATUS;

— 查看特定数据库的存储过程
SHOW PROCEDURE STATUS WHERE db = 'ecommerce_db';

— 查看存储过程的创建代码
SHOW CREATE PROCEDURE procedure_name;

— 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;

1.3 电商实战：订单批量处理

场景描述

电商系统中，运营人员经常需要批量处理订单状态。比如"双十一"结束后，需要将所有"已支付"状态的订单批量发货，或者批量关闭超时未支付的订单。

完整代码实现

// sql
— =============================================
— 电商订单批量处理存储过程
— 场景：批量更新订单状态，支持状态流转校验
— 作者：电商技术团队
— 创建时间：2024-01-15
— =============================================

DELIMITER //

— 存储过程：批量更新订单状态
— 参数说明：
—   p_order_ids: 订单ID列表，格式 '1,2,3,4'
—   p_old_status: 原状态（用于校验）
—   p_new_status: 新状态
—   p_operator_id: 操作人ID
— 返回值：成功更新的订单数量
DROP PROCEDURE IF EXISTS sp_batch_update_order_status//
CREATE PROCEDURE sp_batch_update_order_status(
    IN p_order_ids TEXT,           — 订单ID列表
    IN p_old_status VARCHAR(20),   — 原状态
    IN p_new_status VARCHAR(20),   — 新状态
    IN p_operator_id INT,          — 操作人ID
    OUT p_updated_count INT        — 输出：实际更新的数量
)
BEGIN
    — 声明变量
    DECLARE v_current_id INT;
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE v_order_count INT DEFAULT 0;

    — 游标：逐条处理订单
    DECLARE cur_order CURSOR FOR
        SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(p_order_ids, ',', n), ',', -1) AS UNSIGNED) AS order_id
        FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
              SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t
        WHERE n <= LENGTH(p_order_ids) – LENGTH(REPLACE(p_order_ids, ',', '')) + 1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    — 临时表存储需要处理的订单
    DROP TEMPORARY TABLE IF EXISTS tmp_orders;
    CREATE TEMPORARY TABLE tmp_orders (
        order_id INT PRIMARY KEY
    );

    — 解析订单ID列表并插入临时表
    SET @sql = CONCAT('INSERT INTO tmp_orders VALUES ',
                      (SELECT GROUP_CONCAT(DISTINCT order_id)
                       FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(p_order_ids, ',', n), ',', -1) AS UNSIGNED) AS order_id
                             FROM (SELECT @row := @row + 1 AS n FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                                          (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2,
                                          (SELECT 0 UNION SELECT 1 UNION SELECT 2) t3,
                                          (SELECT @row := 0) r) numbers
                             WHERE n <= LENGTH(p_order_ids) – LENGTH(REPLACE(p_order_ids, ',', '')) + 1) AS ids));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    — 开启事务
    START TRANSACTION;

    — 校验状态是否允许流转
    IF NOT EXISTS (
        SELECT 1 FROM order_status_config
        WHERE from_status = p_old_status AND to_status = p_new_status AND is_active = 1
    ) THEN
        — 抛出异常：不允许的状态流转
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许的状态流转，请检查状态配置';
    END IF;

    — 统计需要处理的订单数量
    SELECT COUNT(*) INTO v_order_count
    FROM orders o
    INNER JOIN tmp_orders t ON o.order_id = t.order_id
    WHERE o.status = p_old_status;

    IF v_order_count = 0 THEN
        SET p_updated_count = 0;
        ROLLBACK;
    ELSE
        — 执行批量更新
        UPDATE orders o
        INNER JOIN tmp_orders t ON o.order_id = t.order_id
        SET o.status = p_new_status,
            o.update_time = NOW(),
            o.operator_id = p_operator_id
        WHERE o.status = p_old_status;

        SET p_updated_count = ROW_COUNT();

        — 记录操作日志
        INSERT INTO order_operation_log (order_ids, old_status, new_status, operator_id, operate_time)
        VALUES (p_order_ids, p_old_status, p_new_status, p_operator_id, NOW());

        COMMIT;
    END IF;

    — 清理临时表
    DROP TEMPORARY TABLE IF EXISTS tmp_orders;
END//

DELIMITER ;

调用示例

// sql
— 调用：批量将"待发货"订单状态更新为"已发货"
SET @updated = 0;
CALL sp_batch_update_order_status('1001,1002,1003,1004,1005', 'pending', 'shipped', 1, @updated);
SELECT @updated AS '成功更新订单数';

— 调用：批量关闭超时未支付的订单
SET @closed_count = 0;
CALL sp_batch_update_order_status(
    '2001,2002,2003',
    'unpaid',
    'cancelled',
    1,
    @closed_count
);
SELECT @closed_count AS '超时关闭订单数';

1.4 参数类型详解

IN 输入参数

将数据传入存储过程使用，参数值在过程内部可以修改，但修改不会影响调用者。

// sql
— 场景：电商订单查询——根据订单ID查询订单详情
DELIMITER //
CREATE PROCEDURE sp_get_order_detail(IN p_order_id INT)
BEGIN
    SELECT
        o.order_id,
        o.order_no,
        o.user_id,
        u.user_name,
        u.phone,
        o.total_amount,
        o.status,
        o.create_time,
        CASE o.status
            WHEN 'unpaid' THEN '待支付'
            WHEN 'paid' THEN '已支付'
            WHEN 'shipped' THEN '已发货'
            WHEN 'completed' THEN '已完成'
            WHEN 'cancelled' THEN '已取消'
        END AS status_text
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.user_id
    WHERE o.order_id = p_order_id;
END //
DELIMITER ;

— 调用
CALL sp_get_order_detail(10086);

OUT 输出参数

将存储过程的执行结果返回给调用者，初始值为NULL。

// sql
— 场景：获取用户订单统计数据
DELIMITER //
CREATE PROCEDURE sp_get_user_order_stats(
    IN p_user_id INT,
    OUT p_total_orders INT,      — 总订单数
    OUT p_total_amount DECIMAL(15,2),  — 总消费金额
    OUT p_avg_amount DECIMAL(15,2)     — 平均订单金额
)
BEGIN
    — 初始化输出参数
    SET p_total_orders = 0;
    SET p_total_amount = 0.00;
    SET p_avg_amount = 0.00;

    — 查询统计数据
    SELECT
        COUNT(*),
        COALESCE(SUM(total_amount), 0),
        COALESCE(AVG(total_amount), 0)
    INTO p_total_orders, p_total_amount, p_avg_amount
    FROM orders
    WHERE user_id = p_user_id
      AND status IN ('paid', 'shipped', 'completed');
END //
DELIMITER ;

— 调用（必须使用用户变量接收输出参数）
SET @orders = 0, @total = 0.00, @avg = 0.00;
CALL sp_get_user_order_stats(1001, @orders, @total, @avg);
SELECT @orders AS '订单总数', @total AS '总消费', @avg AS '平均金额';

INOUT 输入输出参数

既可以传入值，也可以在过程内部修改后返回给调用者。

// sql
— 场景：商品库存批量锁定与解锁
DELIMITER //
CREATE PROCEDURE sp_lock_product_stock(
    INOUT p_product_id INT,
    IN p_quantity INT,
    IN p_action VARCHAR(10)  — 'lock' 或 'unlock'
)
BEGIN
    DECLARE v_current_stock INT;
    DECLARE v_reserved_stock INT;

    — 获取当前库存状态
    SELECT stock, reserved_stock
    INTO v_current_stock, v_reserved_stock
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;  — 加锁防止并发问题

    IF p_action = 'lock' THEN
        — 锁定库存：检查库存是否充足
        IF v_current_stock < p_quantity THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '库存不足，无法锁定';
        END IF;

        UPDATE products
        SET stock = stock – p_quantity,
            reserved_stock = reserved_stock + p_quantity,
            update_time = NOW()
        WHERE product_id = p_product_id;

    ELSEIF p_action = 'unlock' THEN
        — 解锁库存
        UPDATE products
        SET stock = stock + p_quantity,
            reserved_stock = reserved_stock – p_quantity,
            update_time = NOW()
        WHERE product_id = p_product_id
          AND reserved_stock >= p_quantity;

        IF ROW_COUNT() = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '预留库存不足，无法解锁';
        END IF;
    END IF;

    — 返回更新后的商品ID
    SET p_product_id = p_product_id;
END //
DELIMITER ;

— 调用：锁定库存
SET @pid = 101;  — 商品ID
CALL sp_lock_product_stock(@pid, 5, 'lock');
SELECT @pid AS '锁定后的商品ID';

— 调用：解锁库存
SET @pid = 101;
CALL sp_lock_product_stock(@pid, 3, 'unlock');

三种参数类型对比

�� 面试考点：存储过程的参数类型

标准面试话术：

"存储过程的参数有三种类型：

第一种是IN输入参数，这是默认类型，用于将数据传入存储过程内部，就像函数的形参一样，在过程内部可以修改，但这种修改不会影响调用者传入的实际变量。

第二种是OUT输出参数，用于从存储过程返回数据给调用者，它在过程开始时初始值是NULL，需要在过程内部通过SET语句赋值后才能被调用者获取。

第三种是INOUT输入输出参数，它兼具前两者的特点，可以传入值，也可以在过程内部修改后返回给调用者，相当于引用传递。

在实际开发中，输入参数最常用，输出参数用于返回统计结果，INOUT参数使用场景相对较少，主要用于需要返回处理状态的场景。"

1.5 流程控制语句

IF 条件判断

// sql
— 语法结构
IF condition THEN
    statements;
[ELSEIF condition THEN
    statements;]
[ELSE
    statements;]
END IF;

电商实战：根据订单金额计算折扣

// sql
DELIMITER //
CREATE PROCEDURE sp_calculate_discount(
    IN p_order_amount DECIMAL(15,2),
    OUT p_discount_amount DECIMAL(15,2),
    OUT p_final_amount DECIMAL(15,2),
    OUT p_discount_type VARCHAR(20)
)
BEGIN
    SET p_discount_amount = 0.00;
    SET p_final_amount = p_order_amount;
    SET p_discount_type = 'no_discount';

    — 满减活动：满100减10，满200减30，满500减100
    IF p_order_amount >= 500 THEN
        SET p_discount_amount = 100.00;
        SET p_discount_type = '满500减100';
    ELSEIF p_order_amount >= 200 THEN
        SET p_discount_amount = 30.00;
        SET p_discount_type = '满200减30';
    ELSEIF p_order_amount >= 100 THEN
        SET p_discount_amount = 10.00;
        SET p_discount_type = '满100减10';
    END IF;

    — VIP会员额外9折（不与满减叠加，取最大值）
    IF p_order_amount >= 300 THEN
        IF p_order_amount * 0.9 > p_discount_amount THEN
            SET p_discount_amount = p_order_amount * 0.1;
            SET p_discount_type = 'VIP9折';
        END IF;
    END IF;

    SET p_final_amount = p_order_amount – p_discount_amount;
END //
DELIMITER ;

— 测试
SET @dis = 0, @final = 0, @type = '';
CALL sp_calculate_discount(350.00, @dis, @final, @type);
SELECT @type AS '优惠类型', @dis AS '优惠金额', @final AS '实付金额';

CASE 多分支选择

// sql
— 语法结构一：简单CASE
CASE case_value
    WHEN value1 THEN result1;
    WHEN value2 THEN result2;
    [ELSE result;]
END CASE;

— 语法结构二：搜索CASE
CASE
    WHEN condition1 THEN result1;
    WHEN condition2 THEN result2;
    [ELSE result;]
END CASE;

电商实战：根据用户等级计算积分倍率

// sql
DELIMITER //
CREATE PROCEDURE sp_calculate_points(
    IN p_order_amount DECIMAL(15,2),
    IN p_user_level INT,
    OUT p_base_points INT,
    OUT p_bonus_points INT,
    OUT p_total_points INT
)
BEGIN
    — 计算基础积分：每消费1元积1分
    SET p_base_points = FLOOR(p_order_amount);
    SET p_bonus_points = 0;

    — 根据用户等级计算额外积分倍率
    CASE p_user_level
        WHEN 5 THEN  — 至尊会员
            SET p_bonus_points = p_base_points * 3;  — 3倍积分
        WHEN 4 THEN  — 黄金会员
            SET p_bonus_points = p_base_points * 2;  — 2倍积分
        WHEN 3 THEN  — 白银会员
            SET p_bonus_points = FLOOR(p_base_points * 0.5);  — 1.5倍积分
        WHEN 2 THEN  — 普通会员
            SET p_bonus_points = FLOOR(p_base_points * 0.2);  — 1.2倍积分
        ELSE         — 非会员
            SET p_bonus_points = 0;
    END CASE;

    SET p_total_points = p_base_points + p_bonus_points;
END //
DELIMITER ;

— 测试
SET @base = 0, @bonus = 0, @total = 0;
CALL sp_calculate_points(200.00, 4, @base, @bonus, @total);
SELECT @base AS '基础积分', @bonus AS '额外积分', @total AS '总积分';

LOOP 循环

无条件的永久循环，需要配合LEAVE退出

// sql
— 语法结构
[label:] LOOP
    statements;
    IF condition THEN
        LEAVE [label];
    END IF;
END LOOP [label];

电商实战：批量计算商品利润

// sql
DELIMITER //
CREATE PROCEDURE sp_calculate_profits(
    IN p_start_product_id INT,
    IN p_end_product_id INT,
    OUT p_total_profit DECIMAL(15,2)
)
BEGIN
    DECLARE v_current_id INT;
    DECLARE v_cost_price DECIMAL(10,2);
    DECLARE v_sale_price DECIMAL(10,2);

    SET v_current_id = p_start_product_id;
    SET p_total_profit = 0.00;

    profit_loop: LOOP
        — 检查是否超出范围
        IF v_current_id > p_end_product_id THEN
            LEAVE profit_loop;
        END IF;

        — 获取商品成本价和售价
        SELECT cost_price, sale_price
        INTO v_cost_price, v_sale_price
        FROM products
        WHERE product_id = v_current_id;

        — 如果商品存在，累计利润
        IF v_cost_price IS NOT NULL THEN
            SET p_total_profit = p_total_profit + (v_sale_price – v_cost_price);
        END IF;

        — 移动到下一个商品
        SET v_current_id = v_current_id + 1;
    END LOOP profit_loop;
END //
DELIMITER ;

— 调用
SET @profit = 0;
CALL sp_calculate_profits(1, 100, @profit);
SELECT @profit AS '总利润';

WHILE 循环

当条件为真时反复执行循环体

// sql
— 语法结构
[label:] WHILE condition DO
    statements;
END WHILE [label];

电商实战：生成商品编号

// sql
DELIMITER //
CREATE PROCEDURE sp_generate_product_code(
    IN p_category_id INT,
    IN p_quantity INT,
    OUT p_codes TEXT
)
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_category_code VARCHAR(10);
    DECLARE v_max_seq INT;
    DECLARE v_new_seq INT;
    DECLARE v_code VARCHAR(50);

    SET p_codes = '';

    — 获取分类编码
    SELECT category_code INTO v_category_code
    FROM product_categories
    WHERE category_id = p_category_id;

    — 获取当前最大序号
    SELECT COALESCE(MAX(CAST(RIGHT(product_code, 6) AS UNSIGNED)), 0)
    INTO v_max_seq
    FROM products
    WHERE product_code LIKE CONCAT(v_category_code, '%');

    SET v_new_seq = v_max_seq;

    — 生成指定数量的编号
    WHILE v_counter <= p_quantity DO
        SET v_new_seq = v_new_seq + 1;
        SET v_code = CONCAT(v_category_code, '-', LPAD(v_new_seq, 6, '0'));

        IF v_counter > 1 THEN
            SET p_codes = CONCAT(p_codes, ',');
        END IF;
        SET p_codes = CONCAT(p_codes, v_code);

        SET v_counter = v_counter + 1;
    END WHILE;
END //
DELIMITER ;

— 调用：生成3个电子类产品商品编号
SET @codes = '';
CALL sp_generate_product_code(1, 3, @codes);
SELECT @codes AS '生成的商品编号';

REPEAT 循环

先执行循环体，再判断条件，至少执行一次

// sql
— 语法结构
[label:] REPEAT
    statements;
UNTIL condition
END REPEAT [label];

电商实战：计算购物车总价（带商品校验）

// sql
DELIMITER //
CREATE PROCEDURE sp_calculate_cart_total(
    IN p_user_id INT,
    OUT p_total_amount DECIMAL(15,2),
    OUT p_item_count INT,
    OUT p_invalid_items TEXT
)
BEGIN
    DECLARE v_cart_id INT;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_product_price DECIMAL(10,2);
    DECLARE v_stock INT;
    DECLARE v_item_valid BOOLEAN;
    DECLARE v_temp_invalid TEXT DEFAULT '';

    DECLARE cur_item CURSOR FOR
        SELECT product_id, quantity FROM shopping_cart WHERE user_id = p_user_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cart_id = NULL;

    SET p_total_amount = 0.00;
    SET p_item_count = 0;

    OPEN cur_item;

    cart_loop: REPEAT
        FETCH cur_item INTO v_product_id, v_quantity;

        IF v_cart_id IS NULL THEN
            LEAVE cart_loop;
        END IF;

        — 检查商品是否存在且库存充足
        SELECT price, stock INTO v_product_price, v_stock
        FROM products
        WHERE product_id = v_product_id;

        IF v_product_price IS NULL THEN
            — 商品已下架
            SET v_temp_invalid = CONCAT(v_temp_invalid, ',', v_product_id, ':下架');
            ITERATE cart_loop;
        END IF;

        IF v_quantity > v_stock THEN
            — 库存不足
            SET v_temp_invalid = CONCAT(v_temp_invalid, ',', v_product_id, ':库存不足');
            SET v_quantity = v_stock;  — 修正为最大库存
        END IF;

        — 累计金额
        SET p_total_amount = p_total_amount + (v_product_price * v_quantity);
        SET p_item_count = p_item_count + 1;

    UNTIL v_cart_id IS NULL
    END REPEAT cart_loop;

    CLOSE cur_item;

    SET p_invalid_items = TRIM(BOTH ',' FROM v_temp_invalid);
END //
DELIMITER ;

流程控制语句对比

�� 面试考点：存储过程和函数的区别

标准面试话术：

"存储过程和函数虽然都是数据库编程的重要组成部分，但有几个关键区别：

返回值方式不同：函数必须通过RETURN语句返回一个值，而存储过程通过OUT参数返回多个值。

调用方式不同：函数可以在SQL语句中直接调用，比如SELECT、WHERE、ORDER BY子句中都可以使用函数，而存储过程必须通过CALL语句独立调用。

参数类型不同：存储过程支持IN、OUT、INOUT三种参数类型，函数只支持输入参数。

修改数据能力不同：存储过程可以执行INSERT、UPDATE、DELETE等写操作，而函数通常不应该修改数据库状态，以保证其确定性。

典型使用场景：如果需要返回计算结果供SQL使用，选择函数；如果需要执行一系列操作或返回多个结果集，选择存储过程。"

1.6 章节小结

1.7 课堂练习

练习1：会员等级判定

编写一个存储过程，根据用户累计消费金额判定会员等级：

• 消费满10000元：钻石会员（等级5）

• 消费满5000元：黄金会员（等级4）

• 消费满2000元：白银会员（等级3）

• 消费满500元：普通会员（等级2）

• 其他：非会员（等级1）

练习2：订单超时检测

编写一个存储过程，扫描超过24小时未支付的订单，将其状态更新为"超时关闭"，并返回关闭的订单数量。

练习3：批量发货处理

编写一个存储过程，接收一批订单ID，将状态为"待发货"的订单批量发货，需要检查每个订单是否已支付，未支付则跳过。

第二部分：触发器——数据库的自动化引擎

2.1 概念：为什么需要触发器？

触发器是一种特殊的存储过程，当表上发生特定事件（INSERT、UPDATE、DELETE）时自动执行。

想象一下：你在电商平台下单购买iPhone，点击"立即购买"的瞬间，系统自动完成了哪些操作？库存扣减了吗？优惠券核销了吗？积分增加了吗？有没有生成审计日志？

如果这些操作都需要程序员手动编写代码调用，很容易遗漏。但触发器就像一个尽职的"自动管家"，一旦订单表发生INSERT操作，它会自动触发一系列预设的业务逻辑。

触发器的核心价值

2.2 基本语法

// sql
CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
BEGIN
    — 触发器语句
    — 可以使用NEW和OLD关键字访问数据
END;

参数说明

管理命令

// sql
— 查看所有触发器
SHOW TRIGGERS;

— 查看特定数据库的触发器
SHOW TRIGGERS FROM database_name;

— 查看特定表的触发器
SHOW TRIGGERS FROM database_name WHERE `table` = 'orders';

— 从系统表查看
SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

— 删除触发器
DROP TRIGGER IF EXISTS trigger_name;

2.3 触发时机与事件详解

触发时机对比

核心区别：BEFORE触发器中可以修改NEW的值（比如自动设置默认值、修正数据），AFTER触发器中数据已经写入，无法修改。

触发事件说明

2.4 电商实战：库存自动化管理

场景一：BEFORE触发器——订单插入前自动计算金额

// sql
— =============================================
— 电商订单表与BEFORE INSERT触发器
— 场景：订单创建时自动计算总金额并验证数据合法性
— =============================================

— 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(15,2),  — 将由触发器自动计算
    status VARCHAR(20) DEFAULT 'pending',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— BEFORE INSERT触发器：自动计算金额并验证
DELIMITER //

DROP TRIGGER IF EXISTS trg_orders_before_insert//
CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    — 自动生成订单号（年月日+6位序号）
    IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
        SET NEW.order_no = CONCAT(
            DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
            LPAD(FLOOR(RAND() * 999999), 6, '0')
        );
    END IF;

    — 自动计算订单总金额
    SET NEW.total_amount = NEW.quantity * NEW.unit_price – NEW.discount_amount;

    — 数据合法性验证：数量必须大于0
    IF NEW.quantity <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '订单数量必须大于0';
    END IF;

    — 数据合法性验证：单价不能为负
    IF NEW.unit_price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '商品单价不能为负数';
    END IF;

    — 数据合法性验证：优惠金额不能超过商品总价
    IF NEW.discount_amount > (NEW.quantity * NEW.unit_price) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '优惠金额不能超过商品总价';
    END IF;

    — 订单时间自动设置
    IF NEW.create_time IS NULL THEN
        SET NEW.create_time = NOW();
    END IF;
END//

DELIMITER ;

— 测试：插入订单（总金额将由触发器自动计算）
INSERT INTO orders (user_id, product_id, quantity, unit_price, discount_amount)
VALUES (1001, 501, 2, 299.00, 20.00);

— 查看结果
SELECT * FROM orders WHERE user_id = 1001;

场景二：AFTER触发器——订单创建后自动扣减库存

// sql
— =============================================
— 商品库存表与AFTER触发器
— 场景：订单创建后自动扣减库存，订单取消后自动恢复库存
— =============================================

— 商品库存表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE,
    stock INT NOT NULL DEFAULT 0,          — 可用库存
    reserved_stock INT NOT NULL DEFAULT 0, — 预留库存
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),             — 成本价
    status TINYINT DEFAULT 1,  — 1:上架 0:下架
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— 库存变动记录表
CREATE TABLE stock_log (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    order_id INT,
    change_type VARCHAR(20) NOT NULL,  — 'order_lock', 'order_cancel', 'restock', 'adjust'
    quantity_change INT NOT NULL,     — 正数增加，负数减少
    stock_before INT NOT NULL,
    stock_after INT NOT NULL,
    operator_id INT,
    reason VARCHAR(255),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— 插入测试数据
INSERT INTO products (product_name, sku, stock, price, cost_price) VALUES
('iPhone 15 Pro', 'SKU-IP15P-256', 100, 8999.00, 7500.00),
('MacBook Air M2', 'SKU-MBA-M2-256', 50, 9999.00, 8000.00),
('AirPods Pro 2', 'SKU-APP2', 200, 1899.00, 1400.00);

— AFTER INSERT触发器：订单创建自动扣减库存
DELIMITER //

DROP TRIGGER IF EXISTS trg_orders_after_insert//
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE v_current_stock INT;

    — 获取当前库存（加行锁防止并发问题）
    SELECT stock INTO v_current_stock
    FROM products
    WHERE product_id = NEW.product_id
    FOR UPDATE;

    — 检查库存是否充足
    IF v_current_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT('库存不足，当前库存:', v_current_stock, '，订单需求:', NEW.quantity);
    END IF;

    — 扣减库存
    UPDATE products
    SET stock = stock – NEW.quantity
    WHERE product_id = NEW.product_id;

    — 记录库存变动日志
    INSERT INTO stock_log (product_id, order_id, change_type, quantity_change, stock_before, stock_after, reason)
    VALUES (
        NEW.product_id,
        NEW.order_id,
        'order_lock',
        -NEW.quantity,
        v_current_stock,
        v_current_stock – NEW.quantity,
        CONCAT('订单锁定，订单号:', NEW.order_no)
    );
END//

— AFTER UPDATE触发器：订单状态变更自动处理库存
DROP TRIGGER IF EXISTS trg_orders_after_update//
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    DECLARE v_current_stock INT;

    — 订单取消：恢复库存
    IF OLD.status = 'pending' AND NEW.status = 'cancelled' THEN
        SELECT stock INTO v_current_stock
        FROM products
        WHERE product_id = OLD.product_id
        FOR UPDATE;

        UPDATE products
        SET stock = stock + OLD.quantity
        WHERE product_id = OLD.product_id;

        INSERT INTO stock_log (product_id, order_id, change_type, quantity_change, stock_before, stock_after, reason)
        VALUES (
            OLD.product_id,
            OLD.order_id,
            'order_cancel',
            OLD.quantity,
            v_current_stock,
            v_current_stock + OLD.quantity,
            CONCAT('订单取消释放库存，订单号:', OLD.order_no)
        );

    — 订单完成：增加销售数量统计（可选）
    ELSEIF OLD.status = 'shipped' AND NEW.status = 'completed' THEN
        INSERT INTO sales_summary (product_id, sales_count, update_date)
        VALUES (OLD.product_id, OLD.quantity, CURDATE())
        ON DUPLICATE KEY UPDATE sales_count = sales_count + OLD.quantity;
    END IF;
END//

— AFTER DELETE触发器：订单删除后恢复库存（物理删除时）
DROP TRIGGER IF EXISTS trg_orders_after_delete//
CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    — 如果订单状态为pending，说明库存已被扣减，需要恢复
    IF OLD.status = 'pending' THEN
        UPDATE products
        SET stock = stock + OLD.quantity
        WHERE product_id = OLD.product_id;

        INSERT INTO stock_log (product_id, order_id, change_type, quantity_change, stock_before, stock_after, reason)
        VALUES (
            OLD.product_id,
            OLD.order_id,
            'order_delete',
            OLD.quantity,
            (SELECT stock FROM products WHERE product_id = OLD.product_id) – OLD.quantity,
            (SELECT stock FROM products WHERE product_id = OLD.product_id),
            CONCAT('订单物理删除释放库存，订单号:', OLD.order_no)
        );
    END IF;
END//

DELIMITER ;

— 测试：创建订单
INSERT INTO orders (user_id, product_id, quantity, unit_price)
VALUES (1001, 1, 2, 8999.00);  — 购买2台iPhone

— 查看库存变化
SELECT * FROM products WHERE product_id = 1;
SELECT * FROM stock_log WHERE product_id = 1;

— 测试：取消订单
UPDATE orders SET status = 'cancelled' WHERE order_id = 1;

— 再次查看库存
SELECT * FROM products WHERE product_id = 1;
SELECT * FROM stock_log WHERE product_id = 1 ORDER BY create_time DESC;

2.5 NEW与OLD关键字

NEW 和 OLD 是触发器中最重要的两个关键字，用于访问正在操作的数据行。

核心概念

• NEW：代表新插入或更新后的数据行

• OLD：代表更新前的数据行或被删除的数据行

使用对照表

电商实战：用户余额变动审计

// sql
— =============================================
— 用户余额表与审计触发器
— 场景：记录用户余额的所有变动，包括充值、消费、退款
— =============================================

— 用户余额表
CREATE TABLE user_accounts (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,  — 账户余额
    frozen_amount DECIMAL(15,2) DEFAULT 0.00,       — 冻结金额
    total_recharge DECIMAL(15,2) DEFAULT 0.00,     — 累计充值
    total_consume DECIMAL(15,2) DEFAULT 0.00,      — 累计消费
    version INT DEFAULT 1,  — 乐观锁版本号
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

— 余额变动记录表
CREATE TABLE balance_change_log (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    change_type VARCHAR(20) NOT NULL,  — 'recharge', 'consume', 'refund', 'freeze', 'unfreeze'
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    related_order_id INT,              — 关联订单ID
    related_type VARCHAR(20),          — 'order_payment', 'order_refund', 'manual_recharge'
    remark VARCHAR(255),
    operator_id INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

— 金额变动表（用于记录充值、消费、退款流水）
CREATE TABLE account_transactions (
    transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,  — 'credit', 'debit'
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    reference_id VARCHAR(50),  — 订单号/交易流水号
    status VARCHAR(20) DEFAULT 'completed',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

— BEFORE UPDATE触发器：校验余额变动，记录日志
DELIMITER //

DROP TRIGGER IF EXISTS trg_accounts_before_update//
CREATE TRIGGER trg_accounts_before_update
BEFORE UPDATE ON user_accounts
FOR EACH ROW
BEGIN
    DECLARE v_change_amount DECIMAL(15,2);

    — 计算变动金额
    SET v_change_amount = NEW.balance – OLD.balance;

    — 校验：余额不能为负
    IF NEW.balance < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '账户余额不能为负数';
    END IF;

    — 校验：冻结金额不能超过余额
    IF NEW.frozen_amount > NEW.balance THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '冻结金额不能超过账户余额';
    END IF;

    — 校验：版本号（乐观锁）
    IF NEW.version != OLD.version + 1 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '数据已被其他操作修改，请刷新后重试';
    END IF;

    — 自动更新累计充值/消费
    IF v_change_amount > 0 THEN
        SET NEW.total_recharge = OLD.total_recharge + v_change_amount;
    ELSEIF v_change_amount < 0 THEN
        SET NEW.total_consume = OLD.total_consume + ABS(v_change_amount);
    END IF;

    — 禁止直接修改累计字段
    IF NEW.total_recharge != OLD.total_recharge + GREATEST(v_change_amount, 0) THEN
        SET NEW.total_recharge = OLD.total_recharge + GREATEST(v_change_amount, 0);
    END IF;

    IF NEW.total_consume != OLD.total_consume + GREATEST(-v_change_amount, 0) THEN
        SET NEW.total_consume = OLD.total_consume + GREATEST(-v_change_amount, 0);
    END IF;
END//

— AFTER INSERT触发器：记录充值流水
DROP TRIGGER IF EXISTS trg_transactions_after_insert//
CREATE TRIGGER trg_transactions_after_insert
AFTER INSERT ON account_transactions
FOR EACH ROW
BEGIN
    — 记录余额变动日志
    INSERT INTO balance_change_log (
        user_id, change_type, amount, balance_before, balance_after,
        related_order_id, related_type, remark
    ) VALUES (
        NEW.user_id,
        IF(NEW.transaction_type = 'credit', 'recharge', 'consume'),
        NEW.amount,
        NEW.balance_before,
        NEW.balance_after,
        NULLIF(NEW.reference_id, ''),
        NEW.transaction_type,
        NULL
    );

    — 如果是消费，更新用户表余额
    IF NEW.transaction_type = 'debit' THEN
        UPDATE user_accounts
        SET balance = balance – NEW.amount,
            total_consume = total_consume + NEW.amount,
            version = version + 1
        WHERE user_id = NEW.user_id;
    END IF;
END//

DELIMITER ;

— 测试数据
INSERT INTO user_accounts (user_id, user_name, balance) VALUES
(1001, '张三', 10000.00);

— 测试：充值
INSERT INTO account_transactions (user_id, transaction_type, amount, balance_before, balance_after, reference_id)
VALUES (1001, 'credit', 500.00, 10000.00, 10500.00, 'R20240115001');

SELECT * FROM user_accounts WHERE user_id = 1001;
SELECT * FROM balance_change_log WHERE user_id = 1001;

2.6 实战综合：商品价格变更记录

// sql
— =============================================
— 商品价格变更监控触发器
— 场景：监控商品价格变动，记录历史价格，防止价格欺诈
— =============================================

— 商品价格历史表
CREATE TABLE product_price_history (
    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    old_price DECIMAL(10,2) NOT NULL,
    new_price DECIMAL(10,2) NOT NULL,
    price_change DECIMAL(10,2) NOT NULL,      — 价格变动幅度
    price_change_rate DECIMAL(5,2) NOT NULL,  — 变动百分比
    change_reason VARCHAR(100),               — 变动原因
    operator_id INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

— 价格变动原因配置表
CREATE TABLE price_change_reasons (
    reason_id INT PRIMARY KEY AUTO_INCREMENT,
    reason_code VARCHAR(20) UNIQUE NOT NULL,
    reason_name VARCHAR(50) NOT NULL,
    is_active TINYINT DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO price_change_reasons (reason_code, reason_name) VALUES
('promotion', '促销活动'),
('cost_change', '成本调整'),
('market_adjust', '市场调整'),
('clearance', '清仓处理'),
('seasonal', '季节性调价');

— BEFORE UPDATE触发器：记录价格变更
DELIMITER //

DROP TRIGGER IF EXISTS trg_products_price_change//
CREATE TRIGGER trg_products_price_change
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    — 仅当价格发生变动时记录
    IF OLD.price != NEW.price THEN
        — 记录价格历史
        INSERT INTO product_price_history (
            product_id, old_price, new_price,
            price_change, price_change_rate, change_reason, operator_id
        ) VALUES (
            NEW.product_id,
            OLD.price,
            NEW.price,
            NEW.price – OLD.price,
            ROUND((NEW.price – OLD.price) / OLD.price * 100, 2),
            COALESCE(NEW.price_change_reason, '未指定'),
            NEW.last_operator_id
        );

        — 价格涨幅超过20%时记录警告日志（可在监控系统中告警）
        IF ABS((NEW.price – OLD.price) / OLD.price) > 0.2 THEN
            INSERT INTO price_alert_log (
                product_id, alert_type, old_price, new_price,
                change_rate, create_time
            ) VALUES (
                NEW.product_id,
                'price_change_warning',
                OLD.price,
                NEW.price,
                ROUND((NEW.price – OLD.price) / OLD.price * 100, 2),
                NOW()
            );
        END IF;
    END IF;
END//

DELIMITER ;

— 测试：修改商品价格
UPDATE products
SET price = 7999.00, price_change_reason = 'cost_change'
WHERE product_id = 1;

— 查看价格历史
SELECT * FROM product_price_history WHERE product_id = 1;

2.7 章节小结

�� 面试考点：NEW和OLD关键字的区别

标准面试话术：

"NEW和OLD是触发器中访问数据行的两个关键字。

NEW代表新数据：在INSERT触发器中，NEW表示新插入的数据行，可以读取也可以修改NEW的值；在UPDATE触发器中，NEW表示更新后的新数据，可以读取和修改。

OLD代表原数据：在DELETE触发器中，OLD表示被删除的数据行；在UPDATE触发器中，OLD表示更新前的原始数据。

需要注意的是，INSERT触发器中没有OLD，因为新插入的数据还没有"旧的"版本；DELETE触发器中没有NEW，因为数据已经被删除，不存在"新的"版本。

在BEFORE触发器中可以修改NEW的值来实现数据修正，在AFTER触发器中数据已经写入，只能读取。"

�� 面试考点：BEFORE和AFTER触发器的区别

标准面试话术：

"BEFORE和AFTER触发器的主要区别在于执行时机和数据可修改性。

BEFORE触发器在SQL语句执行之前触发，这时候数据还没有真正写入数据库，所以可以修改NEW的值。比如我们可以在BEFORE INSERT触发器中自动计算订单总金额，或者在BEFORE UPDATE触发器中验证数据合法性，如果不符合规则可以通过SIGNAL语句抛出异常阻止操作。

AFTER触发器在SQL语句执行之后触发，数据已经写入，这时候不能修改NEW的值，但可以做些'善后'工作，比如记录审计日志、同步关联数据、发送通知等。

实际开发中，BEFORE触发器常用于数据验证和自动填充默认值，AFTER触发器常用于日志记录和数据同步。"

2.8 课堂练习

练习1：购物车商品下架检测

编写触发器，当购物车中的商品被下架（status=0）时，自动从购物车中删除该记录，并记录删除日志。

练习2：订单完成后自动发放积分

编写触发器，当订单状态从"已发货"变为"已完成"时，自动根据消费金额计算并发放积分（每消费1元积1分）。

练习3：防止商品误删

编写触发器，防止删除已产生订单的商品。如果商品有未完成的订单，则抛出异常拒绝删除。

第三部分：函数——可复用的计算单元

3.1 概念：函数的本质

函数是一段可以返回单个值的可重用代码，可以在SQL语句中直接调用。

函数和存储过程最大的区别是：函数必须返回值，且可以在SELECT等SQL语句中直接使用。

打个比方：函数就像是Excel中的公式。你可以在单元格中写=SUM(A1:A10)，也可以写=IF(A1>60,"及格","不及格")，这些公式可以直接在表达式中使用。数据库函数也是同样的道理。

函数的核心特点

3.2 基本语法

// sql
CREATE FUNCTION function_name([param1 type1, param2 type2, …])
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
[COMMENT 'description']
BEGIN
    — 函数体
    RETURN value;
END;

参数说明

管理命令

// sql
— 查看所有函数
SHOW FUNCTION STATUS;

— 查看函数创建代码
SHOW CREATE FUNCTION function_name;

— 删除函数
DROP FUNCTION IF EXISTS function_name;

— 修改函数注释
ALTER FUNCTION function_name COMMENT 'new description';

3.3 与存储过程的区别

核心区别对比

选择指南

┌─────────────────────────────────────────────────────┐
│              存储过程 vs 函数选择指南                 │
├─────────────────────────────────────────────────────┤
│  �� 选择存储过程的场景                                │
│  • 需要执行多条SQL语句                                │
│  • 需要执行INSERT/UPDATE/DELETE                      │
│  • 需要返回多个结果集                                 │
│  • 用于复杂的业务事务处理                             │
├─────────────────────────────────────────────────────┤
│  �� 选择函数的场景                                   │
│  • 需要计算并返回单个值                               │
│  • 在SELECT中使用                                    │
│  • 进行数据转换和格式化                               │
│  • 复杂的计算逻辑                                      │
└─────────────────────────────────────────────────────┘

3.4 内置函数回顾

常用字符串函数

常用数值函数

常用日期函数

3.5 电商实战：订单金额计算函数

场景一：计算订单最终金额

// sql
— =============================================
— 电商订单金额计算函数
— 场景：综合考虑商品原价、折扣、优惠券、积分抵扣等计算最终金额
— =============================================

DELIMITER //

— 函数：计算订单最终金额
— 参数：商品原价、折扣比例(0-1)、优惠券金额、积分抵扣金额
DROP FUNCTION IF EXISTS fn_calculate_final_amount//
CREATE FUNCTION fn_calculate_final_amount(
    p_original_amount DECIMAL(15,2),    — 商品原价
    p_discount_rate DECIMAL(3,2),        — 折扣比例（0.00-1.00）
    p_coupon_amount DECIMAL(15,2),      — 优惠券金额
    p_points_deduction DECIMAL(15,2)     — 积分抵扣金额（100积分=1元）
)
RETURNS DECIMAL(15,2)
DETERMINISTIC
BEGIN
    DECLARE v_after_discount DECIMAL(15,2);
    DECLARE v_after_coupon DECIMAL(15,2);
    DECLARE v_final_amount DECIMAL(15,2);

    — 步骤1：计算折扣后金额
    SET v_after_discount = p_original_amount * p_discount_rate;

    — 步骤2：减去优惠券
    SET v_after_coupon = v_after_discount – p_coupon_amount;

    — 步骤3：减去积分抵扣
    SET v_final_amount = v_after_coupon – p_points_deduction;

    — 最终金额不能为负
    RETURN GREATEST(v_final_amount, 0.00);
END//

— 函数：计算订单应付金额（简化版）
DROP FUNCTION IF EXISTS fn_calc_order_amount//
CREATE FUNCTION fn_calc_order_amount(
    p_quantity INT,
    p_unit_price DECIMAL(10,2),
    p_discount_percent INT  — 折扣百分比，如80表示8折
)
RETURNS DECIMAL(15,2)
DETERMINISTIC
BEGIN
    DECLARE v_original DECIMAL(15,2);
    DECLARE v_discount DECIMAL(15,2);

    SET v_original = p_quantity * p_unit_price;
    SET v_discount = v_original * p_discount_percent / 100;

    RETURN ROUND(v_original – v_discount, 2);
END//

DELIMITER ;

— 使用示例
SELECT
    product_name,
    price AS 单价,
    fn_calc_order_amount(2, price, 80) AS 8折后价格
FROM products
WHERE product_id = 1;

场景二：订单金额计算（含满减活动）

// sql
— =============================================
— 电商满减活动计算函数
— 场景：根据订单金额自动匹配最优满减方案
— =============================================

— 满减活动配置表
CREATE TABLE promotion_rules (
    rule_id INT PRIMARY KEY AUTO_INCREMENT,
    rule_name VARCHAR(100),
    min_amount DECIMAL(15,2) NOT NULL,    — 满减门槛
    discount_amount DECIMAL(15,2) NOT NULL, — 优惠金额
    priority INT DEFAULT 0,              — 优先级，数字越大优先级越高
    start_time DATETIME,
    end_time DATETIME,
    is_active TINYINT DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO promotion_rules (rule_name, min_amount, discount_amount, priority) VALUES
('满100减10', 100.00, 10.00, 1),
('满200减30', 200.00, 30.00, 2),
('满500减80', 500.00, 80.00, 3),
('满1000减200', 1000.00, 200.00, 4);

DELIMITER //

— 函数：计算最优满减金额
DROP FUNCTION IF EXISTS fn_calculate_promotion//
CREATE FUNCTION fn_calculate_promotion(p_order_amount DECIMAL(15,2))
RETURNS DECIMAL(15,2)
DETERMINISTIC
BEGIN
    DECLARE v_discount DECIMAL(15,2) DEFAULT 0.00;

    — 查找满足条件的最高满减（按门槛降序，取第一个满足的）
    SELECT discount_amount INTO v_discount
    FROM promotion_rules
    WHERE is_active = 1
      AND min_amount <= p_order_amount
      AND (start_time IS NULL OR start_time <= NOW())
      AND (end_time IS NULL OR end_time >= NOW())
    ORDER BY min_amount DESC
    LIMIT 1;

    RETURN COALESCE(v_discount, 0.00);
END//

— 函数：计算VIP会员折扣
DROP FUNCTION IF EXISTS fn_get_vip_discount//
CREATE FUNCTION fn_get_vip_discount(p_user_level INT)
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
    RETURN CASE p_user_level
        WHEN 5 THEN 0.70  — 至尊会员7折
        WHEN 4 THEN 0.80  — 黄金会员8折
        WHEN 3 THEN 0.85  — 白银会员85折
        WHEN 2 THEN 0.90  — 普通会员9折
        ELSE 1.00          — 非会员不打折
    END;
END//

— 函数：计算应付金额（综合版）
DROP FUNCTION IF EXISTS fn_calc_final_payment//
CREATE FUNCTION fn_calc_final_payment(
    p_original_amount DECIMAL(15,2),
    p_user_level INT
)
RETURNS DECIMAL(15,2)
DETERMINISTIC
COMMENT '计算订单最终应付金额，考虑VIP折扣和满减活动'
BEGIN
    DECLARE v_vip_price DECIMAL(15,2);
    DECLARE v_promotion DECIMAL(15,2);
    DECLARE v_final DECIMAL(15,2);

    — 计算VIP折扣后的价格
    SET v_vip_price = p_original_amount * fn_get_vip_discount(p_user_level);

    — 计算满减优惠（基于折扣后价格）
    SET v_promotion = fn_calculate_promotion(v_vip_price);

    — 最终金额
    SET v_final = v_vip_price – v_promotion;

    RETURN ROUND(GREATEST(v_final, 0.00), 2);
END//

DELIMITER ;

— 使用示例
SELECT
    888.00 AS 原价,
    fn_get_vip_discount(4) AS 黄金会员折扣,
    888.00 * fn_get_vip_discount(4) AS VIP价格,
    fn_calculate_promotion(888.00 * fn_get_vip_discount(4)) AS 满减金额,
    fn_calc_final_payment(888.00, 4) AS 最终应付;

— 综合查询：显示所有VIP等级的预估价格
SELECT
    product_name,
    price AS 原价,
    CONCAT('至尊会员:¥', fn_calc_final_payment(price, 5)) AS 至尊价,
    CONCAT('黄金会员:¥', fn_calc_final_payment(price, 4)) AS 黄金价,
    CONCAT('白银会员:¥', fn_calc_final_payment(price, 3)) AS 白银价,
    CONCAT('普通会员:¥', fn_calc_final_payment(price, 2)) AS 普通价,
    CONCAT('非会员:¥', fn_calc_final_payment(price, 1)) AS 非会员价
FROM products;

3.6 电商实战：用户相关函数

场景一：手机号脱敏函数

// sql
— =============================================
— 用户数据处理函数
— 场景：敏感信息脱敏处理
— =============================================

DELIMITER //

— 函数：手机号脱敏（显示前3后4位）
DROP FUNCTION IF EXISTS fn_mask_phone//
CREATE FUNCTION fn_mask_phone(p_phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    — 校验手机号格式（11位数字）
    IF p_phone IS NULL OR LENGTH(p_phone) != 11 OR p_phone NOT REGEXP '^[0-9]{11}$' THEN
        RETURN NULL;
    END IF;

    — 脱敏：138****5678
    RETURN CONCAT(LEFT(p_phone, 3), '****', RIGHT(p_phone, 4));
END//

— 函数：身份证号脱敏（显示前6后4位）
DROP FUNCTION IF EXISTS fn_mask_id_card//
CREATE FUNCTION fn_mask_id_card(p_id_card VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE v_len INT;

    SET v_len = LENGTH(p_id_card);

    IF p_id_card IS NULL OR v_len < 10 THEN
        RETURN NULL;
    END IF;

    — 18位身份证：前6后4，中间8位脱敏
    IF v_len = 18 THEN
        RETURN CONCAT(LEFT(p_id_card, 6), '********', RIGHT(p_id_card, 4));
    END IF;

    — 15位身份证：前6后3，中间脱敏
    RETURN CONCAT(LEFT(p_id_card, 6), '****', RIGHT(p_id_card, 3));
END//

— 函数：邮箱脱敏（显示@前2后1）
DROP FUNCTION IF EXISTS fn_mask_email//
CREATE FUNCTION fn_mask_email(p_email VARCHAR(100))
RETURNS VARCHAR(100)
DETERIMINISTIC
BEGIN
    DECLARE v_local VARCHAR(50);
    DECLARE v_domain VARCHAR(50);
    DECLARE v_at_pos INT;

    IF p_email IS NULL OR p_email NOT LIKE '%@%' THEN
        RETURN NULL;
    END IF;

    SET v_at_pos = LOCATE('@', p_email);
    SET v_local = LEFT(p_email, v_at_pos – 1);
    SET v_domain = SUBSTRING(p_email, v_at_pos);

    — 本地部分小于3位直接返回
    IF LENGTH(v_local) <= 3 THEN
        RETURN CONCAT(LEFT(v_local, 1), '***', v_domain);
    END IF;

    — 显示前2后1
    RETURN CONCAT(LEFT(v_local, 2), '***', RIGHT(v_local, 1), v_domain);
END//

— 函数：用户等级判定
DROP FUNCTION IF EXISTS fn_get_user_level//
CREATE FUNCTION fn_get_user_level(p_total_amount DECIMAL(15,2))
RETURNS VARCHAR(20)
DETERMINISTIC
COMMENT '根据累计消费金额判定用户会员等级'
BEGIN
    RETURN CASE
        WHEN p_total_amount >= 100000 THEN '至尊会员'
        WHEN p_total_amount >= 50000 THEN '黄金会员'
        WHEN p_total_amount >= 20000 THEN '白银会员'
        WHEN p_total_amount >= 5000 THEN '普通会员'
        ELSE '非会员'
    END;
END//

— 函数：用户等级数值（用于排序）
DROP FUNCTION IF EXISTS fn_get_user_level_value//
CREATE FUNCTION fn_get_user_level_value(p_total_amount DECIMAL(15,2))
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN CASE
        WHEN p_total_amount >= 100000 THEN 5
        WHEN p_total_amount >= 50000 THEN 4
        WHEN p_total_amount >= 20000 THEN 3
        WHEN p_total_amount >= 5000 THEN 2
        ELSE 1
    END;
END//

DELIMITER ;

— 使用示例
SELECT
    user_name AS 姓名,
    phone AS 原手机号,
    fn_mask_phone(phone) AS 脱敏手机号,
    fn_mask_email(email) AS 脱敏邮箱,
    fn_get_user_level(total_consume) AS 会员等级,
    fn_get_user_level_value(total_consume) AS 等级值
FROM users
ORDER BY fn_get_user_level_value(total_consume) DESC;

场景二：订单状态判定函数

// sql
DELIMITER //

— 函数：获取订单状态文本
DROP FUNCTION IF EXISTS fn_get_order_status_text//
CREATE FUNCTION fn_get_order_status_text(p_status VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN CASE p_status
        WHEN 'pending' THEN '待支付'
        WHEN 'paid' THEN '已支付'
        WHEN 'shipped' THEN '已发货'
        WHEN 'completed' THEN '已完成'
        WHEN 'cancelled' THEN '已取消'
        WHEN 'refunding' THEN '退款中'
        WHEN 'refunded' THEN '已退款'
        ELSE '未知状态'
    END;
END//

— 函数：判断订单是否可以取消
DROP FUNCTION IF EXISTS fn_can_cancel_order//
CREATE FUNCTION fn_can_cancel_order(p_status VARCHAR(20), p_create_time DATETIME)
RETURNS BOOLEAN
DETERMINISTIC
COMMENT '判断订单是否可以在超过24小时后取消'
BEGIN
    — 只有待支付、已支付状态的订单可以取消
    IF p_status NOT IN ('pending', 'paid') THEN
        RETURN FALSE;
    END IF;

    — 待支付订单：创建超过24小时不能取消
    IF p_status = 'pending' AND TIMESTAMPDIFF(HOUR, p_create_time, NOW()) > 24 THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END//

— 函数：计算订单剩余支付时间（秒）
DROP FUNCTION IF EXISTS fn_get_order_remaining_time//
CREATE FUNCTION fn_get_order_remaining_time(
    p_create_time DATETIME,
    p_timeout_hours INT  — 超时时长（小时）
)
RETURNS INT
DETERMINISTIC
COMMENT '计算订单剩余支付时间，返回秒数，超时返回0'
BEGIN
    DECLARE v_deadline DATETIME;
    DECLARE v_remaining INT;

    SET v_deadline = DATE_ADD(p_create_time, INTERVAL p_timeout_hours HOUR);
    SET v_remaining = TIMESTAMPDIFF(SECOND, NOW(), v_deadline);

    RETURN GREATEST(v_remaining, 0);
END//

DELIMITER ;

— 使用示例
SELECT
    order_no AS 订单号,
    fn_get_order_status_text(status) AS 状态,
    fn_can_cancel_order(status, create_time) AS 可否取消,
    fn_get_order_remaining_time(create_time, 24) AS 剩余支付秒数
FROM orders
WHERE status = 'pending';

3.7 电商实战：分润计算函数

场景：多级分销分润计算

// sql
— =============================================
— 电商分销分润计算函数
— 场景：计算多级分销商的分润金额
— =============================================

— 分销商等级表
CREATE TABLE distributor_levels (
    level_id INT PRIMARY KEY,
    level_name VARCHAR(20),       — 一级/二级/三级
    commission_rate DECIMAL(5,4),  — 分润比例
    min_amount DECIMAL(15,2),     — 最低分润金额
    max_amount DECIMAL(15,2)      — 最高分润金额
) ENGINE=InnoDB;

INSERT INTO distributor_levels VALUES
(1, '一级分销', 0.1000, 1.00, 100.00),  — 10%佣金，1-100元
(2, '二级分销', 0.0500, 0.50, 50.00),   — 5%佣金，0.5-50元
(3, '三级分销', 0.0200, 0.10, 20.00);   — 2%佣金，0.1-20元

— 订单分润记录表
CREATE TABLE order_commission (
    commission_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    distributor_id INT NOT NULL,
    level_id INT NOT NULL,
    order_amount DECIMAL(15,2) NOT NULL,
    commission_rate DECIMAL(5,4) NOT NULL,
    commission_amount DECIMAL(15,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',  — pending/settled/cancelled
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER //

— 函数：根据订单金额计算单级分销佣金
DROP FUNCTION IF EXISTS fn_calculate_commission//
CREATE FUNCTION fn_calculate_commission(
    p_order_amount DECIMAL(15,2),
    p_level_id INT
)
RETURNS DECIMAL(15,2)
DETERMINISTIC
COMMENT '计算指定分销等级的佣金金额'
BEGIN
    DECLARE v_rate DECIMAL(5,4);
    DECLARE v_min DECIMAL(15,2);
    DECLARE v_max DECIMAL(15,2);
    DECLARE v_commission DECIMAL(15,2);

    — 获取分销等级配置
    SELECT commission_rate, min_amount, max_amount
    INTO v_rate, v_min, v_max
    FROM distributor_levels
    WHERE level_id = p_level_id;

    — 计算佣金
    SET v_commission = p_order_amount * v_rate;

    — 限制在区间内
    RETURN GREATEST(v_min, LEAST(v_max, v_commission));
END//

— 函数：计算订单总分销佣金
DROP FUNCTION IF EXISTS fn_calculate_total_commission//
CREATE FUNCTION fn_calculate_total_commission(p_order_amount DECIMAL(15,2))
RETURNS DECIMAL(15,2)
DETERMINISTIC
COMMENT '计算订单所有分销等级的总佣金'
BEGIN
    RETURN fn_calculate_commission(p_order_amount, 1) +
           fn_calculate_commission(p_order_amount, 2) +
           fn_calculate_commission(p_order_amount, 3);
END//

— 函数：验证分销佣金分配合理性
DROP FUNCTION IF EXISTS fn_validate_commission//
CREATE FUNCTION fn_validate_commission(
    p_order_amount DECIMAL(15,2),
    p_total_commission DECIMAL(15,2)
)
RETURNS BOOLEAN
DETERMINISTIC
COMMENT '验证佣金分配是否超过订单金额的20%（安全阈值）'
BEGIN
    — 佣金总额不能超过订单金额的20%
    RETURN p_total_commission <= (p_order_amount * 0.20);
END//

DELIMITER ;

— 使用示例
SELECT
    product_name,
    price AS 商品价格,
    fn_calculate_commission(price, 1) AS 一级佣金,
    fn_calculate_commission(price, 2) AS 二级佣金,
    fn_calculate_commission(price, 3) AS 三级佣金,
    fn_calculate_total_commission(price) AS 总佣金,
    fn_validate_commission(price, fn_calculate_total_commission(price)) AS 分配合理
FROM products;

— 批量计算待分润订单
INSERT INTO order_commission (order_id, distributor_id, level_id, order_amount, commission_rate, commission_amount)
SELECT
    o.order_id,
    d.distributor_id,
    d.level_id,
    o.total_amount,
    (SELECT commission_rate FROM distributor_levels WHERE level_id = d.level_id),
    fn_calculate_commission(o.total_amount, d.level_id)
FROM orders o
INNER JOIN order_distributors d ON o.user_id = d.user_id
WHERE o.status = 'completed'
  AND NOT EXISTS (
      SELECT 1 FROM order_commission c
      WHERE c.order_id = o.order_id AND c.distributor_id = d.distributor_id
  );

3.8 章节小结

�� 面试考点：存储过程和函数的区别

标准面试话术：

"存储过程和函数的主要区别有四点：

第一，返回值不同：函数必须通过RETURN语句返回一个值，而存储过程通过OUT参数返回多个值，甚至可以返回结果集。

第二，调用方式不同：函数可以在SQL语句中直接调用，比如SELECT myfunc(col) FROM table WHERE myfunc(col) > 100，而存储过程必须通过CALL语句单独调用。

第三，参数类型不同：存储过程支持IN、OUT、INOUT三种参数类型，函数只有输入参数类型，不需要指定。

第四，功能限制不同：函数通常不应该修改数据库表数据，以保证其确定性；存储过程可以执行INSERT、UPDATE、DELETE等写操作。

实际选型时，需要返回计算结果供SQL使用选函数，需要执行复杂事务或多步操作选存储过程。"

3.9 课堂练习

练习1：订单发货时间计算

编写函数，根据订单创建时间和配送距离，计算预计发货时间（假设每50公里需要1小时，最少2小时）。

练习2：用户购买力分级

编写函数，根据用户最近3个月的消费金额，将用户分为高、中、低三个购买力等级。

练习3：商品评分计算

编写函数，根据商品的好评、中评、差评数量，计算加权评分（好评5分，中评3分，差评1分）。

第四部分：知识体系总结

4.1 完整知识体系树

MySQL数据库编程
│
├── 存储过程 (Stored Procedure)
│   ├── 概念：预编译的SQL语句集合
│   ├── 基本语法
│   │   └── DELIMITER + CREATE PROCEDURE
│   ├── 参数类型
│   │   ├── IN (输入参数)
│   │   ├── OUT (输出参数)
│   │   └── INOUT (输入输出参数)
│   ├── 流程控制
│   │   ├── IF…THEN…ELSEIF…ELSE…END IF
│   │   ├── CASE…WHEN…THEN…END CASE
│   │   ├── [label:] LOOP…LEAVE…END LOOP
│   │   ├── [label:] WHILE…DO…END WHILE
│   │   └── [label:] REPEAT…UNTIL…END REPEAT
│   ├── 变量声明
│   │   ├── DECLARE (局部变量)
│   │   ├── SET (赋值)
│   │   └── INTO (查询结果赋值)
│   ├── 错误处理
│   │   └── SIGNAL SQLSTATE
│   └── 管理命令
│       ├── SHOW PROCEDURE STATUS
│       ├── SHOW CREATE PROCEDURE
│       └── DROP PROCEDURE
│
├── 触发器 (Trigger)
│   ├── 概念：事件驱动的自动执行单元
│   ├── 基本语法
│   │   └── CREATE TRIGGER + 时机 + 事件
│   ├── 触发时机
│   │   ├── BEFORE (执行前)
│   │   └── AFTER (执行后)
│   ├── 触发事件
│   │   ├── INSERT
│   │   ├── UPDATE
│   │   └── DELETE
│   ├── 数据访问
│   │   ├── NEW (新数据)
│   │   └── OLD (原数据)
│   └── 管理命令
│       ├── SHOW TRIGGERS
│       ├── information_schema查询
│       └── DROP TRIGGER
│
├── 函数 (Function)
│   ├── 概念：返回单个值的可调用单元
│   ├── 基本语法
│   │   └── CREATE FUNCTION + RETURNS
│   ├── 确定性声明
│   │   ├── DETERMINISTIC
│   │   └── NOT DETERMINISTIC
│   ├── 返回值
│   │   └── RETURN 语句
│   ├── 与存储过程区别
│   │   ├── 必须返回值
│   │   ├── SQL语句集成
│   │   └── 仅输入参数
│   └── 管理命令
│       ├── SHOW FUNCTION STATUS
│       ├── SHOW CREATE FUNCTION
│       └── DROP FUNCTION
│
└── 常用内置函数
    ├── 字符串函数
    │   ├── CONCAT / CONCAT_WS
    │   ├── LENGTH / CHAR_LENGTH
    │   ├── SUBSTRING / LEFT / RIGHT
    │   ├── TRIM / LTRIM / RTRIM
    │   ├── UPPER / LOWER
    │   ├── REPLACE
    │   └── LPAD / RPAD
    ├── 数值函数
    │   ├── ABS / ROUND
    │   ├── CEIL / FLOOR
    │   ├── MOD / POWER
    │   └── RAND
    ├── 日期函数
    │   ├── NOW / CURDATE
    │   ├── DATE / TIME
    │   ├── YEAR / MONTH / DAY
    │   ├── DATE_ADD / DATE_SUB
    │   ├── DATEDIFF
    │   └── DATE_FORMAT
    └── 聚合函数
        ├── COUNT / SUM
        ├── AVG / MAX / MIN
        └── GROUP_CONCAT

4.2 企业开发规范 Checklist

// sql
— =============================================
— MySQL数据库编程企业开发规范
— =============================================

— 1. 命名规范
—    – 存储过程：sp_{业务模块}_{操作}
—      示例：sp_order_create, sp_product_update_stock
—    – 触发器：trg_{表名}_{操作}_{时机}
—      示例：trg_orders_before_insert, trg_products_after_update
—    – 函数：fn_{功能描述}
—      示例：fn_calculate_discount, fn_mask_phone

— 2. 参数命名规范
—    – 输入参数：p_{参数名}
—    – 输出参数：p_{参数名}
—    – 变量：v_{变量名}

— 3. 异常处理规范
—    – 使用 SIGNAL SQLSTATE 抛出业务异常
—    – 常用状态码：45000（用户定义异常）

— 4. 事务规范
—    – 敏感操作开启事务
—    – 异常时回滚：ROLLBACK
—    – 成功后提交：COMMIT

— 5. 性能规范
—    – 避免在触发器中执行复杂查询
—    – 使用 FOR UPDATE 防止并发问题
—    – 合理使用索引

第五部分：课后作业

基础题

1. 编写一个订单金额计算存储过程：接收商品ID和数量，返回订单总金额（需要查询商品价格）。

2. 编写一个用户注册验证触发器：在用户表INSERT前，验证手机号格式是否正确（11位数字），邮箱格式是否合法。

3. 编写一个商品名称脱敏函数：将商品名称超过10个字符的截断显示为"商品名称前8位…"。

进阶题

1. 编写一个批量订单处理存储过程：接收一批订单ID，批量更新订单状态为"已发货"，需要记录每个订单的物流单号。

2. 编写库存预警触发器：当商品库存低于10件时，自动向库存预警表插入一条记录。

3. 编写一个订单相似商品推荐函数：根据当前浏览商品的分类，返回同分类销量最高的前5个商品ID。

挑战题

1. 设计一个完整的积分系统：包含积分计算函数（消费1元积1分）、积分兑换函数（100积分抵扣1元）、积分规则触发器（会员等级额外奖励积分）。

2. 设计一个商品促销策略系统：包含促销规则表、促销价格计算函数（支持多种促销类型：折扣、满减、买赠）、促销冲突检测逻辑。

3. 实现一个订单超时自动取消功能：使用触发器记录订单创建时间，创建定时任务检测超时订单（30分钟未支付自动取消）。

第六部分：面试精华回顾

�� 常见面试题汇总

Q1: 存储过程和函数的区别？

标准答案：见3.4节内容

Q2: 触发器的使用场景和注意事项？

标准答案：

"触发器主要用于数据审计、数据校验、级联操作等场景。比如：

• 数据校验：订单创建前验证数据合法性

• 数据同步：订单创建后自动扣减库存

• 审计日志：记录用户余额的每一次变动

注意事项：

• 不要在触发器中执行过于复杂的逻辑，会影响性能

• 避免递归触发，比如A表触发器更新A表

• 大量使用触发器会增加维护成本

• BEFORE触发器可以修改NEW的值，AFTER触发器不能"

Q3: NEW和OLD关键字的区别？

标准答案：见2.5节内容

Q4: BEFORE和AFTER触发器的区别？

标准答案：见2.5节内容

Q5: 存储过程的参数类型IN/OUT/INOUT的区别？

标准答案：见1.4节内容

Q6: 触发器有什么缺点？为什么不推荐大量使用？

标准答案：

"触发器的主要缺点有：

1. 性能影响：每次触发事件都会执行触发器，增加数据库负担

2. 维护困难：业务逻辑分散在应用层和数据库层，难以追踪

3. 调试不便：触发器的执行时机不易观察，错误难以排查

4. 可扩展性差：触发器与表结构耦合，修改表结构可能影响触发器

5. 隐式执行：程序员容易忽略触发器的存在，导致意外结果

因此，推荐在应用层实现业务逻辑，触发器仅用于简单的数据校验和日志记录。"

Q7: 存储过程中如何处理异常？

标准答案：

"MySQL中使用SIGNAL语句抛出异常：

// sql
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '错误描述信息';

可以使用DECLARE CONTINUE HANDLER声明异常处理句柄：

// sql
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        @err_code = RETURNED_SQLSTATE,
        @err_msg = MESSAGE_TEXT;
    ROLLBACK;
END;

总结

舰长，你已成功解锁MySQL数据库编程技能！

今天我们一起学习了MySQL中三大核心组件：

存储过程是业务逻辑的封装工厂，适合执行多步骤的操作流程；

触发器是数据自动化的引擎，让你的数据库拥有"自动响应"能力；

函数是计算单元的复用器，让复杂的计算逻辑在SQL中优雅复用。

记住这些核心原则：

• 存储过程用CALL调用，函数在SQL表达式中调用

• BEFORE触发器可以修改数据，AFTER触发器只能读取

• 触发器虽好，不要滥用，保持业务逻辑清晰

• 异常处理使用SIGNAL SQLSTATE

• 命名规范是团队协作的基础

实战中，多结合电商场景思考：订单处理、库存管理、用户分析、营销计算…