五、MYSQL存储过程和函数

作者: 码农工号9527 | 来源:发表于2020-09-21 16:37 被阅读0次

Create procedure/function语句

• create procedure用来创建存储过程，create function用来创建函数

• 函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可
• Definer和sql security子句指定安全环境
• Definder是MySQL的特殊的访问控制手段，当数据库当前没有这个用户权限时，执行存储过程可能会报错
• sql secuirty的值决定了调用存储过程的方式，取值：definer（默认）或者invoker definer:在执行存储过程前验证definer对应的用户如：cdq@127.0.0.1是否存在，以及是否具有执行存储过程的权限，若没有则报错 invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若没有则报错
• IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数
• IN输入参数用于把数值传入到存储过程中；OUT输出参数将数值传递到调用者，初始值是NULL；INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者

 mysql> delimiter //
 mysql> CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT)
 -> BEGIN
 -> SELECT COUNT(*) INTO param2 FROM students where sid>param1;
 -> END//
 Query OK, 0 rows affected (0.03 sec)
 Delimiter ;
 mysql> CALL simpleproc(1, @a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT @a;
 +------+
 | @a |
 +------+
 | 2 |
 +------+
 1 row in set (0.00 sec)

• Delimiter命令是改变语句的结束符，MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束
• rontine_body子句可以包含一个简单的SQL语句，也可以包含多个SQL语句，通过begin…end将这多个SQL语句包含在一起
• MySQL存储过程和函数中也可以包含类似create和drop等DDL语句
• comment子句用来写入对存储过程和函数的注释
• Language子句用来表示此存储过程和函数的创建语言
• 存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic

 mysql> delimiter //
 mysql> create function simplefunc(param1 int)
 -> returns int
 -> begin
 -> update students set sex=1 where sid=param1;
 -> select count(*) into @a from students where sid>param1;
 -> return @a;
 -> end;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> select simplefunc(1);
 +---------------+
 | simplefunc(1) |
 +---------------+
 | 2             |
+----------------+
 mysql> select * from students where sid=1;
 +------+-------+------+
 | sid | sname  | sex  |
 +------+-------+------+
 | 1   | a      | 1    | 
 +------+-------+------+


[root@localhost ~]# mysql -u cdq –p
delimiter //
CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT)
BEGIN
SELECT COUNT(*) INTO param2 FROM students where sid>param1;
END//
delimiter ;


[root@localhost ~]# mysql -u root –p
mysql> call simpleproc(1,@a);
mysql> drop user cdq@localhost;
mysql> call simpleproc(1,@a);
ERROR 1449 (HY000): The user specified as a definer ('cdq'@'localhost') does not exist
mysql> alter procedure simpleproc sql security invoker;
mysql> call simpleproc(1,@a);

• 相关属性短语只有咨询含义，并不是强制性的约束

• Contains sql表明此存储过程或函数不包含读或者写数据的语句，这是默认属性
• NO SQL表示此存储过程或函数不包含SQL语句
• Reads sql data表示此存储过程包含诸如select的查询数据的语句，但不包含插入或删除数据的语句
• Modifies sql data表示此存储过程包含插入或删除数据的语句

drop procedure/function语句

• Drop procedure/function语句用来删除指定名称的存储过程或函数

• If exists关键词用来避免在删除一个本身不存在的存储过程或函数时，MySQL返回错误

 mysql> drop procedure simpleproc;
 Query OK, 0 rows affected (0.00 sec)
 mysql> drop procedure simpleproc;
 ERROR 1305 (42000): PROCEDURE part2_cn.simpleproc does not exist
 mysql> drop function if exists simplefunc;
 Query OK, 0 rows affected, 1 warning (0.01 sec)

Begin…end复合语句

• Begin…end语句通常出现在存储过程、函数和触发器中，其中可以包含一个或多个语句，每个语句用;号隔开

标签语句

• 标签label可以加在begin…end语句以及loop, repeat和while语句
• 语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签

 mysql> delimiter //
 mysql> CREATE PROCEDURE doiterate(IN p1 int, OUT p2 int)
 -> BEGIN
 -> label1: LOOP
 -> SET p1 = p1 + 1;
 -> IF p1 < 10 THEN ITERATE label1; END IF;
 -> LEAVE label1;
 -> END LOOP label1;
 -> set p2=p1;
 -> END;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> call doiterate(1,@a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> select @a;
 +------+
 | @a |
 +------+
 | 10 |
 mysql> call doiterate(5,@a);
 Query OK, 0 rows affected (0.00 sec)
 mysql> select @a;
 +------+
 | @a |
 +------+
 | 10 | 
 +------+

Declare语句

• Declare语句通常用来声明本地变量、游标、条件或者handler
• Declare语句只允许出现在begin … end语句中而且必须出现在第一行
• Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

存储过程中的变量

• 本地变量可以通过declare语句进行声明
• 声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值
• 通过declare声明变量方法：

• 使用default指定变量的默认值，如果没有指定默认值则初始值为NULL
• Type指明该变量的数据类型
• 声明的变量作用范围为被声明的begin … end语句块之间
• 声明的变量和被引用的数据表中的字段名要区分开来

 delimiter //
 CREATE PROCEDURE sp1 (v_sid int)
 BEGIN
 DECLARE xname VARCHAR(5) DEFAULT 'bob';
 DECLARE xsex INT;
 SELECT sname, sex INTO xname, xsex
 FROM students WHERE sid= v_sid;
 SELECT xname,xsex;
 END;
 //
 delimiter ;

 mysql> call sp1(1);
 +-------+------+
 | xname | xsex |
 +-------+------+
 | a     | 1    |
 +-------+------+

流程控制语句

• MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外return语句也是函数中的特定流程控制语句

流程控制case语句

• Case语句在存储过程或函数中表明了复杂的条件选择语句

• 第一个语句中case_value与后面各句的when_value依次做相等的对比，如果碰到相等的，则执行对应的后面的statement_list，否则接着对比，如果都没有匹配，则执行else后面的statement_list
• 第二个语句中当search_condition满足true/1的结果时，则执行对应的statement_list，否则执行else对应的statement_list
• Statement_list可以包含一个或多个SQL语句

 delimiter //
 CREATE PROCEDURE exp_case(v_sid int)
 BEGIN
 DECLARE v INT DEFAULT 1;
 select sex into v from students where sid=v_sid;
 CASE v
 WHEN 0 THEN update students set sex=1 where sid=v_sid;
 WHEN 1 THEN update students set sex=0 where sid=v_sid;
 ELSE
 update students set sex=-1 where sid=v_sid;
 END CASE;
 END;
 //
 delimiter ;

 mysql> call exp_case(1);
 Query OK, 1 row affected (0.01 sec) 

 delimiter //
 CREATE PROCEDURE exp_case2(v_sid int)
 BEGIN
 DECLARE v INT DEFAULT 1;
 select sex into v from students where sid=v_sid;
 CASE
 WHEN v=0 THEN update students set sex=1 where sid=v_sid;
 WHEN v=1 THEN update students set sex=0 where sid=v_sid;
 ELSE
 update students set sex=-1 where sid=v_sid;
 END CASE;
 END;
 //
 delimiter ;

 mysql> call exp_case2(1);
 Query OK, 1 row affected (0.01 sec)

流程控制IF语句

• IF语句在存储过程或函数中表明了基础的条件选择语句

• IF语句中如果search_condition满足true/1的条件，则执行对应的statement_list，否则再判断elseif中的search_condition是否满足true/1的条件，如果都不满足则执行else中的statement_list语句
• Statement_list中可以包含一个或多个SQL语句

 DELIMITER //
 CREATE FUNCTION SimpleCompare(n INT, m INT)
 RETURNS VARCHAR(20)
 BEGIN
 DECLARE s VARCHAR(20);
 IF n > m THEN SET s = '>';
 ELSEIF n = m THEN SET s = '=';
 ELSE SET s = '<';
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m);
 RETURN s;
 END //
 DELIMITER ;

 mysql> select simplecompare(1,2);
 +--------------------+
 | simplecompare(1,2) |
 +--------------------+
 | 1 < 2              |
 +--------------------+
 1 row in set (0.02 sec)

 DELIMITER //
 CREATE FUNCTION VerboseCompare (n INT, m INT) ##嵌套if语句
 RETURNS VARCHAR(50)
 BEGIN
 DECLARE s VARCHAR(50);
 IF n = m THEN SET s = 'equals';
 ELSE
  IF n > m THEN SET s = 'greater';
  ELSE SET s = 'less';
  END IF;
 SET s = CONCAT('is ', s, ' than');
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m, '.');
 RETURN s;
 END //
 DELIMITER ;

 mysql> select verbosecompare(1,2);
 +---------------------+
 | verbosecompare(1,2) |
 +---------------------+
 | 1 is less than 2.   | 
 +---------------------+
 1 row in set (0.02 sec)

注：
如果报错：ERROR 1418 (HY000): 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 log_bin_trust_function_creators variable)
翻译：这个函数没有确定性，没有SQL，或者在声明中读取SQL数据，并且启用了二进制日志记录(您可能想要使用不太安全的log bin trust函数creator变量)
这是我们开启了 bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在 function 里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的 function 指定一个参数。
在 MySQL 中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;

流程控制iterate语句

• Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环

• Label表示自定义的标签名

流程控制leave语句

• Leave语句表明退出指定标签的流程控制语句块
• 通常会用在begin…end，以及loop,repeat,while的循环语句中

• Label表明要退出的标签名

流程控制iterate和leave语句

 mysql> delimiter //
 mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
 -> BEGIN
 -> label1: LOOP
 -> SET p1 = p1 + 1;
 -> IF p1 < 10 THEN ITERATE label1; END IF;
 -> LEAVE label1;
 -> END LOOP label1;
 -> set p2=p1;
 -> END;
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;

流程控制loop语句

• Loop语句是存储过程或函数中表达循环执行的一种方式

• 其中的statement_list可以包含一个或多个SQL语句

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
 label1: LOOP
 SET p1 = p1 + 1;
 IF p1 < 10 THEN
 ITERATE label1;
 END IF;
 LEAVE label1;
 END LOOP label1;
 SET @x = p1;
END;

流程控制repeat语句

• repeat语句是存储过程或函数中表达循环执行的一种方式

• Repeat语句中statement_list一直重复执行直到search_condition条件满足
• Statement_list可以包含一个或多个SQL语句

 mysql> delimiter //
 mysql> CREATE PROCEDURE dorepeat(p1 INT)
 -> BEGIN
 -> SET @x = 0;
 -> REPEAT
 -> SET @x = @x + 1;
 -> UNTIL @x > p1 END REPEAT;
 -> END
 -> //
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter ;
 mysql> CALL dorepeat(1000)//
 Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT @x//
 +------+
 | @x   |
 +------+
 | 1001 |
 +------+
 1 row in set (0.00 sec)

流程控制while语句

• while语句是存储过程或函数中表达循环执行的一种方式

• 当search_condition返回为true时，则循环执行statement_list中的语句，直到search_condition的结果返回为false
• Statement_list中可以包含一个或多个SQL语句

 DELIMITER //
 CREATE PROCEDURE dowhile()
 BEGIN
 DECLARE v1 INT DEFAULT 5;
 WHILE v1 > 0 DO
 update students set sex=-1 where sid=v1;
 SET v1 = v1 - 1;
 END WHILE;
 END;
 //
 DELIMITER ;

 mysql> call dowhile();
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from students;
 +------+-------+------+
 | sid  | sname | sex  |
 +------+-------+------+
 | 1    | a     | -1   |
 | 2    | b     | -1   |
 | 3    | c     | -1   |
 +------+-------+------+

流程控制return语句

• Return语句用在函数中，用来终结函数的执行并将指定值返回给调用者

• 在函数中必须要有至少一个return语句，当有多个return语句时则表明函数有多种退出的方式

 delimiter //
 create function doreturn()
 returns int
 begin
 select sex into @a from students where sid=1;
 if @a=1 then return 1;
 elseif @a=0 then return 0;
 else return 999;
 end if;
 end;
 //
 delimiter ;
 mysql> select doreturn();
 +------------+
 | doreturn() |
 +------------+
 | 999 |
 +------------+

cursor游标

• Cursor游标用来声明一个数据集
• 游标的声明必须在变量和条件声明之后，在handler声明之前

 CREATE PROCEDURE curdemo()
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE a CHAR(16);
 DECLARE b, c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cur1;
 OPEN cur2;
 read_loop: LOOP
 FETCH cur1 INTO a, b;
 FETCH cur2 INTO c;
 IF done THEN
 LEAVE read_loop;
 END IF;
 IF b < c THEN
 INSERT INTO test.t3 VALUES (a,b);
 ELSE
 INSERT INTO test.t3 VALUES (a,c);
 END IF;
 END LOOP;
 CLOSE cur1;
 CLOSE cur2;
 END;

cursor游标close语句

• Cursor close语句用来关闭之前打开的游标

• 如果关闭一个未打开的游标，则MySQL会报错
• 如果在存储过程和函数中未使用此语句关闭已经打开的游标，则游标会在声明的begin…end语句块执行完之后自动关闭

cursor游标declare语句

• Cursor declare语句用来声明一个游标和指定游标对应的数据集合，通常数据集合是一个select语句

• Select_statement代表一个select语句

cursor游标fetch语句

• Cursor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予后面的变量

• 数据集中的字段需要和INTO语句中定义的变量一一对应
• 数据集中的数据都fetch完之后，则返回NOT FOUND

cursor游标open语句

• Open cursor语句用来打开一个之前已经声明好的游标

Declare condition语句

• Declare condition语句命名特定的错误条件，而该特定错误可以在declare…handler中指定处理方法

• Condition_value指定特定的错误条件，可以有以下两种形式

• Mysql_err_code表示MySQL error code的整数
• SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态

• 比如在MySQL中1051error code表示的是unknown table的错误，如果要对这
个错误做特殊处理，可以用三种方法：

 DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END; 

 DECLARE no_such_table CONDITION FOR 1051;
 DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;

 DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
 DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;

Declare handler语句

• Declare handler语句用来声明一个handler来处理一个或多个特殊条件，当其中的某个条件满足时则触发其中的statement语句执行
• Statement可以是一个简单SQL语句，也可以是begin…end组成的多个语句

• Handler_action子句声明当执行完statement语句之后应该怎么办

• Continue代表继续执行该存储过程或函数
• Exit代表退出声明此handler的begin…end语句块
• Undo参数已经不支持

Condition_value的值有以下几种：

• Mysql_err_code表示MySQL error code的整数
• SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态
• Condition_name表示之前在declare…condition语句中声明的名字
• SQLWARNING表示所有的警告信息，即SQLSTATE中01打头的所有错误
• NOT FOUND表示查完或者查不到数据，即SQLSTATE中02打头的所有错误
• SQLEXCEPTION表示所有的错误信息

 DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLWARNING
 BEGIN
 -- body of handler
 END;

 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
 -- body of handler
 END;

• 当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理

• 发生SQLEXCEPTION错误，则执行exit退出
• 发生SQLWARNING警告，则执行contine继续执行
• 发生NOT FOUND情况，则执行continue继续执行

 比如SQLSTATE '23000'表示主键冲突错误
 mysql> CREATE TABLE t (s1 INT, PRIMARY KEY (s1));
 Query OK, 0 rows affected (0.00 sec)
 mysql> delimiter //
 mysql> CREATE PROCEDURE handlerdemo ()
 -> BEGIN
 -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
 -> SET @x = 1;
 -> INSERT INTO test.t VALUES (1);
 -> SET @x = 2;
 -> INSERT INTO test.t VALUES (1);
 -> SET @x = 3;
 -> END;
 -> //
 Mysql> delimiter ;
 mysql> CALL handlerdemo();
 mysql> SELECT @x;
 +------+
 | @x |
 +------+
 | 3 |
 +------+
 1 row in set (0.00 sec)

 CREATE PROCEDURE curdemo()
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE a CHAR(16);
 DECLARE b, c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cur1;
 OPEN cur2;
 read_loop: LOOP
 FETCH cur1 INTO a, b;
 FETCH cur2 INTO c;
 IF done THEN
 LEAVE read_loop;
 END IF;
 IF b < c THEN
 INSERT INTO test.t3 VALUES (a,b);
 ELSE
 INSERT INTO test.t3 VALUES (a,c);
 END IF;
 END LOOP;
 CLOSE cur1;
 CLOSE cur2;
 END;

操作

创建一个过程，将男生和女生的学生数据分别存储到男生表和女生表中。

create procedure proc1() as BeginInsert 
into students_male select * from students where gender=0;
Insert into students_female select * from students where gender=1;
End;

创建一个过程，将每个学生的课程数，平均成绩，及格课程数，非及格课程数都存放在单独的表中

create procedure proc2() as BeginInsert 
into temp1 select sid,count(*),avg(score),sum(case when score>=60 then 1 else 0 end),
sum(case when score<60 then 1 else 0 end) from score group by sid;
End;

创建一个过程，将学生sid作为输入参数，结果展示出该学生的课程数和平均成绩

create procedure proc3(IN st_id) as Begin
select sid,count(*),avg(score) from score where sid=st_id group by sid;
End;

创建一个函数，以学生sid作为输入参数，将该学生的课程数和平均成绩存放在单独的表中，并返回平均成绩

create function func1(st_id int) as Begin
return int 
declare avg_score int;
insert into temp2 select sid,count(*),avg(score) from score where sid=st_id group by sid;
select avg(score) into avg_score from score where sid=st_id group by Sid;
return avg_score; 
End;

用游标的方法实现创建一个函数，输入参数是老师的id，函数返回该老师所教授的课程数量，并将这些学习这些课程的每个学生如果成绩不及格，把学生的sid和对应课程名字、成绩insert到表A中，如果成绩及格，把学生的sid和对应的课程名字、成绩insert到表B中

 delimiter //
 Create function func3(v_teacher_id int)
 Returns int
 Begin
 Declare n_course int;
 Declare v_sid int default null;
 Declare v_course_name varchar(60);
 Declare v_score int;
 Declare cur1 cursor for select a.sid,b.course_name,a.score
 From score a inner join course b on a.coure_id=b.id
 Where b.teacher_id=v_teacher_id;
 declare continue handler for not found set v_sid=null;
 Select count(*) into n_course from course where teacher_id=v_teacher_id;
 Open cur1;
 Fetch cur1 into v_sid,v_course_name,v_score;
 While v_sid is not null do
 If v_score<60 then
 insert into A select v_sid,v_course_name,v_score;
 Else
 insert into B select v_sid,v_course_name,v_score;
 End if;
 Fetch cur1 into v_sid,v_course_name,v_score;
 End while;
 Close cur1;
 Return n_course;
 End;
 //
 Delimiter ;

create trigger语句

• create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行
• 触发器创建时需要指定对应的表名tbl_name

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

• Definer关键词用来指定trigger的安全环境
• Trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行
• Trigger_event指定触发该触发器的具体事件
• INSERT当新的一行数据插入表中时触发，比如通过执行insert,load data,replace语句插入新数据
• UPDATE当表的一行数据被修改时触发，比如执行update语句时
• DELETE当表的一行数据被删除时触发，比如执行delete,replace语句时
• 当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器
• 从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下按照创建的时间依次执行，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器后执行，PRECEDES则表示新触发器先执行
• Trigger_body表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

 delimiter //
 create trigger simple_trigger
 after update
 on students for each row
 begin
 insert into students_bak values(old.sid,old.sname,new.sname,old.sex,new.sex,now());
 end;
 //
 delimiter ;
 mysql> update students set sname='abc',sex=1;
 mysql> select * from students_bak;
 +------+----------+----------+--------+--------+---------------------+
 | sid  | oldsname | newsname | oldsex | newsex | tstamp              |
 +------+----------+----------+--------+--------+---------------------+
 | 1    | a        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 | 2    | b        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 | 3    | c        | abc      | -1     | 1      | 2017-03-28 16:28:42 |
 +------+----------+----------+--------+--------+---------------------+

Drop trigger语句

• Drop trigger语句用来删除一个触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

• If exists短语用来避免删除不存在的触发器时引发报错
• 当你执行drop table时，表上的触发器也被drop掉了

 mysql> Drop trigger simple_trigger;
 Query OK, 0 rows affected (0.00 sec)

操作

在score表上创建一个触发器，当有新的数据插入时，在score_bak表里记录新插入的数据的所有字段信息，并用tstamp字段标注数据的插入时间

 Delimiter //
 create trigger trig1
 after insert on score
 For each row
 Begin
 Insert into score_bak(Sid,course_id,score,tstamp) values(new.sid,new.course_id,new.score,now());
 End;
 //
 Delimiter ;

在score表上创建一个触发器，当有新的数据插入时，在score_avg表里记录对应学生的所有课程的平均成绩（注意，如果在score_avg表里已经有了学生的记录，需要update）

 Delimiter //
 create trigger trig2
 After insert on score
 For each row
 Begin
 Declare n int;
 Select count(*) into n from score_avg where sid=new.sid;
 If n=1 then
 update score_avg set avg_score=(select avg(score) from score where sid=new.sid) where sid=new.sid;
 Else
 insert into score_avg select sid,avg(score) from score where sid=new.sid group by sid;
 End if;
 End;
 //
 Delimiter ;