MYSQL存储过程梳理

最新推荐文章于 2024-04-09 09:07:12 发布

「已注销」

最新推荐文章于 2024-04-09 09:07:12 发布

阅读量347

点赞数

本文链接：https://blog.csdn.net/lisen2285884335/article/details/103505143

版权

MySQL IF ELSEIF ELSE语句

MySQL IF语句示例

IF表达式

作为表达式的if也可以用CASE when来实现：

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

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

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

「已注销」

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL存储过程梳理

目录事件调度器事件任务存储过程错误处理MySQL 存储过程 if语句MySQL IF语句语法MySQL IF ELSE语句MySQL IF ELSEIF ELSE语句MySQL IF语句示例IF表达式作为表达式的if也可以用CASE when来实现：IFNULL(expr1,expr2)IF ELSE 做为流程控制语句使用MySql中创建定...
复制链接

扫一扫