MySQL create timed execute stored procedure task

There are many SQL syntax, which is a complete language. This only implements a function, does not do in-depth research.

Target: Update the table regularly or clear the table.

Case: A task that has been timed to empty the position information table. (However, it was not considered when the server was hung off)

This test: Table field +1 per 5s update

1.prepare

Create a table:

DROP TABLE IF EXISTS ' Test_sche '; CREATE TABLE ' Test_sche ' (  ' id ' int (one) not NULL,  ' counts ' int (one) DEFAULT NULL,  PRIMARY KEY (' id ')) engine=in Nodb DEFAULT Charset=utf8;--------------------------------Records of Test_sche------------------------------INSERT Into ' Test_sche ' VALUES (' 1 ', ' 188 ');

　　

2. Create a stored procedure

CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Add ' () Begin#routine body goes here ... UPDATE test_sche SET counts=counts+1 WHERE id=1; END

Note that when you create a stored procedure, you need to modify the command terminator to change the semicolon to another symbol if you are using the command line method.

--Set the delimiter to ' $$ ', MySQL default statement delimiter is '; ', so that the subsequent create to end code will be treated as a statement to execute  DELIMITER $$  //Create stored procedure or Event statement//END $$-Split statement Set the character back to '; '  

　　

3. Create an event and call the stored procedure

CREATE definer= ' root ' @ ' localhost ' EVENT ' test_sche_event ' on SCHEDULE every 5 SECOND starts ' 2016-07-12 22:11:50 ' on compl Etion not PRESERVE ENABLE does call ' add '

　　

3.1 Turn on timer

Set GLOBAL Event_scheduler = 1;  

　　

Here, the scheduled task is ready to execute, and the query can find that the Count field has been accumulating. If you want to see the event run:

3.2 Viewing event run status

SELECT * FROM Mysql.event  

　　

3.3 Turning events on or off

　　

4. Lazy People's practice

Long time not to write SQL, the grammar is almost forgotten, but with the help of tools is easy to make a timer. Here, Navicat for MySQL is used:

4.1 Creating a stored procedure

4.2 Creating events

MySQL create timed execute stored procedure task