MySQL中存储函数及触发器的使用

#运行创建函数权限信任

SET GLOBAL log_bin_trust_function_creators=TRUE;

#1、创建存储函数——没有参数

DROP FUNCTION IF EXISTS myfunc1_emp;

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();

#2、创建存储过程——有输入参数

#需求：传入一个员工的编号，返回员工的名字

DROP FUNCTION IF EXISTS myfunc2_emp;

DELIMITER $$

CREATE FUNCTION myfunc2_emp(in_empno INT)RETURNS VARCHAR(50)

BEGIN

DECLARE out_ename VARCHAR(50);

SELECT ename INTO out_ename FROM emp WHERE empno=in_empno;

RETURN out_ename;

END $$

DELIMITER;

#调用存储函数

SELECT myfunc2_emp('1008');

触发器：

触发器，是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的sql片段，但是触发器无需调用，当对数据表中的数据执行DML操作时自动触发这sql片段的执行，无需手动调佣。

#删除触发器

drop trigger if exists trigger_test;

#查看触发器 

show triggers;

1、在mysql中，只有执行insert，delete，update操作

时才能触发触发器的执行

2、触发器的这种特征可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

3、使用别名OLD和new来引用触发器中的发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器的特性

1、什么条件会触发：I、D、U（增删改）

2、什么时候触发：在增删改前或者后

3、触发频率：针对每一行执行

4、触发器定义在表上，附着在表上

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

create trigger 触发器名 before|after 触发事件

on 表名 for each row

执行语句;

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

create trigger 触发器名 before|after 触发事件

on 表名 for each row

begin

执行语句列表

end ;

#数据准备

create database if not exists mydb_trigger;

use mydb_trigger;

--用户表

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(50)

);

#需求1：当user表添加一行数据，则会自动在user_log添加日志记录

#定义触发器:trigger_test1

create trigger trigger_test1 after insert

on user for each row

insert into user_logs values(null,now(),'有新用户添加');

#在user表中添加数据，让触发器自动执行

insert into user values (1,'张三','123456');

#需求2：当user表数据被修改时，则会自动在user_log添加日志记录

delimiter $$

create trigger trigger_test2 before update

on user for each row

begin

insert into user_logs values(null,now(),'有用户信息被更新');

end $$

delimiter;

#在user表中修改数据，让触发器自动执行

update user set password='888888' where uid='1';

#操作new与old

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

insert 触发器：new表示将要或者已经新增的数据

update触发器：old表示修改之前的数据，new表示将要或已经修改后的数据

delete触发器：old表示将要或者已经删除的数据

使用方法：new.columnname(columnname为对应数据表的某一列名)

对同个表，同个类型的触发器（如insert，update，delete），创建多个会触发多次，所以触发器如果不使用了，需要将其删除

DELIMITER $$

CREATE TRIGGER trigger_test3 AFTER INSERT

ON USER FOR EACH ROW

BEGIN

INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT('有新用户被添加，信息为：',NEW.uid,new.username,new.password));

END $$

DELIMITER;

#在user表中修改数据，让触发器自动执行

INSERT INTO USER VALUES (16,'李四','123456');

触发器：更新

DELIMITER $$

CREATE TRIGGER trigger_test4 AFTER UPDATE

ON USER FOR EACH ROW

BEGIN

INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT('有用户信息被修改，原信息为：',old.uid,old.username,old.password,'修改为',NEW.uid,new.username,new.password));

END $$

DELIMITER;

#在user表中修改数据，让触发器自动执行

UPDATE USER SET PASSWORD='888888' WHERE uid='5';

注意事项:

1、MySQL中触发器中不能对本表数据进行insert、update、delete操作，以免递归循环触发

2、尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert 的效率就非常低了。

3、触发器是针对每一行的：对增删改非常频繁的表上切记不要使用触发器，因为他会非常消耗资源。