mysql - 错误代码: 1109 Unknown table 'CONSUMER' in field list

Question

我希望制作一个通过存储过程进行操作的表。我有两个执行选择和更新的过程。第一个执行完美，但最后一个有问题。绝对感觉像是 mysql 解析器问题。我希望我能在这里找到一些帮助，因为一个项目很快就要到期了。一旦运行第二个 sp，无论是通过 SP 还是只是基本查询，每个后续更新语句都会失败，直到我删除该记录；然后我可以再次插入，然后通过基本查询调用进行更新。我需要使用 SP，因为我正在与运行 NodeJS 的 Web 服务器进行通信。我正在使用 node-mysql 包。我使用的是mysql-5.7。

第二个 SP 错误:错误代码:1109 字段列表中未知表“CONSUMER”

谢谢，时间捕捉器

表架构

    CREATE TABLE IF NOT EXISTS CONSUMER (
        signUpVerified BIT(1),
        signUpTimeStamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        loggedIn BIT(1),
        loginTimeStamp TIMESTAMP NOT NULL,
        consumerName VARCHAR(100),
        consumerEmail VARCHAR(254) UNIQUE NOT NULL,
        consumerPassword VARCHAR(20) NOT NULL,
        consumerAddress VARCHAR(100),
        consumerMapLocation VARCHAR(50),
        consumerMobileNumber VARCHAR(20) UNIQUE NOT NULL,
        consumerBalance FLOAT,
        lastSessionID BINARY(32),
        consumerID INT(30) UNIQUE AUTO_INCREMENT NOT NULL,
        PRIMARY KEY(consumerID)
    );

第一SP

    CREATE DEFINER=`root`@`%` PROCEDURE `CONSUMER_SIGN_UP_VERIFICATION`(IN lastSessionID BINARY(32), IN consumerEmail VARCHAR(254))
    BEGIN 
        IF ((consumerEmail IS NULL) OR (lastSessionID IS NULL)) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "EMAIL ADDRESS & SESSION ID REQUIRED";
        END IF;
        IF (EXISTS(SELECT * FROM CONSUMER WHERE (CONSUMER.consumerEmail = consumerEmail) AND (CONSUMER.signUpVerified = b'0'))) THEN
            IF ((SELECT CONSUMER.signUpTimeStamp FROM CONSUMER WHERE CONSUMER.consumerEmail = consumerEmail) = CHECK_SESSION_ID(lastSessionID, CONCAT("SANTEX", consumerEmail))) THEN
                UPDATE CONSUMER SET CONSUMER.signUpVerified = b'1' WHERE CONSUMER.consumerEmail = consumerEmail;
            ELSE
                DELETE FROM CONSUMER WHERE CONSUMER.consumerEmail = consumerEmail;
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "THIS SIGN UP VERIFICATION EMAIL SETUP HAS BEEN TAMPERED WITH, SO PLEASE START A NEW ACCOUNT";
            END IF;
        ELSE 
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "THIS ACCOUNT IS ALREADY REGISTERED OR THERE IS NO ACCOUNT REGISTERED WITH THIS EMAIL ADDRESS";
        END IF;
    END

第二个SP

    CREATE DEFINER=`root`@`%` PROCEDURE `CONSUMER_LOGIN`(IN consumerEmail VARCHAR(254), IN consumerPassword VARCHAR(20), OUT lastSessionID BINARY(32))
    BEGIN
        IF (EXISTS(SELECT * FROM CONSUMER WHERE (CONSUMER.consumerEmail = consumerEmail) AND (CONSUMER.signUpVerified = b'1'))) THEN
            IF (EXISTS(SELECT * FROM CONSUMER WHERE (CONSUMER.consumerEmail = consumerEmail) AND (CONSUMER.consumerPassword = consumerPassword))) THEN
                SELECT @consumerID := CONSUMER.consumerID FROM CONSUMER NATURAL JOIN CONSUMER_SESSION WHERE (CONSUMER.consumerEmail = consumerEmail) AND (CONSUMER.consumerPassword = consumerPassword) AND ((CONSUMER.loggedIn = b'1') OR ((CONSUMER_SESSION.loginTimeStamp != CONSUMER_SESSION.logoutTimeStamp) AND (CONSUMER_SESSION.logoutTimeStamp IS NULL)));
                IF (@consumerID IS NULL) THEN
                    SET @loginTimeStamp := NOW();
                    SET lastSessionID = CREATE_SESSION_ID(@loginTimeStamp, CONCAT("SANTEX", consumerEmail));
                    UPDATE CONSUMER SET CONSUMER.loggedIn = b'1', CONSUMER.lastSessionID = lastSessionID WHERE (CONSUMER.consumerEmail = consumerEmail) AND (CONSUMER.consumerPassword = consumerPassword);
                    INSERT INTO CONSUMER_SESSION(CONSUMER_SESSION.consumerID, CONSUMER_SESSION.loginTimeStamp, CONSUMER_SESSION.sessionID, CONSUMER_SESSION.logoutTimeStamp) VALUES(@consumerID, @loginTimeStamp, lastSessionID, NULL);
                ELSE 
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "THIS ACCOUNT HAS NOT LOGGED OUT FROM ITS PREVIOUS SESSION OR REQUESTED A PASSWORD RESET. PLEASE TRY AGAIN LATER";
                END IF;
            ELSE 
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "INCORRECT EMAIL ADDRESS OR PASSWORD";
            END IF;
        ELSE 
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "THERE IS NO ACCOUNT REGISTERED WITH THIS EMAIL ADDRESS OR SIGN UP VERIFICATION PENDING";
        END IF;
    END

Answer 1

我很抱歉。我的触发器文件有 1500 行代码。我通过调用 CONSUMER.loggedIn 在触发器中犯了一个错误。感谢您的宝贵时间