mysql的event schedule 可以让你设置你的mysql数据库再某段时间执行你想要的动作
## Create event ev_t001_user_remain delimiter $$ Create event ev_t001_user_remain on schedule every 1 day starts '2014-07-01 23:00:00' DO Begin TRUNCATE TABLE nowagamedata.t001_user_remain; insert into nowagamedata.t001_user_remain(ur_id,ur_game_id,ur_server_id,ur_platform_id,ur_channel_id,ur_calc_date,ur_user_reg_count,ur_remain_2,ur_remain_3,ur_remain_7,ur_remain_15,ur_create_time) select ur_id,ur_game_id,ur_server_id,ur_platform_id,ur_channel_id,ur_calc_date,ur_user_reg_count,ur_remain_2,ur_remain_3,ur_remain_7,ur_remain_15,date_format(now(),'%Y-%m-%d %H:%i:%s') from meishidata.t001_user_remain; END $$ delimiter ;
引用自 http://blog.sina.com.cn/s/blog_95153c710101fzmn.html
mysql的event schedule 可以让你设置你的mysql数据库再某段时间执行你想要的动作
这与视图是不相同的。
create event test1
on schedule every 1 day
starts '2007-09-01 12:00:00'
on completion not preserve
do insert into yyy values('hhh','uuu');
或
create event test
ON SCHEDULE AT '2007-09-01 12:00:00' + INTERVAL 1 DAY
on completion not preserve
do insert into yyy values('hhh','uuu');
解释:从 2007-09-01开始,每天对表yyy在12:00:00进行一个插入操作。而且只执行一次(on completion not preserve )
使用这个功能之前必须确保event_scheduler已开启,可执行
SET GLOBAL event_scheduler = 1;
或
SET GLOBAL event_scheduler = ON;
来开启(在mysql重启后会失效),也可以直接在启动命令加上“–event_scheduler=1”,例如:
mysqld ... --event_scheduler=1
要使设置永久生效,必须修改/etc/mysql/my.cnf文件:
[mysqld]
添加 event_scheduler=ON
要查看当前是否已开启事件调度器,可执行如下SQL:
SHOW VARIABLES LIKE 'event_scheduler';
或
SELECT @@event_scheduler;
或
SHOW PROCESSLIST;
二、创建事件(CREATE EVENT)
先来看一下它的语法:
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]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表
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> SELECT * FROM aaa;
+---------------------+
| timeline |
+---------------------+
| 2007-07-18 20:44:26 |
| 2007-07-18 20:44:27 |
| 2007-07-18 20:44:28 |
+---------------------+
2) 5天后清空test表:
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
3) 2007年7月20日12点整清空test表:
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
4) 每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
5) 5天后开启每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
6) 每天定时清空test表,5天后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
7) 5天后开启每天定时清空test表,一个月后停止执行:
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 PRESERVE。
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’]可以给该事件加上注释。
三、修改事件(ALTER 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) 临时关闭事件
ALTER EVENT e_test DISABLE;
2) 开启事件
ALTER EVENT e_test ENABLE;
3) 将每天清空test表改为5天清空一次:
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
四、删除事件(DROP EVENT)
语法很简单,如下所示:
DROP EVENT [IF EXISTS] event_name
例如删除前面创建的e_test事件
DROP EVENT e_test;
当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown event错误,因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;
mysql的sql代码块中进行变量定义、赋值和使用
## Create event ev_t006 delimiter $$ Create event ev_t01_tmp_ins on schedule every 1 MINUTE DO Begin declare v_name varchar(50); set v_name=concat('tom',round(rand()*10000)); insert into t01_tmp(date_time,name) select date_format(now(),'%Y-%m-%d %H:%i:%s'),v_name; END $$ delimiter ;
相关推荐
文件级别数据库恢复工具软件,本软件适合于操作系统崩溃后将mysql数据目录拷贝出进行恢复,在恢复前需要使用之前备份的数据库结构新建空库,然后将还原文件的数据恢复到新库,目前只能恢复InnoDB引擎、MyISAM引擎 表...
本文实例讲述了mysql事件之修改事件(ALTER EVENT)、禁用事件(DISABLE)、启用事件(ENABLE)、事件重命名及数据库事件迁移操作。分享给大家供大家参考,具体如下: 我们要知道,MySQL允许我们更改现有事件的各种...
事件调度器相当于操作系统中的定时任务(如:Linux中的cron、Window中的计划任务),但MySql的事件调度器可以精确到秒,对于一些实时性要求较高的数据处理非常有用。 1. 创建/修改事件(EVENT) 在MySql中,创建一个新...
查看event是否开启 代码如下:show variables like ‘%sche%’; 将事件计划开启 代码如下:set global event_scheduler ... 代码如下:create event if not exists e_test on schedule every 30 second on completion p
要使定时起作用 MySQL的常量GLOBAL event_scheduler必须为on或者是1 — 查看是否开启定时器 SHOW VARIABLES LIKE ‘%sche%’; — 开启定时器 0:off 1:on SET GLOBAL event_scheduler = 1; — 创建事件 –每隔一秒...
检测事件是否开启 代码如下: show variables like ‘event_scheduler’; 2.... 代码如下: set global event_scheduler = on;... 3.... 4....create event if not exists e_test on schedule every 30 second
Table of Contents Preface, Notes, Licenses . . . . . . . . ....1. Licenses for Third-Party Components ....1.1. FindGTest.cmake License ....1.2. LPeg Library License ....1.3. LuaFileSystem Library License ....
ALTER DEFINER=`root`@`%` EVENT `NewEvent` ON SCHEDULE EVERY 30 SECOND STARTS '2018-10-08 14:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL update_a()$$ DELIMITER ; 储存过程随机数: ...
要查看当前是否已开启事件调度器 SHOW VARIABLES LIKE ‘event_scheduler’; 开启事件查看器 SET GLOBAL event_scheduler = 1; 创建事件 语法: CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON ...
首先来看一个简单的例子来演示每秒插入一条记录到数据表 USE test; CREATE TABLE aaa (timeline ...CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
Navicat Premium combines the functions of other Navicat members and supports most of the features in MySQL, SQL Server, SQLite, Oracle and PostgreSQL including Stored Procedure, Event, Trigger, ...
Navicat Premium combines the functions of other Navicat members and supports most of the features in MySQL, SQL Server, SQLite, Oracle and PostgreSQL including Stored Procedure, Event, Trigger, ...