存储过程与函数

存储过程与函数

存储过程（Stored Procedure）和存储函数（Stored Function）是预编译存储在 MySQL Server 端的 SQL 程序，减少网络往返、实现复杂业务逻辑封装。MySQL 9.0 还新增了 JavaScript 存储程序（基于 GraalVM）。

基础语法

创建存储过程

DELIMITER $$

CREATE PROCEDURE sp_get_user_orders(
    IN  p_user_id  BIGINT,
    IN  p_status   TINYINT,
    OUT p_count    INT
)
BEGIN
    -- 查询用户订单
    SELECT id, order_no, total_amount, created_at
    FROM `order`
    WHERE user_id = p_user_id
      AND (p_status IS NULL OR status = p_status)
    ORDER BY created_at DESC
    LIMIT 100;

    -- 设置输出参数
    SELECT COUNT(*) INTO p_count
    FROM `order`
    WHERE user_id = p_user_id
      AND (p_status IS NULL OR status = p_status);
END$$

DELIMITER ;

-- 调用
CALL sp_get_user_orders(12345, 20, @cnt);
SELECT @cnt;

-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_get_user_orders\G

-- 删除
DROP PROCEDURE IF EXISTS sp_get_user_orders;

变量与参数

DELIMITER $$
CREATE PROCEDURE sp_variable_demo()
BEGIN
    -- 局部变量声明（必须在 BEGIN 之后最前面）
    DECLARE v_count   INT DEFAULT 0;
    DECLARE v_name    VARCHAR(100);
    DECLARE v_done    BOOLEAN DEFAULT FALSE;

    -- 赋值方式 1：SET
    SET v_count = 100;

    -- 赋值方式 2：SELECT INTO
    SELECT COUNT(*) INTO v_count FROM users WHERE status = 1;

    -- 用户变量（@前缀，会话级，无需声明）
    SET @session_var = 'hello';
    SELECT @session_var;

    -- 系统变量
    SELECT @@global.max_connections;    -- 全局系统变量
    SELECT @@session.time_zone;         -- 会话系统变量
END$$
DELIMITER ;

参数模式

流程控制

DELIMITER $$
CREATE PROCEDURE sp_control_flow_demo(IN p_score INT)
BEGIN
    DECLARE v_grade VARCHAR(10);

    -- IF-ELSEIF-ELSE
    IF p_score >= 90 THEN
        SET v_grade = 'A';
    ELSEIF p_score >= 80 THEN
        SET v_grade = 'B';
    ELSEIF p_score >= 60 THEN
        SET v_grade = 'C';
    ELSE
        SET v_grade = 'F';
    END IF;

    -- CASE
    SET v_grade = CASE
        WHEN p_score >= 90 THEN 'A'
        WHEN p_score >= 80 THEN 'B'
        WHEN p_score >= 60 THEN 'C'
        ELSE 'F'
    END;

    -- WHILE 循环
    DECLARE v_i INT DEFAULT 1;
    WHILE v_i  100 THEN
            LEAVE my_loop;
        END IF;
        IF v_i MOD 2 = 0 THEN
            ITERATE my_loop;  -- 类似 continue
        END IF;
        -- 处理奇数...
    END LOOP my_loop;

    -- REPEAT-UNTIL（类似 do-while）
    REPEAT
        SET v_i = v_i + 1;
    UNTIL v_i >= 10 END REPEAT;

    SELECT v_grade;
END$$
DELIMITER ;

游标（Cursor）

游标逐行处理查询结果集，适用于无法用集合操作完成的场景（如逐行处理后插入另一张表）。

性能警告：游标在 MySQL 中性能较差，逐行处理 100 万行可能需要几分钟。优先考虑用集合 SQL（INSERT...SELECT、UPDATE...JOIN）替代游标。游标只用于确实需要逐行处理的场景。

DELIMITER $$
CREATE PROCEDURE sp_batch_process()
BEGIN
    DECLARE v_done   BOOLEAN DEFAULT FALSE;
    DECLARE v_id     BIGINT;
    DECLARE v_amount DECIMAL(10,2);

    -- 1. 声明游标
    DECLARE cur_orders CURSOR FOR
        SELECT id, total_amount FROM `order` WHERE status = 10;

    -- 2. 声明 NOT FOUND 处理器（游标到尾部时触发）
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 3. 打开游标
    OPEN cur_orders;

    -- 4. 循环读取
    read_loop: LOOP
        FETCH cur_orders INTO v_id, v_amount;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- 处理每行
        IF v_amount > 10000 THEN
            UPDATE `order` SET remark = '大额订单' WHERE id = v_id;
        END IF;
    END LOOP;

    -- 5. 关闭游标
    CLOSE cur_orders;
END$$
DELIMITER ;

异常处理

DELIMITER $$
CREATE PROCEDURE sp_safe_transfer(
    IN p_from BIGINT,
    IN p_to   BIGINT,
    IN p_amt  DECIMAL(10,2)
)
BEGIN
    -- 声明错误标志
    DECLARE v_error BOOLEAN DEFAULT FALSE;

    -- CONTINUE HANDLER: 遇错继续执行，设置标志
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = TRUE;

    -- EXIT HANDLER: 遇错立即退出（适合回滚场景）
    -- DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -- BEGIN
    --     ROLLBACK;
    --     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed';
    -- END;

    START TRANSACTION;

    UPDATE account SET balance = balance - p_amt WHERE id = p_from AND balance >= p_amt;
    IF ROW_COUNT() = 0 THEN
        SET v_error = TRUE;
    END IF;

    IF NOT v_error THEN
        UPDATE account SET balance = balance + p_amt WHERE id = p_to;
    END IF;

    IF v_error THEN
        ROLLBACK;
        -- 抛出自定义错误
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer failed: insufficient balance',
                MYSQL_ERRNO  = 1001;
    ELSE
        COMMIT;
    END IF;
END$$
DELIMITER ;

-- 常用 SQLSTATE 码
-- '45000': 用户自定义错误（通用）
-- '23000': 约束违反（重复键、外键）
-- '22003': 数值超出范围
-- '01000': 警告（一般不触发 HANDLER）

存储函数

存储函数（FUNCTION）只返回单值，可在 SQL 表达式中直接调用，与存储过程的核心区别。

DELIMITER $$

-- 示例：根据生日计算年龄
CREATE FUNCTION fn_age(p_birth_date DATE)
RETURNS TINYINT UNSIGNED
DETERMINISTIC   -- 相同输入总是返回相同结果（重要！影响 binlog 记录方式）
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birth_date, CURDATE());
END$$

-- 示例：手机号脱敏
CREATE FUNCTION fn_mask_mobile(p_mobile VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC NO SQL
BEGIN
    IF LENGTH(p_mobile) != 11 THEN RETURN p_mobile; END IF;
    RETURN CONCAT(LEFT(p_mobile, 3), '****', RIGHT(p_mobile, 4));
END$$

DELIMITER ;

-- 在 SQL 中直接使用
SELECT name, fn_mask_mobile(mobile) AS masked_mobile,
       fn_age(birth_date) AS age
FROM users
WHERE fn_age(birth_date) BETWEEN 18 AND 30;

DETERMINISTIC 声明很重要：非确定性函数（如含 NOW()、RAND()）在 binlog_format=STATEMENT 下会导致主从不一致。如果函数不含 SQL 或只读数据，加上 NO SQL 或 READS SQL DATA 声明，MySQL 可以更好地优化。

触发器

DELIMITER $$

-- BEFORE INSERT: 自动填充 created_by
CREATE TRIGGER trg_order_before_insert
BEFORE INSERT ON `order`
FOR EACH ROW
BEGIN
    IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'order_no is required';
    END IF;
    SET NEW.expire_time = DATE_ADD(NOW(), INTERVAL 30 MINUTE);
END$$

-- AFTER UPDATE: 记录状态变更历史
CREATE TRIGGER trg_order_after_update
AFTER UPDATE ON `order`
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END$$

DELIMITER ;

-- 查看表的所有触发器
SHOW TRIGGERS LIKE 'order'\G

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

事件调度器（定时任务）

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';

DELIMITER $$

-- 每天凌晨 2 点清理 30 天前的过期订单
CREATE EVENT evt_cleanup_expired_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
ON COMPLETION PRESERVE   -- 执行后保留事件定义
DO
BEGIN
    DELETE FROM `order`
    WHERE status = -10
      AND cancel_time < DATE_SUB(NOW(), INTERVAL 30 DAY)
    LIMIT 1000;   -- 分批删除，避免长事务
END$$

-- 每 5 分钟执行一次（实时任务）
CREATE EVENT evt_process_pending_payments
ON SCHEDULE EVERY 5 MINUTE
DO
    CALL sp_process_pending_payments();$$

DELIMITER ;

-- 查看所有事件
SELECT * FROM information_schema.EVENTS\G

-- 临时禁用/启用事件
ALTER EVENT evt_cleanup_expired_orders DISABLE;
ALTER EVENT evt_cleanup_expired_orders ENABLE;

最佳实践

MySQL 9.0 JavaScript 存储程序

-- MySQL 9.0+ 支持 JavaScript（基于 GraalVM MLE）
CREATE FUNCTION js_fibonacci(n INT)
RETURNS INT
LANGUAGE JAVASCRIPT AS $$
    if (n <= 1) return n;
    let a = 0, b = 1;
    for (let i = 2; i <= n; i++) {
        [a, b] = [b, a + b];
    }
    return b;
$$;

SELECT js_fibonacci(10);  -- 返回 55

-- JS 存储过程可以访问数据库
CREATE PROCEDURE js_audit_proc()
LANGUAGE JAVASCRIPT AS $$
    const result = session.runSql("SELECT COUNT(*) as cnt FROM users");
    const row = result.fetchOne();
    session.runSql("INSERT INTO audit_log (msg) VALUES (?)",
                   [`User count: ${row.cnt}`]);
$$;

何时使用存储过程：适合减少网络往返的复杂多步操作（如上文的转账）、数据库层面的数据完整性逻辑、需要在多个应用共享的业务规则。不适合需要频繁迭代的业务逻辑（部署繁琐）、需要调试的复杂算法（建议放应用层）。