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. See if Evevt is turned on and Evevt is turned 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, turn on evevt function SET GLOBAL event_scheduler = 1;2. Procedure for creating a timer 2.1, creating a test table Testdrop table if  exists test;create table test (id int (one) NOT NULL Auto_increment primary key,time datetime NOT NULL) ENGINE=INNODB default charset=utf8;2.2, creating evevt stored procedure to invoke Test_procedelimiter//drop procedure if exists test_proce//create procedure Test_ Proce () Begininsert into Test (time), End//delimiter; 2.3, turn on evevt (to make timing work, MySQL constant global event_ Scheduler must be on or 1) to perform show variables like ' Event_scheduler ', to see if Evevt is turned on, or not to execute set global event_scheduler= ' on '; 2.4, Create Event Test_event (IT functions: Automatically call TEST_PROCE () stored procedure every second) Drop event if exists Test_event;create event Test_eventon schedule every 1 Secondon Completion Preserve Disabledo call TEST_PROCE (); 2.5,Turn on Events Test_eventalter event test_event on completion preserve enable;2.6, shutdown events Test_eventalter event test_event on Completion preserve disable;2.7, view table Testselect * from test; 3. View the event you created if you want to view more detailed information, you need the root user's authorization, if it is your own database you can use the following statement to view the select * from Mysql.event; Below the view results of my evevt MySQL Create timer (event), view timer, open timer, set timer time 4.event time settings settings The event is simple, but the trouble is how to set the time for execution, online to find some, I summed up a bit. Look at the statement first, such as the following CREATE EVENT test_event on SCHEDULE Every 1 day starts ' 2012-09-24 00:00:00 ' on completion PRESERVE ENA BLE do call Test_procedure (); Every back is the time interval, you can choose 1 second,3 minute,5 hour,9 day,1 month,1 Quarter (quarter), 1 year starting from January 13, 2013 0 o'clock, run once a day on SCHEDULE every 1 D AY starts ' 2013-01-13 00:00:00 ' starts from now every nine days on the SCHEDULE every 9 day starts today (); 1 o'clock in the morning per month execute on SCHEDULE every 1 month s Tarts Date_add (Date_add (Date_sub (Curdate (), Interval Day (Curdate ())-1 day), Interval 1 month), Interval 1 hour); Each quarter of the 1 o'clock in the morning executes on schedule every 1 quarter starts date_add (Date_add (Date (Concat (Curdate ()), '-', ELT (quarter ( Curdate ()), 1,4,7,10), '-', 1)), Interval 1 quarter), Interval 1 hour) each year, January 1 1 o'clock in the morning executes 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);  The rest of us are free to combine. Appendix 1> when you invoke an SQL statement using the event using permissions 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) Querying the Mysql.event table, (2) through the show Events command, (3) through the show full Events command, and (4) by querying Information_ Schema.events table (5) SHOW CREATE EVENT. In summary, the low frequency of event usage is recommended for root user creation and maintenance. 2>create EVENT Syntax CREATE event[if not EXISTS]------------------------------------------------* Callout 1event_name----- ------------------------------------------------* Callout 2ON 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] use if not EXISTS, which is only created if an event of the same name does not exist, otherwise ignored. It is not recommended to ensure that event creation succeeds. The maximum length of the callout 2:event_name 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: the 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: I Nsert (delete) _into (from) _fans_day; annotation 3:on SCHEDULE on SCHEDULE Scheduled Tasks, there are two ways to set scheduled tasks: 1.       At timestamp, which is 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.                  Hint: Other time units are also legal such 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 will not happen again", 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 | The DISABLE] 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.        The callout 7:do sql_statement 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;//Create a table (need to test it)                   drop table test2;//Delete tables call Proc_test1 ();//Calling Stored procedures End uses the begin and end identifiers to place composite SQL statements between 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. Specifically, there are several: LOCK TABLES UNLOCK TABLES CREATE EV EN ALTER Event LOAD DATA 3> execute logic for (event created each events that have been CRE ated) If (the state of the event is not disable) and (the current time before ends time) and (the current time is after the starts time) and (elapsed time after the last execution) and (not executed) then: The SQL statement that establishes a new thread-passing event to the new thread (which is automatically closed after execution) 4> modifies the event with the Alter event, which is similar to the syntax for creating the event: Alter event EVENT_ Nameon SCHEDULE Schedule[rename to New_event_name][on completion [NOT] preserve][enable | Disable][comment ' COMMENT ']do sql_statement; 5> Delete Event Events use the Drop Event statement to delete an event that has already been created with the following syntax: Drop event [IF EXISTS] event_name; However, when an event is running, deleting the event does not cause the event to stop, and the eventUntil the execution is complete. 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