引言在前面的 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 语言的函数——封装一段逻辑可以传参数可以反复调用。对比项普通 SQL存储过程执行方式每次发送 SQL 文本需要解析预编译一次编译多次执行网络开销每条 SQL 都要网络传输只需传输调用命令业务逻辑放在应用层可以放在数据库层安全性直接暴露表结构可以只授权调用过程隐藏表结构二、创建与调用-- 修改分隔符因为存储过程内部有分号 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参数类型方向说明IN传入调用者传值给过程默认类型OUT传出过程将结果传回调用者INOUT双向既传入又传出四、变量与赋值DELIMITER $$ CREATE PROCEDURE demo_variables() BEGIN -- 声明局部变量 DECLARE stu_name VARCHAR(20); DECLARE stu_age INT DEFAULT 0; -- 赋值方式1SET SET stu_age 25; -- 赋值方式2SELECT ... 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 ;循环类型语法特点WHILEWHILE 条件 DO ... END WHILE先判断再执行REPEATREPEAT ... UNTIL 条件 END REPEAT至少执行一次LOOPloop_name: LOOP ... END LOOP无限循环需LEAVE退出七、游标逐行处理结果集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 时设置 done1 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触发事件NEWOLDINSERT新插入的行无全是 NULLUPDATE更新后的行更新前的行DELETE无全是 NULL被删除的行-- 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$$四、触发器的应用场景场景触发器类型说明审计日志AFTER INSERT/UPDATE/DELETE记录谁在什么时候改了什么数据校验BEFORE INSERT/UPDATE检查数据合法性不合法则拒绝自动计算BEFORE INSERT/UPDATE自动填充计算字段如总价单价×数量级联操作AFTER DELETE删主表时自动删从表数据同步AFTER INSERT/UPDATE/DELETE一张表变化时同步更新另一张表数据校验示例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 函数对比项存储过程触发器存储函数调用方式CALL proc()自动触发SELECT func()参数支持 IN/OUT/INOUT不支持只支持 IN返回值可通过 OUT 参数无必须有返回值事务控制可以 COMMIT/ROLLBACK不可以不可以使用场景封装业务逻辑审计/校验/同步计算并返回结果第五部分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; }总结一、核心要点主题关键语法使用场景存储过程CREATE PROCEDURE ... BEGIN ... END封装业务逻辑、批量操作参数IN/OUT/INOUT传参和返回值变量DECLARE/SET/SELECT INTO临时存储数据循环WHILE/REPEAT/LOOP批量处理游标CURSOR→OPEN→FETCH→CLOSE逐行处理结果集触发器CREATE TRIGGER ... BEFORE/AFTER审计日志、数据校验NEW/OLDNEW.列/OLD.列获取变更前后的数据二、一句话记忆存储过程把 SQL 封装成数据库端的函数支持参数和变量触发器绑定在表上自动响应增删改事件。存储过程用于封装逻辑减少网络开销触发器用于审计日志和数据校验保证数据一致性。