<pre name="code" class="sql"># @note: 用户登录
# @param [IN] cUserName <STR>: 用户名
# @param [IN] cPasswd <STR>: 密码
# @param [IN] uState <INT>: 状态(0:离线1:在线2:隐身)
# @param [IN] sID <INT>: 用户服ID
# @param [IN] pID <INT>: 平台服ID(若无中央服,则默认值为1)
# @param [IN] cIp <STR>: 客户端ip
# @param [IN] lng <FLOAT>:经度
# @param [IN] lat <FLOAT>:纬度
DELIMITER $$
DROP PROCEDURE IF EXISTS procLogin;$$
CREATE PROCEDURE procLogin(IN cUserName TEXT,IN cPasswd TEXT,IN uState INT,IN sID INT,IN pID INT,IN cIp TEXT,IN lng FLOAT,IN lat FLOAT)
top:BEGIN
SET @term = 'unknow';
CALL procCheckUserName(cUserName,@userId,@passwd,@userTable,@uCode,@cError);
IF @uCode = 5 THEN
IF cPasswd = @passwd THEN ######## 密码匹配成功 ########
# 安全锁以防同一个账号在多处同时登陆情况
# ---------------------------------------------------------------------------------------------
SET @sqlStr = CONCAT('UPDATE ',@userTable,' SET ',
'loginid = (SELECT @loginId := loginid),',
'tsession = (SELECT @tableName := tsession),',
'rlock = 1 WHERE userid = ',@userId,' AND NOT rlock');
PREPARE expr FROM @sqlStr;
EXECUTE expr;
SET @isLock = ROW_COUNT();
DEALLOCATE PREPARE expr;
IF @isLock THEN
IF NOT @loginId THEN ######### 首次登陆 #########
SET @limits = 500,@sessionId = 1001,@times = 1;
CALL procGetLoginTable(@limits,@tableName,@lastId,@newFlag);
CALL procRandomString('AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz',20,@token);
SET @sqlStr = CONCAT('INSERT INTO ',@tableName,'(userid,uname,sessionid,state,sID,pID,term,token,times,sx,sy,ex,ey,ip,loginline,updateline) VALUES(',
@userId,
',\'',cUserName,'\'',
',' ,@sessionId,
',' ,uState,
',' ,sID,
',' ,pID,
',\'',@term,'\'',
',\'',@token,'\'',
',' ,@times,
',' ,lng,
',' ,lat,
',' ,lng,
',' ,lat,
',\'',cIp,'\',NOW(),NOW())');
PREPARE expr FROM @sqlStr;
EXECUTE expr;
SET @loginId = LAST_INSERT_ID();
DEALLOCATE PREPARE expr;
IF @newFlag THEN
# -----------------------------------------------------------------------------------------
SET @sqlStr = CONCAT('UPDATE Ro_index_session SET len = len + 1,state = (len >= max) WHERE tid = ',@lastId);
PREPARE expr FROM @sqlStr;
EXECUTE expr;
DEALLOCATE PREPARE expr;
END IF;
# ---------------------------------------------------------------------------------------------------------------------------------------
SET @sqlStr = CONCAT('UPDATE ',@userTable,' SET loginid = @loginId,tsession = @tableName,rlock = 0 WHERE userid = ',@userId,' AND rlock');
PREPARE expr FROM @sqlStr;
EXECUTE expr;
DEALLOCATE PREPARE expr;
SET @uCode = 1,@cError = CONCAT('success_login(first):',cUserName);
#SELECT @uCode,@cError,@userId,@sessionId,@token,'0.0.0.0',0,0,@term,@times;
SELECT @uCode,@cError,@userId,@sessionId;
ELSE ######### 非首次登陆 #########
# # ----------------------------------------------------------------------------------------------
SET @sqlStr = CONCAT('UPDATE ',@userTable,' SET rlock = 0 WHERE userid = ',@userId,' AND rlock');
PREPARE expr FROM @sqlStr;
EXECUTE expr;
DEALLOCATE PREPARE expr;
# CALL procRandomString('AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz',20,@token);
SET @sqlStr = CONCAT('UPDATE ',@tableName,' SET ',
# 'sessionid = (SELECT @sessionId := (sessionid + 1)),',
'sessionid = (SELECT @sessionId := sessionid),',
'state = (SELECT @state := state),',
'state = ',uState,',',
'sID = (SELECT @sID := sID),',
'sID = ',sID,',',
'pID = (SELECT @pID := pID),',
'pID = ',pID,',',
'term = (SELECT @term := term),',
'term = \'',@term,'\',',
'token = (SELECT @token := token),',
# 'token = \'',@token,'\',',
'times = (SELECT @times := (times + 1)),',
'sx = ',lng,',',
'sy = ',lat,',',
'ex = ',lng,',',
'ey = ',lat,',',
'ip = (SELECT @ip := ip),',
'ip = \'',cIp,'\',',
'updateline = NOW(),',
'loginline = (SELECT @loginline := loginline),',
'loginline = NOW(),',
'leaveline = (SELECT @leaveline := leaveline),',
'leaveline = \'0000-00-00 00:00:00\' ',
'WHERE loginid = ',@loginId);
PREPARE expr FROM @sqlStr;
EXECUTE expr;
DEALLOCATE PREPARE expr;
IF @state = 0 THEN
SET @uCode = 1,@cError = 'success_login';
#SELECT @uCode,@cError,@userId,@sessionId,@token,@ip,@sID,@pID,@term,@times;
SELECT @uCode,@cError,@userId,@sessionId;
ELSE
IF @ip <> cIp THEN
# IP或终端型号不同,异地登陆,通知原登陆者被踢下线
SET @uCode = 2,@cError = 'success_login(another)';
#SELECT @uCode,@cError,@userId,@sessionId,@token,@ip,@sID,@pID,@term,@times;
SELECT @uCode,@cError,@userId,@sessionId;
ELSE
SET @uCode = 3,@cError = 'already_login';
#SELECT @uCode,@cError,@userId,@sessionId,@token,@ip,@sID,@pID,@term,@times;
SELECT @uCode,@cError,@userId,@sessionId;
END IF;
END IF;
END IF;
ELSE
SET @uCode = 6,@cError = CONCAT('login_failed:system_busy:',cUserName);
SELECT @uCode,@cError;
END IF;
ELSE ######## 密码不匹配 ########
SET @uCode = 0,@cError = 'error_user_password';
SELECT @uCode,@cError;
END IF;
ELSE
SELECT @uCode,@cError,@userId; ######## 未注册用户 ########
END IF;
END;$$
DELIMITER;$$
如何用 MySQL 进行存储过程编写,以用户登陆为例,结构清晰,涵盖内容很多
最新推荐文章于 2021-11-29 17:25:36 发布