mysql定时任务

1.语法讲解

SET GLOBAL event_scheduler = 1;

或
SET GLOBAL event_scheduler = ON;
来开启，也可以直接在启动命令加上“–event_scheduler=1”，例如：
mysqld ... --event_scheduler=1
my.ini or my.cnf 中的
[mysqld]
添加 event_scheduler=ON
创建事件(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}

2.实际演示

实例1：

public function addorder() {$data ['order_sn'] = '5555588888';$data ['user_id'] = 5555;$data ['status'] = 0;$data ['goods_id'] = 1555;$data ['sku_id'] = 0;$data ['price'] = 50;$re = M ( 'big.ih_order' )->add ( $data );if ($re) {$sql = "CREATE EVENT order_".$re."                    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE                     ON COMPLETION NOT PRESERVE                    DO                     begin                    update big.ih_order set status=1 where id=".$re.";                    update big.ih_store set number=number+1 where id=1;                    end                    ";$res=M()->execute($sql);}echo $re;echo '<br/>';echo $res;}

实例2

public function addorderevent($uid=21,$oid=602){$otime = M('chaindb.user_set')->field('otime')->where("uid={$uid}")->find();    if(!empty($otime)&&$otime['otime']>0){/*/*查询已经过期订单，并还原对应库存*/    $sql = "CREATE EVENT order_".$oid."                    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL ".$otime['otime']." SECOND                     ON COMPLETION NOT PRESERVE                    DO                     begin                    update chaindb.cus_order as co, chaindb.user_goods as g,chaindb.cus_order_detail as o set g.inventory=g.inventory+o.quantity,g.sales=g.sales-o.quantity,co.ostatus=0 where co.oid=".$oid." and co.ostatus=1 and co.pstatus=0 and (co.paytype=0 or co.paytype>1) and co.oid=o.oid and o.oid=".$oid."  and g.gid=o.gid and o.aid=0;                    update chaindb.cus_order as co, chaindb.user_goods as g,chaindb.cus_order_detail as o,chaindb.user_goods_attr as a set a.inventory=a.inventory+o.quantity,g.sales=g.sales-o.quantity,co.ostatus=0 where co.oid=".$oid." and co.ostatus=1 and co.pstatus=0 and (co.paytype=0 or co.paytype>1) and co.oid=o.oid and o.oid=".$oid."  and g.gid=o.gid and o.aid>0 and o.aid=a.aid;                    end                    ";$res=M()->execute($sql);}}