我正在研究一个存储过程来登录用户,我需要返回玩家id。提供的名称和密码是正确的,当我测试它,结果返回1,但playerID保持NULL
`player_login` (
username TEXT CHARACTER SET utf8,
txtPassword TEXT CHARACTER SET utf8,
OUT playerID INTEGER,
OUT result INTEGER
)
BEGIN
DECLARE password TEXT DEFAULT player_hash_password(txtPassword);
DECLARE num INTEGER DEFAULT 0;
SELECT PlayerID INTO playerID
FROM players
WHERE (LOWER(players.PlayerName)=LOWER(username) OR LOWER(players.PlayerEmail)=LOWER(username))
AND players.PlayerPassword = password
LIMIT 1;
...
为什么PlayerID没有被选择到PlayerID ?PlayerID是一个非空的自动递增整数
参数匹配列名,这是MySQL存储过程的一个长期问题。为参数使用前缀,例如:
`player_login` (
p_username TEXT CHARACTER SET utf8,
p_txtPassword TEXT CHARACTER SET utf8,
OUT p_playerID INTEGER,
OUT p_result INTEGER
)
BEGIN
DECLARE p_password TEXT DEFAULT player_hash_password(p_txtPassword);
DECLARE num INTEGER DEFAULT 0;
SELECT p_PlayerID = playerID
FROM players p
WHERE (LOWER(p.PlayerName)=LOWER(p_username) OR LOWER(p.PlayerEmail)=LOWER(p_username))
AND p.PlayerPassword = p_password
LIMIT 1;
.