mysql存储过程数据量大很慢 mysql8.0存储过程

MYSQL8存储过程

• MYSQL存储过程常见用法

• 创建存储过程

• 创建带参数的存储过程
• 通过`call`语句调用存储过程

• 查看存储过程

• 查看存储过程的状态
• 查看存储过程的定义

• 删除存储过程
• 修改存储过程

• MYSQL8存储过程

• 什么是存储过程?

• 作用

• MYSQL变量

• 局部变量
• 用户变量
• 系统变量--全局变量
• 持久化修改系统变量

• 会话变量

• select ... into 为变量赋值

• 创建存储过程

• 调用存储过程
• 示例

• 查看存储过程创建sql
• 创建带参数的存储过程

• 调用带参数的存储过程

• 参数传递

• 存储过程传参--in

• 多个入参

• 存储过程传参--out
• 存储过程传参--inout

• 删除存储过程
• 流程控制

• 流程控制-判断
• 流程控制-case
• 流程控制-循环

• while
• while+leave
• while+iterate
• repeat
• loop

• 游标
• 异常处理-handle句柄
• 存储过程综合练习

MYSQL存储过程常见用法

创建存储过程

// 定义结束
 delimiter // CREATE PROCEDURE ShowStuScore1() 
BEGIN
SELECT *
  FROM tb_students_score; END //

创建带参数的存储过程

通过call语句调用存储过程

// 通过call语句调用存储过程
delimiter ; CALL GetStu('李军')

查看存储过程

查看存储过程的状态

// 查看存储过程的状态
SHOW PROCEDURE STATUS LIKE 'GetStu';

查看存储过程的定义

// 查看存储过程的定义
show CREATE PROCEDURE GetStu;

删除存储过程

// 删除存储过程 
drop PROCEDURE ShowStuScore1

修改存储过程

只能修改存储过程的状态，修改内容必须先删除再修改

// 修改存储过程
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;

MYSQL8存储过程

什么是存储过程?

多句sql同时执行，先编译好存储服务端，就是数据库里的自定义方法。

作用

执行效率高

重复使用

MYSQL变量

局部变量

MySQL的局部变量在begin 和and 之间，出了end不能使用。

MySQL中的DECLARE语句用于定义局部变量。

set 变量名=变量值

用户变量

-- 定义一个存储过程
delimiter $$ 
CREATE procedure 存储过程名()
begin
    -- 声明用户变量var_name1
    set @var_name1='ZS'
    SELECT @var_name1;
END $$
delimiter;

-- 调用存储过程,会打印用户变量"var_name1"的值
call 存储过程名()

-- 也可以直接使用用户变量
select @var_name1;

系统变量–全局变量

全局变量的修改是持久性的，即使在MySQL Server重启后，新的变量值仍然会被保留，并应用于所有连接。

-- 查看全局变量
show global variables;

-- 修改全局变量的值
set global sort_buffer_size=40000;

-- 查看指定全局变量
SELECT @@global.auto_increment_increment;

-- 修改全局变量
set global sort_buffer_size=40000;

-- 查看该变量的值
SELECT @@global.sort_buffer_size;

-- 修改全局变量
set @@global.sort_buffer_size=35000;

-- 查看该变量的值
SELECT @@global.sort_buffer_size;

持久化修改系统变量

SET PERSIST ：立即修改当前配置，并永久保存配置到DATADIR/mysqld-auto.cnfSET PERSIST_ONLY ：只更新配置，当前不生效。
SET GLOBAL ：只对当前服务器立即生效，重启MySQL服务器后失效。SET PERSIST 本质上结合了 SET GLOBAL 和 SET PERSIST_ONLY。

-- 将MySQL Server的最大连接数增加到200，并且会在服务器重新启动后保留该值。
-- PERSIST  持久配置
SET PERSIST max_ connections=200;  

-- 只会在服务器重启时保留该值，并不会立即生效。
SET PERSIST_ ONLY max_ connections=200; 

-- 清空持久化配置
reset persist;

重启MySQL服务器后失效。

-- 将MySQL Server的最大连接数增加到200，但是在服务器重新启动后不会保留该值。
SET GLOBAL max_ connections=200;

会话变量

-- 查看会话变量
show session variables;

-- 将当前会话的最大连接数设置为200，但不会影响其他会话或者在服务器重启后保留该值。
SET SESSION max_ connections=200; 

-- 修改会话变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=60000;

-- 查看修改后的会话变量的值
SELECT @@session.sort_buffer_size;

-- 查看某会话变量
SELECT @@session.auto_increment_increment;

select … into 为变量赋值

-- 定义一个存储过程
delimiter $$ 
CREATE procedure 存储过程名()
begin
    -- 声明局部变量
    declare 局部变量名 VARCHAR(20);
    -- 从emp表中查询ename结果符值给"局部变量名"
    SELECT ename into 局部变量名 FROM emp WHERE empno=1001;
        -- 打印局部变量的值
        SELECT 局部变量名;
END $$
delimiter;

-- 调用存储过程,会打印"局部变量名"的值
call 存储过程名()

创建存储过程

-- 临时改变结束结束符合
delimiter $$
CREATE PROCEDURE show_studnet()
BEGIN
	SELECT * FROM student;
END $$

-- 再将结束符改回;
delimiter ;

调用存储过程

-- 调用存储过程
CALL show_studnet();

示例

查看存储过程创建sql

-- 查看存储过程创建sql
SHOW CREATE PROCEDURE show_studnet;

创建带参数的存储过程

-- 创建带参数的存储过程
delimiter $$
CREATE PROCEDURE show_studnet_un(IN name VARCHAR(30))
BEGIN
	SELECT * FROM student WHERE sname=name;
END $$
delimiter ;

调用带参数的存储过程

-- 调用存储过程
CALL show_studnet_un('吊炸天')

参数传递

存储过程传参–in

-- 传入员工编号，查找员工信息
delimiter $$
CREATE procedure proc_find_emp(in empno int)
begin
    SELECT * FROM emp WHERE emp.empno=empno;
END $$
delimiter;
-- 调用存储过程
call proc_find_emp(1001);

多个入参

存储过程传参–out

-- 创建存储过程：传入员工编号，返回员工名字和工资
delimiter $$
create procedure proc_find_emp01(in in_empno int, out out_ename VARCHAR(50),out out_sal decimal(7,2))
begin
    -- 将查询结果中的ename赋值给out_ename,sal赋值给out_sal
    SELECT ename,sal into out_ename,out_sal
     FROM emp 
     WHERE emp.empno=in_empno;
END $$
delimiter;

-- 调用存储过程，传入员工编号1001，输出ename为@o_ename，sal为@o_sal
call proc_find_emp01(1001,@o_ename,@o_sal);

-- 打印出参out_ename，并用@o_ename接收
SELECT @o_ename;

-- 打印出参out_sal，并用@@o_sal接收
SELECT @o_sal

存储过程传参–inout

inout既是入参，也是出参

-- 传入员工名字，输出员工年薪
delimiter $$
-- 员工名字入参: inout_ename
-- 员工月薪入参: inout_sal
create procedure proc11(inout inout_ename varchar(50), inout inout_sal int)
begin
    -- 将查询出的员工名字和员工薪水 通过字符串拼接为 姓名_薪水
    SELECT concat_ws('_',deptno,ename) into inout_ename from emp WHERE emp.ename=inout_ename;
    -- 将传入的月薪*12，求出年薪
    set inout_sal=inout_sal*12;
END $$
delimiter;
-- 定义员工名字
set @inout_ename='关羽';
-- 定义员工薪水
set @inout_sal=3000;
-- 调用存储过程
call proc11(@inout_ename,@inout_sal);

-- 打印员工姓名
SELECT @inout_ename;

-- 打印员工年薪
SELECT @inout_sal

删除存储过程

-- 删除存储过程
drop PROCEDURE show_studnet_un

流程控制

流程控制-判断

调用上面定义的存储过程。

流程控制-case

流程控制-循环

while

while+leave

leave类似于break，跳出，结束当前所在的循环

while+iterate

iterate类似于continue，跳过本次循环。

repeat

loop

游标

mysql的游标类似于python中的迭代器。

循环一次，读取一次。

1. 声明游标
2. 打开游标

1. 通过游标获取每一行数据

3. 关闭游标

没有handle做异常处理，数据读完后，没有数据可读了，就会报错。

1329是错误代码：没有数据

异常处理-handle句柄

用游标循环读取数据时，因为是一行一行读取，读完最后一行还会继续读下一行，下一行没有数据，就会报错1329。

存储过程综合练习

需求：创建下个月的每日表

这段SQL代码是创建一个MySQL存储过程，用于自动生成每天的表格来记录用户搜索和购买行为。这个存储过程的实现思路是通过循环构建表名来达到目的。

具体操作流程如下：

1. 获取当前日期，计算出下个月的年份、月份和该月最后一天的日期。
2. 使用while循环生成每天的表名，并拼接出创建表的SQL语句。
3. 将SQL语句编译成准备语句并执行。
4. 释放准备语句。

-- 数据库准备
CREATE DATABASE IF NOT EXISTS mydb18_proc_demo;
-- 进入数据库
USE mydb18_proc_demo;

-- 如果存储过程存在则删除
DROP PROCEDURE IF EXISTS proc22_demo;

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE proc22_demo()
BEGIN
    -- 定义变量
    -- 定义下一个月的年份
    DECLARE next_year INT;
    -- 定义下个月的月份
    DECLARE next_month INT;
    -- 定义下个月的最后一天
    DECLARE next_month_day INT;
    -- 定义下个月的月份字符串
    DECLARE next_month_str VARCHAR(2);
    -- 定义下个月的最后一天字符串
    DECLARE next_month_day_str VARCHAR(2);
    -- 定义下个月的每天表名称字符串
    DECLARE table_name_str VARCHAR(10);
    -- 定义下个月的天数(while循环初始值) 初始值 1
    DECLARE t_index INT DEFAULT 1;
    
    -- 获取日期变量值
    SET next_year = YEAR(DATE_ADD(NOW(), INTERVAL 1 MONTH));
    SET next_month = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
    SET next_month_day = DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)));
    
    -- 生成下个月的月份字符串
    IF next_month < 10 THEN
        SET next_month_str = CONCAT('0', next_month);
    ELSE
        SET next_month_str = CAST(next_month AS CHAR);
    END IF;
    
    -- 循环构建表名并执行创建表语句
    -- 如果t_index 小于等于 下个月的最后一天，就执行循环
    WHILE t_index <= next_month_day DO
        -- 生成下个月的最后一天字符串
        -- 判断：如果t_index 值小于10，就补0并转换为字符串，
        IF t_index < 10 THEN
            SET next_month_day_str = CONCAT('0', t_index);
        ELSE
            -- 如果不小于10，就直接转成字符串
            SET next_month_day_str = CAST(t_index AS CHAR);
        END IF;
        
        -- 拼接表名日期格式2023_06_04
        SET table_name_str = CONCAT(next_year, '_', next_month_str, '_', next_month_day_str);
        -- 拼接 创建下个月的每日表的sql语句
        SET @create_table_sql = CONCAT(
            'CREATE TABLE user_', table_name_str, 
            '(`uid` INT, `uname` VARCHAR(50), `information` VARCHAR(50)) COLLATE=\'uft8_general_cli\' ENGINE=InnoDB;'
        );
        
        -- 将@create_table_sql变量里的sql语句编译为准备语句create_table_stmf
        PREPARE create_table_stmf FROM @create_table_sql;
        -- 执行 SQL 语句
        EXECUTE create_table_stmf;
        -- 释放准备语句create_table_stmf
        DEALLOCATE PREPARE create_table_stmf;
        
        -- 更心循环的值
        SET t_index = t_index + 1;
    END WHILE;

END$$

DELIMITER ;