我有一个存储过程,它检查是否有一个用户的电子邮件地址与输入相同,如果没有,则注册一个。
CREATE TABLE IF NOT EXISTS `overkill`.`accounts` (
`accountID` INT NOT NULL AUTO_INCREMENT ,
`email` VARCHAR(64) NOT NULL ,
`firstName` VARCHAR(32) NOT NULL ,
`lastName` VARCHAR(32) NOT NULL ,
`passSaltedSHA` BINARY(20) NOT NULL ,
`salt` BINARY(20) NOT NULL ,
`gender` ENUM('m','f') NOT NULL ,
`birthDate` DATE NOT NULL ,
`regTime` TIMESTAMP NOT NULL ,
PRIMARY KEY (`accountID`) )
ENGINE = InnoDB;
下面是存储过程:
DELIMITER $$
CREATE PROCEDURE `overkill`.`registerUser` (
IN emailIN VARCHAR(64),
IN passwordIN VARCHAR(16),
IN firstNameIN VARCHAR(32),
IN lastNameIn VARCHAR(32),
IN birthIN DATE,
IN genderIN ENUM('f','m'))
BEGIN
DECLARE existingMailAccLOG INT DEFAULT NULL;
DECLARE saltLOC CHAR(40);
DECLARE regSuccessLOC BOOLEAN DEFAULT FALSE;
SELECT COUNT(*) INTO existingMailAccLOG FROM `overkill`.`accounts` WHERE `accounts`.`email` = emailIN;
IF existingMailAccLOG = 0 THEN
SET saltLOC = SHA1(rand());
SET regSuccessLOC = TRUE;
INSERT INTO `overkill`.`accounts` (`email`, `firstName`, `lastName`, `passSaltedSHA`, `salt`, `gender`, `birthDate`) VALUES(emailIN, firstNameIN, lastNameIn, UNHEX(SHA1(CONCAT(passwordIN, saltLOC))), UNHEX(saltLOC), genderIN, birthIN);
END IF;
SELECT regSuccessLOC AS `registered`, saltLOC AS `salt`;
END
如果我调用
CALL registerUser("abc@def.com", "pass", "firstn", "lastn", "2012-01-01", "f");
It 将一行插入到accounts
表中,但是忘记返回我在IF
SET saltLOC = SHA1(rand());
SET regSuccessLOC = TRUE;
这怎么可能?为什么这些行被跳过而INSERT仍然执行,没有错误?
尝试在DECLARE
关键字之后的变量名前面添加"@"。它可能会引起一些混淆,如下所述:MySQL: @variable vs. variable。有什么区别吗?(第2部分)和这里:MySQL: @variable vs. variable。有什么区别吗?