php - 将 PHP 代码转换为(Mysql 存储过程

Question

我刚刚将以下 PHP 代码转换为 MySQL 存储过程。没有明显的语法错误，因为我可以使用 PHPMyAdmin 执行它。我可以看到它

SELECT routine_definition
FROM information_schema.routines
WHERE routine_schema = 'chess';

由于是第一次写存储过程，想了解一下

• 存储过程是否如我所想的那样工作？ (参见“它应该做什么”中的流程图)
• 存储过程是纯 SQL(符合某些标准)还是只能用于 MySQL 数据库？ MySQL具体是什么？我可以摆脱它吗？
• 有什么方法可以改进这个存储过程吗？有没有我违反的最佳做法？
• 使用存储过程时是否必须清理输入数据？

这是一个简短的overview over the database和 all code .但我希望这不是回答我的问题所必需的。

它应该做什么

新的存储过程

DELIMITER //
CREATE PROCEDURE ChallengeUser(
   IN challengedUserID INT,
   IN currentUserID INT,
   OUT startedGamePlayerUsername varchar(255),
   OUT startedGameID INT,
   OUT incorrectID BIT,
   OUT alreadyChallengedPlayer BIT,
   OUT alreadyChallengedGameID INT
  )
    BEGIN
        SELECT `username` AS startedGamePlayerUsername FROM chess_users
          WHERE `user_id` = challengedUserID 
               AND `user_id` != currentUserID LIMIT 1;

        IF startedGamePlayerUsername IS NOT NULL THEN
            SELECT `id` FROM `chess_games` 
              WHERE `whiteUserID` = currentUserID 
                AND `blackUserID` = challengedUserID 
                AND `outcome` = -1 LIMIT 1;
            IF id IS NULL THEN
                SELECT `softwareID` AS `whitePlayerSoftwareID` 
                  FROM chess_users 
                WHERE `user_id`=currentUserID LIMIT 1;
                SELECT `softwareID` AS `blackPlayerSoftwareID` 
                  FROM chess_users 
                  WHERE `user_id`=challengedUserID LIMIT 1;
                INSERT INTO `chess_games` (`tournamentID`, `whiteUserID`, 
                                  `blackUserID`, `whitePlayerSoftwareID`, 
                                     `blackPlayerSoftwareID`, `moveList`) 
                    VALUES (NULL, currentUserID, challengedUserID, 
                            whitePlayerSoftwareID, 
                            blackPlayerSoftwareID, "");

                /* Get the id of the just inserted tuple */
                SELECT `id` AS startedGameID FROM chess_games 
                  WHERE `whiteUserID` = whitePlayerSoftwareID 
                    AND `blackUserID` = blackPlayerSoftwareID 
                    AND `whitePlayerSoftwareID` = whitePlayerSoftwareID 
                    AND `blackPlayerSoftwareID` = blackPlayerSoftwareID 
                    AND `moveList` = "" LIMIT 1;
            ELSE
                SET alreadyChallengedPlayer = 1;
                SET alreadyChallengedGameID = id;
            END IF;
        ELSE
            SET incorrectID = 1;
        END IF;
    END //
DELIMITER ;

新的 PHP 代码

function challengeUser2($user_id, $t) {
    global $conn;
    $stmt = $conn->prepare("CALL ChallengeUser(?,?,@startedGamePlayerUsername,".
                       ."@startedGameID,@incorrectID,"
                       ."@alreadyChallengedPlayer,@alreadyChallengedGameID)");
    $test = USER_ID;
    $stmt->bindParam(1, $user_id);
    $stmt->bindParam(2, $test);
    $returnValue = $stmt->execute();

    echo "Return Value\n";
    print_r($returnValue);
    echo "################\n\nstmt\n";
    print_r($stmt);

    echo "################\n\nrow\n";
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    print_r($row);
}

打印出来的内容

Return Value
1################

stmt
PDOStatement Object
(
    [queryString] => CALL ChallengeUser(?,?,@startedGamePlayerUsername,
       @startedGameID,@incorrectID,
       @alreadyChallengedPlayer,@alreadyChallengedGameID)
)
################

row
Array
(
    [startedGamePlayerUsername] => test
)

它应该做什么

它应该在表 chess_games 中创建一个新条目。但是没有新条目，incorrectID 或 alreadyChallengedPlayer 也没有值。所以我想我犯了一个错误。

Answer 1

对于如此困惑的代码感到抱歉 - 我在工作 = 没有太多时间，但应该可以帮助您。您必须将数据添加到表中:USERS 和 SOFTWARE。 NULL 处理和将查询结果传递到变量时出现问题。

编辑:修复查询“获取刚刚插入的元组的 id”

DELIMITER $$

DROP PROCEDURE IF EXISTS `ChallengeUser`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ChallengeUser`(
challengedUserID INT,
currentUserID INT,
startedGamePlayerUsername  VARCHAR(255),
startedGameID INT,
incorrectID INT,
alreadyChallengedPlayer INT,
alreadyChallengedGameID INT
)
BEGIN

DECLARE TMP_ID INT DEFAULT 0;
DECLARE TMP_W_PLAYER INT DEFAULT 0;    
DECLARE TMP_B_PLAYER INT DEFAULT 0;


    SELECT `username` INTO startedGamePlayerUsername 
      FROM chess_users
    WHERE `user_id` = challengedUserID 
      AND `user_id` != currentUserID LIMIT 1;

    IF startedGamePlayerUsername IS NOT NULL THEN

        SELECT `id` INTO TMP_ID 
          FROM `chess_games` 
        WHERE `whiteUserID` = currentUserID 
            AND `blackUserID` = challengedUserID 
            AND `outcome` = -1 
        LIMIT 1;

        -- here was bad NULL handling    
        IF TMP_ID IS NULL OR TMP_ID='' THEN

            SELECT `softwareID` INTO TMP_W_PLAYER
              FROM chess_users 
            WHERE `user_id`=currentUserID 
            LIMIT 1;

            SELECT `softwareID` INTO TMP_B_PLAYER 
              FROM chess_users 
            WHERE `user_id`=challengedUserID 
            LIMIT 1;

            INSERT INTO `chess_games` 
        (`tournamentID`, `whiteUserID`,`blackUserID`, `whitePlayerSoftwareID`,`blackPlayerSoftwareID`, `moveList`)
                     SELECT NULL, currentUserID, challengedUserID, TMP_W_PLAYER, TMP_B_PLAYER, "";           


            /* Get the id of the just inserted tuple */
            SELECT max(`id`) INTO startedGameID FROM chess_games 
              WHERE `whiteUserID` = currentUserID 
                AND `blackUserID` = challengedUserID 
                AND `whitePlayerSoftwareID` = TMP_W_PLAYER 
                AND `blackPlayerSoftwareID` = TMP_B_PLAYER
                AND `moveList` = "";
        ELSE
            SET alreadyChallengedPlayer = 1;
            SET alreadyChallengedGameID = TMP_ID;
        END IF;
    ELSE
        SET incorrectID = 1;
    END IF;

    SELECT startedGamePlayerUsername,startedGameID,   incorrectID ,   alreadyChallengedPlayer ,   alreadyChallengedGameID;

END$$

DELIMITER ;