Mysql 存储过程+变量详解

第一篇存储过程 三大变量体系一、存储过程是什么为什么用1. 定义存储过程Stored Procedure是一组预编译的SQL语句集合以固定名称存储在数据库中可通过一条调用指令触发执行。类比Java存储过程 ≈ 数据库里的“方法”你把多条SQL封装成一个“方法”调用一次执行全部。2. 为什么后端开发要用它场景不用存储过程用存储过程批量更新订单状态Java里for循环逐条update → 1000次网络IO一次CALL → 1次网络IO数据库内部循环处理复杂统计报表查3张表 → 3次查询 Java内存组装一次CALL返回最终汇总结果数据迁移清洗查出来 → Java改 → 写回去 → 循环N次存储过程内部完成ETL减少中间传输核心价值减少网络往返RTT复杂逻辑下沉数据库。3. 执行流程图解Java应用 MySQL服务器 │ │ │── CALL proc_order() ──────│ │ │ ┌─────────────┐ │ │ │ 预编译SQL块 │ │ │ │ SELECT... │ │ │ │ UPDATE... │ │ │ │ INSERT... │ │ │ └─────────────┘ │──── 最终结果集 ───────────│ │ │只发一条指令数据库内部执行整套逻辑结果返回应用。二、存储过程基础语法1. 分隔符 DELIMITER为什么需要改分隔符MySQL客户端默认以分号;作为一条语句的结束。但存储过程体内本身就包含多条以;结尾的SQL。如果不改客户端读到第一个;就认为CREATE PROCEDURE结束了导致语法错误。-- 正确写法模板照抄即可 DELIMITER $$ -- 临时把结束符改成 $$ CREATE PROCEDURE 过程名() BEGIN -- 这里面可以放心写多条SQL每条以;结尾 SELECT * FROM users; SELECT COUNT(*) FROM orders; END$$ -- 用$$表示整个存储过程定义结束 DELIMITER ; -- 立即改回分号不影响后续普通SQL写过程前 DELIMITER $$写完后 DELIMITER ;成对出现。2. 创建、查看、删除完整示例-- 创建统计学生总数 DELIMITER $$ CREATE PROCEDURE p_count_student() BEGIN SELECT COUNT(*) AS total FROM student; END$$ DELIMITER ; -- 调用执行 CALL p_count_student(); -- 查看所有存储过程当前库 SHOW PROCEDURE STATUS WHERE Db your_database_name; -- 查看创建语句看源码 SHOW CREATE PROCEDURE p_count_student; -- 删除 DROP PROCEDURE IF EXISTS p_count_student;三、三大变量体系1. 系统变量 —— MySQL的“全局配置会话配置”本质MySQL服务自己的“环境变量”控制数据库怎么运行。两个作用域新手必懂作用域关键字生命周期典型例子GLOBALGLOBALMySQL服务重启后恢复默认max_connections最大连接数SESSIONSESSION或省略当前客户端断开即失效autocommit自动提交开关查看与设置-- 查看所有会话变量 SHOW SESSION VARIABLES; -- 模糊搜索找和事务相关的 SHOW VARIABLES LIKE transaction%; -- 查看单个 SELECT SESSION.autocommit; -- 0关闭自动提交1开启 -- 修改当前会话关闭自动提交需要手动COMMIT SET SESSION autocommit 0; -- 或 SET SESSION.autocommit 0;修改重启失效需要修改my.cnf配置文件永久生效。2. 用户变量 var —— 会话级的“临时便签本”本质当前数据库连接内的临时存储关掉连接就没了。无需提前声明随用随写。最常用写法记住4种就够了-- 赋值方式1SET支持 和 : SET user_name 张三; SET user_age : 25; -- 赋值方式2查询结果存入变量最实用 SELECT COUNT(*) INTO total_users FROM user; -- 赋值方式3查询直接赋值查出来就一行一列 SELECT max_price : MAX(price) FROM products; -- 使用变量 SELECT user_name, user_age, total_users, max_price;实际场景存储过程中先查出某个值存到xxx后面多条SQL共用这个中间结果。变量跨存储过程、跨普通SQL都能用但换了数据库连接就全部清空。Navicat每个查询窗口是一个独立会话。3. 局部变量 —— 存储过程内部的“局部变量”本质只在BEGIN...END块内有效类似Java方法内定义的int i 0;。必须先声明后使用。语法模板DELIMITER $$ CREATE PROCEDURE p_example() BEGIN -- 局部变量声明必须写在BEGIN后的最前面不能混在SQL中间 DECLARE v_count INT DEFAULT 0; DECLARE v_name VARCHAR(50); DECLARE v_price DECIMAL(10,2) DEFAULT 0.00; -- 赋值 SELECT COUNT(*) INTO v_count FROM orders; SET v_name 测试订单; SELECT AVG(amount) INTO v_price FROM orders; -- 输出 SELECT v_count AS 订单总数, v_name AS 名称, v_price AS 均价; END$$ DELIMITER ;DECLARE位置踩坑示范-- ❌ 错误DECLARE放在了SQL语句后面 CREATE PROCEDURE p_wrong() BEGIN SELECT * FROM users; DECLARE v_count INT; -- 报错 END; -- ✅ 正确所有DECLARE必须在最前面 CREATE PROCEDURE p_right() BEGIN DECLARE v_count INT; SELECT * FROM users; END;四、三类变量终极对比表对比维度系统变量用户变量 var局部变量 DECLARE谁创建的MySQL自带用户手动赋值用户DECLARE声明是否需要声明否否是必须作用范围GLOBAL全库 / SESSION当前连接当前数据库会话当前BEGIN...END块生命周期服务启动到重启 / 连接到断开连接到断开存储过程调用开始到结束典型用途调整数据库运行参数临时存中间结果跨SQL共用存储过程内部计算逻辑五、新手最容易犯的5个错误序号错误行为后果正确做法1写存储过程不先DELIMITER $$分号截断创建失败先改结束符写完后改回2DECLARE写在BEGIN后的SQL中间语法错误所有DECLARE必须紧跟BEGIN3以为SET GLOBAL改了永久生效重启MySQL恢复默认永久改需修改my.cnf4在不同查询窗口用变量查出来是NULL同一会话内使用5局部变量和字段名同名逻辑混乱难调试局部变量加前缀v_如v_id