MySQL資料庫事件調度(Event)

MySQL資料庫事件調度(Event)

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

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

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>

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁