MySql数据库存储过程实战

   Mysql存储过程的实战积累

一．一个简单存储过程

delimiter$$

create procedure testa()

begin

  select uid from hxf where uid=2;

end;

$$

delimiter;  // 把命令行模式下的 $$ 修改为 ; 表示结束符

 

存储过程：

1.创建格式：create procedure 存储过程名

2.包含一个以上代码块，代码块使用begin和end 之间

3.在命令行中创建需要定义分隔符 delimiter $$

4.存储过程调用使用call命令

二．存储过程的变量

1需求:编写存储过程，使用变量取uid=2的用户名

Delimiter $$

create procedure testa()

Begin

Declare my_uname varchar(32) default  ‘’;

  set my_uname=‘hxf’;

  select uname into my_uname from users where uid=2;

  select my_uname;

end;

$$

Delimiter ;

特点：

1.变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用

2.变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能指定默认值、字符集和排序规则等

3.变量可以通过set来赋值，也可以通过select into的方式赋值

4.变量需要返回，可以使用select语句,如：select 变量名

2存储过程变量应用示例

需求：统计表hxf,users的行数量和users表中最早，最晚的注册时间。

Delimiter $$

create procedure stats_user()

Begin

     begin

          Declare hxf_sum int default 0;

          Declare users_sum int default 0;

          select count(*) into hxf_sum from hxf;

          select count(*) into users_sum from users;

          select hxf_sum,users_sum;

     end;

 

     begin

         Declare max_regtime  timestamp;

         Declare min_regtime  timestamp;

         select max(regtime),min(regtime)  into max_regtime,min_regtime from users ;

         select  hxf_sum,users_sum,max_regtime,min_regtime;

     end;

end;

$$

Delimiter ;

 

1.变量是有作用域的，作用范围在begin与end块之间，end结束变量的作用范围即结束。

2.需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前。

3.传参变量是全局的，可以在多个块之间起作用

三．存储过程的参数

1存储过程的传入参数IN

需求：编写存储过程，传入uid，返回该用户的uname

Delimiter $$

create procedure testa(my_uid int)

Begin

  Declare my_uname varchar(32) default  ‘’;

  select uname into my_uname from users where uid=my_uid;

  select my_uname;

end;

$$

Delimiter ;

 

1.传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显式指定为IN，那么默认就是IN类型。

2.IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回

3.如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

 

2存储过程的传出参数OUT

需求：调用存储过程时，传入uid返回该用户的uname

Delimiter $$

create procedure testa(IN my_uid int,OUT my_uname varchar(32))

Begin

select uname into my_uname from users where uid=my_uid;

  select my_uname;

end;

$$

Delimiter ;

 

1.传出参数：在调用存储过程中，可以改变其值，并可返回

2.OUT是传出参数，不能用于传入参数值

3.调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量

4.如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

 

3存储过程的可变参数INOUT

需求：调用存储过程时，参数my_uid和my_uname,既是传入，也是传出参数

Delimiter $$

create procedure testa(INOUT my_uid int,INOUT my_uname varchar(32))

Begin

  set my_uid=2;

  set my_uname=‘hxf3’;

  select uid,uname into my_uid,my_uname from users where uid=my_uid;

  select my_uid,my_uname;

end;

$$

Delimiter ;

 

1.可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值。

2.INOUT参数集合了IN和OUT类型的参数功能

3.INOUT调用时传入的是变量，而不是常量

四．存储过程条件语句

1存储过程的条件语句

需求：编写存储过程，如果用户uid是偶数则就给出uname,其它情况只返回uid

 

Delimiter $$

create procedure testa(IN my_uid int )

Begin

Declare my_uname varchar(32) default  ‘’;

  

  if(my_uid%2=0)

  then

     select uname into my_uname from users where uid=my_uid;

     select my_uname;

  else

     select my_uid;

  end if;

end;

$$

Delimiter ;

 

1.条件语句最基本的结构：if() then …else …end if;

2.If判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

1. 存储过程的条件语句应用示例

需求：根据用户传入的uid参数判断：

（1）如果用户状态status为1，则给用户score加10分

（2）如果用户状态status为2，则给用户score加20分

（3）其它情况加30分

 

Delimiter $$

create procedure addscore(IN my_uid int )

Begin

Declare my_status int default 0;

 

Select status into my_status from users where uid=my_uid;

  

  if(my_status =1)

  then

     update users set score=score+10 where uid=my_uid;

  elseif(my_status =2)

  then

     update users set score=score+20 where uid=my_uid;

  else

    update users set score=score+30 where uid=my_uid;

  end if;

end;

$$

Delimiter ;

1. 小结

1.条件语句基本结构：if() then …else…end if;

1. 2.多条件判断结构：
2.      if()
3.      then
4.         …
5.      elseif()
6.      then
7.         ….
8.       else
9.          …
10.       end if

五存储过程循环语句

1. While循环语句

需求：使用循环语句，向表testa(uid)中插入10条uid连续的记录。

Delimiter $$

create procedure insertdata1()

Begin

Declare i int default  0;

  

  while(i<=10) do

  begin

     select i;

     set i=i+1;

     insert into testa(uid) values(i);

  end ;

End while;

End;

$$

Delimiter ;

 

1.while语句最基本的结构：while() do…end while;

2.while判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

 

2 repeat循环语句

 

需求：使用repeat循环向表testa插入10条uid连续的记录

 

Delimiter $$

create procedure insertdata2()

Begin

Declare i int default  100;

  

  repeat

  begin

     select i;

     set i=i+1;

     insert into testa(uid) values(i);

end ;

until i>=110

End repeat;

End;

$$

Delimiter ;

 

1.repeat语句最基本的结构：repeat…until …end repeat;

2.until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

只有当until语句为真时，循环结束。

• 存储过程游标的使用

什么是游标

需求：编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名。

 

Delimiter $$

create procedure testcursor()

Begin

Declare stopflag int default  0;

Declare my_uname varchar(20);

Declare uname_cur cursor for select uname from users where uid%2=0 ;

#1.游标是保存查询结果的临时内存区域，

#2.游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集

declare continue handler for not found set stopflag=1;

#3.当游标变量中保存的结果都查询一遍（遍历），到达结尾，把变量stopflag设置为1

#用于循环中判断是否结束

 

Open uname_cur; #打开游标

Fetch uname_cur into my_uname; #游标向前走一步，取出一条记录放到变量my_uname中。

  

  while( stopflag=0 ) do  #如果游标还没有到结尾，就继续

  begin

     update testa set uname=concat(my_uname,’_cur’) where uname=my_uname;

     Fetch uname_cur into my_uname;

  end ;

End while;

Close uname_cur;

End;

$$

Delimiter ;

 

 

第10讲 子程序的高级特性1

DETERMINISTIC特性

delimiter$$

create procedure testa()

NOT DETERMINISTIC

Begin

  declare my_uid int;

  select uid into my_uid  from hxf where  salary<3000 limit 1;

  update user set regtime=now() where uid=my_uid;

end;

$$

delimiter;

 

1.DETERMINISTIC的涵义指的是什么？

      not  deterministic说明子程序包含了不确定的函数等。

2.类似的存储过程和函数高级特性还有：

     { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

存储过程与DETERMINISTIC特性对复制的影响

delimiter$$

create procedure testb()

NOT DETERMINISTIC | DETERMINISTIC

Begin

  declare my_uid int;

  select uid into my_uid  from hxf where  salary<3000 limit 1;

  update user set regtime=now() where uid=my_uid;

end;

$$

delimiter;

 

1.存储过程执行的语句在二进制日志中体现，复制保证精确性。

3.存储过程与其它高级特性对复制的影响测试

其它特性：{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 与存储过程也无关性测试

 

第11讲 子程序的高级特性2

1. 函数的只读高级特性
2. delimiter$$
3. Create function testa() returns int
4. READS SQL DATA
5. Begin
6.   declare my_uid int;
7.   select uid into my_uid  from hxf where  salary<3000 limit 1;
8.   return my_uid;
9. end;
10. $$
11. delimiter;
12.  
13. 提供子程序使用数据的内在信息：
14.         READS SQL DATA  只包含读的SQL语句,说明只有读，没有更新，对复制精确性是安全的，
15. 则可以放行。
16.  

 

2.函数包含可更新语句的高级特性

 

delimiter$$

create function testa()

CONTAINS SQL  à READS SQL DATA

Begin

   update user set level=5 where uid=5;

   return 5;

end;

$$

delimiter;

 

如果函数包含了更新语句，指定为CONTAINS SQL ， MODIFIES SQL DATA 等写的高级特性是不允许创建的。

包含更新语句的函数可以指定为NO SQL,或者READS SQL DATA只读特性，才可以放行创建，复制可以正常进行

1. 函数包含可更新语句对复制的影响

delimiter$$

create function testa() returns int

Modifies sql data

Begin

   update user set level=50 where uid=5;

   return 5;

end;

$$

delimiter;

 

如果函数包含了更新语句，指定为CONTAINS SQL ， MODIFIES SQL DATA，并且允许创建，有两个方案：修改log_bin_trust_function_creators=1，或者关闭binlog日志。

 

对复制的影响：这种更新记录二进制日志，也能正常复制。

 

 

1. 函数的高级特性特点

1.提供了函数使用数据的内在信息：

       CONTAINS SQL  包含读写语句

       NO SQL               无SQL语句（只读）

      READS SQL DATA  只读的SQL语句

      MODIFIES SQL DATA   只写的SQL语句

 

2.函数设计原则上不允许更新，历史上函数更新对复制可能是不安全的。

   二进制不会记录select语句，但只有函数中有更新语句时是例外 。

 

3.如果包含了更新语句，指定为CONTAINS SQL ， MODIFIES SQL DATA 是不允许创建的。

但可以指定为NO SQL,或者READS SQL DATA只读特性，才可以放行创建，复制可以正常进行。

 

4.如果函数 包含了更新语句，指定为CONTAINS SQL ， MODIFIES SQL DATA写的高级特性，并且允许创建，有两个方案：修改log_bin_trust_function_creators=1，或者关闭binlog日志。但对数据复制可能是不安全的。

 

第12讲 子程序的高级特性3

1. SQL SECURITY特性

delimiter$$

Create procedure testa()  

SQL SECURITY  DEFINER

Begin

  declare aa int;

  select sleep(30) into aa;

end;

$$

delimiter;

2. SQL SECURITY特性特点

1.SQL SECURITY 特性说明子程序运行使用创建者的许可来执行，还是使用调用者的许可来执行。默 认值是DEFINER。

 2.当sql security 是DEFINER时，即使是普通的只读用户，只要有这个存储过程的execute权限，那么也能以root@`localhost`的用户来运行存储过程进行更新操作。

 

第13讲 declare处理程序和处理条件

1. Declare处理程序与条件值示例

Delimiter $$

create procedure testcursor()

Begin

Declare stopflag int default  0;

Declare my_uname varchar(20);

Declare uname_cur cursor for select uname from users where uid%2=0 ;

declare continue         handler for    not found         set stopflag=1;

 

#continue 是指执行什么样的handler的处理程序。

什么时候执行处理程序呢？在SQLSTATE的条件值为not found时执行。

 

2. Declare处理程序

（1）声明形式：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

declare continue         handler for    not found         set stopflag=1;

 

声明将一个句柄类型（处理程序）和指定的错误条件值以及设置的变量关联起来

 

（2）处理程序 handler_type

 CONTINUE 在代码块中继续，在循环中继续下一次循环

 EXIT  退出BEGIN..END的代码块

 UNDO（暂不支持）

 

1. Declare处理程序和条件综合示例

Create procedure test_handler()

Begin

    declare stopFlag=1;

    declare continue handler  for SQLSTATE ‘23000’   SET stopflag=1;

    set stopFlag=1;

    SELECT stopFlag;

 

    insert into user(uid) values(1);

    set stopFlag=2;

    SELECT stopFlag;

 

    insert into user(uid) values(1);

    set stopFlag=3;

    SELECT stopFlag;

 

END;

4. Declare处理条件

（1）声明形式：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

declare continue         handler for    not found         set stopflag=1;

 

（2）条件值  condition_value

        SQLSTATE   [VALUE]

        SQLWARNING  是对所有以01开头的SQLSTATE代码的速记

        NOT FOUND 是对所有以02开头的SQLSTATE代码的速记

        SQLEXCEPTION  是对所有没有被SQLWARNING或NOT FOUND

                                        捕获的SQLSTATE代码的速记

第14讲 再论流程控制

1.Case分支

子程序中的case不同于select语句中的case分支

Create procedure testcase(my_uid int)

begin   

declare my_lev int default 0;   

select level into my_lev from user where uid=my_uid;   

 

case my_lev        

       when 1 then update user set level=10 where uid=my_uid; #执行SQL语句

       when 2 then update user set level=20 where uid=my_uid;

       when 3 then update user set level=30 where uid=my_uid;    

       else update user set level=40 where uid=my_uid;    

end case;

End;

1. 流程控制的查询条件应用

任何流程控制的条件中，可以是复杂的SQL查询混合表达式

IF (select salary from `user` where uid=1)<3000 THEN

   UPDATE `user` set salary=salary+1000 where uid=1;

END IF;

 

 

begin   

declare my_uid int default 0;

WHILE(select count(*) from `user` where isadd=1)>0 DO

BEGIN

   select uid into my_uid from `user` where isadd=1 limit 1;

   update `user` set isadd=0 where uid=my_uid;

end ;

END WHILE;

End

 

第15讲 存储过程生产环境应用综合示例详解

1.存储过程需求分析

生产场景描述：

1.在游戏中，每个账号(acccountid)可以最多5个角色(characterid)。角色表：t_game_character

2.5个角色放到一定的槽位(slot)中，槽位值必须是0-4，不能有相同的槽位，否则在游戏中就无法正常看到所有角色。

3.两个游戏分区需要合服，合服之后每个账号按规则删除只剩下5个角色，但槽位值可能有重复的。

 

实现需求：同一个账号有多个角色有重复槽位值，需要重置槽位值，使其值在0-4且不重复。

 

实现难点：有多个账号accountid，多个账号下又有多个角色。

                      多个账号使用一个循环，每个账号下多个角色又使用循环，即使用嵌套循环。

                      多个账号使用游标来实现，每个账号下多个角色又使用嵌套游标来实现。

1. 存储过程生产环境综合应用示例详解

CREATE  procedure procharslot()

BEGIN

DECLARE AID int;

declare stopFlag int default 0;   

DECLARE  CurAcc cursor FOR SELECT AccountId FROM reset_slot_accountid ;

declare  continue handler for not found set stopFlag=1;

OPEN CurAcc;

REPEAT

FETCH  CurAcc INTO  AID;

BEGIN

 DECLARE  CId int;

 DECLARE  CSlot int;

 DECLARE done int default 0;

 DECLARE  CurChar CURSOR  FOR SELECT CharacterId from t_game_character where AccountID=AID;

 declare continue handler for not found set done=1;

 SET  CSlot=0;

 OPEN CurChar;

 FETCH CurChar INTO CId;

 WHILE(done=0) DO   

BEGIN

   UPDATE t_game_character SET CharSlot=CSlot WHERE CharacterId=CId;

   SET CSlot=CSlot+1;

   FETCH CurChar INTO CId;

END;

 END WHILE;

 CLOSE CurChar;

END;

UNTIL stopFlag=1 END REPEAT;

CLOSE CurAcc;

END;

第16讲 存储过程+event福彩开奖

1.一个简单的event scheduler实现福彩3D开奖

1.需求：设计一个福彩的开奖存储过程，每3分钟开奖一次。

 

第一步：先编写一个存储过程open_lottery ，产生随机3个数，生成一条开奖记录。

 

第二步：编写一个事件调度器，每3分钟调用一次这个存储过程

 

 

 

2.存储过程与event实现

Create procedure open_lottery()

Begin

   insert into lottery(num1,num2,num3,ctime)

   select floor(rand()*9)+1, floor(rand()*9)+1, floor(rand()*9)+1,now();

End;

 

 

create event if not exists lottery_event 
on schedule every 3 minute 
on completion preserve 
do call open_lottery; 

3. Event scheduler的创建

1.创建event的格式

create event if not exists lottery_event  创建使用create event
on schedule every 3 minute   说明什么时候执行，多长时间执行一次
on completion preserve  调度计划执行完成的后是否还保留
do call open_lottery;    这个调度计划要做什么？

 

Event scheduler创建语法：

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]  是否开启事件
[COMMENT ‘comment’]  事件的注释
DO sql_statement;

1.Event事件就是数据库的计划任务，按一定的时间和频率执行SQL数据库操作

2.Event是如何 创建的？

3.Event是如何调用存储过程

 

第17讲 event的schedule

1. 事件调度器如何生效？

1.我的event为什么没有运行？

   show variables like '%event_scheduler%';

   set global event_scheduler=on;

 

2.如果event没有enable，那么需要修改为enable可用状态。

   创建时不指定，默认为enable

   alter event lottery_event enable;

1. 事件调度器计划示例

1.单次计划任务示例：

On schedule at ‘2016-12-12 04:00:00’ 在2016-12-12 04:00:00执行一次

 

2. 重复计划任务

on schedule every 1 scond 每秒执行一次

on schedule every 1 minuter 每分钟执行一次

on schedule every 1 day 每天执行一次

 

3.指定时间范围的重复计划任务

on schedule every 1 day starts ‘2016-12-12 20:20:20’ 每天在20：20：20执行一次

 

 

3.事件调度器的schedule语法

schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

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

第18讲 event+存储函数收集数据库性能数据

1. 在特定时间按一定频繁收集数据库性能数据

1.存储过程的实现

 

  create procedure monitor_proc

BEGIN

  create table if not exists monitor_processlist as select now() as ctime ,db,info,time_ms,ROWS_SENT,ROWS_EXAMINED from information_schema.processlist where 0=9;

 

insert into monitor_processlist select NOW(), db,info,time_ms,ROWS_SENT,ROWS_EXAMINED from information_schema.processlist where info is not null;

END

 

1. 监控子程序的event设置

1.Event事件设置

   需求：双12当天9点开抢，9点至11点高峰期，高峰期间每1分钟收集一次监控性能数据。

 

  create event monitor_event

  on schedule every 1  minute

  starts ‘2016-12-12 9:00:00’  ends ‘2016-12-12 11:00:00’

  do call monitor_proc ;

1. event的维护

1.发现1分钟时间间隔太大了，对性能分析并不合理，需要修改为每10秒收集一次。

  如何修改event?

 alter event monitor_event

 on schedule every 10 second

 starts ‘2017-01-01 9:00:00’;

 

 

2.收集性能数据后问题已经解决，如何临时性的关闭event,全局关闭event?

  alter event monitor_event  disable; 只关闭monitor_event这个事件

  set global event_scheduler=0; 关闭所有event事件

 

3.删除event

  drop event monitor_event;

 

第7讲 简单的自定义函数

1.一个简单的函数

需求：编写函数，传入一个用户uid，返回用户的uname

Delimiter $$

create function getuname(my_uid int) returns varchar(32)

Reads sql data

Begin

 Declare my_uname varchar(32) default  ‘’;

 select uname into my_uname from users where uid=my_uid;

 return my_uname;

End;

$$

Delimiter ;

 

1.创建函数使用create function 函数名（参数 ） returns 返回类型

2.函数体放在begin和end之间

3.Return指定函数的返回值

4.函数调用:select getuname()

第8讲 自定义函数综合应用示例

1. 自定义函数示例01

1.需求：输入用户ID，获得accountid,uid,uname组合的UUID值，在全区游戏中作为用户的唯一标识

 

CREATE   FUNCTION getuuid(my_uid int) RETURNS varchar(30) CHARSET utf8

    READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。

begin

declare uuid varchar(30) ;                      

select concat(accountid,uid,uname) into uuid from users where uid=my_uid;

return uuid;

end

 

1. 自定义函数示例02、

1.需求：输入参数uid，计算该uid所在账号下的所有游戏角色的金币总量.

CREATE   FUNCTION  statgold(my_uid int) RETURNS int

    READS SQL DATA

begin

    declare my_accoutid int default 0;

    declare gold_sum int default 0;

    select accountid into my_accountid from users where uid=my_uid;

     select sum(gold)  into gold_sum from users where uid=my_accountid;

     return gold_sum ;

end

第9讲 MySQL触发器示例

1. 什么是触发器

需求：出于审计目的，当有人往表users插入一条记录时，把插入的uid,uname,插入动作和操作时间记录下来。

Delimiter $$

CREATE  trigger tr_users_insert after insert on users  

for each row

begin  

  insert into oplog(uid,uname,action,optime)     

  values(NEW.uid,NEW.uname,'insert',now());

End;

$$

Delimiter ;

 

1.创建触发器使用create trigger 触发器名

2.什么时候触发？After insert on users,除了after还有before,是在对表操作之前(before)或者之后(after)触发动作的。

3.对什么操作事件触发？after insert on users ，操作事件包括insert,update,delete

4.对什么表触发？after insert on users   

5.影响的范围？For each row

触发器：与函数、存储过程一样，触发器是一种对象，它能根据对表的操作事件，触发一些动作，这些动作可以是insert,update,delete等修改操作。

 

2.生产环境触发器实例

需求：出于 审计目的，当删除users表时，记录删除前该记录的主要字段值

Delimiter $$

CREATE  trigger tr_users_delete before delete on users  

for each row

begin   

   insert into oplog(uid,uname,action,optime,old_value,new_value)       

   values(OLD.uid,OLD.uname,'delete',now(),OLD.regtime,OLD.regtime);

End;

 $$

Delimiter ;

 

show variables like '%character%';  查看数据库的字符编码

 

1.修改mysql表的字符编码方式:

alter table t_name convert to character set utf8;

2.修改数据库的字符集

alter database mydb character set utf8;

3.创建数据库指定数据库的字符集

create database mydb character set utf8;

mysql修改表名，列名，列类型，添加表列，删除表列

alter table test rename test1; --修改表名

alter table test add  column name varchar(10); --添加表列

alter table test drop  column name; --删除表列

alter table test modify address char(10) --修改表列类型

||alter table test change address address  char(40)

alter table test change  column address address1 varchar(30)--修改表列名