Mysql语法帮助

Question

我在MySQL中编写SP时遇到问题。从我在网上读到的信息来看，以下内容应该是可行的，但事实并非如此。我使用Mysql v5.1.35

CREATE DEFINER=`My_Username`@`%` PROCEDURE `ChangePassword`(IN SPusername VARCHAR(100),
    IN SPoldPassword VARCHAR(100),
    IN SPnewPassword VARCHAR(100))
BEGIN
IF EXISTS(SELECT Password_Hash 
                FROM Customer
                WHERE SPusername = Email 
                AND  SPoldPassword = Password_Hashed)
THEN
UPDATE Customer
SET Password_Hashed = SPnewPassword
END IF;
END;

Answer 1

我认为这是因为过程名称不应该在‘s中。

在第一行中，尝试如下所示：

CREATE DEFINER='My_Username'@'%' PROCEDURE ChangePassword(IN SPusername VARCHAR(100),

哦..。记住还要看一下astander在UPDATE语句中关于WHERE子句的回答。如果不扩展WHERE子句，所有密码都将设置为相同的内容...每次都是这样！他的建议是好的！(尽管SQL语句可以工作) :-)

问候

*Sigersted

PS:完整的查询(带分隔符和astander的建议)：

DELIMITER //
CREATE DEFINER='My_Username'@'%' PROCEDURE ChangePassword(
    IN SPusername VARCHAR(100),
    IN SPoldPassword VARCHAR(100),
    IN SPnewPassword VARCHAR(100))
BEGIN
IF EXISTS(SELECT Password_Hash 
          FROM Customer
          WHERE SPusername = Email 
          AND  SPoldPassword = Password_Hashed)
THEN
  UPDATE Customer
  SET Password_Hashed = SPnewPassword
  WHERE SPusername = Email 
  AND  SPoldPassword = Password_Hashed;
END IF;
END//
DELIMITER ;

Answer 2

看起来您可能在UPDATE语句中缺少IF EXISTS检查中的WHERE语句。

将其更改为

UPDATE Customer
SET Password_Hashed = SPnewPassword
WHERE SPusername = Email
AND  SPoldPassword = Password_Hashed