MySQL Create timer (event), view timer, turn on timer, set timer time

Because the project needs to create a timer (EVEVT), so Baidu a bit, found that the basic is from a template, some functions are not complete, now summarize their own.

Note: The MySQL version is supported for event starting from 5.1. If your version is below 5.1, upgrade the version first.

There are a lot of ways to view the version, just give you a select version ();

1. Check to see if Evevt is turned on and evevt on.

1.1, MySQL evevt function is closed by default, you can use the following statement to see the state of evevt, if it is off or 0, it is closed.

              show VARIABLES LIKE ‘%sche%‘ ; 1.2、开启evevt功能             SET GLOBAL event_scheduler = 1; 2.创建定时器的过程 2.1、创建测试表test
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、创建evevt要调用的存储过程test_proce
delimiter //
drop procedure if exists test_proce//
create procedure test_proce()
begin
insert into test(time) values(now());
end//
delimiter ; 2.3、开启evevt（要使定时起作用,MySQL的常量GLOBAL event_scheduler必须为on或者是1）
执行show variables like ‘event_scheduler‘;查看evevt是否开启；
若没开启执行set global event_scheduler=‘on‘; 2.4、创建事件test_event（其作用：每隔一秒自动调用test_proce()存储过程）
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、开启事件test_event
alter event test_event on completion preserve enable;
2.6、关闭事件test_event
alter event test_event on completion preserve disable;
2.7、查看表test
select * from test; 3.查看自己创建的event 如果要查看更加详细的信息，你需要root用户的授权，如果是你自己的数据库你可以用下面语句查看 select * from  mysql.event; 下面的我的evevt的查看结果

4.event的时间设置 设置event很简单，但是麻烦的是如何设置执行的时间，网上找了一些，自己总结了一下。 先看语句，如下面这个      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 后面的是时间间隔，可以选 1 second，3 minute，5 hour，9 day，1 month，1 quarter（季度），1 year  从2013年1月13号0点开始，每天运行一次 ON SCHEDULE EVERY 1 DAY STARTS ‘2013-01-13 00:00:00‘ 从现在开始每隔九天定时执行 ON SCHEDULE EVERY 9 DAY STARTS NOW() ； 每个月的一号凌晨1 点执行 on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour); 每个季度一号的凌晨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); 每年1月1号凌晨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); 其他的 就大家自由组合了。。 Appendix 1> Use Rights

When invoking an SQL statement using event alone, viewing and creating requires the user to have the event permission, and when the SQL statement is called, the user is required to have permission to execute that SQL. The settings for the event permission are saved in the Event_priv field of the Mysql.user table and the Mysql.db table.

When event and procedure are used together, viewing and creating stored procedures requires the user to have the Create routine permission, and the call to the stored procedure executes with the Excute permission, which requires the user to have permission to execute the SQL statement when the stored procedure invokes it.

There are several types of view event commands:

(1) Inquiry mysql.event form;

(2) through the show events command;

(3) through the show full events command;

(4) by querying the Information_schema.events table

(5) SHOW CREATE EVENT.

In summary, the low frequency of event usage is recommended for root user creation and maintenance.

Syntax for 2>create EVENT

CREATE EVENT

[IF not EXISTS]------------------------------------------------* Callout 1

Event_Name-----------------------------------------------------* Callout 2

On SCHEDULE SCHEDULE-------------------------------------------* Callout 3

[on completion [NOT] PRESERVE]---------------------------------* Callout 4

[ENABLE | DISABLE]---------------------------------------------* Callout 5

[COMMENT ' COMMENT ']--------------------------------------------* Callout 6

do sql_statement;----------------------------------------------* Callout 7

Description

Callout 1:[if not EXISTS]

If not EXISTS is used, it is only created if the event with the same name does not exist, otherwise it is ignored. It is not recommended to ensure that event creation succeeds.

Callout 2:event_name

The maximum length of a name can be 64 bytes. The name must be unique in the current dateabase, and the same database cannot have an event with the same name.

Common work with event is to create tables, insert data, delete data, empty tables, and delete tables.

To avoid the inconvenience of naming the specification, it is best to have the event name have the ability to describe the entire event. The recommended naming rules are as follows:

Action Name _ (into/from_) Table name _time, for example:

1. Create (empty/delete) Fans table every month:

Create (Truncate/drop) _table_fans_month;

2. Insert (delete) data from fans table daily:

Insert (delete) _into (from) _fans_day;

Callout 3:on SCHEDULE

On SCHEDULE Scheduled Tasks, there are two ways to set up scheduled tasks:

1. At timestamp, used to complete a single scheduled task.

2. Every time unit [starts timestamp] [ends timestamp], which is used to complete a recurring scheduled task.

In both scheduled tasks, the timestamp can be any of the timestamp and datetime data types, and the timestamp needs to be greater than the current time.

In a recurring scheduled task, the number of times (units) can be an integer of any non-null (NOT NULL).

Time units are keywords: year,month,day,hour,minute or second.

Tip: Other time units are also valid as:

QUARTER, Week,year_month,day_hour,day_minute,day_second,hour_minute,hour_second, Minute_second

It is not recommended to use these non-standard time units on the previous line.

Callout 4: [on completion [NOT] PRESERVE]

The on completion parameter means "When this event does not recur", that is, when a single scheduled task is completed or when a recurring scheduled task is executed to the ends stage. The role of preserve is to allow the event to be dropped after execution, and it is recommended to use this parameter in order to view the event details.

Callout 5:[enable | DISABLE]

The parameter enable and disable indicate the state of the set event. Enable indicates that the system will execute this event. Disable indicates that the system does not execute the event.

The event can be closed or turned on with the following command:

ALTER EVENT Event_Name enable/disable

Callout 6:[comment ' COMMENT ']

The comment appears in the metadata, which is stored in the comment column of the Information_schema table, with a maximum length of 64 bytes.

' Comment ' means placing comments between single quotes, and it is recommended to use annotations to express more comprehensive information.

Callout 7:do sql_statement

The Do sql_statement field represents the SQL statement or stored procedure that the event needs to execute.

The SQL statement here can be a compound statement, for example:

BEGIN

CREATE table test1;//(need to test)

drop table test2;//Delete tables

Call Proc_test1 ();//Invoke stored procedure

END

Use the Begin and end identifiers to place the composite SQL statements between the execution order. Of course, the SQL statement is limited, and its limitations are the same as the function functions and trigger trigger in the SQL statement, if you do not use in function functions and trigger trigger some SQL, the same can not be used in the event. Clearly, there are several:

LOCK TABLES

UNLOCK TABLES

CREATE even

ALTER EVENT

LOAD DATA

3> Execution Logic

For (events created each event this has been created)

If (the state of the event is not disable)

and (current time before ends time)

and (the current time is after starts time)

and (elapsed time since last execution)

and (not executed)

Then:

To create a new thread

Pass the SQL statement of the event to the new thread

(The thread will close automatically when it finishes executing)

4> Modifying events

Modify the event using Alter event, with the specific alter syntax similar to the syntax for creating an event:

ALTER EVENT Event_Name

On SCHEDULE SCHEDULE

[RENAME to New_event_name]

[on completion [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT ' COMMENT ']

Do sql_statement;

5> Delete Events

Event using the Drop EVENT statement to delete the events that have been created, the syntax is as follows:

DROP EVENT [IF EXISTS] Event_Name;

However, when an event is running, deleting the event does not cause the event to stop, and the event is executed until it finishes. Using the drop user and drop DATABASE statements will also delete the events contained therein.

MySQL Create timer (event), view timer, turn on timer, set timer time