event事件语句:
DELIMITER $$ALTER DEFINER=`root`@`%` EVENT `buysell_ev` ON SCHEDULE EVERY 1 DAY STARTS '2011-05-28 14:00:00' ON COMPLETION NOT PRESERVE DISABLE ON SLAVE DO BEGIN
CALL buy_sell();
END$$DELIMITER ;buy_sell()存储过程语句:DELIMITER $$USE `destoon`$$DROP PROCEDURE IF EXISTS `buy_sell`$$CREATE DEFINER=`root`@`%` PROCEDURE `buy_sell`()
BEGIN
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 1,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_sell a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.ADDDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 2,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_sell a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.editdate=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 3,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_buy a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.ADDDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 4,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_buy a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.editDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
END$$DELIMITER ;
谁能帮我解决一下 谢谢 !
DELIMITER $$ALTER DEFINER=`root`@`%` EVENT `buysell_ev` ON SCHEDULE EVERY 1 DAY STARTS '2011-05-28 14:00:00' ON COMPLETION NOT PRESERVE DISABLE ON SLAVE DO BEGIN
CALL buy_sell();
END$$DELIMITER ;buy_sell()存储过程语句:DELIMITER $$USE `destoon`$$DROP PROCEDURE IF EXISTS `buy_sell`$$CREATE DEFINER=`root`@`%` PROCEDURE `buy_sell`()
BEGIN
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 1,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_sell a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.ADDDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 2,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_sell a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.editdate=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 3,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_buy a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.ADDDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
INSERT INTO test_buysell(,DATE,temid,ADDTIME,username,ADDDATE,title,adittime,editdate,vip,catname) SELECT 4,DATE_FORMAT(NOW(),'%Y-%m-%d'),a.itemid,FROM_UNIXTIME(a.ADDTIME),a.username,a.ADDDATE,a.title,FROM_UNIXTIME(a.edittime),a.editdate,a.vip,b.catname FROM de_buy a,de_category b WHERE a.catid=b.catid AND a.STATUS='3' AND a.editDATE=DATE_FORMAT(NOW(),'%Y-%m-%d');
END$$DELIMITER ;
谁能帮我解决一下 谢谢 !
or
SET GLOBAL event_scheduler = ON;
但是在我只想在一台服务器上面运行这个存储过程。
show variables like 'event_scheduler';贴出来看一下。
*************************** 1. row ***************************
Db: destoon
Name: createtb_ev
Definer: root@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2011-05-28 09:25:00
Ends: NULL
Status: SLAVESIDE_DISABLED (这个状态存储过程不会执行的。) Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: destoon
Name: createtb_ev1
Definer: root@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2011-05-28 09:21:00
Ends: NULL
Status: ENABLED (这个状态存储过程可以执行的) Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
mysql> show variables like 'event_sche%'; 这个都是开启的。
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.01 sec)
问:我如何能使得只在一台服务器上执行,另一台不使用存储过程呢?