Using SELECT resultset to run UPDATE query with My

问题:

I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status:

-- DROP PROCEDURE IF EXISTS checkUser;
DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    SELECT id, name FROM users WHERE email = in_email AND password = in_password LIMIT 1;
    -- If result is 1, UPDATE users SET online = 1 WHERE id = "result_id";
END //
DELIMITER ;

How Can I make this if-statement based on the resultsets number of rows == 1 or id IS NOT NULL?

回答1:

DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    DECLARE tempId INT DEFAULT 0;
    DECLARE tempName VARCHAR(50) DEFAULT NULL;
    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR 
        SELECT id, name FROM users WHERE email = in_email AND password = in_password;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    REPEAT
        FETCH cur INTO tempId, tempName;
        UPDATE users SET online = 1 WHERE id = tempId;
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;

    SELECT tempName;
END //
DELIMITER ;

NB: I have not tested this. It's possible that MySQL doesn't like UPDATE against a table it currently has a cursor open for.

PS: You should reconsider how you're storing passwords.

---

Re comment about RETURN vs. OUT vs. result set:

RETURN is used only in stored functions, not stored procedures. Stored functions are used when you want to call the routine within another SQL expression.

SELECT LCASE( checkUserFunc(?, ?) );

You can use an OUT parameter, but you have to declare a user variable first to pass as that parameter. And then you have to select that user variable to get its value anyway.

SET @outparam = null;
CALL checkUser(?, ?, @outparam);
SELECT @outparam;

When returning result sets from a stored procedure, it's easiest to use a SELECT query.

回答2:

Use:

UPDATE USERS
   SET online = 1
 WHERE EXISTS(SELECT NULL
                FROM USERS t
               WHERE t.email = IN_EMAIL
                 AND t.password = IN_PASSWORD
                 AND t.id = id)
   AND id = 'result_id'

Why do you have LIMIT 1 on your SELECT? Do you really expect an email and password to be in the db more than once?

回答3:

You could try an if statement if you have an result which returns 1 i looked at yor code, it seems nothing returns a true so you have to refactor it, as above omg wrote thats realy true why do you have an limit 1 in your select query where only one emailadress can exisst? something like this

update users set if(result==1,online=1,online=0) where email=emailadress