MySQL学习笔记-进阶部分

1、MySQL编程

MySQL是一个关系型数据库管理系统，它将数据保存在不同表中以提供数据的灵活性。MySQL所使用的 SQL 是用于访问数据库的最常用标准化语言。MySQL基础介绍 部分的创建数据库、创建数据表、更新数据、查询数据等语句都是MySQL编程的一部分。

1.1、MySQL编程组成

在 MySQL 编程中，可以将其分为以下4类。

1．数据定义语言

数据定义语言（ Data Definition Language , DDL ）用于执行数据库的任务，对数据库及数据库中的各种对象进行创建（ CREATE )、删除（ DROP ）和修改（ ALTER ）等操作。数据库对象可以包括表、默认约束、规则、视图、触发器以及存储过程等。不同数据库对象，其执行语句（如 CREATE 和 DROP ）的语法形式也不同。

2．数据操作语言

数据操作对象（ Data Manipulation Language , DML ）用于操纵数据库中的各种对象，检索和修改数据。常用的数据操作语句及其说明如下。

(1) INSERT 语句：用于向已经存在的表或视图中插入新的数据。

(2) SELECT 语句：用于查询表或视图中的数据。

(3) UPDATE 语句：用于更新表或视图中的数据。

(4) DELETE 语句：用于删除表或视图中的数据。

3．数据控制语言

数据控制语言（ Data Control Language , DCL ）用于安全管理，确定哪些用户可以查或修改数据库中的数据。数据控制语言包括的主要语句及其说明如下。

(1) GRANT ：授予权限，可把语句许可或对象许可的权限授予其他用户和角色。

(2) REVOKE ：收回权限，与 GRANT 的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限。

4.MySQL增加的语言元素

MySQL 增加的语言元素并不是 SQL 标准所包含的内容，因此有些资料中可能不会出现该组成。它是为了用户编程的方便所增加的语言元素，这些语言元素包括常量、变量、运算符、函数、流程控制语句和注释等。每个 SQL 语句都以分号（;）结束，并且 SQL 处理器会忽略空格、制表符和回车符

1.2、变量

MySQL变量分为系统变量和用户变量两种。系统变量分为 系统会话变量 和系统全局变量；实际开放时还会用到局部变量、会话变量等内容。

1.2.1、局部变量

局部变量一般用的SQL 语句块（比如存储过程的begin 和 end）中。其作用域仅限于该语句块，该语句块执行完后，局部变量就会消失。局部变量一般用 declare 来声明，可使用declare 说明默认值。

代码语言：txt复制

//定义名称为proc_add的存储过程，想该存储过程传入两个int类型的变量a、b
create procedure proc_add (in a int,in b int)
	begin
		//declare申明局部变量c
		declare c int default 0;
		set c = a b;
		select c as 'result';
	end;

1.2.2、用户变量

用户变量就是用户定义的变量，会话变量和全局变量都可以是用户定义的变量，只是他们对当前客户端生效 还是对所有客户端生效的区别不同。因此，可以说用户变量包括 会话变量和 全局变量。用户变量可以被赋值，还可以在后面的其他语句里引用。

用户变量的名称有'@'符号紧随其后的一个标识符组成。用户变量名称可以包含小数点(.)而无需用括号括起来。MySQL5.0前，用户变量名区分大小后，之后不再区分。

用户变量作用域比局部变量广。用户变量可以作用于当前整个连接，但是一旦与MySQL服务器的连接断开，那所定义的用户变量将不复存在。

代码语言：txt复制

set @x=0,@y=3;
set @color:='red',@size:='small';
select @x,@y,@color,@size;

select @count:=count(*) from Student;

1.2.3、会话变量

服务器未每个链接的客户端维护一系列会话变量。在客户端连接时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量。不能更改其他客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接，当前连接断开后，那所定义的会话变量将失效。

1、查看会话变量

代码语言：txt复制

//查看指定变量
select @@var_name;
select @@session.var_name;
//查看满足条件的部分变量
show session variables like '%var%';
//查看当前会话所有变量
show session variables;
show variables;

select @@autocommit;

2、设置会话变量

代码语言：txt复制

set session var_name = value;
set @@session.var_name = value;
set var_name = value;

set session autocommit=off;
select @@session.autucommit;

1.2.4、全局变量

1、查看全局变量

全局变量影响服务器整体操作，当服务器启动时，它将所有全局变量初始化为默认值，要想更改全局变量，必须具有 super 权限。

全局变量作用于服务器的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效，要想全局变量重启后继续生效，需要更改相应的配置文件。

代码语言：txt复制

select @@global.var_name;
show global variables like '%var%';

show global variables;
show global variables like '%char%'

2、设置全局变量

代码语言：txt复制

//set 命令设置变量时若不指定 global、session、local，默认使用session。
set global var_name = value;
set @@global.var_name = value;

1.3、常量

包括字符串常量，数值常量、十六进制常量、日期时间常量、其他常量：位字段值常量、布尔值常量、null值常量。

代码语言：txt复制

1、十六进制值的默认类型是字符串，如果想要确保该值作为数字处理，可使用cast()函数。
如果要将一个字符串或数字转换成十六进制格式的字符串，可以用hex()函数。

1.4、流程控制语句

1.4.1、if 条件语句

1、if语句的基本语法

if语句相当于一个三目运算符，若条件成立，返回结果1，否则返回结果2.适用于二选一的情况。

代码语言：txt复制

语法格式：
if(条件,结果1,结果2);

set @a='';
set @b = if(@a='','nothing',@a);
select @b;

2、存储过程 if 语句的语法

代码语言：txt复制

//search_condition：表示搜索表达式条件，如果值为真，则执行相应的SQL语句列表
//如果没有search_condition匹配，else子句被执行。
//statement_list 可包含一个或多个语句。
if search_condition then statement_list
	[elseif search_condition then statement_list]...
	[else statement_list]
end if

1.4.2、case 条件语句

if语句适用于二选一的情况，如果提供多个选择时，可以使用case语句。

代码语言：txt复制

语法1：

case 字段名称
　　when e1
　　then v1
　　when e2
　　then e2
　　...
　　else vn
end [as 字段别名]
　　case表示函数开始，end表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

select case 
　　when 1 > 0
　　then '1 > 0'
　　when 2 > 0
　　then '2 > 0'
　　else '3 > 0'
　　end
->1 > 0

代码语言：txt复制

语法2：

case expr 
　　when e1 then v1
　　when e1 then v1
　　...
　　else vn
end [as 字段别名]
　　如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。

select cno,case cno
	 when '3011' then '线性代数'
    when '3012' then '计算机原理'
    when '3013' then 'Java语言'
    when '3014' then '物理学'
	 when '3015' then '会计学'
    when '3016' then '商务英语'
    when '3017' then 'C语言'
    when '3018' then '世界地理'
    when '3019' then '高等数学'
    else '待更新'
    end as '课程名称'
from Students.StudentCourse

• 存储过程 case 语句的语法

代码语言：txt复制

第一种语法：

case 字段名
	when value1 then statement_list
	[when value1 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

1.4.3、循环语句

• 1、while 循环

condition是一个逻辑表达式，如果为真，则会一直执行statement_list，直到condition为假为止。statements是需要被重复执行的语句或语句块。

代码语言：txt复制

语法格式：
[begin_lable:] while condition do
    statement_list
end while [end_lable];

set @i = 1;
while @i 
    if @i % 2  0 then
        select @i;
    end if;
    set @i = @i   1;
end while;

• 2、repeat循环语句

repeat和while循环语句类似，不同的是它是先执行一次statement_list，然后在满足指定条件的情况下重复执行。expression是一个逻辑表达式，如果为真，则会结束循环。

代码语言：txt复制

语法格式：
[begin_lable:]  repeat
    statement_list
until expression
end repeat [end_lable];

• 3、loop循环语句

loop 语句允许内部语句重复执行，直到循环被退出，退出循环通常使用 leave 语句。

loop 与 while 相似之处：它们都不需要初始条件。

loop 与 repeat 相似之处：它们都不需要结束条件。

代码语言：txt复制

语法格式：
[begin_lable:] loop
    statements
end loop [end_lable];

//创建名称为Pro的存储过程
delimiter $$
create procedure pro()
begin
	declare num int default 1;
    labe11:loop
		if num < 6 then
			select num;
			set num= num 1;
			iterate labe11;//循环迭代labe11的内容
        end if;
	leave labe11;
    end loop labe11;
end$$ delimiter

注意：当使用 delimiter 命令时，应该避免使用反斜杠“”字符，因为反斜杠是 MySQL 的转义字符。

由于loop循环没有初始条件和结束条件，因此需要使用 leave 语句来结束循环。

代码语言：txt复制

1、iterate是重新启动循化的意思，使用在循 loop、 repeate 和 while语句内，用法leave label。类比Java的continue。

label 是标签 可以用在表示一个循环体。

2、leave 语句用于退出循环。类比Java的break。

在SQL中还有return关键字，但只用于函数，存储过程是不能使用的。

1.5、自定义函数

1.5.1、自定义函数语法

自定义函数时可以定义一次作用于一行的简单函数，也可以定义作用于多行的组的集合函数。自定义函数需要使用 create function 关键字，

代码语言：txt复制

语法格式：
create function function_name([func_parameter[,...]])
returns type
[characteristic ...] 
begin
	//函数实现的语句
	declare '变量名' '变量类型' default '变量默认值';
	set '参数设置'; 
	'SQL语句';
end

（1）function_name：自定义函数的名称。
（2）func_parameter：自定义函数的参数列表。这些参数都是输入参数，运算结果通过 returns 语句返回，并且该语句只能返回一个结果。
（3）returns type：指定返回值的类型，可以是字符串、也可以是整数、还可以是其他类型。
（4）begin 和 end ：分别标记 SQL 代码的开始和结束。

create function Sayhello(name varchar(50))
return varchar(100)
begin
	return concat(name,'说：很高兴认识大家');
end	

characteristic取值列表：

值	说明
language sql	指明函数体的语言类型, 目前仅支持sql
not deterministic	deterministic 指明函数的结果是确定的，即相同的输入会得到相同的输出；not deterministic意为结果不确定。默认为 not deterministic
contains sql	指明函数体使用sql语句的限制。contains sql意为函数体包含sql语句，但不包含读写数据的sql语句；no sql意为函数体不包含sql语句；reads sql data意为函数体包含读数据sql语句；modifies sql data意为函数体包含写数据的sql语句。默认为contains sql
sql security	{ definer/invoker } 指明谁有权限执行该函数。definer意为只有定义者才能执行；invoker意为拥有权限的调用者可以执行。默认为definer
comment 'message'	函数的注释信息，指明函数的功能

错误case解决：

代码语言：txt复制

create function Sayhello(name varchar(50))
return varchar(100)
begin
	return concat(name,'说：很高兴认识大家');
end	

创建这个函数时，会报错“Error 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe 

问题原因：创建自定义函数时，函数中没有包含 deterministic、nosql、reads sql语句，即没有涉及修改数据时，会报这个错误

解决方法：
（1）将log_bin_trust_function_creators设置为1，默认值是0，开启了log-bin日志，关闭后就正常创建自定义函数

delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号（;）。其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。 如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"。

1.5.2、调用自定义函数

代码语言：txt复制

语法格式：
select fuc_name(参数列表);

select Sayhello('jimimy') as '打招呼';

1.5.2、查看和删除自定义函数

代码语言：txt复制

-- 查看自定义函数
show create function 函数名;

show create function Sayhello;

-- 删除自定义函数
drop function 函数名;

drop function Sayhello;

2、存储过程和触发器

存储过程是一些由 MySQL 服务器直接存储和执行的定制过程或函数。存储过程的加入把 SQL 扩展成了一种程序设计语言，可利用存储过程把一个客户/服务器体系的数据库应用软件中的部分逻辑保存起来供日后使用。

触发器是在 insert、update、delete 命令之前或之后对 SQL命令或存储过程的自动调用。

2.1、存储过程

创建存储过程和函数是指经常使用的一组SQL语句组合在一起，并将这些SQL 语句当做一个整体存储在 MySQL 服务器中。

2.1.1、存储过程的优缺点

优点：

（1）更快的速度。在进行数据库操作时，经常必须在PHP程序和数据库服务器之间来回传输大量数据的情况：PHP 程序执行一条 select 命令，对查询结果进行某种处理，根据查询结果执行一条 update 命令，返回 last_insert_id等，如果将上述步骤都纳入存储过程在服务器上执行，数据传输方面许多开销都节省了下来。

（2）避免代码冗余。把相同功能的代码编写到一个存储过程中，这样不仅可以减少冗余代码，还可以使有关应用程序变得更加容易维护。当某个数据库的结构被改变时，程序员只需要修改存储过程就可以，不用再去修改每一个会用到这个数据库的应用程序的代码。

（3）提高数据库的安全性。对于数据库管理员来说，这样可以对每次数据访问进行监控，并在必要时把操作情况记录到日志。总之，人们可以根据具体的情况为不同的数据和数据访问操作设置不同严格程序的安全检查规则。

缺点：

（1）最大缺点：很难把他们从一个数据库移植到另一个数据库里去，因为每一个数据库系统所使用的存储过程语法或语法扩展是不同的。

（2）调试麻烦、维护性差。

2.1.2、创建存储过程

代码语言：txt复制

语法格式：
create procedure proc_name([proc_parameter[,...]])
[characteristic...] routine_body

上述语法说明如下：
（1）proc_name：存储过程的名称。

（2）proc_parameter：参数列表，如果没有参数，就使用一个空看书列表()。多个参数用逗号隔开。参数列表中每个参数都有输入、输出类型、参数名称、参数类型三部分组成。语法如下：
[in | out | inout] param_name type
in 表示 输入参数，out表示输出参数，inout表示既可以是输入也可以是输出。
param_name 和 type 分别表示参数名称和参数类型。

（3）routine_body：sql代码的内容，可以用begin...end来标识SQL代码的开始和结束。
（4）characteristic：指定存储过程的特性，包括多个取值，如下说明

characteristic取值列表：

值	说明
language sql	指明函数体的语言类型, 目前仅支持sql
not deterministic	deterministic 指明函数的结果是确定的，即相同的输入会得到相同的输出；not deterministic意为结果不确定。默认为 not deterministic
contains sql	指明函数体使用sql语句的限制。contains sql意为函数体包含sql语句，但不包含读写数据的sql语句；no sql意为函数体不包含sql语句；reads sql data意为函数体包含读数据sql语句；modifies sql data意为函数体包含写数据的sql语句。默认为contains sql
sql security	{ definer/invoker } 指明谁有权限执行该函数。definer意为只有定义者才能执行；invoker意为拥有权限的调用者可以执行。默认为definer
comment 'message'	函数的注释信息，指明函数的功能

创建存储过程时，系统默认指定contains SQL，它标识存储过程使用了SQL语句，如果存储过程中没有使用SQL语句，最好设置为 no SQL。而且存储过程中最好在comment进行简单注释，方便以后阅读。

创建存储过程示例：

代码语言：txt复制

delimiter $$
create procedure show_cno_grade(Son int,Cno varchar(10))
reads sql data
comment '查询学生课程的成绩'
begin
	select * from Students.StudentCourse where students.StudentCourse.Sno=Sno and students.StudentCourse.Cno= Cno;
end
delimiter ;

2.1.3、调用存储过程

存储过程需要使用call 语句来调用，是一个独立的操作，不能用在表达式里。使用存储过程的情况主要有两种：只能通过运算来实现某种效果或动作而无需返回一个值；运算会返回多个结果集。

代码语言：txt复制

//获取学号：3，课程号：3012的成绩
call show_cno_grade(3,3012);

2.1.4、查看存储过程

（1）show create语句

代码语言：txt复制

语法格式：
show create {procedure | function } sp_name;

sp_name：储存过程的名称

（2）show status语句

代码语言：txt复制

语法格式：
show create {procedure | function } status [like 'pattren'];

pattren:用来匹配存储过程和自定义函数名称。

（3）routines表

在MySQL中，information_schema 数据库下的routines 表中也保存了存储过程和自定义函数的信息，可以通过查询该表的记录来获取存储过程和自定义函数的信息。

代码语言：txt复制

语法格式：
select * from information_schema.routines where routine_name = 'sp_name';

2.1.5、删除存储过程

代码语言：txt复制

语法格式：
/使用if exists防止因不存在的存储过程而出错，给定了该子句时，将为每个不存在的存储过程生成NOTE.
drop procedure [if exists] sp_name;

2.1.6、修改存储过程

代码语言：txt复制

语法格式：
alter procedure sp_name [characteristic...];

alter procedure show_cno_grade reads sql data comment '查询学生课程的成绩-1';

select specific_name,sql_data_access,routine_comment from information_schema.routines where routine_name = 'show_cno_grade';

2.1.7、自定义函数和存储过程的区别

（1）总述

存储过程和自定义函数，两者的语法很相似，但却是不同的内容。

自定义函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。

而存储过程的限制就相对比较少，要实现的功能比较复杂一些。

（2）返回值上的不同

自定义函数必须向调用者返回一个结果，且仅有一个结果值。

存储过程将返回一个或多个结果集（函数做不到这一点），或者只是来实现某种效果或动作而无需返回结果。

（3）调用方式的不同

存储过程一般是作为一个独立的部分来执行，自定义函数嵌入在sql中使用的，可以在select中调用，就像内置函数一样，比如cos()、sin()。SQL 语句中不可用存储过程，而可以使用函数。

（4）参数的不同

自定义函数的参数类型类似于IN参数

存储过程的参数类型有三种：IN参数、OUT参数、INOUT参数

in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

存储过程是用户定义的一系列sql语句的集合，设计特定表或其它对象的任务，用户可以调用存储过程。

而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

2.1.8、在存储过程中定义和使用游标

存储过程功能很强大，在存储过程中可以声明全局变量，也可使用if语句，循环语句。还可以使用游标。

查询语句能查询出多条记录，在存储过程和函数中使用游标来读取出现结果集中的记录，在有些资料中，游标又被称为 光标 。游标的使用包括：声明游标、打开游标、使用游标、关闭游标。

游标必须申明在处理程序之前，并且在变量和条件之后。

（1）声明游标

声明游标和声明局部变量一样，都需要使用 declare 关键字。

代码语言：txt复制

语法格式：
declare cursor_name cursor for select_statement;

cursor_name：游标名称。
select_statement：select 语句内容。

（2）打开游标

打开游标需要使用 open 关键字，在该关键字后紧跟着游标名称即可。

（3）使用游标

MySQL中使用 fetch 关键字来 使用游标。

代码语言：txt复制

语法格式：
fetch cursor_name into var_name[, var_name...];

cursor_name：游标名称。
var_name：表示将游标中的select 语句查询出来的信息存入到该参数中。var_name参数必须在声明光标之前就可以定义好。

（4）关闭游标

关闭游标需要使用 close 关键字，在该关键字后紧跟着游标名称即可。

注意：如果存储过程或自定义函数中执行select 语句，并且select语句会查询出多条记录，这种情况最好使用 游标 来逐行读取记录。

游标必须在处理程序之前且在变量和条件之后声明，而且游标使用完成一定要关闭。

代码语言：txt复制

delimiter $$
drop procedure if exists proc_test $$

create procedure proc_test(in date_day datetime)
begin
	declare done int default 0;
	declare _prono varchar(10) default '';
	declare _proname varchar(50) default '';
	declare mycursor cursor for select proNo,proName from product;
	-- done用来跟踪是否有数据（not found），将值设为1表示找不到数据
	declare continue handler for not found set done=1;
	
	-- if语句根据date_day 参数把数据取出来，并放在result表中。否则打开光标，通过repeat循环从光标中取数据，将数据合并起来放到result表中。
	if date_day then
		create table result (select concat_ws(',', proNo, proName) from 
		product where datediff(proPubDate,date_day)=0);
	else
		open mycursor;
		repeat
			fetch mycuror into _prono,_proname;
			if not done then
				create table result (select concat_ws('_',result, _prono, 			_proname));
			end if;
		until done end repeat;
		close mycursor;
	end if;
	select * from result;
end $$

delimiter ;

2.2、触发器

触发器在数据库系统开发过程中具有非常重要的作用，例如，可防止有害数据录入数据库，可以改变或取消 insert、update、delete语句的执行 及 在一个会话中监听数据库中的数据的改变。

2.2.1、触发器的作用

触发器是一个特殊的存储过程，它的执行不是由程序调用的，也不是手工启动，而是由事件来触发。例如：当数据表被 insert、update、delete修改时，触发器将会自动执行。触发器可被设置成在这几种语句处理每个数据行之前或之后触发。以下是触发器的优点：

（1）触发器可以检查或修改将被插入或用来更新数据行的新数据值。这意味着开发者可以利用触发器强制实现数据的完整性，例如，检查某个百分比数值是不是落在了0 - 100区间内。触发器还可以用来对输入数据进行必要过滤。

（2）触发器可以把表达式的结果赋值给数据列作为其默认值。这使开发者可以绕开数据列定义中的默认值必须是常数的限制。

（3）触发器可以在删除 或修改数据行之前先检查它的当前内容。这种能力可以用来实现许多功能，比如，把对现有数据行的修改记载到一个日记里。

由于触发器代码是再数据表里的数据发生变化后自动执行的，因此比较复杂的触发器往往会对数据库的正常使用造成严重的影响，使用触发器有如下限制：

（1）触发器不能调用将数据返回客户端的存储过程，也不能使用采用 call 语句的动态SQL（允许存储过程通过参数将数据返回触发器）。

（2）触发器不能通过使用以显式或隐式方式 开始或结束事务的语句，如 start transaction、commit、rollback。

MySQL中触发器的执行顺序是，before 触发器、表操作、after 触发器。

2.2.2、创建触发器

代码语言：txt复制

语法格式：
create trigger trigger_name
{before | after}
{insert | update | delete}
on tabl_name
for each row trigger_stmt

上述语法说明：
（1）trigger_name：触发器名称。
（2）before | after：触发程序的动作时间，在触发事件之前还是之后触发语句。
（3）insert | update | delete：触发条件。
（4）for each row ：表示任何一条记录上的操作满足触发事件都会触发该触发器。
（5）trigger_stmt：触发器被触发后执行的语句。

-- 创建触发器
-- delimiter $$
-- create trigger trigger_deleteForStudent after delete 
-- on Students.Student for each row
-- begin
-- 	insert into Students.student_log(content,create_time) values(concat('将id为：',old.Sname,'已删除'),now());
-- end$$
-- delimiter ;

在创建触发器主体时，还可以使用old和new来获取 SQL 执行insert，update和delete操作前后的写入数据。

注意：

对于insert语句，只有new是合法的，表示当前已插入的记录；

对于delete语句，只有old才合法，表示当前删除的记录；

而update语句可以和new（更新后）以及old（更新前）同时使用。

在MySQL中，一个表在相同时间触发事件，只能创建一个触发器，例如在product表中，触发事件insert，触发时间为 after 的触发器只能有一个。但是，可以定义触发事件为 before 的触发器，如果该表中执行insert 语句，这个触发器将自动执行。

注意：尽量少使用触发器，不建议使用。 触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

注意：触发器中不能对本表进行insert，update，delete操作，以免递归循环触发

错误case解决：

代码语言：txt复制

delimiter $$
create trigger trigger_deleteForStudent after delete 
on Students.Student for each row
begin
	-- select Sno from Students.Student into @p;
    select concat_ws('_',Sno,Sname) from Students.Student  order by Sno desc limit 1 into @p;
	call insertStudent('已删除','男',0,'已删除',0,'已删除');
end$$
delimiter ;

报错1：创建这个触发器时会报错：‘Error Code: 1415. Not allowed to return a result set from a trigger’，

原因：从MySQL5开始不支持触发器返回结果集。

解决方法：在结果集后加上 into @p，需要用一个变量接收结果集输出;

select concat_ws('_',Sno,Sname) from Students.Student  order by Sno desc limit 1 into @p;

报错2：Error Code: 1442. Can't update table 'student' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

原因：存储的函数或触发器不能修改已被调用该函数或触发器的语句（用于读取或写入）使用的表。

解决办法：student表上的触发器里 不能再更新（insert、update、delete）student表，只能操作其他表。

2.2.3、查看和删除触发器

代码语言：txt复制

-- 查看触发器(所有)
show triggers;
-- 查看触发器(按触发器名称)
select * from information_schema.triggers where trigger_name='trigger_deleteForStudent';

-- 删除触发器
-- drop trigger if exists trigger_deleteForStudent;

-- 列出当前用户对当前目录的所有模式中所有表的权限
-- show grants;

3、MySQL中的异常处理

3.1、定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个 错误名字 和 指定的 错误条件 关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

代码语言：txt复制

语法格式如下：
declare condition_name condition for 错误码(condition_value)

condition_name：参数表示条件的名称；
condition_value：参数表示条件的类型；
sqlstate_value 参数和 mysql_error_code 参数都可以表示 MySQL 的错误。
sqlstate_value：表示长度为 5 的字符串类型错误代码，
mysql_error_code：表示数值类型错误代码。例如 ERROR 1146(42S02) 中，

下面定义“error 1089 (HU123)”这个错误，名称为 can_not_find。 可以用两种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value
declare can_not_find condition for sqlstate 'HU123';

//方法二：使用 mysql_error_code
declare can_not_find condition for 1089 ;

3.2 定义异常处理程序

代码语言：txt复制

基本语法如下：
declare handler_type handler for condition_value[...] sp_statement

handler_type：continue | exit | undo
condition_value：
sqlstate [value] sqlstate_value | condition_name | sqlwarning | not found | sqlexception | mysql_error_code

其中，handler_type 参数指明错误的处理方式，该参数有 3 个取值。这 3 个取值分别是 continue、exit 和 undo。
（1）continue 表示遇到错误不进行处理，继续向下执行；
（2）exit 表示遇到错误后马上退出；
（3）undo 表示遇到错误后撤回之前的操作，mysql 中暂时还不支持这种处理方式。

参数指明错误类型，该参数有 6 个取值：
（1）sqlstate_value：包含 5 个字符的字符串错误值；
（2）condition_name：表示 declare 定义的错误条件名称；
（3）sqlwarning：匹配所有以 01 开头的 sqlstate_value 值；
（4）not found：匹配所有以 02 开头的 sqlstate_value 值；
（5）sqlexception：匹配所有没有被 sqlwarning 或 not found 捕获的 sqlstate_value 值；
（6）mysql_error_code：匹配数值类型错误代码。
sp_statement 参数为程序语句段，表示在遇到定义的错误时，需要执行的一些存储过程或函数。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，mysql 中现在还不能支持 undo 操作。因此，遇到错误时最好执行 exit 操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行 continue 操作。

代码语言：txt复制

下面是定义处理程序的几种方式，代码如下：
//方法一：捕获 sqlstate_value
declare continue handler for sqlstate '42s02' set @info='can not find';

//方法二：捕获 mysql_error_code
declare continue handler for 1146 set @info='can not find';

//方法三：先定义条件，然后调用
declare can_not_find condition for 1146;
declare continue handler for can_not_find set @info='can not find';

//方法四：使用 sqlwarning
declare exit handler for sqlwarning set @info='error';

//方法五：使用 not found
declare exit handler for not found set @info='can not find';

//方法六：使用 sqlexception
declare exit handler for sqlexception set @info='error';

mysql

0 人点赞