翻开event是或不是开启

要翻开当前是或不是已拉开事件调解器,可实行如下SQL:

复制代码 代码如下:show variables like
‘%sche%’;

SHOW VARIABLES LIKE ‘event_scheduler’;

将事件安排开启

SELECT @@event_scheduler;

复制代码 代码如下:set global
event_scheduler =1;

SHOW PROCESSLIST;
若显示:

创建存储进度test

+—————–+——-+
| Variable_name   | Value |
+—————–+——-+
| event_scheduler | OFF   |
+—————–+——-+
则可实行

复制代码 代码如下:CREATE PROCEDURE test
() BEGIN update examinfo SET endtime = now() WHERE id = 14; END;

SET GLOBAL event_scheduler = 1;

创建event e_test

SET GLOBAL event_scheduler = ON;
来拉开,也得以一贯在起步命令加上“–event_scheduler=1”,例如:

复制代码 代码如下:create event if not
exists e_test on schedule every 30 second on completion preserve do
call test();

mysqld … –event_scheduler=1

每隔30秒将进行存款和储蓄进度test,将目今天子更新到examinfo表中id=14的笔录的endtime字段中去.

my.ini or my.cnf 中的
[mysqld]
添加 event_scheduler=ON

闭馆事件职分

创办事件(CREATE EVENT)
先来看一下它的语法:

复制代码 代码如下:alter event e_test ON
COMPLETION PRESERVE DISABLE;

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ‘comment’]
DO sql_statement;

开户事件职分

schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

复制代码 代码如下:alter event e_test ON
COMPLETION PRESERVE ENABLE;

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 5.4.2-beta-community mysql community
server

1)首先来看一个总结的例证来演示每秒插入一条记下到数据表

上述的相关内容正是对MySQL准时实践的牵线,望你能有所收获。

USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等候3分钟后,再执行查询成功。

mysql 计划职务重启后消失

**2) 5天后清空test表:

我们假使改过风流倜傥配置即可

**CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

event_scheduler在mysql的config中安装为OFF有关。去mysql旅长配置改为ON则就消除了。更详实的门阀可以往下看

3) 二零零六年十月十二日12点整清空test表:

MySQL5.1.x版本中引进了生机勃勃项新性格EVENT,顾名思义便是事件、准时职务机制,在钦赐的年华单元内实行一定的职分,因而未来有个别对数据准期性操作不再信任外界程序,而直白接收数据库本人提供的效应。

CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP ‘2007-07-20 12:00:00’
DO TRUNCATE TABLE test.aaa;

要翻看当前是或不是已拉开事件调节器,可进行如下SQL:

4) 每一天依期清空test表:

SHOW VARIABLES LIKE ‘event_scheduler’;或

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;

SELECT @@event_scheduler;或

5) 5天后展开每日准期清空test表:

SHOW PROCESSLIST;若显示:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

+—————–+——-+| Variable_name | Value
|+—————–+——-+| event_scheduler | OFF
|+—————–+——-+则可实行

6) 每日准时清空test表,5天后结束实践:

SET GLOBAL event_scheduler = 1;或

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

SET GLOBAL event_scheduler =
ON;来张开,也能够平昔在开发银行命令加上“–event_scheduler=1”,例如:

7) 5天后张开每一天准时清空test表,三个月后终止实行:

mysqld … –event_scheduler=1

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT]
PRESERVE]能够安装这几个事件是施行贰遍依然悠久实践,默以为NOT PRESEPAJEROVE。

my.ini or my.cnf 中的[mysqld]添加 event_scheduler=ON

8) 每一日依期清空test表(只进行三回,职责成功后就止住该事件):

创办事件先来看一下它的语法:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE |
DISABLE]而是设置该事件创立后情况是不是开启或关闭,默感觉ENABLE。
[COMMENT ‘comment’]能够给该事件加上注释。

CREATE EVENT [IF NOT EXISTS] event_nameON SCHEDULE schedule[ON
COMPLETION [NOT] PRESERVE][ENABLE | DISABLE][COMMENT ‘comment’]DO
sql_statement;

改良事件(ALTE奥迪Q5 EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ‘comment’]
[ENABLE | DISABLE]
[DO sql_statement]
1) 有时关闭事件

schedule:AT TIMESTAMP [+ INTERVAL INTERVAL]| EVERY INTERVAL [STARTS
TIMESTAMP] [ENDS TIMESTAMP]

ALTER EVENT e_test DISABLE;

INTERVAL:quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK |
SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND |
HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

2) 开启事件

1)首先来看叁个简约的例证来演示每秒插入一条记下到数据表

ALTER EVENT e_test ENABLE;
3) 将每一天清空test表改为5天清空二遍:

USE test;CREATE TABLE aaa ;CREATE EVENT e_test_insertON SCHEDULE EVECRUISERY
1 SECOND DO INSERT INTO test.aaa VALUES ;等待3分钟后,再实行查询成功。

ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;

2) 5天后清空test表:

除去事件(DROP EVENT)
语法很简短,如下所示:

CREATE EVENT e_testON SCHEDULE AT CURRENT_TIMESTAMP + INTELANDVAL 5 DAYDO
TRUNCATE TABLE test.aaa;3) 2006年1二月27日12点整清空test表:

DROP EVENT [IF EXISTS] event_name
举例删除前边创设的e_test事件

CREATE EVENT e_testON SCHEDULE AT TIMESTAMP ‘二〇〇七-07-20 12:00:00’DO
TRUNCATE TABLE test.aaa;4) 每日准期清空test表:

DROP EVENT e_test;
当然前提是以那件事件存在,不然会时有爆发E驭胜ROCR-V 1513 (HY000): Unknown
event错误,由此最好增长IF EXISTS

CREATE EVENT e_testON SCHEDULE EVEEscortY 1 DAYDO TRUNCATE TABLE test.aaa;5)
5天后敞开每日定期清空test表:

DROP EVENT IF EXISTS e_test;

CREATE EVENT e_testON SCHEDULE EVERY 1 DAYSTARTS CURRENT_TIMESTAMP +
INTERVAL 5 DAYDO TRUNCATE TABLE test.aaa;

SHOW VARIABLES LIKE ‘event_scheduler’; 或 SELECT @@event_scheduler; 或
SHOW PROCESSLIST; 若显示: +—–…

6) 每日准期清空test表,5天后停下推行:

CREATE EVENT e_testON SCHEDULE EVERY 1 DAYENDS CURRENT_TIMESTAMP +
INTERVAL 5 DAYDO TRUNCATE TABLE test.aaa;

7) 5天后拉开天天准期清空test表,八个月后甘休试行:

CREATE EVENT e_testON SCHEDULE EVERY 1 DAYSTARTS CURRENT_TIMESTAMP +
INTERVAL 5 DAYENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTHDO TRUNCATE
TABLE test.aaa;[ON COMPLETION [NOT]
PRESERVE]能够安装那几个事件是实行叁次还是长久施行,默以为NOT PRESEENVISIONVE。

8) 每日依期清空test表:

CREATE EVENT e_testON SCHEDULE EVERY 1 DAYON COMPLETION NOT PRESERVEDO
TRUNCATE TABLE test.aaa;[ENABLE |
DISABLE]唯独设置该事件创立后情形是或不是开启或关闭,默以为ENABLE。[COMMENT
‘comment’]能够给该事件加上注释。

纠正事件ALTE大切诺基 EVENT event_name[ON SCHEDULE schedule][RENAME TO
new_event_name][ON COMPLETION [NOT] PRESERVE][COMMENT
‘comment’][ENABLE | DISABLE][DO sql_statement]1) 不时关闭事件

ALTER EVENT e_test DISABLE;2) 开启事件

ALTER EVENT e_test ENABLE;3) 将每一天清空test表改为5天清空一回:

ALTER EVENT e_testON SCHEDULE EVERY 5 DAY;

剔除事件语法很简短,如下所示:

DROP EVENT [IF EXISTS] event_name举例删除前边制造的e_test事件

DROP EVENT e_test;当然前提是其一事件存在,不然会生出E卡宴RO哈弗 1513 :
Unknown event错误,因而最佳增进IF EXISTS

DROP EVENT IF EXISTS e_test;

相关文章