1000字范文 > mysql自定义函数存储过程游标触发器事件(定时任务)

mysql自定义函数存储过程游标触发器事件(定时任务)

时间：2019-01-11 21:02:47

创建函数FUN_GET_ORG_CHILD(rootId)

CREATE FUNCTION `FUN_GET_ORG_CHILD`(rootId varchar(100))RETURNS varchar(2000) CHARSET utf8mb4//设置返回值类型BEGIN DECLARE sTemp varchar(2000); //定义局部变量DECLARE sTempChd varchar(1000); //定义局部变量SET sTemp = '$'; //变量赋值SET sTempChd = rootId; //变量赋值WHILE sTempChd is not null and sTempChd <> '0' DO //while循环SET sTemp = concat(sTemp, ',', sTempChd); SELECT group_concat(ORG_ID) INTO sTempChd FROM T_SYS_ORG where FIND_IN_SET(PARENT_ID, sTempChd)>0; END WHILE; RETURN sTemp; END

此sql：SELECT * FROM T_SYS_ORG where FIND_IN_SET(ORG_ID, FUN_GET_ORG_CHILD(#{orgId})) 这样就能查询某个机构的本下级信息了。简单记录一下

扩展一：DEFINER

扩展二：select * into 变量A from table_name 或者 select * from table_name into 变量A

将查出的结果赋值给变量A

MySQL自定义函数：

mysql 自定义函数（user-defined function）简称UDF 是对mysql功能的一个扩展。

语法：

DELIMITER $$CREATE FUNCTION 函数名(变量名1 数据类型1,变量名2 数据类型2,...) RETURNS 返回值类型BEGIN变量的声明sql语句RETURN 值; END $$DELIMITER;/*注意：1、参数包含两部分：参数名参数类型2、函数体：肯定会有return语句，如果没有会报错如果return语句没有放在函数体的最后也不报错，但不建议3、函数体中仅有一句话，则可以省略begin end4、使用 delimiter语句设置结束标记*/

简单示例：统计user表的用户数量

DELIMITER //CREATE FUNCTION countUser() RETURNS INTBEGINDECLARE total int default 0;select count(*) into total from user;return total;END //DELIMITER;

1、DELIMITER定界符

需要定义定界符，否则是创建不了函数的，因为mysql见到'分号'就认为执行结束了，只有开始

创建时定义分界符，结束时在配对一个分界符，mysql认为这个时候才结束，使得函数能够完整编译创建

附一个详细讲delimiter的博客：MySql中 delimiter 详解

2、变量的声明

MYSQL中，变量分为三类:局部变量，用户变量、系统变量(会话变量，全局变量)

局部变量：

(存储过程中)通过DECLARE可以定义一个局部变量，default设置默认值。

declare声明变量必须放在最前面（可能是语法要求吧：declare声明的变量要放在begin...end块的第一行）

作用范围：BEGIN...END块中

语法：DECLARE变量1[,变量2,... ]变量类型 [DEFAULT默认值]

多个同类型的变量可以分开或者一起声明 DECLARE total,total1,total2 VARCHAR(10) default null;

赋值：通过set或者 select * into 变量 from table 或者 select * from table into 变量

用户变量：

用户变量不用事前申明，在用的时候直接用“@变量名”使用即可。

第一种用法：set @num=1; 或set @num:=1; //这里要使用set语句创建并初始化变量，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where …

注意上面两种赋值符号，使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”

作用范围：在客户端链接到数据库实例整个过程中都是有效的。

系统变量

会话变量

仅对当前客户端连接有效，

使用set session varname=value;或者set @@session.varname=value;设置会话级别的系统变量

或者使用set local varname=value;或者set @@local.varname=value;设置会话级别的系统变量

其中，修改会话变量不需要root权限，且session可以省略，如：set varname=value;

查看所有的会话变量：

SHOW [SESSION] VARIABLES;

查看一个会话变量也有如下三种方式：

select @@var_name;

select @@session.var_name;

show session variables like “%var%”;

全局变量

使用set global varname=value;或者set @@global.varname=value;设置全局系统变量的值

其中，修改全局变量时需要root权限，且global不可省略。

查看所有的全局变量：

show global variables;

要想查看一个全局变量，有如下两种方式：

select @@global.var_name;

show global variables like “%var%”;

3、函数

存储过程和函数区别: 存储没有返回值，使用输出参数返回数据，可以有多个,函数有返回值，只能返回一个。

字符串函数

CHARSET(str) //返回字串字符集CONCAT (string2 [,... ]) //连接字串INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) //转换成小写LEFT (string2 ,length ) //从string2中的左边起取length个字符LENGTH (string ) //string长度，字节的长度LOAD_FILE (file_name ) //从文件读取内容LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置LPAD (string ,length ,pad ) //重复用pad加在string开头,直到字串长度为lengthLTRIM (string ) //去除前端空格REPEAT (string2 ,count ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_strRPAD (string ,length ,pad) //在str后用pad补充,直到长度为lengthRTRIM (string ) //去除后端空格STRCMP (string1 ,string2) //逐字符比较两字串大小,SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符UCASE (string2 ) //转换成大写RIGHT(string2,length) //取string2最后length个字符SPACE(count) //生成count个空格注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

数学函数

ABS (number2 ) //绝对值BIN (decimal_number) //十进制转二进制CEILING (number2 ) //向上取整CONV(number2,from_base,to_base) //进制转换FLOOR (number2 ) //向下取整FORMAT (number,decimal_places ) //保留小数位数HEX (DecimalNumber) //转十六进制POWER (number ,power ) //求指数RAND([seed]) //随机数ROUND (number [,decimals ]) //四舍五入,decimals为小数位数] 注：返回类型并非均为整数

日期函数

ADDTIME (date2 ,time_interval) //将time_interval天加到date2 秒ADDDATE(‘-04-15’,2); #2天CURRENT_DATE ( ) //当前日期CURRENT_TIME ( ) //当前时间CURRENT_TIMESTAMP ( ) //当前时间戳DATE (datetime ) //返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差DAY (date ) //返回日期的天DAYNAME (date ) //英文星期DAYOFWEEK (date ) //星期(1-7) ,1为星期天DAYOFYEAR (date ) //一年中的第几天EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串MAKETIME (hour ,minute ,second ) //生成时间串MONTHNAME (date ) //英文月份名NOW ( ) //当前时间SEC_TO_TIME (seconds ) //秒数转成时间STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) //两个时间差TIME_TO_SEC(time) //时间转秒数WEEK (date_time [,start_of_week ]) //第几周YEAR (datetime ) //年份DAYOFMONTH(datetime) //月的第几天HOUR(datetime) //小时

4、函数中常用的流程控制语句

ifcaseloopleaveiteraterepeatwhile

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套

a）if语句

语法：类似java中的 if...elseif...else....

if 条件1 then ...;elseif 条件2 then ...;else ...;end if;

重点：有 if 则必有 end if if语句可用在begin...end 中或外边。

if示例：

DROP FUNCTION IF EXISTS testIf;//判断函数是否存在存在则删除CREATE FUNCTION `testIf`(age INT) RETURNS VARCHAR(100)//固定写法创建函数定义返回值类型BEGIN //固定语法 begin ... end 块#DECLARE a INT DEFAULT 0; //declare声明变量俗称局部变量#DECLARE message VARCHAR(100) DEFAULT '';//declare声明变量#IF a < 18 then SET message = "你的age小于18";#elseif a > 60 THEN SET message ="你的age大于60";#else SET message = "age处于18和60之间";#end if;//if语句的结束固定写法DECLARE message VARCHAR(100) DEFAULT '';SET @a = age; // 俗称用户变量 set声明IF @a < 18 THEN SET message = "你的age小于18";ELSEIF @a > 60 THEN SET message ="你的age大于60";ELSE SET message = "age处于18和60之间";END IF;RETURN message;//返回值END

b）case语句

语法：

语法一：case when 条件1 then ...;when 条件2 then ...;else ...;end case;语法二：case 变量或表达式when 值1 then 语句1;when 值2 then 语句2;...;else 语句n;end case;

有case 必有end case,case语句可应用在begin end 中外

示例：

DROP FUNCTION IF EXISTS testCase;CREATE FUNCTION `testCase`(age INT) RETURNS VARCHAR(100)BEGINDECLARE message VARCHAR(100) DEFAULT '';SET @a = age;CASE WHEN @a < 18 THEN SET message = "你的age小于18";WHEN @a > 60 THEN SET message ="你的age大于60";ELSE SET message = "age处于18和60之间";END CASE;//case的结束固定写法RETURN CONCAT('你输入的age是',@a,',',message);END

c)loop和leave语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

loop语法格式：

[begin_label:] LOOP statement_list END LOOP [end_label]

begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

特点：

loop相当于java中的while(true){ }有loop 必有 end loop

简单的loop示例：

add_num: LOOP SET @count=@count+1; END LOOP add_num ;

由于loop没有停止循环的语句，所以此loop示例是一个死循环，要想停止循环，需要借助leave语句来跳出循环

//如果变量@count的值等于100 则停止循环add_num: LOOP SET @count=@count+1; IF @count=100 THEN LEAVE add_num; END IF;END LOOP add_num ;

简单示例：

DROP FUNCTION IF EXISTS testLoopAndLeave;CREATE FUNCTION testLoopAndLeave() RETURNS VARCHAR(1000)BEGINDECLARE TOTAL VARCHAR(1000) DEFAULT '';//存放loop循环中@count值的变化SET @count= 1;//声明变量@countadd_count:LOOPSET @count= @count+FLOOR(RAND()*10);//@count每次加上一个随机数SET TOTAL = CONCAT(TOTAL,',',@count);//记录@count值循环中的变化IF @count > 1000 THEN LEAVE add_count;//@count大于1000则停止loop循环END IF;END LOOP add_count;RETURN total;END

d)repeat语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT statement_list; //循环执行体UNTIL search_condition //满足条件结束体注意:until结束没有分号(容易加分号报错) END REPEAT [end_label]; //begin_label end_label 开始结束标识位两者一样哈

特点：

先执行后判断相当于java 中的do...while...语句有repeat 必有 end repeatuntil 后写的时候不要加分号begin_lable和end_label 用到leave或iterate时需要加上，否则可省略

简单示例：

DROP FUNCTION IF EXISTS testRepeat;CREATE FUNCTION testRepeat() RETURNS VARCHAR(1000)BEGINDECLARE d VARCHAR(1000) DEFAULT '0';//创建变量d,记录@a的变化过程SET @a=0;//声明变量amy_repeat:REPEATSET @a=@a+1;//repeat的循环体每次加1SET d = CONCAT(d,'-->',@a);//d变量记录UNTIL @a =100 //repeat有条件循环的结束条件满足则停止循环此语句后没有分号END REPEAT my_repeat;RETURN d;END

e)while语句

while语句也是有条件控制的循环语句。但while语句和repeat语句是不一样的,while语句是当满足条件时，执行循环内的语句,而repeat是先循环，满足until条件停止循环。

while语法：

[begin_label:] WHILE 条件 DO 满足条件的执行体END WHILE [end_label]

注意：

while是先判断后执行。有while 必有 end while 当需要用到 leave和iterate时，则需要加上begin_lable和end_lable，否则可省略不写。

简单示例：

DROP FUNCTION IF EXISTS testWhile;CREATE FUNCTION testWhile() RETURNS VARCHAR(1000)BEGINDECLARE detail VARCHAR(1000) DEFAULT '0';SET @num = 0;while @num < 50 DOset @num = @num + 2;SET detail = CONCAT(detail,'>',@num);END WHILE;RETURN detail;END

f)iterate语句

iterate语句也是用来跳出循环的语句。但是，iterate语句是跳出本次循环，然后直接进入下一次循环，iterate语句只可以出现在LOOP、REPEAT、WHILE语句内。

语法格式：iterate lable lable 是标识位即前面所说的 begin_lable 和end_lable

简单示例：

DROP FUNCTION IF EXISTS testLeaveAndIterate;CREATE FUNCTION testLeaveAndIterate() RETURNS VARCHAR(1000)BEGINDECLARE msg VARCHAR(1000) DEFAULT '1';set @age = 0;add_loop:LOOPSET @age = @age + 1;IF @age = 100 THEN LEAVE add_loop;ELSEIF MOD(@age,2) = 0 THEN ITERATE add_loop;ELSE SET msg = CONCAT(msg,',',@age);END IF;END LOOP add_loop;return msg;END

小结：

leave与iterate的区别：leave等同于java中的break iterate等同于java中的continue。有if 必有end if 有case 必有 end case有loop 必有 end loop 有repeat 必有end repeat 有while 必有end whiledeclare声明的变量要放在begin...end块的第一行写repeat语句时 until后的语句结束不要带分号（自己写的时候带分号一直报错...看了好久才发现）

删除函数：DROPFUNCTIONfunction_name

调用函数：SELECTfunction_name(parameter_value,...)

查看函数：SHOW FUNCTION STATUS

MYSQL存储过程

什么是存储过程？

简而言之，一组sql语句集，功能强大，可以实现比较复杂的逻辑功能。

存储过程和触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用。

优点：

提高代码的重用性简化操作减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程的语法

1、创建：

CREATE PROCEDURE 存储过程名(参数列表)BEGIN# 存储过程体（一组合法的SQL语句）END

2、参数列表

1) 参数列表包含三部分

参数模式参数名参数类型

in stuname varchar(20)

也可以写成stuname varchar(20) 但最好把 in 加上。

2) 参数模式

in：该参数可以作为输入，也就是该参数需要调用方传入值。out：该参数可以作为输出，也就是该参数可以作为返回值。inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

3、调用

CALL 存储过程名(实参列表);

4、删除

如果存在该存储过程则删除该存储过程。

drop procedure if exists 存储过程名称

重点：存储过程体中的每条sql语句的结尾要求必须加分号。

注意：如果存储过程体仅仅只有一句话，begin end可以省略。

注意：存储过程的结尾可以使用 delimiter 重新设置（一般如果存储过程中存在多个分号结尾，就可以使用delimiter）

5、mysql自定义函数所说的流程控制语句同样适用mysql的存储过程，不在叙述......

存储过程示例：

1、无参列表存储过程

DROP PROCEDURE if EXISTS test1;CREATE PROCEDURE `test1`()BEGININSERT INTO test_user(id,name) VALUES(UUID(),'name1'),(UUID(),'name2'),(UUID(),'name3'),(UUID(),'name4'),(UUID(),'name5'),(UUID(),'name6');END

看一个实际开发中遇到一个运用MYSQL存储过程实现按天建表的例子：

DROP PROCEDURE IF EXISTS my_test;CREATE PROCEDURE `my_test`(IN startDate VARCHAR(10),IN dateNum INT)BEGINdeclare k int;set k=0;SET @dateNum = dateNum; SET @startDate = startDate;while k < dateNum doset @num =k;set @dateName=DATE_ADD(startDate,INTERVAL @num DAY);set @tname=concat('my_test',DATE_FORMAT(@dateName,'%Y%m%d'));set @dwhe="(`id` bigint(11) NOT NULL AUTO_INCREMENT,`ipaddr` varchar(50) DEFAULT NULL COMMENT '',`ipaddrlong` decimal(50,0) DEFAULT NULL COMMENT '',`domain` varchar(255) DEFAULT NULL COMMENT '',`firstdomain` varchar(255) DEFAULT NULL COMMENT '',`first_time` datetime DEFAULT NULL COMMENT '',`last_time` datetime DEFAULT NULL COMMENT '',`visits_count` varchar(20) DEFAULT NULL COMMENT '',`create_time` timestamp NULL DEFAULT NULL COMMENT '',`update_time` timestamp NULL DEFAULT NULL COMMENT '',`is_check` char(1) DEFAULT NULL COMMENT '',PRIMARY KEY (`id`),UNIQUE KEY `ipaddrlong` (`ipaddr`,`firstdomain`) USING BTREE,KEY `domain_idx` (`domain`) USING BTREE,KEY `last_time_idx` (`last_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=3232405 DEFAULT CHARSET=utf8 COMMENT='';";set @sql_text:=concat('create table IF NOT EXISTS ',@tname,@dwhe);prepare stmt from@sql_text;execute stmt;set k=k+1;end while;END

引入这个例子除了看下存储过程，其实想记录下这个东西：

prepare stmt from@sql_text;execute stmt;

了解下mysql 存储过程中的 prepare语句：mysql 存储过程中的 prepare语句_嗯哼的博客-CSDN博客

2、带IN模式参数的存储过程

DROP PROCEDURE IF EXISTS testIn;CREATE PROCEDURE testIn(IN myName VARCHAR(20))BEGINselect * from test_user where name = myName;END#调用CALL testIn('name1');

注意：如果传参带有中文，如果上面这样会报字符转换错误，需要将VARCHAR(20)改成NVARCHAR(20)

3、模拟用户登陆成功与失败

DROP PROCEDURE IF EXISTS testUserLogin;CREATE PROCEDURE testUserLogin(IN username VARCHAR(20),IN pwd VARCHAR(20))BEGINDECLARE result INT DEFAULT 0;SELECT COUNT(1) FROM test_user WHERE name = username and password=pwd INTO result;SELECT IF(result > 0,'登陆成功','登陆失败') as result;END#调用CALL testUserLogin('name1','123456');

调用结果：

4、带IN OUT模式的存储过程

DROP PROCEDURE IF EXISTS testInOut;CREATE PROCEDURE testInOut(IN inId VARCHAR(64),OUT userName VARCHAR(64),OUT pwd VARCHAR(64))BEGINSELECT name,password FROM test_user where id = inId into userName,pwd;END#调用CALL testInOut('d03e536d-f747-11ea-997c-00ff5f20e2ec',@name,@pwd);#查询函数返回值 userName 和 pwdSELECT @name as name,@pwd as password;

@name和@pwd的查询结果：

5、INOUT模式的存储过程

DROP PROCEDURE IF EXISTS testInOut;CREATE PROCEDURE testInOut(INOUT a INT,INOUT b INT)BEGINSET a=a*2;SET b=b*3;END#调用SET @m=10;SET @n=10;CALL testInOut(@m,@n);#查询返回值SELECT @m,@n;

查询返回结果：

mysql自定义函数和存储过程都是完成特定功能的SQL集合，二者的区别？

区别：

调用不一样函数：select 函数名(参数) 存储过程：call 存储名(参数) 或者 call.存储名(参数)。参数：存储过程有IN OUT INOUT 三种模式的参数而自定义函数只有IN类的参数。返回值：存储过程可以有多个返回值，而函数只能有一个且函数声明的过程中需要定义返回值类型和写return语句，而存储过程不需要。存储过程一般单独执行而自定义函数往往作为其他sql的一部分去使用。存储过程可以有select update delete insert create 语句和一些换算而mysql自定义函数只能有select语句和一些换算。所以存储过程的功能比较强大。存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数（这个网上看的，后面这句不是很理解）

mysql游标

1、什么是游标？

游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集。

2、游标什么作用？

SELECT name,age from person where age>10;

在实际开发中后台代码调用这个sql语句，一般会返回一个List<Object>对象，进而通过java的遍历对这个集合做一系列的操作。那么在MYSQL中也有这么一个东西可以储存集合，这个东西就是游标。

注意：MySQL游标只能用于存储过程(和函数)。游标主要用于交互式应用。

3、游标的使用：

声明游标：这个过程实际上是没有遍历数据的，它只是定义要使用的select语句来获取数据。打开游标: 上面定义好后，那么这里就需要打开游标。这个过程用前面定义的select语句把数据实际检索出来。即这个步骤之后，我们就可以遍历游标中的数据了。遍历数据: 对于有数据的游标，根据需要取出各行的数据来进行一定的操作。关闭游标: 使用完游标后，一定要关闭游标。

4、游标的语法

a)声明游标

DECLARE cursor_name CURSOR FOR select_statement

cursor_name是游标名称，在一个块中游标名称是唯一的。select_statement是一个select语句(select语句不能有into子句)

b)打开游标

OPEN cursor_name ;

c)遍历游标

FETCH cursor_name INTO var_name ;

这个语句是获取游标当前指向的数据行，并将指针指向下一行。var_name 是定义的变量，此遍历是将select_statement中查询字段赋值给var_name

此处说遍历游标不合适，真正遍历游标是mysql自定义函数中的 loop或者repeat或者while循环语句。

d)关闭游标

CLOSE cursor_name ;

下面举例子来细品游标的使用：

有这么一张表

现在需要统计下用户名称包含test出现的次数：

DROP PROCEDURE IF EXISTS countUserLikeName;CREATE PROCEDURE countUserLikeName(IN param VARCHAR(64))BEGIN#n 存的是每次遍历的用户名称declare n varchar(20);# 拼装所有的满足查询的用户名称DECLARE allName VARCHAR(1000) DEFAULT '$';#满足查询条件的信息数量DECLARE total INT DEFAULT 0;# 创建结束标志变量DECLARE done int default false;#声明游标DECLARE myCursor CURSOR FOR SELECT name from test_user where name like CONCAT('%',param,'%');# 指定游标循环结束时的返回值DECLARE continue HANDLER for not found set done = true; #此处这个玩意可能不懂？？，后面会说#total变量初始化SET total=0;#打开游标OPEN myCursor;#采用loop去遍历游标add_loop:LOOP #loop遍历# 根据游标当前指向的一条数据插入到上面申明的局部变量中fetch myCursor into n;# 判断游标的循环是否结束if done then #if语句leave add_loop;# leave跳出游标循环end if; #有if 必有 end ifSET allName=CONCAT(allName,',',n); # 将满足查询条件的用户名称拼接到一起SET total = total + 1; #统计满足查询条件的用户数量END LOOP; #有loop 必有 end loop#关闭游标close myCursor; # 输出结果即存储过程的返回值select allName,total;END#调用存储过程CALL countUserLikeName('test');

调用结果：

虽然这样的结果用mysql的group by + group_concat就可以统计出来，这不是重点，只是想展示下游标的遍历。

强调下 fetch:

是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。

使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；

read_loop:loopfetch cur into n;set total = total + 1;end loop;

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

# 指定游标循环结束时的返回值DECLARE continue HANDLER for not found set done = true; #此处这个玩意可能不懂？？，后面会说

所以在loop循环中加上

if done then #if语句leave add_loop;# leave跳出游标循环end if; #有if 必有 end if

如果done的值是true，就结束循环。继续执行下面的代码。

MYSQL触发器

1、触发器概念

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。——百度百科

简而言之，就是一个 insert、update、delete这样的sql语句会触发触发器执行。触发器是特殊的存储过程，与存储过程的区别是存储过程的执行需要被调用而触发器是靠事件的触发去执行，除了语法上不一样，begin.......end块中都是一样的东西。

2、触发器语法

CREATE TRIGGER <触发器名> <BEFORE|AFTER> <INSERT|UPDATE|DELETE> ON <表名> FOR EACH RowBEGIN<触发器主体>END

触发器四个点：

监视对象--------table监视事件--------table的update、insert、delete触发时间--------before(操作前)、after(操作后)触发事件--------update、insert、delete

注意事项：

触发器绑定的实质是表中的每一行，当某一行发生update、insert、delete操作就会触发触发器。如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)触发器名称对于一个table来说是唯一的，跨表或者跨库可以有相同名称的触发器每一个table最多支持6个触发器： before/update、after/update、before/insert、after/insert、before/delete、after/delete

3、触发器的引用行变量

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

语法：old/new.字段名

在触发目标上执行insert操作后会有一个新行，如果在触发事件中需要用到这个新行的变量，可以用new关键字表示在触发目标上执行delete操作后会有一个旧行，如果在触发事件中需要用到这个旧行的变量，可以用old关键字表示在触发目标上执行update操作后原纪录是旧行，新记录是新行，可以使用new和old关键字来分别操作

示例展示：

业务需求：用户增删改的时候，产生操作日志信息

涉及到表：用户表(user)、日志表(user_log)、日志详情表(user_log_detail)

新增删除用户的时候要在user_log插入对应的日志信息。修改的时候也要在user_log中插入一条日志信息，且在 user_log_detail插入详情信息(即修改字段、修改前什么值、修改后什么值)

表结构如下:

##用户表CREATE TABLE `user` (`id` varchar(64) NOT NULL,`name` varchar(64) DEFAULT NULL,`phone` varchar(64) DEFAULT NULL,`address` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;##日志表CREATE TABLE `user_log` (`id` varchar(64) NOT NULL,`user_id` varchar(255) DEFAULT NULL,`type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '操作类型',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;##日志详情表CREATE TABLE `user_log_detail` (`id` varchar(64) NOT NULL,`user_log_id` varchar(255) DEFAULT NULL,`col` varchar(255) DEFAULT NULL COMMENT '列名称',`old` varchar(255) DEFAULT NULL COMMENT '修改前字段值',`new` varchar(255) DEFAULT NULL COMMENT '修改后字段值') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

触发器创建：

#新增用户后插入日志DROP TRIGGER IF EXISTS insert_user_after;CREATE TRIGGER insert_user_after after INSERT ON user FOR EACH ROWBEGININSERT INTO user_log(id,user_id,type)VALUES(UUID(),new.id,'insert(after)');END;#新增用户前插入日志DROP TRIGGER IF EXISTS insert_user_before;CREATE TRIGGER insert_user_before before INSERT ON user FOR EACH ROWBEGININSERT INTO user_log(id,user_id,type)VALUES(UUID(),new.id,'insert(before)');END;#删除用户前插入日志DROP TRIGGER IF EXISTS delete_user_before;CREATE TRIGGER delete_user_before before delete ON user FOR EACH ROWBEGININSERT INTO user_log(id,user_id,type)VALUES(UUID(),old.id,'delete(before)');END;#删除用户后插入日志DROP TRIGGER IF EXISTS delete_user_after;CREATE TRIGGER delete_user_after after delete ON user FOR EACH ROWBEGININSERT INTO user_log(id,user_id,type)VALUES(UUID(),old.id,'delete(after)');END;#修改用户后插入日志表和日志详情表DROP TRIGGER IF EXISTS update_user_after;CREATE TRIGGER update_user_after after update ON user FOR EACH ROWBEGINDECLARE name VARCHAR(64) DEFAULT '';DECLARE phone VARCHAR(64) DEFAULT '';DECLARE address VARCHAR(64) DEFAULT '';SET @log_id = UUID();INSERT INTO user_log(id,user_id,type)VALUES(@log_id,new.id,'update(after)');IF old.name <> new.name THEN INSERT INTO user_log_detail VALUES(UUID(),@log_id,'用户名称',old.name,new.name);END IF;IF old.phone <> new.phoneTHEN INSERT INTO user_log_detail VALUES(UUID(),@log_id,'手机号',old.phone,new.phone);END IF;IF old.address <> new.addressTHEN INSERT INTO user_log_detail VALUES(UUID(),@log_id,'用户地址',old.address,new.address);END IF;END;

新增用户：INSERT INTO user(id,name,phone,address) values(UUID(),'test','999999','北京市');

结果：

删除用户：INSERT INTO user(id,name,phone,address) values(UUID(),'test','999999','北京市');

结果：

修改用户：update user SET name='test_update',phone='66666_update',address='俺是那嘎达的' where id = '67ef0412-f97f-11ea-8349-00ff5f20e2ec';

结果：

小结：

触发器是对某张table的insert、update、delete操作(事件)进行绑定的一种特殊的存储过程，依靠这些事件的发生去触发执行触发器，触发器实质上是绑定数据表的行，新增的行数据保存在new上、删除的行数据保存在old上、修改的行原始数据保存在old上，新的数据保存在new上。调用：old.col_name new.col_name

PS:MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

测试触发器insert本表：

DROP TRIGGER IF EXISTS insert_user_after;CREATE TRIGGER insert_user_after after INSERT ON user FOR EACH ROWBEGININSERT INTO user_log(id,user_id,type)VALUES(UUID(),new.id,'insert(after)');#插入本表user的操作INSERT INTO user(id,name,phone,address) values(UUID(),'test123','123','123');END;##执行insert语句去触发insert_user_after触发器INSERT INTO user(id,name,phone,address) values(UUID(),'test','999999','北京市');报错信息：[SQL]INSERT INTO user(id,name,phone,address) values(UUID(),'test','999999','北京市');[Err] 1442 - Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

MYSQL事件(定时任务)

在MySQL 5.1中新增了一个特色功能事件调度器（Event Scheduler），简称事件。它可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL的事件可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下是非常实用的。

事件调度器是定时触发执行的，从这个角度上看也可以称作是“临时触发器”。但是它与触发器又有所区别，触发器只针对某个表产生的事件执行一些语句，而事件调度器则是在某一段（间隔）时间执行一些语句。

查看事件调度器是否开启

事件由一个特定的线程来管理。启用事件调度器后，拥有SUPER权限的账户执行SHOW PROCESSLIST就可以看到这个线程了。

查看事件是否开启

SHOW VARIABLES LIKE 'event_scheduler';SELECT @@event_scheduler;SHOW PROCESSLIST;

开启或关闭事件调度器

使用SET GLOBAL命令可以开启或关闭事件。将event_scheduler参数的值设置为ON，则开启事件；如果设置为OFF，则关闭事件。

方法一：

-- 开启事件调度器SET GLOBAL event_scheduler = ON;-- 关闭事件调度器SET GLOBAL event_scheduler = OFF;-- 查看事件调度器状态SHOW VARIABLES LIKE 'event_scheduler';

注意：如果想要始终开启事件，那么在使用SET GLOBAL开启事件后，还需要在my.ini（Windows系统）/f（Linux系统）中添加event_scheduler=on。因为如果没有添加，MySQL重启事件后又会回到原来的状态。

方法二：通过更改配置文件

在MySQL的配置文件my.ini（Windows系统）/f（Linux系统）中，找对[mysqld]，然后在下面添加以下代码开启事件。

# 事件调度器启动状态event_scheduler = on

在配置文件中添加代码并保存文件后，还需要重新启动MySQL服务器才能生效。通过该方法开启事件，重启MySQL服务器后，不恢复为系统默认的状态。

创建时间的语法格式

CREATE[DEFINER={user | CURRENT_USER}]EVENT [IF NOT EXISTS] event_nameON SCHEDULE schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE | DISABLE ON SLAVE][COMMENT 'comment']DO event_body;

参数释义：

这个参数要详细了解下

1、ON SCHEDULE schedule，参数schedule的值为一个AS子句，用于指定事件在某个时刻发生，其语法格式如下：

AT timestamp [+ INTERVAL interval] ...| EVERY interval[STARTS timestamp [+ INTERVAL interval] ...][ENDS timestamp [+ INTERVAL interval] ...]

参数说明：

（1）timestamp：表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。

（2）EVERY子句：用于表示事件在指定时间区间内每隔多长时间发生一次，其中 STSRTS子句用于指定开始时间；ENDS子句用于指定结束时间。

（3）interval：表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示4周；使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。间隔的距离用DATE_ADD()函数来支配。

interval参数值的语法格式如下：

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

一些常用的时间间隔设置：

（1）每隔5秒钟执行

ON SCHEDULE EVERY 5 SECOND

（2）每隔1分钟执行

ON SCHEDULE EVERY 1 MINUTE

（3）每天凌晨1点执行

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

（4）每个月的第一天凌晨1点执行

ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)

（5）每 3 个月，从现在起一周后开始

ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK

（6）每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

（7）在yyyy-MM-dd hh:mm:ss执行

on schedule at '-10-01 21:50:00' -- 在10月1日，晚上9点50执行

示例1：创建名称为event_user的事件，用于每隔5秒钟向数据表tb_user（用户信息表）中插入一条数据。

（1）首先创建tb_user（用户信息表）。

-- 创建用户信息表CREATE TABLE IF NOT EXISTS tb_user(id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',name VARCHAR(30) NOT NULL COMMENT '用户姓名',create_time TIMESTAMP COMMENT '创建时间') COMMENT = '用户信息表';

（2）创建事件。

-- 创建事件CREATE EVENT IF NOT EXISTS event_userON SCHEDULE EVERY 5 SECONDON COMPLETION PRESERVECOMMENT '新增用户信息定时任务'DO INSERT INTO tb_user(name,create_time) VALUES('pan_junbiao的博客',NOW());

执行结果：

示例2：创建一个事件，实现每个月的第一天凌晨1点统计一次已经注册的会员人数，并插入到统计表中。

（1）创建名称为p_total的存储过程，用于统计已经注册的会员人数，并插入到统计表tb_total中。

CREATE PROCEDURE p_total()BEGINDECLARE n_total INT default 0;SELECT COUNT(*) INTO n_total FROM db_database11.tb_user;INSERT INTO tb_total (userNumber,createtime) VALUES(n_total,NOW());END;

（2）创建名称为e_autoTotal的事件，用于在每个月的第一天凌晨1点调用存储过程。

CREATE EVENT IF NOT EXISTS e_autoTotalON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)ON COMPLETION PRESERVE ENABLEDO CALL p_total();

查询事件

在MySQL中可以通过查询information_schema.events表，查看已创建的事件。其语句如下：

SELECT * FROM information_schema.events;

修改事件

在MySQL 5.1及以后版本中，事件被创建之后，还可以使用ALTER EVENT语句修改其定义和相关属性。其语法如下：

ALTER[DEFINER={user | CURRENT_USER}]EVENT [IF NOT EXISTS] event_nameON SCHEDULE schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE | DISABLE ON SLAVE][COMMENT 'comment']DO event_body;

ALTER EVENT语句与CREATE EVENT语句基本相同。另外ALTER EVENT语句还有一个用法就是让一个事件关闭或再次活动。