使用这个功能之前必须确保event_scheduler已开启 SHOW VARIABLES LIKE 'event_scheduler';如果value是OFF,就是没开,用以下命令开 SET GLOBAL event_scheduler = 1; 创建事件的语法:CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] DO sql_statement;
建立event: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 sql_statement;schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]示例: CREATE EVENT e_daily ON SCHEDULE EVERY 1 DAY COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END | ---------------------------------------------------- 更改event: ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO sql_statement]示例: ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;ALTER TABLE myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable;ALTER EVENT myevent DISABLE;ALTER EVENT myevent RENAME TO yourevent;------------------------------------------------------ 建立作业后,查看作业的信息: mysql> select * from information_schema.events where event_name='event_name';
检查event功能: mysql> show variables like '%sche%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.01 sec)启动event功能: mysql> set global event_scheduler =1; Query OK, 0 rows affected (0.00 sec)
ACMAIN_CHM 你说的 :每次你修改时,能过你的C#要 alter event 来修改event 的代码。是什么意思啊?可以不通过查表就可以把界面的那个值赋给event吗?谢谢了
不是赋给event 而是修改EVENT的代码。另外的确不能用alter event 来实现,只能用 drop , create drop event xxxx; create event xxx ..... delete from tableName where ptime < date_sub(now(),interval " + n + " day);"
ptime和 date_sub是数据库中的字段还是mysql特有的变量啊?
对了 我可以用触发器吧,通过触发器来开启Mysql的自动删除功能
看来你SQL基础得补下咯 ptime是你的表的字段,date_sub是mysql自身带的时间函数
楼主啊,建议多看一下帮助文档。 在十楼已经贴给过你一次了。12.1.11. CREATE EVENT Syntax http://dev.mysql.com/doc/refman/5.1/en/create-event.html12.1.22. DROP EVENT Syntax http://dev.mysql.com/doc/refman/5.1/en/drop-event.html 对照文档理解一下下面的语句。DROP EVENT IF EXISTS event_hahaxj;CREATE EVENT event_hahaxj ON SCHEDULE EVERY 1 DAY STARTS '2009-10-21 01:00:00' DO delete from a where ptime < date_sub(now(),interval 5 day); 然后在你的这个按键事件中cmd.ExecuteNonQuery() 这些SQL语句。 SQL语句的内容你可以用字符串运算生成。
各位高手,是不是子c#程序里面 只能对mysql的event一些属性进行修改啊,怎么我把drop和create event相关的语句放到c#中,通过command.ExcuteNonQuery执行会报错啊?SET GLOBAL event_scheduler = 1; 这样的就可以运行 谢谢了
------------------------------
如果全部是由你程序控制的,则程序退出后,肯定不能控制啦
是不是要用mysql的event来实现?
---------------------------
用event肯定可以实现c#可以调用mysql的event吗?
-------------------------
可以更改event的一些属性来达到控制
SHOW VARIABLES LIKE 'event_scheduler';如果value是OFF,就是没开,用以下命令开
SET GLOBAL event_scheduler = 1;
创建事件的语法:CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
[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 sql_statement;schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]示例:
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
----------------------------------------------------
更改event:
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO sql_statement]示例:
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;ALTER TABLE myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;ALTER EVENT myevent
DISABLE;ALTER EVENT myevent
RENAME TO yourevent;------------------------------------------------------
建立作业后,查看作业的信息:
mysql> select * from information_schema.events where event_name='event_name';
mysql> show variables like '%sche%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)启动event功能:
mysql> set global event_scheduler =1;
Query OK, 0 rows affected (0.00 sec)
可以试试临时关闭事件,直接用命令是可以的alter event 事件名 disable;重新开启的命令就是:alter event 事件名 enable;
MYSQL 5.1 以上你可以用 create event 创建一个计划任务,drop event 删除这个任务。12.1.11. CREATE EVENT Syntax
http://dev.mysql.com/doc/refman/5.1/en/create-event.html12.1.22. DROP EVENT Syntax
http://dev.mysql.com/doc/refman/5.1/en/drop-event.html
将正常操作sql语句那样去操作就可以了
谢谢提醒,主要是我要在一个winform界面中来进行控制,可以在界面中直接对EVENT进行开启或者终止操作把?我实际的语句就是删除几个表中的n天前的数据,也可以不用存储过程把?
那你只需要在你的EVENT中执行如下语句即可。 下例是从 tableName 表中删除 5 天前的数据。
delete from tableName where ptime < date_sub(now(),interval 5 day);
ACMAIN_CHM上面的写法就是指这样
每次你修改时,能过你的C#要 alter event 来修改event 的代码。
和普通SQL语句一样执行。
你说的 :每次你修改时,能过你的C#要 alter event 来修改event 的代码。是什么意思啊?可以不通过查表就可以把界面的那个值赋给event吗?谢谢了
不是赋给event 而是修改EVENT的代码。另外的确不能用alter event 来实现,只能用 drop , create
drop event xxxx;
create event xxx ..... delete from tableName where ptime < date_sub(now(),interval " + n + " day);"
ptime和 date_sub是数据库中的字段还是mysql特有的变量啊?
ptime是你的表的字段,date_sub是mysql自身带的时间函数
http://dev.mysql.com/doc/refman/5.1/en/create-event.html12.1.22. DROP EVENT Syntax
http://dev.mysql.com/doc/refman/5.1/en/drop-event.html
对照文档理解一下下面的语句。DROP EVENT IF EXISTS event_hahaxj;CREATE EVENT event_hahaxj
ON SCHEDULE EVERY 1 DAY
STARTS '2009-10-21 01:00:00'
DO
delete from a where ptime < date_sub(now(),interval 5 day);
然后在你的这个按键事件中cmd.ExecuteNonQuery() 这些SQL语句。
SQL语句的内容你可以用字符串运算生成。
不是,drop event ... 同样也可以运行。 cmd.ExecuteNonQuery() 不是 ExcuteNonQuery