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

最新推荐文章于 2024-06-29 21:57:14 发布

原创最新推荐文章于 2024-06-29 21:57:14 发布 · 3.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

MYSQL 专栏收录该内容

57 篇文章

订阅专栏

本文介绍了一种通过存储过程更新用户登录状态及Token有效期的方法，并提供了一个检查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成功