第二次调用MySQL存储过程触发1172错误：返回多行结果

错误根源分析 #

1. 变量名与字段名冲突：存储过程输入参数Username与表users_login_attempts的字段Username同名，导致所有WHERE Username = Username的条件被解析为字段等于自身（永远为真），会返回表中所有行，触发1172错误。
2. TIMESTAMPDIFF语法错误：临时锁定提示中的时间计算函数参数顺序错误，且minutes应为大写MINUTE，导致语法异常。
3. 未处理空值场景：用户首次错误登录时，users_login_attempts无对应记录，直接执行SELECT Attempts INTO UAttempts会抛出空值错误。

修复后的存储过程代码 #

CREATE PROCEDURE sp_user_login(
    IN p_Username VARCHAR(75),
    IN p_LoginIP VARBINARY(16),
    IN p_IPGeoLocation VARCHAR(30),
    OUT p_ReturnValue VARCHAR(350)
)
BEGIN
DECLARE UStatus BOOLEAN DEFAULT 0;
DECLARE UStatusID TINYINT(1);
DECLARE UPwdHash VARBINARY(256);
DECLARE UAttempts TINYINT(1) DEFAULT 0;
DECLARE ULastAttempt TIMESTAMP DEFAULT NOW();
DECLARE UErrorMsg VARCHAR(250) DEFAULT 'An error has occurred!';

    # 检查登录尝试表，判断用户是否超时或被锁定（使用带前缀参数避免冲突）
    IF EXISTS (SELECT Username FROM users_login_attempts WHERE ((Username = p_Username) AND ((Attempts = 5 AND DATE_ADD(LastAttempt, INTERVAL 10 MINUTE) <= NOW()) OR Attempts = 7))) THEN
        # 合并查询获取尝试次数和最后尝试时间
        SELECT Attempts, LastAttempt INTO UAttempts, ULastAttempt FROM users_login_attempts WHERE Username = p_Username;

        IF (UAttempts = 5) THEN
            SET UStatus = 0;
            # 修正TIMESTAMPDIFF参数顺序与语法
            SET UErrorMsg = CONCAT('由于多次登录失败，您的账户已被临时锁定，请在 ', TIMESTAMPDIFF(MINUTE, NOW(), DATE_ADD(ULastAttempt, INTERVAL 10 MINUTE)), ' 分钟后重试。');
        ELSE
            SET UStatus = 0;
            SET UErrorMsg = '由于多次登录失败，为安全起见，您的账户已被锁定。';
        END IF;

        # 返回状态和错误信息
        SELECT UStatus AS Status, UErrorMsg AS Error;
    ELSE
        # 查询用户表判断用户是否存在
        IF EXISTS (SELECT Email FROM users WHERE (Email = p_Username OR Mobile = p_Username)) THEN
            # 获取用户状态
            SELECT StatusID INTO UStatusID FROM users WHERE (Email = p_Username OR Mobile = p_Username);
            SET UStatus = 0;
            SET UErrorMsg = '抱歉，用户状态不可用！';
            SET UPwdHash = '';

            # 优化条件判断逻辑
            IF (UStatusID = 1) THEN
                SELECT Password INTO UPwdHash FROM users WHERE (Email = p_Username OR Mobile = p_Username);
                SET UStatus = 1;
                SET UErrorMsg = '';
            ELSEIF (UStatusID = 2) THEN
                SET UStatus = 0;
                SET UErrorMsg = '您的账户已被临时暂停。';
            ELSEIF (UStatusID = 3) THEN
                SET UStatus = 0;
                SET UErrorMsg = '您的账户已被停用。';
            ELSEIF (ISNULL(UStatusID)) THEN
                SET UStatus = 0;
                SET UErrorMsg = '登录前请完成账户注册。';
            END IF;
        ELSE
            # 处理首次错误登录的空值情况
            SELECT COALESCE(Attempts, 0) INTO UAttempts FROM users_login_attempts WHERE Username = p_Username;
            
            IF (UAttempts < 7) THEN
                INSERT INTO users_login_attempts(
                    Username,
                    Attempts,
                    IPAddress,
                    IPGeoLocation
                ) VALUES(
                    p_Username,
                    1,
                    p_LoginIP,
                    p_IPGeoLocation
                ) ON DUPLICATE KEY UPDATE
                    Attempts = Attempts + 1,
                    IPAddress = p_LoginIP,
                    IPGeoLocation = p_IPGeoLocation;
            END IF;
            
            SET UStatus = 0;
            SET UErrorMsg = '提供的凭证无效！';
        END IF;

        # 返回状态、密码哈希和错误信息
        SELECT UStatus AS Status, UPwdHash AS PwdHash, UErrorMsg AS Error;
    END IF;
END

关键修复点说明 #

• 参数名区分：输入参数改为p_Username等带前缀的命名，彻底避免与表字段名冲突，确保条件判断仅针对目标用户。
• 合并查询：将两次独立查询合并为单次查询，减少数据库IO操作，提升执行效率。
• 时间函数修正：修正TIMESTAMPDIFF的参数顺序（正确格式为TIMESTAMPDIFF(单位, 开始时间, 结束时间)），并统一使用大写MINUTE。
• 空值处理：用COALESCE(Attempts, 0)处理首次错误登录时的无记录场景，避免空值赋值错误。
• 逻辑优化：将多个独立IF改为ELSEIF结构，减少不必要的条件判断。

内容的提问来源于stack exchange，提问作者PHP Addict