SQL function: If Statement dosen't return specific value-CodePudding

When I INSERT a User who is already in the Database, then the RETURN "Error_UsernameExist" should trigger but instead I get following Error message from the function: Error Code: 1062. Duplicate entry 'testUser' for key 'Username_UNIQUE'

following function:

CREATE FUNCTION `registration`(usernameP TEXT, passwordP TEXT, saltP BLOB) RETURNS TEXT
BEGIN
    IF ((SELECT `Username` FROM `DEV`.`RUser` WHERE `Username` = usernameP) = 1)  THEN
        RETURN "Error_UsernameExist";
    END IF;
    
    INSERT INTO `DEV`.`RUser` (`Username`, `Password`, `Salt`) VALUES (usernameP, passwordP, saltP);
    RETURN "OK";
END

CodePudding user response：

(SELECT `Username` FROM `DEV`.`RUser` WHERE `Username` = usernameP) evaluates to the actual Username being returned. Assuming the Username does not equal 1, your IF statement is not true. Change your IF to IF ((SELECT COUNT(`Username`) FROM `DEV`.`RUser` WHERE `Username` = usernameP) = 1) to check the number of users with that user name. You could also use IF EXISTS instead.

IF EXISTS (SELECT `Username` FROM `DEV`.`RUser` WHERE `Username` = usernameP)