mysql学习记录(十一)
一、理论: 1.优势: 二、实践:
mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.01 sec)ERROR: No query specifiedmysql> 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: test1Command: Sleep Time: 247901 State: Info: NULL*************************** 2. row *************************** Id: 21 User: root Host: localhost db: test1Command: Query Time: 0 State: NULL Info: show processlist*************************** 3. row *************************** Id: 22 User: event_scheduler Host: localhost db: NULLCommand: Daemon Time: 1 State: Waiting for next activation Info: NULL3 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: test1Command: Sleep Time: 248177 State: Info: NULL*************************** 2. row *************************** Id: 21 User: root Host: localhost db: test1Command: Query Time: 0 State: NULL Info: show processlist*************************** 3. row *************************** Id: 22 User: event_scheduler Host: localhost db: NULLCommand: Daemon Time: 2 State: Waiting for next activation Info: NULL3 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: 1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)ERROR: No query specified |