CODEWITHSUNDEEP
mysqlstored-procedures
André Fratelli
André Fratelli

Reputation: 6068

Check if a row exists inside a MySQL function

In my user table the IDs user_id are randomly generated by the following functions:

DELIMITER #
CREATE FUNCTION GenerateRandomUserID()
    RETURNS CHAR(15)
    BEGIN
        DECLARE user_id CHAR(15) DEFAULT '';

        WHILE LENGTH(user_id) < 15 DO
            SET user_id = CONCAT(user_id, SUBSTRING('0123456789', RAND() * 10 + 1, 1));
        END WHILE;

        RETURN user_id;
    END#
DELIMITER ;

DELIMITER #
CREATE FUNCTION GenerateUniqueUserID()
    RETURNS CHAR(15)
    BEGIN
        DECLARE user_id CHAR(15) DEFAULT '';

        REPEAT
            SET user_id = GenerateRandomUserID();
        UNTIL NOT UserIDExists(user_id) END REPEAT;

        RETURN user_id;
    END#
DELIMITER ;

I then insert a user generating its ID with this function. So far so good. The problem is when I insert another user, which generates an infinite loop. I managed to boil down the problem to yet another function, UserIDExists, which always returns TRUE (it shouldn't, obviously).

DELIMITER #
CREATE FUNCTION UserIDExists(user_id CHAR(15))
    RETURNS BOOL
    BEGIN
        RETURN EXISTS(SELECT 1 FROM `user` WHERE `user_id` = user_id);
    END#
DELIMITER ;

This last function does not do what it's supposed to, but I can't figure out why. If a single user exists with any password this will return TRUE, only if there are no users does it return FALSE. Can anyone figure out why?

Update:

I also tried this. Same result:

DELIMITER #
CREATE PROCEDURE UserIDExists(IN user_id CHAR(15), OUT user_id_exists BOOL)
    BEGIN
        SET user_id_exists = 0;
        SELECT 1 INTO user_id_exists FROM `user` WHERE `user_id` = user_id;
    END#
DELIMITER ;

Update:

Tried this as well:

DELIMITER #
CREATE PROCEDURE UserIDExists(IN user_id CHAR(15), OUT user_id_exists BOOL)
    BEGIN
        IF ((SELECT `user_id` FROM `user` WHERE `user_id` = user_id) != NULL) THEN
            SET user_id_exists = TRUE;
        ELSE
            SET user_id_exists = FALSE;
        END IF;
    END#
DELIMITER ;

Update:

I tried a few variantes with the following:

DELIMITER #
CREATE PROCEDURE UserIDExists_2(IN user_id CHAR(15), OUT user_id_exists CHAR)
    BEGIN
        SELECT `user_id` FROM `user` WHERE `user_id` = user_id;
    END#
DELIMITER ;

And came to the conclusion that this returns 1 when user_id is also 1 and returns nothing for any other value. This is pretty odd, because this is the only row I have on the table:

# user_id, first_name, last_name, pwd, is_confirmed
'252316605573186', 'André', 'Fratelli', NULL, '0'

This is getting on my nerves...

Upvotes: 1

Views: 1677

Answers (1)

Andr&#233; Fratelli
Andr&#233; Fratelli

Reputation: 6068

This works. I though that the quotes where enough to distinguish the procedure's arguments from the table's columns, but I guess I was wrong.

DELIMITER #
CREATE PROCEDURE UserIDExists(IN p_user_id CHAR(15), OUT p_user_id_exists BOOL)
    BEGIN
        SET p_user_id_exists = EXISTS(SELECT `user_id` FROM `user` WHERE `user_id` = p_user_id);
    END#
DELIMITER ;

Upvotes: 1

Related Questions