Create a timer (event) in MySQL, view the timer, enable the timer, and set the timer time.

Source: Internet
Author: User

Reprinted from: http://blog.sina.com.cn/s/blog_6d39ac7e01017sd6.html

Because the project needs to create a timer (evevt), Baidu just found that it basically comes from a template, and some functions are not complete, so I will summarize it myself.

Note: The MySQL version supports event only from 5.1. If your version is earlier than 5.1, upgrade it first.

There are many ways to view the version. Here we only provide one  Select version ();

1. Check whether EVT is enabled and evevt is enabled.

1.1 The MySQL evevt function is disabled by default. You can use the following statement to check the evevt status. If it is off or 0, it indicates it is disabled.

              show VARIABLES  LIKE  '%sche%' ; 1.2 enable the EVT Function             SET GLOBAL event_scheduler = 1; 2. Create a timer 2.1 create a test table
Drop table if exists test;
Create Table Test
(
Id int (11) not null auto_increment primary key,
Time datetime not null
) Engine = InnoDB default charset = utf8;
2.2 create the Stored Procedure test_proce to call evevt
Delimiter //
Drop procedure if exists test_proce //
Create procedure test_proce ()
Begin
Insert into test (time) values (now ());
End //
Delimiter;
2.3 enable evevt (to enable timing, MySQL constant global event_scheduler must be on or 1)
Run show variables like 'event _ schedount' to check whether evevt is enabled;
If set global event_scheduler = 'on' is not enabled ';
2.4 create an event test_event (its function: automatically call the Stored Procedure test_proce () every second)
Drop event if exists test_event;
Create event test_event
On schedule every 1 second
On completion preserve disable
Do call test_proce ();
2.5 enable the event test_event
Alter event test_event on completion preserve enable;
2.6 close the event test_event
Alter event test_event on completion preserve disable;
2.7 view the table test
Select * from test;
  3. view the event you created If you want to view more detailed information, you need the authorization of the root user. If it is your own database, you can use the following statement to view select * from  mysql.event; View the result of my EVT below

4. event time settingsSetting event is simple, but the trouble is how to set the execution time. I found some information online and summarized it myself.First read the statement, as shown in the following

     CREATE EVENT test_event ON SCHEDULE EVERY 1 DAY STARTS '2012-09-24
00:00:00'
     ON  COMPLETION PRESERVE ENABLE DO CALL test_procedure(); Every is followed by a time interval. You can select 1 second, 3 minute, 5 hour, 9 day, 1 month, 1 quarter (quarter), 1 year. Run once a day from, January 1, January 13, 2013 ON SCHEDULE EVERY 1 DAY STARTS '2013-01-13 00:00:00' Scheduled execution every nine days from now on ON SCHEDULE EVERY 9 DAY STARTS NOW() ; Execute the task at on the first day of each month. on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour); Run the command at on Monday 1. on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour); Run at on January 1, January 1 every year. on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour);  Others can be freely combined ..

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.