现有mysql5.1数据库表casual_user:CREATE TABLE `casual_user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`verification` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`del` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`time` datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)需要在每天晚上00点整的时候删除表中del为‘T’,time为一星期前的数据,请问大侠们怎么写sql语句~~
`id` int(11) NOT NULL AUTO_INCREMENT ,
`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`verification` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`del` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`time` datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)需要在每天晚上00点整的时候删除表中del为‘T’,time为一星期前的数据,请问大侠们怎么写sql语句~~
DELIMITER $$CREATE EVENT `FF` ON SCHEDULE EVERY 1 DAY STARTS '2011-05-31 22:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
delete from `casual_user` where del='T' and week(time)=week(now())-1;
END$$DELIMITER ;
DO
DELETE FROM `casual_user` WHERE del='T' AND `time` < curdate() - interval 7 days;