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);
		}
	}