[大数据]MySQL数据库学习日志（七）:存储过程 存储函数 触发器 存储引擎

MySQL数据库学习日志（七）:存储过程 存储函数 触发器 存储引擎

存储过程

（一）什么是存储过程

MySQL5.0版本开始支持存储过程。

存储过程是事先编译好并存储在数据库中的一段SQL语句的集合，可以重复直接调用。功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。

存储过程就是数据库SQL语言层面的代码封装与重用。

（二）存储过程的优缺点

1. 存储过程优点

(1)存储过程在创建的时候直接编译，而sql语句每次使用都要编译，因此提高了执行效率。

(2)一条sql语句，可能需要访问几张表，对数据库连接好几次，存储过程只会连接一次。

(3)存储过程是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

2. 存储过程的缺点

(1)可移植性差。

(2)对于简单的sql语句，毫无意义。

(3)对于只有一类用户的系统安全性毫无意义。

(4)团队开发，标准不定好的话，后期维护很麻烦。

(5)对于开发和调试都很不方便。

(6)复杂的业务逻辑，用存储过程还是很吃力的。

（三）创建存储过程

语法

delimiter 符号  -- 自定义结束符号(为了区分普通的SQL语句结束符)
create procedure 名称([in,out,inout] 参数名 数据类型 ...)
begin
sql语句
end
符号  -- 自定义结束符号
delimiter;

1. 创建简单存储过程

-- 创建查询dept表中数据条数的存储过程
Delimiter$$
create procedure dept_count_pro()
comment '查询dept表数据条数的存储过程'
begin
select count(*)from dept;
end;
$$
delimiter;

-- 调用存储过程：
call dept_count_pro();

说明：

comment ‘查询dept表数据条数的存储过程’ 表示对存储过程的说明！

在MySQL中，默认使用;来表示一条语句的结束。在创建存储过程时，为了避免冲突，使用delimiter命令来改变MySQL数据库中结束符，将其改变为 $$。注意，使用delimiter命令可以自定义结束符，在创建完存储过程后使用delimiter;命令将结束符更改回MySQL原有的;。

2. 带输入参数的存储过程

MySQL操作-变量的定义（补充内容）

• 局部变量

用户自定义，在begin/end块中有效

语法：声名变量 declare var_name type [default var_value];
eg: declare nickname varchar(32);

MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

select col_name [...] into var_name[,...] 
from table_name wehre condition 

其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

• 用户变量

用户自定义，当前会话（连接）有效。类比java的成员变量。

语法： 
@var_name
不需要提前声明，使用即声明

• 系统变量（了解–>用到再说）

系统变量又分为全局变量与会话变量

全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。

全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

• 系统变量-全局变量

由系统提供，在整个数据库有效。

语法：
@@global.var_name

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

• 系统变量-会话变量

由系统提供，当前会话（连接）有效

语法：
@@session.var_name

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; set @@session.sort_buffer_size = 50000 ;

带输入参数的存储过程示例

in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

-- 向dept表中添加数据的存储过程
delimiter $$

create procedure dept_insert_pro(in id int,in dept_name varchar(20))
begin
declare v1 int default 0;  /* 定义变量*/
declare v2 varchar(20); /* 定义变量*/
set v1=id; /* 把参数赋给变量*/
set v2 = dept_name; /* 把参数赋给变量*/
insert into dept(id,name) values(v1,v2);
end
$$
delimiter;

call dept_insert_pro(5,'后勤部');

-- 在声明和使用变量时，可以采用另外一种方式，可以省略declare
delimiter $$

create procedure dept_insert_pro2(in id int,in dept_name varchar(20))
begin
set @v1=id; /* 声明变量并赋值*/
set @v2 = dept_name; /* 声明变量并赋值*/
insert into dept(id,name) values(@v1,@v2);
end
$$
delimiter;
call dept_insert_pro2(6,'安保部');

3. 带输出参数的存储过程

out 表示从存储过程内部传值给调用者

-- 获取dept表中的id最大值
delimiter $$
create procedure dept_getmaxid_pro(out maxid int)
begin
select max(id) into maxid from dept;
end
$$
delimiter;

call dept_getmaxid_pro(@aaa) 
-- 调用该存储过程，注意：输出参数必须是一个带@符号的变量
select @aaa

4. 带输入和输出参数的存储过程

delimiter $$
create procedure sp(in p1 int , out p2 int)
begin
if p1 = 1 then
set @v = 100;
else
set @v = 200;
end if;
/* 语句体内可以执行多条sql，但必须以分号分隔 */
insert into dept(id) values(@v);
select max(id) into p2 from dept;
end
$$
delimiter;
call sp(1,@aaa) 
select @aaa

说明：存储过程中，可以使用if then … else … end if; 结构来做条件判断语句。（具体的存储过程中的流程控制语句if then , case ,while, repat, loop以及循环控制关键字 leave（类似于break），iterate（类似于continue）在这不详细介绍了！！！大家自己了解即可 ）

-- case
	-- 语法一（类比java的switch）：
	case case_value
    	when when_value then statement_list
    	[when when_value then statement_list] ...
    	[else statement_list]
	end case
	-- 语法二：
	case
    	when search_condition then statement_list
    	[when search_condition then statement_list] ...
    	[else statement_list]
	end case

-- while 语法
[标签:] while 循环条件 do
    循环体;
end while[ 标签];

-- repeat 语法
[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];

-- Loop 语法
[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

5. 既做输入又做输出参数的存储过程

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

delimiter $$
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if; 
select @pg;
end
$$
delimiter;
set @pp = 4;
call sp4(@pp)

（四）游标

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

语法

-- 声明语法
declare cursor_name（游标名） cursor for select_statement（结果集）
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

示例：

-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin
 -- 定义局部变量
 declare var_empno varchar(50);
 declare var_ename varchar(50);
 declare var_sal  decimal(7,2);
 
 -- 声明游标
 declare my_cursor cursor for
  select empno , ename, sal 
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据
  label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        select var_empno, var_ename, var_sal;
    end loop label;  -- 会出现异常读到0行 可用handler句柄处理
    
    -- 关闭游标
    close my_cursor;
end
 
 -- 调用存储过程
 call proc_cursor('销售部');

（五）异常处理handler句柄

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.

语法：

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action: { -- 异常处理完之后程序该怎么执行
    CONTINUE  -- 继续执行剩余代码
  | EXIT      -- 直接终止程序
  | UNDO	  -- 官网: 不支持
}
 
condition_value: { -- 触发条件
    mysql_error_code  -- 条件码 eg：1329
  | condition_name  -- 条件名
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  }

示例：

-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    
    -- 定义标记值
    declare flag int default 1; 
    
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0;
    
      -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位
        if flag = 1 then
            select var_empno, var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end $$;
 
delimiter ;
call proc21_cursor_handler('销售部');

存储函数

（一）存储函数概述

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

• 存储函数和存储过程的区别

1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；

4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

5.存储过程可以调用存储函数。但函数不能调用存储过程。

6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

（二）创建存储函数

语法：

在MySQL中，创建存储函数使用create function关键字，其基本形式如下：

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

示例：

set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
 
-- 创建存储函数-没有输输入参数 
delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 调用存储函数
select myfunc1_emp();

-- 创建存储过程-有输入参数
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
    declare out_name varchar(50);
    select ename into out_name from emp where  empno = in_empno;
    return out_name;
end $$
delimiter ;
 
select myfunc2_emp(1008);

触发器

（一）触发器概述

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。
• 在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行。
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
• 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发的特性：

• 什么条件会触发：I、D、U
• 什么时候触发：在增删改前或者后
• 触发频率：针对每一行执行
• 触发器定义在表上，附着在表上

（二）触发器的相关操作

1. 创建触发器

语法

（1）创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row 
执行语句;

（2）创建有多个执行语句的触发器

create trigger 触发器名 before|after  触发事件 
on 表名 for each row
begin
     执行语句列表
end;

示例：

-- 用户表
create table user(
    uid int primary key ,
    username varchar(50) not null,
    password varchar(50) not null
);
-- 用户信息操作日志表
create table user_logs(
    id int primary key auto_increment,
    time timestamp,
    log_text varchar(255)
);

-- 需求1:当user表添加一行数据，则会自动再user_log添加日志记录
-- 如果触发器存在，则先删除
drop trigger if  exists trigger_test1;
 
-- 创建触发器trigger_test1
create trigger trigger_test1
after insert on user -- 触发时机：当添加user表数据时触发
for each row
insert into user_logs values(NULL,now(), '有新用户注册');   -- now() 获取当前系统时间
 
-- 添加数据，触发器自动执行并添加日志代码
insert into user values(1,'张三','123456');

-- 需求2:当user表修改一行数据，则会自动再user_log添加日志记录
-- 如果触发器trigger_test2存在，则先删除
drop trigger if exists trigger_test2;

-- 创建触发器
trigger_test2delimiter $$
create trigger trigger_test2after update on user  -- 触发时机：当修改user表数据时触发
for each row -- 每一行
begininsert into user_logs values(NULL,now(), '用户修改发生了修改');
end $$
delimiter ; 

-- 添加数据，触发器自动执行并添加日志代码
update user set password = '888888' where uid = 1;

2. NEW和OLD

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

使用方法：

NEW.columnName （columnName为相应数据表某一列名）

示例：

create trigger trigger_test3 after insert
on user for each row
insert into user_logs values(NULL,now(),concat('有新用户添加，信息为:',NEW.uid,NEW.username,NEW.password));
 
-- 测试
insert into user values(4,'赵六','123456');

3. 其它操作

• 查看触发器

show triggers;

• 操作-删除触发器

-- drop trigger [if exists] trigger_name 
drop trigger if exists trigger_test1;

（三）触发器注意事项

1.MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发
2.尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。且不易后期维护。
3.触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。（可以用后端代码替换）

存储引擎（简单了解）

（一）存储引擎概述

• 存储引擎是MySQL中特有的术语，主要是指在MySQL中，用户可以根据应用的需求，选择如何存储、更新、查询和索引数据，是否使用事务等。
• 在MySQL中，支持多种不同类型的存储引擎，从而满足用户使用各种方式存储数据的需求。

（二）存储引擎的类型

MySQL提供多种存储引擎，但不同版本的MySQL所提供的存储引擎类型稍有不同。可以使用SQL语句查看当前版本数据库中都提供了哪些存储引擎。

语句:

show engines;

当前8.0.23包含9种存储引擎。

（三）常见存储引擎简介

MySQL8.0.23中包括9种存储引擎。其中InnoDB、MyISAM、MRG_MYISAM(又称为MERGE)、MEMORY、BLACKHOLE比较常见。

查看当前系统使用的存储引擎

show variables like '%storage_engine%';

（四）InnoDB存储引擎介绍

在MySQL被Oracle收购之后，其开发重心转移到InnoDB存储引擎上，并且在MySQL5.5之后默认存储引擎改为了InnoDB。

1. 存储结构

在InnoDB存储引擎中，所有数据都被逻辑地存储在表空间（tablespace）中，表空间又由段（segment）、区（extent）、页（page）组成。其中，一个表空间包括多个段，一个段由多个区组成，一个区内又可以分为多个页。

（1）表空间

表空间可以分为系统表空间、独立表空间和临时表空间。

当数据库初始化时，一些初始信息都是被存在一个叫做ibdata1的表空间文件中。对于系统表空间，其大小随着数据量的增加会自动扩展，一般默认自动扩展大小是64M。

独立表空间意味着数据库中的每个表都有自己的表空间文件，使用独立表空间只需将innodb_file_per_table参数设置为1即可。

临时表空间是从5.7版本开始从系统表空间中抽离出来而形成的，将临时表中的相关信息保存在其中。

（2）段

在InnoDB存储引擎中，常见的段主要有数据段、索引段、回滚段。

（3）区

区由连续的页构成，在物理存储上由系统分配一段连续的空间进行存储。每个区由64个连续的页构成，大小都是1MB。

（4）页

页是InnoDB存储引擎中最小的存储分配单位，默认大小为16KB。

2. 内存结构

在MySQL中，当使用InnoDB存储引擎时，其内存结构与Oracle数据库的内存结构类似，分为系统全局区（SGA）和程序缓冲区（PGA）两部分。

用户可以查看InnoDB存储引擎的内存结构参数，SQL语句如下

show variables like '%buffer%';

系统全局区常用参数

程序缓存区常用参数

3. 特性

InnoDB存储引擎有三个主要特性分别是插入缓冲（change buffer）、两次写（double write）、自适应哈希索引（adaptive hash index）。

（1）插入缓存

对于插入缓存，首先根据插入的普通索引页在缓冲池里查找，如果找到则直接将待插入页插入表中，如果没找到则先将待插入页放入缓冲池中，与之前的缓冲池进行合并操作，当有多个插入进行时，可以将其合并到一个操作中，从而提高插入的性能。

（2）两次写

当InnoDB缓冲池刷出新的“脏页”要写入磁盘文件时，由于两次写机制的存在，会首先将“脏页”写入两次写缓存中，随后两次写缓存以每次1MB的大小将数据写入两次写所在的磁盘表空间，并同时写入数据文件中。正是由于两次写的存在，保证了MySQL数据库中数据的安全性。

（3）自适应哈希索引

当使用InnoDB存储引擎时，会对索引的搜索进行监控，如果某个查询能使用哈希索引来优化查询索引速度，那么数据库系统将会自动完成这项操作，而不需要人为操作来完成。用户可以查看是否打开了该功能，SQL语句如下：

show variables like '%innodb_adaptive_hash_index%';

（五）修改MySQL的存储引擎

修改my.ini文件

找到my.ini文件

通过文本编辑工具打开，可以进行修改

还可以建表时指定存储引擎，不做详细介绍。

end