程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql數據庫事件調度(Event),mysqlevent

mysql數據庫事件調度(Event),mysqlevent

編輯:MySQL綜合教程

mysql數據庫事件調度(Event),mysqlevent


 mysql中的事件調度器可以定時對數據庫增加,刪除和執行操作,相當於數據庫中的臨時觸發器,與Linux系統中的執行計劃任務一樣,這樣就可以大大降低工作量.

 

1.開啟事件調度器

[root@node1 ~]# vim /usr/my.cnf         --在配置文件中加入以下語句啟用調度器

event_scheduler=1

[root@node1 ~]# /etc/init.d/mysql restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL... SUCCESS!

[root@node1 ~]#

 

2.查看事件調度是否開啟

[root@node1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "event_%";           --查看調度器是否啟用
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> ? create event;              --查看創建事件的語法
Name: 'CREATE EVENT'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
mysql> create database test123;
Query OK, 1 row affected (0.00 sec)
mysql> \u test123
Database changed

mysql>

 

3.創建事件調度5秒鐘後創建t表

mysql> create event if not exists event_t on schedule at current_timestamp + interval 5 second  do create table t (a int,b nchar(10),c timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> show events;        --查看事件是否創建成功
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db      | Name     | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| test123 | event_t1 | root@localhost | SYSTEM    | RECURRING | NULL       | 5              | SECOND         | 2014-11-12 15:29:13 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;               --創建表成功
+-------------------+
| Tables_in_test123 |
+-------------------+
| t                 |
+-------------------+
1 row in set (0.00 sec)

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| a     | int(11)   | YES  |     | NULL              |                             |
| b     | char(10)  | YES  |     | NULL              |                             |
| c     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)

mysql>

 

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>

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved