大家好,我正在尝试手动验证OTP验证。对于我已经编写了此存储过程。当我启动此代码时,此代码工作正常,但是一段时间后,它开始给出错误。花费太长的时间来执行大约50秒,之后它给出了错误锁定的等待时间。因此,有人可以告诉我为什么它给出了这样的错误以及如何解决它?
CREATE DEFINER=`xxxxx`@`xxxx` PROCEDURE `new_mobile_authentication`(
IN in_macID VARCHAR(500),IN in_otp INT(5),OUT in_msg VARCHAR(100))
BEGIN
DECLARE userCount INT(10);
DECLARE emailID VARCHAR(100);
DECLARE mobileNumber BIGINT(11);
DECLARE checkmatched INT(5);
DELETE FROM mob_user WHERE NOW()>end_time;
SELECT COUNT(*),email,mobile,otp into userCount,emailID,mobileNumber,checkmatched FROM mob_user WHERE mac_id=in_macID ;
SET @checkEmailPresent=(SELECT COUNT(*) FROM table A WHERE email_id=emailID);
IF(userCount!=0 AND @checkEmailPresent!=0)THEN
IF(checkmatched=in_otp)THEN
UPDATE table A SET auth='YES',mac_id=in_macID,mobile_num=mobileNumber WHERE email=emailID;
SET @affRow=(SELECT ROW_COUNT());
DELETE FROM mob_user WHERE mac_id=in_macID;
SELECT @affRow AS affRow,email FROM table A WHERE mac_id=in_macID;
ELSE
SELECT 'invalid otp' INTO in_msg;
END IF;
ELSEIF(userCount!=0 AND @checkEmailPresent=0)THEN
IF(checkmatched=in_otp)THEN
INSERT INTO table A(email,mobile_num,mac_id) VALUE (emailID,mobileNumber,in_macID,);
SET @affRow=(SELECT ROW_COUNT());
DELETE FROM mob_user WHERE mac_id=in_macID;
SELECT @affRow AS affRow,email FROM table A WHERE mac_id=in_macID;
ELSE
SELECT 'invalid otp' INTO in_msg;
END IF;
ELSE
SELECT 'session expired' INTO in_msg;
END IF;
END
通过添加索引和/或重新调整查询来修复超时。
mob_user
需要INDEX(end_time)
和INDEX(mac_id)
。
没有GROUP BY
的SELECT COUNT(*), this, that ...
没有意义。GROUP BY
也不会正常工作。您期望什么?
SET @checkEmailPresent=(SELECT COUNT(*) FROM table A WHERE email_id=emailID)
可以重写SELECT @checkEmailPresent := COUNT(*) FROM table A WHERE email_id=emailID)
。注意:=
。该表需要INDEX(email_id)
。
IF(checkmatched=in_otp)THEN
没有任何意义,因为checkmatched
无处不在。不,SELECT
没有设置它。
什么是ROW_COUNT()
?我认为这不是MySQL函数。