"MySQL" Event events and cursors

MySQL events are like timed tasks on a Linux system that perform set-up tasks at a set time or time interval.

If you use a SQLyog class of write stored procedures, triggers, or events will be easier, such as SQLyog will generate an approximate template:

1 DELIMITER $$2 CREATEEVENT ' report '. ' Monitor_user4cx ' onSCHEDULE every theMINUTE Do3 BEGIN4 DECLAREcx_idINT(Ten);5 DECLARET_queryVARCHAR( -);6 DECLAREDoneINT DEFAULTFALSE;7 DECLARECurCURSOR  for SELECTId fromInformation_schema. ProcesslistWHERE`USER`='CX'  and' Time '>  -  and' Command '='Query';8 DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE;9 OPENcur;Ten Read_loop:loop One FETCHCur intocx_id; A IFDone Then - LEAVE Read_loop; - END IF; the SELECTT.trx_query,t.trx_started,p. 'USER` fromInformation_schema. Processlist PINNER JOINInformation_schema.innodb_trx T onP.id=t.trx_mysql_thread_idWHEREP.id=cx_id into @t_query,@t_time,@p_user; - INSERT  intoTEST.MONITOR_USER4CX (' p_id ', ' start_time ', 'User', ' time ', ' query ')VALUES(CX_ID,@t_time,@p_user, now (),@t_query); - KILLcx_id; - ENDLOOP; + CLOSEcur; - END$$ +DELIMITER;

View Code

How cursors are spelled:

CREATE PROCEDURE' test '. ' New_procedure ' ()BEGIN  --you need to define variables that receive cursor data  DECLAREACHAR( -); --declares the end flag of a cursor  DECLAREDoneINT DEFAULTFALSE; --To assign the required data to the cursor, the cursor must be defined in the variable/condition before handler;
--ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration
  DECLARECurCURSOR  for SELECTI fromtest.t; --binds the end flag to the cursor, and if the not found exception is caught, the variable done is set to True,done=true can be used as a loop to jump out of the condition  DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE; --Open Cursor  OPENcur; --Start LoopRead_loop:loop--extract the data in the cursor, there is only one, there can be multiple, such as fetch < cursor name > into < variable 1>,< variable 2>    FETCHCur intoA; --at the end of the statement    IFDone ThenLEAVE Read_loop; END IF; --here do the loop events you want to doSQL; ENDLOOP; --Close Cursors  CLOSEcur;END

Examples of tour signs:

1, repeat cycle (the loop with Do while, first execution after judgment)

Drop procedure if existsTest_proce2; Create procedureTest_proce2 ()begin      Declaretemp_idint( One); DeclareTemp_timedatetime; DeclareIsfinished Booleandefaultfalse; DeclareTest_cursorcursor  for SelectId,time fromtest; Declare ContinueHandler for  notFoundSetIsfinished=true; OpenTest_cursor; RepeatFetchTest_cursor intoTemp_id,temp_time; if  notIsfinished Then              Selectconcat (concat (temp_id, ":"), temp_time); End if; Until IsfinishedEndrepeat; CloseTest_cursor; End  

2, Loop loop

 Drop procedure if existsTest_proce3; Create procedureTest_proce3 ()begin      Declaretemp_idint( One); DeclareTemp_timedatetime; DeclareIsfinished Booleandefaultfalse; DeclareTest_cursorcursor  for SelectId,time fromtest; Declare ContinueHandler for  notFoundSetIsfinished=true; OpenTest_cursor; Test_loop:loopFetchTest_cursor intoTemp_id,temp_time; ifIsfinished Thenleave Test_loop; End if; //if the IF statement is placed after a fetch, the loop is a while type, and if the IF statement is immediately before the end loop, the loop is a do and type. Selectconcat (concat (temp_id, ":"), temp_time); EndLoop Test_loop; CloseTest_cursor; End  

Example:

Regularly check long-executed queries, record and kill

DELIMITER $$CREATEEVENT ' report '. ' Monitor_user4cx ' onSCHEDULE every theMINUTE DoBEGINDECLAREcx_idINT(Ten);DECLARET_queryVARCHAR( -);DECLAREDoneINT DEFAULTFALSE;DECLARECurCURSOR  for SELECTId fromInformation_schema. ProcesslistWHERE`USER`='CX'  and' Time '>  -  and' Command '='Query';DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE;OPENCur;read_loop:loopFETCHCur intocx_id;IFDone ThenLEAVE Read_loop;END IF;SELECTT.trx_query,t.trx_started,p. 'USER` fromInformation_schema. Processlist PINNER JOINInformation_schema.innodb_trx T onP.id=t.trx_mysql_thread_idWHEREP.id=cx_id into @t_query,@t_time,@p_user;INSERT  intoTEST.MONITOR_USER4CX (' p_id ', ' start_time ', 'User', ' time ', ' query ')VALUES(CX_ID,@t_time,@p_user, now (),@t_query);KILLcx_id;ENDLOOP;CLOSEcur;END$ $DELIMITER;

Some of the content goes from:

Http://www.cnblogs.com/trying/p/3296793.html

http://blog.csdn.net/willchyis/article/details/7943467

"MySQL" Event events and cursors