mysql EVENT定时事件

数据库初步学习：

DELIMITER $$

/*创建 A表*/
DROP TABLE IF EXISTS A;  
CREATE  TABLE  A
(
         UserID varchar(32) NOT NULL, 
         PlatformCode char(1) NOT NULL, 
         RegDate TIMESTAMP NOT NULL default 0, 
         LastestActiveTime TIMESTAMP default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
         PushToken varchar(128)
);

/*模拟注册两个用户*/
  INSERT INTO A(UserID ,PlatformCode ,RegDate ,LastestActiveTime,PushToken) VALUES(5,6,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,678910);
  INSERT INTO A(UserID ,PlatformCode ,RegDate ,LastestActiveTime,PushToken) VALUES(1,2,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,123456);

/*创建定时事件 E_INSERT_TEST 模拟一个用户五秒注册一次的定时事件*/ 
  DROP EVENT IF EXISTS E_INSERT_TEST;
 CREATE EVENT E_INSERT_TEST
 ON SCHEDULE EVERY 5 SECOND
 DO
    INSERT INTO A(UserID ,PlatformCode ,RegDate ,LastestActiveTime,PushToken) VALUES(3,4,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,123321);
   
 
 /*创建定时事件 E_TEST 模拟30秒查找假用户并在user_v0200表中删除的定时事件*/  
 DROP EVENT IF EXISTS E_TEST;
 CREATE EVENT E_TEST
 ON SCHEDULE EVERY 30 SECOND
 DO
    BEGIN
 
   /*创建临时表 tmp_stoken_user */
 DROP TABLE  IF EXISTS tmp_stoken_user;
 CREATE   TABLE  tmp_stoken_user(
  RegDate TIMESTAMP,
  PushToken VARCHAR(256),
  count INT
  );
  
 DROP TABLE IF EXISTS tmp_fake_users;
 CREATE   TABLE tmp_fake_users(
  UserID VARCHAR(32),
  PlatformCode VARCHAR(256));
   /* 把多余的Fake用户存入*/
 
 INSERT INTO tmp_stoken_user(RegDate, PushToken, count) SELECT
        max(RegDate), PushToken, count(*) AS ct FROM A 
     WHERE PushToken <> '' GROUP BY PushToken HAVING ct> 1;
  
    INSERT INTO tmp_fake_users(UserID, PlatformCode)
  SELECT UserID, PlatformCode FROM A 
  WHERE EXISTS(SELECT 1 FROM  tmp_stoken_user
         WHERE user_v0200.PushToken=tmp_stoken_user.PushToken AND
               user_v0200.RegDate <> tmp_stoken_user.RegDate);
 
 /* 构造返回记录集 */
 SELECT * FROM tmp_fake_users;

 /* 直接把A中的假用户删除 */
 DELETE FROM A WHERE EXISTS(
  SELECT 1 FROM tmp_fake_users
        WHERE A.UserID = tmp_fake_users.UserID AND
        A.PlatformCode = tmp_fake_users.PlatformCode);
       
 END

$$
DELIMITER ;