目录
事件调度器
事件任务
存储过程
错误处理
MySQL 存储过程 if语句
MySQL IF语句语法
MySQL IF ELSE语句
MySQL IF ELSEIF ELSE语句
MySQL IF语句示例
IF表达式
作为表达式的if也可以用CASE when来实现:
IFNULL(expr1,expr2)
IF ELSE 做为流程控制语句使用
MySql中创建定时任务
MySQL取得某一范围随机数
①直接取值
②创建函数
MySQL5.1.6起增加了事件调度器(Event Scheduler),可用来做定时执行某些特定任务,用于取代原先只能由操作系统的计划任务来执行的工作。MySQL的事件调度器可以精确到每秒执行一个任务,而操作系统的计划任务只能精确到分钟级别。对于对数据实时性要求比较高的应用非常合适。
事件调度器也称为临时触发器(Temporal Triggers),因为事件调度器是基于特定时间周期触发来执行某些任务,而触发器(Triggers)是基于某个表所产生的事件触发的。
MySQL定时任务的实现方式有两种:
- 使用MySQL的
event
定时任务
使用MySQL的事件计划,首先需要在服务器开启event_scheduler
后才能处理。 - 使用Linux的定时任务
crontab
如何开启事件计划呢?
$ SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set
如果执行命令后返回值为OFF
则表示目前事件计划是处于关闭的状态。
开启的方式也分为两种,临时方式使用命令行或脚本操作,永久修改则需要修改MySQL主配置文件my.ini
在其中添加event_schduler=1
的配置后重启MySQL。
临时性修改只要不重启MySQL在当前运行状态下会直接生效,一旦重启后则失效。
$ SET GLOBAL event_scheduler = ON;
$ SET @@global.event_scheduler = ON;
$ SET GLOBAL event_scheduler = 1;
$ SET @@global.event_scheduler = 1;
事件调度器
要保证能够执行事件,就必须保证事件计划是开启状态,事件计划默认为关闭状态。
# 查看MySQL版本
$ SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.18-log |
+------------+
1 row in set
# 事件计划是否开启
$ SHOW VARIABLES LIKE 'event%'
$ SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set
# 查看事件任务是否开启
$ SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set
# 开启事件计划
$ SET GLOBAL event_scheduler=1
$ SET GLOBAL event_scheduler=ON
duler=1;
Query OK, 0 rows affected
# 关闭事件计划
$ SET GLOBAL event_scheduler=0
在真实开发环境中会遇到MySQL服务重启或断电的情况,此时会出现事件调度器被关闭的情况。所有事件都不再起作用,解决的方式需要在MySQL的配置文件mysql.ini
中加入event_scheduler=ON
的配置。
事件任务
事件任务
# 查看事件任务
$ SHOW EVENTS;
Empty set
# 查看事件任务错误 - 权限不足
$ SELECT * FROM mysql.event
1142 - SELECT command denied to user 'username'@'127.0.0.1' for table 'event'
# 开启事件任务
$ ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE
# 关闭事件任务
$ ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE
# 删除事件
$ DROP EVENT [IF EXISTS] event_name
设置定时任务执行SQL语句
例如:从当日开始每天凌晨4点删除fight表超过一个月的数据
计划任务
DROP EVENT IF EXISTS event_fight_delete;
CREATE EVENT event_fight_delete
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(CURDATE() + 1), INTERVAL 4 HOUR)
DO
BEGIN
DELETE FROM center_fight WHERE 1=1 AND createdate < DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
END
设置定时任务调用存储过程
# 若计划任务存在则删除
DROP EVENT IF EXISTS event_name
# 创建计划任务
CREATE EVENT event_name
ON SCHEDULE EVERY 10 second
STARTS TIMESTAMP '2018-07-12 00:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
CALL producer()
END
参数说明
-
ON SCHDULE schduler
定义执行的时间和时间间隔 -
ON COMPLETION [NOT] PRESERVE
定义事件是一次性执行还是永久执行,默认为一次性执行,即NOT PRESERVE
。
在事件中ON SCHEDULE
计划任务中有2种设定的方式
- 用来完成单次计划任务。
AT 时间戳
eg:5天后
AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
eg:某时间点
AT TIMESTAMP '2018-07-12 12:00:00'
- 用来完成重复的计划任务
EVERY 时间(单位)的数量 时间单位 [STARTS 时间戳] [ENDS时间戳]
eg:每隔1秒
EVERY 1 SECOND
eg:每隔10分钟。
EVERY 10 MINUTE
eg:从2018-08-01 12:00:00开始每隔1天
EVERY 1 DAY STARTS '2018-08-01 12:00:00'
EVERY 10 second STARTS TIMESTAMP '2018-08-01 12:00:00'
eg:5天后开启每天定时处理
EVERY 1 DAY START CURRENT_TIMESTAMP + INTERVAL 5 DAY
eg:每天定时处理5天后停止
EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
在两种计划任务中,时间戳可以是任意的TIMESTAMP
和DATETIME
数据类型,时间戳需要大于当前时间。
在重复的计划任务中,时间(单位)的数量可以是任意非空(NOT NULL)的整数形式,时间单位是关键词:YEAR
、MONTH
、DAY
、HOUR
、MINUTE
、SECOND
...
[ON COMPLETION [NOT] PRESERVE]
ON COMPLETION
参数表示“当这个事件不会再发生的时候”,即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS
阶段。而PRESERVE
的作用是使事件在执行完毕后不会被DROP
掉,建议使用该参数,以便于查看EVENT
具体信息。
CREATE DEFINER=`root`@`localhost` EVENT `event_knapsacks_remember_expire`
ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-13 15:09:49'
ON COMPLETION PRESERVE ENABLE
COMMENT '每分钟检测背包中换牌卡到期并每日自动减少'
DO
BEGIN
CALL produce_knapsacks_remember_expire();
END
存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS procedure_name
CREATE PROCEDURE procedure_name()
BEGIN
INSERT INTO procedure_name(name, create_time) VALUES('name_value', now())
END $$
DELIMITER ;
-- 存储过程 produce_knapsacks_remember_expire
-- 作用:判断背包中道具记牌卡,是否过期,且每日减一。
CREATE DEFINER=`root`@`localhost` PROCEDURE `produce_knapsacks_remember_expire`()
BEGIN
DECLARE pk INT DEFAULT 0;
DECLARE sec INT DEFAULT 0;
DECLARE days INT DEFAULT 0;
DECLARE expire INT DEFAULT 0;
DECLARE mc CURSOR FOR (SELECT id,TIMESTAMPDIFF(SECOND,effect_time,NOW()) AS diff,TIMESTAMPDIFF(SECOND,NOW(),expire_time) AS expire FROM knapsacks WHERE name='REMEBER' AND expired=0);
OPEN mc;
ml:LOOP
FETCH mc INTO pk,sec,expire;
IF(expire <= 0) THEN
UPDATE `knapsacks` SET `expired`=1 WHERE `id`=pk;
ELSE
IF(sec>0 && sec<=86400) THEN
SET days = 1;
ELSEIF(sec>86400) THEN
SET days=CEILING(sec/86400);
END IF;
UPDATE `knapsacks` SET `quantity`=`purchase`-days,`consume`=days WHERE `id`=pk;
END IF;
COMMIT;
END LOOP ml;
CLOSE mc;
END
错误处理
出现错误
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方案
$ SELECT VERSION();
$ @@sql_mode;
$ SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY' , ''));
出现提示
ERROR
# 在SQL中查询计划事件的状态
$ SHOW VARIABLES LIKE 'event_scheduler'
# 在mysql程序的目录下找到my.ini文件添加
$ vim my.ini
event_scheduler = 1
# 保存后重启mysql服务
# 用脚本来实现
# 开启event_scheduler sql指令:
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
$ 关闭event_scheduler指令:
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
MySQL 存储过程 if语句
MySQL IF
语句允许您根据表达式的某个条件或值结果来执行一组SQL语句。 要在MySQL中形成一个表达式,可以结合文字,变量,运算符,甚至函数来组合。表达式可以返回TRUE
,FALSE
或NULL
,这三个值之一。
MySQL IF语句语法
下面说明了IF
语句的语法:
IF expression THEN statements; END IF;
如果表达式(expression
)计算结果为TRUE
,那么将执行statements
语句,否则控制流将传递到END IF
之后的下一个语句。
以下流程图演示了IF
语句的执行过程:
MySQL IF ELSE语句
如果表达式计算结果为FALSE
时执行语句,请使用IF ELSE
语句,如下所示:
IF expression THEN statements; ELSE else-statements; END IF;
以下流程图说明了IF ELSE
语句的执行过程:
MySQL IF ELSEIF ELSE语句
如果要基于多个表达式有条件地执行语句,则使用IF ELSEIF ELSE
语句如下:
IF expression THEN statements; ELSEIF elseif-expression THEN elseif-statements; ... ELSE else-statements; END IF;
如果表达式(expression
)求值为TRUE
,则IF
分支中的语句(statements
)将执行;如果表达式求值为FALSE
,则如果elseif_expression
的计算结果为TRUE
,MySQL将执行elseif-expression
,否则执行ELSE
分支中的else-statements
语句。具体流程如下
MySQL IF语句示例
以下示例说明如何使用IF ESLEIF ELSE
语句,GetCustomerLevel()
存储过程接受客户编号和客户级别的两个参数。
首先,它从customers
表中获得信用额度
然后,根据信用额度,它决定客户级别:PLATINUM
, GOLD
和 SILVER
。
参数p_customerlevel
存储客户的级别,并由调用程序使用。
USE yiibaidb; DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; END$$
以下流程图演示了确定客户级别的逻辑
IF表达式
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
select *,if(sva=1,"男","女") as ssva from taname where sva != ""
作为表达式的if也可以用CASE when来实现:
select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''
在第一个方案的返回结果中, value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为 NULL。
例如:
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol
将输出one
IFNULL(expr1,expr2)
假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。
mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'
IFNULL(expr1,expr2)
的默认结果值为两个表达式中更加“通用”的一个,顺序为STRING、 REAL或 INTEGER。
IF ELSE 做为流程控制语句使用
if实现条件判断,满足不同条件执行不同的操作,这个我们只要学编程的都知道if的作用了,下面我们来看看mysql 存储过程中的if是如何使用的吧。
IF search_condition THEN statement_list [ELSEIF search_condition THEN] statement_list ... [ELSE statement_list] END IF
与PHP中的IF语句类似,当IF中条件search_condition成立时,执行THEN后的statement_list语句,否则判断ELSEIF中的条件,成立则执行其后的statement_list语句,否则继续判断其他分支。当所有分支的条件均不成立时,执行ELSE分支。search_condition是一个条件表达式,可以由“=、<、<=、>、>=、!=”等条件运算符组成,并且可以使用AND、OR、NOT对多个表达式进行组合。
例如,建立一个存储过程,该存储过程通过学生学号(student_no)和课程编号(course_no)查询其成绩(grade),返回成绩和成绩的等级,成绩大于90分的为A级,小于90分大于等于80分的为B级,小于80分大于等于70分的为C级,依次到E级。那么,创建存储过程的代码如下:
create procedure dbname.proc_getGrade (stu_no varchar(20),cour_no varchar(10)) BEGIN declare stu_grade float; select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no; if stu_grade>=90 then select stu_grade,'A'; elseif stu_grade<90 and stu_grade>=80 then select stu_grade,'B'; elseif stu_grade<80 and stu_grade>=70 then select stu_grade,'C'; elseif stu_grade70 and stu_grade>=60 then select stu_grade,'D'; else select stu_grade,'E'; end if; END
注意:IF作为一条语句,在END IF后需要加上分号“;”以表示语句结束,其他语句如CASE、LOOP等也是相同的。
MySql中创建定时任务
1.需求描述
每天0时定时调用存储过程
2.创建定时任务
方式一
SELECT @@global.sql_mode;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ##当前session变量生效
-- 创建每天0时执行一次的定时任务,调用存储过程
CREATE DEFINER=`root`@`localhost` EVENT IF NOT EXISTS event_call_proc_modify_primary_key_value
-- 每天0时执行一次
-- ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE,'00:00:00')
-- 每3秒执行一次
ON SCHEDULE EVERY 3 SECOND STARTS TIMESTAMP(CURRENT_DATE,'00:00:00')
ON COMPLETION PRESERVE ENABLE
DO
CALL proc_modify_primary_key_value('primary_key_generator_table','value','1','name','PK_CUSTOMER_ID')
-----------------------------------------------------------------------------------------------------------
方式二
-- 创建每天0时执行一次的定时任务,调用存储过程
CREATE DEFINER=`root`@`localhost` EVENT IF NOT EXISTS event_call_proc_modify_primary_key_value
-- 每天0时执行一次
-- ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE,'00:00:00')
-- 每3秒执行一次
ON SCHEDULE EVERY 3 SECOND STARTS TIMESTAMP(CURRENT_DATE,'00:00:00')
ON COMPLETION PRESERVE
DO
CALL proc_modify_primary_key_value('primary_key_generator_table','value','1','name','PK_CUSTOMER_ID')
3.开启定时任务
方式一
-- 开启定时器
SET GLOBAL event_scheduler=1;
-----------------------------------------------------------------------------------------------------------
方式二
-- 开启定时器
SET GLOBAL event_scheduler=1;
-- 开启事件任务
ALTER EVENT event_call_proc_modify_primary_key_value ENABLE;
4.查看定时任务
-- 查看定时任务
SELECT * FROM information_schema.`EVENTS`;
5.删除定时任务
-- 删除定时任务
DROP EVENT event_call_proc_modify_primary_key_value;
这里我们要完成的定时任务,比较简单
效果:每过X秒 某一些用户积分 加X
第一步:我们先创建一个表和插入数据
/* 创建一个表 */
CREATE TABLE `test_name` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键,自增' ,
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '用户名' ,
`integral` int(11) NULL COMMENT '积分' ,
`is_adm` tinyint(1) NULL COMMENT '管理员,1-是,0-不是' ,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
CHECKSUM=0
DELAY_KEY_WRITE=0
;
/* 添加数据 */
INSERT INTO `test_name` VALUES ('1', '会员1', '0', '1');
INSERT INTO `test_name` VALUES ('2', '会员2', '0', '0');
INSERT INTO `test_name` VALUES ('3', '会员3', '0', '0');
看看效果:
ok,表和数据做好了,我们说一下需求:
需求1:没经过2秒管理员的积分加1,字段is_adm为1的则是管理员
以上为添加表和数据,并提出需求
--------------------------------分割线--------------------------------
以下内容,我们会提到如何启动定时任务,来完成我们的需求
首先第一步,我们要检查我们数据库,是否开启了event(事件)
SHOW VARIABLES LIKE 'event_scheduler' #查看状态
运行以上代码,如果你的结果,Value字段的值为"OFF",代表你的事件属于关闭状态,那么我们就要开启它,运行以下代码:
SET GLOBAL event_scheduler = ON; #ON开启,OFF关闭
运行之后,再返回上一个,再次检查事件的状态,当结果为"ON",则代表你的事件已开启,我们就继续往下走。。
既然是定时任务,那么我们就要封装一个函数,流程是这样的:
- 封装一个函数
- 封装一个事件
- 事件开始运行,满足条件,调用 1(封装的函数)
那么我们先来封装一个函数:
#写一个函数 begin------------
CREATE PROCEDURE test()
BEGIN
update test_name SET integral= integral + 1 WHERE is_adm = 1;
END;
#写一个函数 end------------
运行以上代码成功之后,大家不用提心吊胆,不会立刻修改你的数据,因为这里只是封装了一个函数,我们并没有调用它,它仅仅是存在。所以需要写一个事件,来调用它
#写一个事件 begin------------
create event if not exists e_test
on schedule every 2 second
on completion preserve
do call test();
#写一个事件 end------------
运行以上代码,成功之后,大家也别着急,还不会立刻运行此事件。虽然在上面,我们已经开启了event。但是我们还要单个来运行,指定一个事件来运行,就跟访问接口一样,指定一个接口。。。
#开启事件
alter event e_test ON
COMPLETION PRESERVE ENABLE;
这里能看见,开启事件,指定的是 e_test 这个事件名字,跟我们上面创建的是一样的,那么运行这一段代码之后,你会发现,你的表 test_name 里,is_adm = 1 的行,字段为 integral(积分) 的会每 2秒 加1
那么一个简单的定时任务,我们完成了。
噢,对了,关闭单个事件,使用以下的代码:
#关闭事件
alter event e_test ON
COMPLETION PRESERVE DISABLE;
运行之后,就会停止对 e_test 事件的使用
以上需要注意:
- event默认情况下,都是OFF关闭状态,在MYSQL配置里可以找到,如果你在配置里修改成ON,就会默认成ON了
- 关闭数据库之后,如果你的event还是OFF,也会因为配置里默认的OFF因为,而停止
- 当event关闭之后,你的单个事件属于开启状态,那么当你开启event之后,单个事件状态依旧是开启的
- 如果你要关闭某个事件,请使用最后一个“关闭事件”的代码来关闭,不要使用event关闭,会影响到其他的事件
- event如同是总闸,关闭之后,所有定义的事件断电
MySQL取得某一范围随机数
①直接取值
若要在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。
例如, 若要在7 到 12 的范围(包括7和12)内得到一个随机整数, 可使用以下语句:
SELECT FLOOR(7 + (RAND() * 6));
②创建函数
CREATE FUNCTION rand_num ( start_num INTEGER, end_num INTEGER
) RETURNS INTEGER
BEGIN
RETURN FLOOR(start_num + RAND() * (end_num - start_num + 1));
END;
比如,要获取1-9的随机数,如此调用即可:
select rand_num(1, 9);