MYSQL 储存过程 用户登录更新token创建时间，过期时间，账户是否冻结

CREATE  PROCEDURE `memberTables`(username VARCHAR(50),password VARCHAR(50),mac char(17),token varchar(50))
BEGIN

 DECLARE num int;
 DECLARE sqlstatus int;
 DECLARE code int;

 select count(*) into num from ott_member 
 where  name=username  and `password`=password and mac = mac
				and token_create_time <= now() and  now()<= token_expire_time;
 
 if num <= 0
 THEN
		update ott_member a INNER JOIN ( 
			select * FROM ott_member where  name=username  and `password`=password and mac=mac
		) as t 
		on a.id =t.id
		set a.token = token , a.`token_create_time` = now(), a.token_expire_time = date_add(now(),INTERVAL 30 MINUTE);
 END IF; 
 
set sqlstatus = ROW_COUNT();
if sqlstatus > 0 then
    set code = 1;
else
		set code = 0;
end if;
select code;

END

方法二 用户登录更新token创建时间，过期时间，账户是否冻结

DROP FUNCTION IF EXISTS checkLogin;
create FUNCTION checkLogin(tokens varchar(255)) RETURNS int(10)
READS SQL DATA
BEGIN

 DECLARE num varchar(20) DEFAULT '';
 DECLARE nums INT DEFAULT 0;
 DECLARE showStatus varchar(20) DEFAULT '';
 DECLARE var_status varchar(20) DEFAULT '';
 DECLARE var_num varchar(20) DEFAULT '';
 DECLARE done INT DEFAULT 0;

 DECLARE sqlstr cursor for select `status`,count(*) as total  from ott_member where `token`=tokens;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 OPEN sqlstr;
 REPEAT
 FETCH sqlstr INTO var_status,var_num;
 IF NOT done THEN
 set showStatus = var_status;
 set num = var_num;
 end if;
 UNTIL done end repeat;
 close sqlstr;

 if num > 0 THEN
		 if showStatus ='1' THEN
				return 119;
		 END IF;
		 select count(*) into nums from ott_member where `token`=token and now() > token_expire_time;
     if nums > 0 then
			  return 105;
		 ELSE
				return 0;
     end if;
 ELSE
    return 104;
 end if;

return 0;
END

119 账户冻结  105 token过期   104 token不存在   0成功