4.創建事件調度每5秒在表中插入數據

MySQL> create event if not exists event_t1 on schedule every? 5 second? do insert into t values(1,1,sysdate());

Query OK, 0 rows affected (0.01 sec)

mysql> select * from t;? ? ? ? ? --查看事件執行數據

+------+------+---------------------+

| a? ? | b? ? | c? ? ? ? ? ? ? ? ? |

+------+------+---------------------+

|? ? 1 | 1? ? | 2014-11-12 15:33:31 |

|? ? 1 | 1? ? | 2014-11-12 15:33:36 |

|? ? 1 | 1? ? | 2014-11-12 15:33:41 |

|? ? 1 | 1? ? | 2014-11-12 15:33:46 |

|? ? 1 | 1? ? | 2014-11-12 15:33:51 |

|? ? 1 | 1? ? | 2014-11-12 15:33:56 |

|? ? 1 | 1? ? | 2014-11-12 15:34:01 |

+------+------+---------------------+

7 rows in set (0.00 sec)

mysql>

5.創建事件調度10秒鐘后刪除t表中所有數據

mysql> create event if not exists event_t2 on schedule every? 10? second? do truncate table t;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+------+------+---------------------+

| a? ? | b? ? | c? ? ? ? ? ? ? ? ? |

+------+------+---------------------+

|? ? 1 | 1? ? | 2014-11-12 15:36:36 |

|? ? 1 | 1? ? | 2014-11-12 15:36:41 |

+------+------+---------------------+

2 rows in set (0.00 sec)

mysql> select * from t;

Empty set (0.00 sec)

mysql>

6.在指定時間刪除t表數據

mysql> create event if not exists event_t2 on schedule at timestamp '2014-11-12 15:39:00' do truncate table t;;

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from t;

Empty set (0.00 sec)

mysql>

7.創建事件調度每天刪除t表數據

mysql> create event if not exists event_t2 on schedule every 1 day? do truncate table t;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from t;

Empty set (0.00 sec)

mysql>

8.創建事件調度5天后開啟刪除t表中數據,一個月后停止

mysql> create event if not exists event_t2 on schedule every 1 day? starts current_timestamp + interval 5 day ends current_timestamp + interval 1 month do truncate table t;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

9.禁用事件調度器

mysql> alter? event event_t2 disable;

Query OK, 0 rows affected (0.00 sec)

mysql>

10.啟用事件調度器

mysql> alter? event event_t2 enable;

Query OK, 0 rows affected (0.00 sec)

mysql>

11.修改事件調度器10天后啟用

mysql> alter? event event_t2 on schedule every 10 day;

Query OK, 0 rows affected (0.00 sec)

mysql>

12.重命名事件調度器

mysql> alter event event_t2 rename to event_t1;

Query OK, 0 rows affected (0.00 sec)

mysql>

13.查看事件調度器的信息

mysql> show events like "event_t1" \G;

*************************** 1. row ***************************

Db: test123

Name: event_t1

Definer: root@localhost

Time zone: SYSTEM

Type: RECURRING

Execute at: NULL

Interval value: 10

Interval field: DAY

Starts: 2014-11-12 15:47:31

Ends: NULL

Status: ENABLED

Originator: 0

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

14.查看事件調度器的內容

mysql> show create event event_t1 \G;

*************************** 1. row ***************************

Event: event_t1

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

time_zone: SYSTEM

Create Event: CREATE DEFINER=`root`@`localhost` EVENT `event_t1` ON SCHEDULE EVERY 10 DAY STARTS '2014-11-12 15:47:31' ON COMPLETION NOT PRESERVE ENABLE DO truncate table t

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

總結

以上是生活随笔為你收集整理的mysql originator_MySQL数据库事件调度(Event)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。