如题, 我写了一个event schedule,没有起作用,也没有任何出错信息,代码如下delimiter |
create event myevent on schedule every 2 second
starts current_timestamp
ends current_timestamp+interval 10 second
do
begin
if v_studentName like '汪%' then
update manager set v_teacherID=v_teacherID+1;
end if;
end;|
delimiter ;
建立定时器后显示Query OK, 0 rows affected (0.01 sec)
但是查询表后发现v_studentName like '汪%'的 v_teacherID没有变,
有什么问题么?
create event myevent on schedule every 2 second
starts current_timestamp
ends current_timestamp+interval 10 second
do
begin
if v_studentName like '汪%' then
update manager set v_teacherID=v_teacherID+1;
end if;
end;|
delimiter ;
建立定时器后显示Query OK, 0 rows affected (0.01 sec)
但是查询表后发现v_studentName like '汪%'的 v_teacherID没有变,
有什么问题么?
你的系统变量 event_scheduler 有没有激活?
SET GLOBAL event_scheduler = ON;
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)确定起用event功能后,执行下面的建立event:(只update不insert哦)
delimiter //
create EVENT my_stat_event
ON SCHEDULE
EVERY 24 HOUR
STARTS concat(date_format(now(),'%Y-%m-%d'),' 23:00:00')
on completion preserve ENABLE
do
begin
update order_total a inner join (select company_id, sum(goods_number) as total_count from order_goods group by company_id) b on a.company_id=b.company_id
set a.total=b.total_count;
end;
//执行上面的建立作业后,查看作业的信息:
mysql> select * from information_schema.events where event_name='my_stat_event';
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF |
+-------------------+
1 row in set (0.00 sec)mysql>如果是OFF,则按楼上的。 SET GLOBAL event_scheduler = ON; 一下。
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)现在我打开了event_scheduler 查询mananger表发现还是没有变化
我的event_scheduler 写的没错吧,那会是什么原因呢
create event myevent on schedule every 2 second
starts current_timestamp
ends current_timestamp+interval 10 second
do
begin
if 1=1 then
update manager set v_teacherID=v_teacherID+1;
end if;
end;|
delimiter ;
将条件改为if 1=1 then
有结果了,但和预测结果不一样,应该是我的字段以v_开头的表示是varchar型的,我把它换成Int型应该会和预测结果一致,但为什么if v_studentName like '汪%' then 这个条件就不能执行呢?
begin
if v_studentName like '汪%' then
update manager set v_teacherID=v_teacherID+1;
end if;
end;
”
这是一个语句块,你把这个语句块拿到一个存储过程上面测试否通,就知道错在哪里了,自己动手试试才明白得透彻,如果在存储过程内部执行都没问题了,那拷贝来这里也可以一样执行了
if v_studentName like '汪%' then 这种只是 v_studentName 为一个变量啊。create event myevent on schedule every 2 second
starts current_timestamp
ends current_timestamp+interval 10 second
do
update manager v_teacherID=v_teacherID+1 where v_studentName like '汪%';
ACMAIN_CHM的方法我试过了,不行.有语法错误
check the manual that corresponds to your MySQL server version for the right syntax to use near '=v_teacherID+1 where v_studentName like '汪%';
starts current_timestamp
ends current_timestamp+interval 10 second
do
update manager set v_teacherID=v_teacherID+1 where v_studentName like '汪%'; 少了一个 set
如果是想改当前插入的这条记录,则
if new.v_studentName like like '王%' then
set new.v_teacherID=10 ;
end if 2. 如果是想把表中已经记录更新 update manager set v_teacherID=10 where v_studentName like '王%'; 则触发器中无法对自身表进行这种操作。
我受到这个因素的影响没有这样写,mysql用户手册我下载了一份,写的挺详细的,但内容不全面,event schedule就没有,又搞了一个英文版的,看的我很辛苦,不知道哪里有最新的资料(中文版的).