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)
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 |
+--------