MySQL create timed execute stored procedure task

Source: Internet
Author: User
Tags mysql create

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.