MySQL 存储过程与触发器完全指南

引言

在前面的 MySQL 文章中，我们学习了 SQL 的基础操作、事务、索引、视图和多表查询。这些已经能覆盖大部分 CRUD 需求。但在实际项目中，还有两类重要的数据库编程技术需要掌握：

• 存储过程：把一组 SQL 语句封装成可重复调用的"函数"，存储在数据库服务器端

• 触发器：在特定表上发生 INSERT/UPDATE/DELETE 时自动执行的代码

它们能让你把复杂的业务逻辑下沉到数据库层，减少网络开销，保证数据一致性。

第一部分：准备测试数据

CREATE DATABASE IF NOT EXISTS school; USE school; -- 学生表 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, age INT DEFAULT 18, total_score INT DEFAULT 0 ); -- 成绩日志表（触发器演示用） CREATE TABLE score_log ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT, old_score INT, new_score INT, change_time DATETIME, action VARCHAR(20) ); INSERT INTO student (name, age) VALUES ('张三', 20), ('李四', 22), ('王五', 21);

第二部分：存储过程

一、什么是存储过程

存储过程是一组预编译的 SQL 语句集合，存储在数据库服务器上，通过一个名称即可调用。

类比：就像 C 语言的函数——封装一段逻辑，可以传参数，可以反复调用。

二、创建与调用

-- 修改分隔符（因为存储过程内部有分号） DELIMITER $$ -- 最简单的存储过程 CREATE PROCEDURE show_all_students() BEGIN SELECT * FROM student; END$$ DELIMITER ; -- 调用 CALL show_all_students();

注意：因为存储过程体内部有;，所以需要先用DELIMITER $$把分隔符改成$$，定义完再改回来。

三、带参数的存储过程

DELIMITER $$ -- IN 参数：传入值 CREATE PROCEDURE get_student_by_id(IN stu_id INT) BEGIN SELECT * FROM student WHERE id = stu_id; END$$ -- OUT 参数：传出值 CREATE PROCEDURE get_student_count(OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM student; END$$ -- INOUT 参数：传入并传出 CREATE PROCEDURE double_number(INOUT num INT) BEGIN SET num = num * 2; END$$ DELIMITER ;

调用：

-- IN 参数 CALL get_student_by_id(1); -- OUT 参数（需要用变量接收） CALL get_student_count(@count); SELECT @count; -- 查看结果 -- INOUT 参数 SET @x = 10; CALL double_number(@x); SELECT @x; -- 20

四、变量与赋值

DELIMITER $$ CREATE PROCEDURE demo_variables() BEGIN -- 声明局部变量 DECLARE stu_name VARCHAR(20); DECLARE stu_age INT DEFAULT 0; -- 赋值方式1：SET SET stu_age = 25; -- 赋值方式2：SELECT ... INTO SELECT name, age INTO stu_name, stu_age FROM student WHERE id = 1; SELECT stu_name, stu_age; END$$ DELIMITER ;

五、条件判断

DELIMITER $$ CREATE PROCEDURE check_age(IN stu_id INT) BEGIN DECLARE stu_age INT; SELECT age INTO stu_age FROM student WHERE id = stu_id; IF stu_age < 18 THEN SELECT '未成年'; ELSEIF stu_age < 22 THEN SELECT '青年'; ELSE SELECT '成年'; END IF; END$$ DELIMITER ;

六、循环结构

DELIMITER $$ -- WHILE 循环：批量插入学生 CREATE PROCEDURE insert_students(IN num INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= num DO INSERT INTO student (name, age) VALUES (CONCAT('学生', i), 18 + (i % 5)); SET i = i + 1; END WHILE; SELECT CONCAT('插入完成，共', num, '条') AS result; END$$ -- REPEAT 循环（至少执行一次） CREATE PROCEDURE repeat_demo() BEGIN DECLARE i INT DEFAULT 1; REPEAT SELECT CONCAT('第', i, '次') AS msg; SET i = i + 1; UNTIL i > 3 END REPEAT; END$$ -- LOOP 循环（需要 LEAVE 退出） CREATE PROCEDURE loop_demo() BEGIN DECLARE i INT DEFAULT 1; my_loop: LOOP IF i > 3 THEN LEAVE my_loop; END IF; SELECT CONCAT('循环第', i, '次') AS msg; SET i = i + 1; END LOOP my_loop; END$$ DELIMITER ;

七、游标（逐行处理结果集）

DELIMITER $$ CREATE PROCEDURE list_student_names() BEGIN DECLARE done INT DEFAULT 0; DECLARE stu_name VARCHAR(20); -- 声明游标 DECLARE cur CURSOR FOR SELECT name FROM student; -- 声明结束处理（NOT FOUND 时设置 done=1） DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO stu_name; IF done THEN LEAVE read_loop; END IF; SELECT stu_name; END LOOP; CLOSE cur; END$$ DELIMITER ;

游标使用四步：DECLARE→OPEN→FETCH（循环）→CLOSE

八、查看与删除存储过程

-- 查看所有存储过程 SHOW PROCEDURE STATUS WHERE Db = 'school'; -- 查看创建语句 SHOW CREATE PROCEDURE get_student_by_id; -- 删除 DROP PROCEDURE IF EXISTS get_student_by_id;

第三部分：触发器

一、什么是触发器

触发器是一种特殊的存储过程，它不能手动调用，而是在特定表上发生 INSERT、UPDATE 或 DELETE 操作时自动触发执行。

二、创建触发器

DELIMITER $$ -- 记录 student 表的修改日志 CREATE TRIGGER log_score_update AFTER UPDATE ON student -- 在 student 更新之后触发 FOR EACH ROW -- 每行都触发一次 BEGIN -- NEW：更新后的行数据 -- OLD：更新前的行数据 INSERT INTO score_log (student_id, old_score, new_score, change_time, action) VALUES (NEW.id, OLD.total_score, NEW.total_score, NOW(), 'UPDATE'); END$$ DELIMITER ;

测试：

-- 更新学生成绩 UPDATE student SET total_score = 90 WHERE id = 1; UPDATE student SET total_score = 85 WHERE id = 2; -- 查看日志 SELECT * FROM score_log;

结果：

三、NEW 和 OLD

-- INSERT 触发器：新学生自动记录 CREATE TRIGGER log_student_insert AFTER INSERT ON student FOR EACH ROW BEGIN INSERT INTO score_log (student_id, old_score, new_score, change_time, action) VALUES (NEW.id, 0, NEW.total_score, NOW(), 'INSERT'); END$$ -- DELETE 触发器：删除学生时记录 CREATE TRIGGER log_student_delete BEFORE DELETE ON student -- BEFORE：在删除前可以获取 OLD 数据 FOR EACH ROW BEGIN INSERT INTO score_log (student_id, old_score, new_score, change_time, action) VALUES (OLD.id, OLD.total_score, 0, NOW(), 'DELETE'); END$$

四、触发器的应用场景

数据校验示例：

DELIMITER $$ CREATE TRIGGER check_age_before_insert BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.age < 0 OR NEW.age > 150 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在 0~150 之间'; END IF; END$$ DELIMITER ; -- 测试非法数据 INSERT INTO student (name, age) VALUES ('测试', -1); -- ERROR：年龄必须在 0~150 之间

五、查看与删除触发器

-- 查看所有触发器 SHOW TRIGGERS; -- 查看特定表的触发器 SHOW TRIGGERS LIKE 'student'; -- 删除 DROP TRIGGER IF EXISTS log_score_update;

第四部分：存储过程 vs 触发器 vs 函数

第五部分：C 语言调用存储过程

#include <stdio.h> #include <stdlib.h> #include <mysql/mysql.h> int main() { MYSQL *conn = mysql_init(NULL); if (conn == NULL) { fprintf(stderr, "mysql_init 失败\n"); return -1; } if (mysql_real_connect(conn, "127.0.0.1", "root", "123456", "school", 3306, NULL, 0) == NULL) { fprintf(stderr, "连接失败: %s\n", mysql_error(conn)); mysql_close(conn); return -1; } // 调用存储过程（和普通 SQL 一样，只是用 CALL） if (mysql_query(conn, "CALL get_student_by_id(1)") != 0) { fprintf(stderr, "调用失败: %s\n", mysql_error(conn)); } else { MYSQL_RES *result = mysql_store_result(conn); if (result) { MYSQL_ROW row; while ((row = mysql_fetch_row(result))) { printf("ID: %s, Name: %s, Age: %s\n", row[0], row[1], row[2]); } mysql_free_result(result); } } mysql_close(conn); return 0; }

总结

一、核心要点

二、一句话记忆

存储过程把 SQL 封装成数据库端的"函数"支持参数和变量，触发器绑定在表上自动响应增删改事件。存储过程用于封装逻辑减少网络开销，触发器用于审计日志和数据校验保证数据一致性。