存储过程学习:http://www.111cn.net/tags.php/mysql%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B/ #查看event是否开启 mysql> show variables like '%sche%'; +---------------------------------------------------+---------+ | Variable_name | Value | +---------------------------------------------------+---------+ | event_scheduler | OFF | | performance_schema | OFF | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | +---------------------------------------------------+---------+ 17 rows in set (0.00 sec) #将事件计划开启 mysql> set global event_scheduler = on; Query OK, 0 rows affected (0.00 sec) #创建表 mysql> create table test( -> id int primary key auto_increment, -> endtime timestamp default CURRENT_TIMESTAMP not null -> ); Query OK, 0 rows affected (0.03 sec) #插入数据 mysql> insert into test values(null,null); Query OK, 1 row affected (0.00 sec) #查询数据是否插入 mysql> select * from test; +----+---------------------+ | id | endtime | +----+---------------------+ | 1 | 2011-01-03 16:57:00 | +----+---------------------+ 1 row in set (0.00 sec) #创建存储过程 mysql> delimiter // mysql> create procedure test_proc() -> begin -> update test set endtime=CURRENT_TIMESTAMP where id=1; -> end; -> // #恢复分隔符为';' mysql> delimiter ; #创建event test_event,每隔30秒将执行存储过程test_proc,将当前时间更新到test表中id=1的记录的endtime字段中去. create event if not exists test_event on schedule every 30 second on completion preserve do call test_proc(); #一会儿后首次查看数据 mysql> select * from test; +----+---------------------+ | id | endtime | +----+---------------------+ | 1 | 2011-01-03 17:00:19 | +----+---------------------+ 1 row in set (0.00 sec) # 一分钟到一分半钟之内再次查看数据 mysql> select * from test; +----+---------------------+ | id | endtime | +----+---------------------+ | 1 | 2011-01-03 17:01:19 | +----+---------------------+ #这看看起来就像数据库中的触发器的作用很相似吧 -- 现在关闭事件任务 mysql> alter event test_event ON -> COMPLETION PRESERVE DISABLE; Query OK, 0 rows affected (0.00 sec) #select查看数据后,再过30秒后再查看数据,发现数据不再改变了 -- 事件的开启 mysql> alter event test_event ON -> COMPLETION PRESERVE ENABLE; Query OK, 0 rows affected (0.00 sec) #将事件计划关闭
mysql> set global event_scheduler = off; Query OK, 0 rows affected (0.00 sec)
/* 注意到了么,这里还包含了三种mysql注释 从‘#’字符从行尾。 从/*序列到后面的*/序列 从‘-- ’序列到行尾。请注意‘-- ’(双破折号)注释风格要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。 */
|