MySQL Database set timed tasks

Recently on hand to do an auction of e-commerce projects.

In the middle you need to set the auction state of the point to in progress.

our solution is to Mysql Timer task, here is a simple summary.

1. Scope of Use

not all of them. MySQL version is supported,MySQL 5.1 only after the new features that start to support

2. syntax related to MySQL

# Query MySQL Scheduled task support is turned on

Show variables like '%sche% ';

# Turn on MySQL Scheduled task support

SET GLOBAL event_scheduler = on;

SET @ @global. Event_scheduler = on;

SET GLOBAL event_scheduler = 1;

SET @ @global. Event_scheduler = 1;

# Turn off MySQL Scheduled Tasks support

SET GLOBAL event_scheduler = OFF;

SET @ @global. Event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @ @global. Event_scheduler = 0;

# If a scheduled task exists , Delete the scheduled task

DROP EVENT IF EXISTS Testinsert;

# Create a scheduled task

CREATE EVENT IF not EXISTS Testinsert

On SCHEDULE every 3 SECOND

On completion PRESERVE

Do INSERT into AA (' name ', sex) VALUES (' AA ', 2), (' BB ', 1);

# Open a scheduled task

ALTER EVENT Testinsert ENABLE;

# Close Scheduled Tasks

ALTER EVENT Testinsert DISABLE;

# Query MySQL Scheduled task support is turned on

Show variables like '%sche% ';

# Turn on MySQL Scheduled task support

SET GLOBAL event_scheduler = on;

SET @ @global. Event_scheduler = on;

SET GLOBAL event_scheduler = 1;

SET @ @global. Event_scheduler = 1;

# Turn off MySQL Scheduled Tasks support

SET GLOBAL event_scheduler = OFF;

SET @ @global. Event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @ @global. Event_scheduler = 0;

# If a scheduled task exists , Delete the scheduled task

DROP EVENT IF EXISTS Testinsert;

# Create a scheduled task

CREATE EVENT IF not EXISTS Testinsert

On SCHEDULE every 3 SECOND

On completion PRESERVE

Do INSERT into AA (' name ', sex) VALUES (' AA ', 2), (' BB ', 1);

# Open a scheduled task

ALTER EVENT Testinsert ENABLE;

# Close Scheduled Tasks

ALTER EVENT Testinsert DISABLE;

Finally, give a complete test case.

Mysqltimers are provided by the systemEvent, create the table below:
CREATE TABLE MyTable (
ID int auto_increment NOT NULL,
Name varchar (+) NOT null default ' ',
Introduce text NOT NULL,
Createtime timestamp NOT NULL,
Constraint Pk_mytable primary key (ID)
)


Create stored procedures, where the stored procedures are primarily provided toMysqlthe timerEventto invoke to execute:
CREATE PROCEDURE Mypro ()
BEGIN
Insert INTO MyTable (name,introduce,createtime) VALUES (' 1111 ', ' Inner Mongolia ', now ());
End
here is just a simple write-up, just to illustrate the example.


immediately after creatingMysqlthe timerEvent:
Create event if not exists eventjob
On schedule every 1 second
On completion PRESERVE
Do call Mypro ();
This is set to execute once per second


At this point all the preparations have been finished and done,MysqlTo make use of the timer must be done to prepare the work, is toMySQLtimer is turned on:
SET GLOBAL event_scheduler = 1; --Start Timer
SET GLOBAL event_scheduler = 0; --Stop Timer


The event is also opened immediately:
ALTER EVENT eventjob on completion PRESERVE ENABLE; --Turn on Events
ALTER EVENT eventjob on completion PRESERVE DISABLE; --Close Event


SHOW VARIABLES like '%sche% '; --View timer Status


at this point, you go to the table inside the databaseMyTablelook inside, the system will be every second to insert a piece of data, hehe, the task is completed.
SELECT * FROM MyTable

MySQL Database set timed tasks