Shulou(Shulou.com)06/01 Report--

one。 Brief introduction of event

Events (event) are procedural database objects that MySQL calls at the appropriate time. An event can be called once or started periodically, and it is managed by a specific thread, which is called the event Scheduler.

Events, like triggers, are started when something happens. When a statement is started on the database, the trigger starts, and the event starts based on the scheduled event. Because they are similar to each other, events are also called temporary triggers.

Events replace work that can only be performed by the scheduled tasks of the operating system, and MySQL's event Scheduler can be accurate to execute one task per second, while the scheduled tasks of the operating system (such as CRON under Linux or task scheduling under Windows) can only be executed once per minute.

two。 Advantages and disadvantages of events

2.1 benefits

Some data timed operations no longer rely on external programs, but directly use the functions provided by the database itself.

It can execute one task per second, which is very practical in some environments with high real-time requirements.

2.2 shortcomings

It is triggered regularly and cannot be called.

three。 Create an event

Check to see if evevt is enabled and evevt is enabled.

The MySQL evevt function is off by default. You can use the following statement to see the status of evevt. If it is OFF or 0, it means it is off.

Mysql > show VARIABLES LIKE 'event_schedu%'

+-+ +

| | Variable_name | Value |

+-+ +

| | event_scheduler | OFF |

+-+ +

1 row in set (0.00 sec)

Enable the event function:

Mysql > SET GLOBAL event_scheduler = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > show VARIABLES LIKE 'event_schedu%'

+-+ +

| | Variable_name | Value |

+-+ +

| | event_scheduler | ON |

+-+ +

1 row in set (0.00 sec)

An example of creating an event:

Mysql > use test-_T

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

First create a stored procedure:

Mysql > delimiter / /

Mysql > create procedure del_proc ()

-> begin

-> INSERT INTO req (entname) SELECT t.`NAME` FROM (SELECT NAME, min (ZCSJ) AS mydate FROM test-_T.t_user_sign GROUP BY NAME) t WHERE t.mydate > NOW ()-36 * 60 * 1000 and t.`NAME` not in (SELECT entname from req)

-> UPDATE req SET `STATUS` ='0' where `STATUS` is null

-> end

-> / /

Query OK, 0 rows affected (0.00 sec)

Execute the contents of the stored procedure:

Mysql > call del_proc ()

-> / /

Query OK, 2 rows affected, 1 warning (0.29 sec)

Create an event deldata_event (its purpose is to automatically call the contents of the del_proc () stored procedure every other minute)

Mysql > create event deldata_event

-> on schedule every 1 minute

-> on completion preserve disable

-> do call del_proc ()

-> / /

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql >

Enable event deldata_event

Mysql > alter event deldata_event on completion preserve enable

-> / /

Query OK, 0 rows affected (0.00 sec)

Close event deldata_event

Mysql > alter event deldata_event on completion preserve disable

View the event you created:

Mysql > select * from mysql.event

+- -+- -+- -+

| | db | name | body | definer | execute_at | interval_value | interval_field | created | modified | last_executed | starts | ends | status | on_completion | sql_mode | comment | originator | time_zone | character_set_client | collation_connection | db_collation | body_utf8 |

+- -+- -+- -+

| | Test-_T | deldata_event | call del_proc () | mdba@% | NULL | 1 | MINUTE | 2017-05-09 10:21:57 | 2017-05-09 10:22:11 | 2017-05-09 02:24:57 | 2017-05-09 02:21:57 | NULL | ENABLED | PRESERVE | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 15 | SYSTEM | utf8 | utf8_general_ci | utf8_general_ci | call del_proc () |

+- -+- -+- -+

1 row in set (0.00 sec)

Check the implementation of Event in MySQL

By executing the following statement:

SELECT * FROM information_schema.EVENTS

For example:

Mysql > SELECT * FROM information_schema.events\ G

* * 1. Row *

EVENT_CATALOG: def

EVENT_SCHEMA: ixinnuo_sjcj

EVENT_NAME: deldata_event

DEFINER: mdba@%

TIME_ZONE: SYSTEM

EVENT_BODY: SQL

EVENT_DEFINITION: call del_proc ()

EVENT_TYPE: RECURRING

EXECUTE_AT: NULL

INTERVAL_VALUE: 1

INTERVAL_FIELD: MINUTE

SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

STARTS: 2017-05-09 10:21:57

ENDS: NULL

STATUS: ENABLED

ON_COMPLETION: PRESERVE

CREATED: 2017-05-09 10:21:57

LAST_ALTERED: 2017-05-09 10:22:11

LAST_EXECUTED: 2017-05-11 08:43:57

EVENT_COMMENT:

ORIGINATOR: 15

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.00 sec)

You can see the job information in the corresponding library, where the LAST_EXECUTED field reflects the last execution time of the corresponding job.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.