집 >데이터 베이스 >MySQL 튜토리얼 >mysql学习记录(十一)--事件调度器_MySQL
一、理论:
1.优势:
1)减少系统管理员的操作风险,有利于数据库整体迁移。因数据库迁移时即可同时迁移调度事件
2.场景:
1)定期收集统计信息
2)清理历史数据
3)数据库检查
3.要点:
1)在性能要求高且重要的数据库中要慎重部署和启用调度器
2)过于复杂的更适合用程序
3)开启和关闭事件调度器需要具有超级用户权限
二、实践:
mysql> use test1; 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 mysql> create table test(id1 varchar(10),create_time datetime); Query OK, 0 rows affected (0.02 sec) mysql> create event test_event_1 -> on schedule -> every 5 second -> do -> insert into test1.test(id1,create_time) -> values ('test',now()); Query OK, 0 rows affected (0.01 sec) mysql> show events \G; *************************** 1. row *************************** Db: test1 Name: test_event_1 Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 5 Interval field: SECOND Starts: 2015-10-14 06:33:17 Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.01 sec) ERROR: No query specified mysql> select * from test; Empty set (0.00 sec) mysql> show variables like '%scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec) mysql> set global event_scheduler = 1 ; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec) mysql> show processlist \G; *************************** 1. row *************************** Id: 17 User: root Host: localhost db: test1 Command: Sleep Time: 247901 State: Info: NULL *************************** 2. row *************************** Id: 21 User: root Host: localhost db: test1 Command: Query Time: 0 State: NULL Info: show processlist *************************** 3. row *************************** Id: 22 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 1 State: Waiting for next activation Info: NULL 3 rows in set (0.00 sec) ERROR: No query specified mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:33:57 | | test | 2015-10-14 06:34:02 | | test | 2015-10-14 06:34:07 | | test | 2015-10-14 06:34:12 | | test | 2015-10-14 06:34:17 | | test | 2015-10-14 06:34:22 | +------+---------------------+ 6 rows in set (0.00 sec) mysql> create event trunc_test -> on schedule every 1 minute -> do truncate table test; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:34:52 | | test | 2015-10-14 06:34:57 | | test | 2015-10-14 06:35:02 | | test | 2015-10-14 06:35:07 | | test | 2015-10-14 06:35:12 | | test | 2015-10-14 06:35:17 | | test | 2015-10-14 06:35:22 | | test | 2015-10-14 06:35:27 | | test | 2015-10-14 06:35:32 | | test | 2015-10-14 06:35:37 | +------+---------------------+ 10 rows in set (0.00 sec) mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:37:52 | | test | 2015-10-14 06:37:57 | +------+---------------------+ 2 rows in set (0.00 sec) mysql> show processlist \G; *************************** 1. row *************************** Id: 17 User: root Host: localhost db: test1 Command: Sleep Time: 248177 State: Info: NULL *************************** 2. row *************************** Id: 21 User: root Host: localhost db: test1 Command: Query Time: 0 State: NULL Info: show processlist *************************** 3. row *************************** Id: 22 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 2 State: Waiting for next activation Info: NULL 3 rows in set (0.00 sec) ERROR: No query specified mysql> alter event trunc_test disable; Query OK, 0 rows affected (0.02 sec) mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | +------+---------------------+ 10 rows in set (0.00 sec) mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | | test | 2015-10-14 06:39:42 | | test | 2015-10-14 06:39:47 | | test | 2015-10-14 06:39:52 | | test | 2015-10-14 06:39:57 | | test | 2015-10-14 06:40:02 | | test | 2015-10-14 06:40:07 | +------+---------------------+ 16 rows in set (0.00 sec) mysql> alter event test_event_1 disable; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; +------+---------------------+ | id1 | create_time | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | | test | 2015-10-14 06:39:42 | | test | 2015-10-14 06:39:47 | | test | 2015-10-14 06:39:52 | | test | 2015-10-14 06:39:57 | | test | 2015-10-14 06:40:02 | | test | 2015-10-14 06:40:07 | | test | 2015-10-14 06:40:12 | | test | 2015-10-14 06:40:17 | | test | 2015-10-14 06:40:22 | | test | 2015-10-14 06:40:27 | +------+---------------------+ 20 rows in set (0.00 sec) mysql> drop event test_event_1; Query OK, 0 rows affected (0.01 sec) mysql> show events \G; *************************** 1. row *************************** Db: test1 Name: trunc_test Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: MINUTE Starts: 2015-10-14 06:34:48 Ends: NULL Status: DISABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) ERROR: No query specified