56,677
社区成员
发帖
与我相关
我的任务
分享
#增加测试表及测试数据
create table if not exists a(
id int primary key,
c datetime not null,
d int not null
);
truncate table a;
insert into a(id,c,d) values(1,'2018-01-15 06:00:00',0);
insert into a(id,c,d) values(2,'2018-01-15 10:00:00',0);
#关闭再开启事件
#SET GLOBAL event_scheduler = OFF;
SET GLOBAL event_scheduler = ON;
show variables like '%event_scheduler%';
#创建事件 禁用->删除->创建
#alter event `eventTest` disable; #如果有先禁用避免没有生效
drop event if exists `eventTest`;
CREATE EVENT `eventTest`
ON SCHEDULE
EVERY '2' second #测试成功后可改成 Hour
DO update a set d=d+1 where TIMESTAMPDIFF(Hour, c, '2018-01-15 10:00')>2;
mysql> select * from a;
+----+---------------------+---+
| id | c | d |
+----+---------------------+---+
| 1 | 2018-01-15 10:09:23 | 1 |
| 2 | 2018-01-15 10:00:00 | 0 |
+----+---------------------+---+
2 rows in set (0.02 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.14-log |
+------------+
1 row in set (0.02 sec)
#创建表的代码是:
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`c` timestamp NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ;
# show create table a 显示出来是:
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`d` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;