MySQL - Return from function only if single row matches-CodePudding

I have a MySQL table of users as follows:

CREATE TABLE `users` (
    `ID` INT NOT NULL, -- NOTE: in practice, I'm using BINARY(16) and
                       --       the UUID()-function to create user IDs.
                       --       'INT' is only a simplification for this
                       --       stackoverflow question.

    `FirstName` NVARCHAR(100) NOT NULL,
    `LastName` NVARCHAR(100) NOT NULL,
    -- ...
    PRIMARY KEY (`ID`)
);

INSERT INTO `users` (`ID`, `FirstName`, `LastName`)
VALUES (0, 'Albus', 'Dumbledore'),
       (1, 'Lord', 'Voldemort'),
       (2, 'Harry', 'Potter'),
       (3, 'Hermione', 'Granger');

I'd like to create a user-defined function which returns the ID of the row matching a FirstName and LastName combination if (and only if) the results are unique (i.e. only one row matches the query):

CREATE FUNCTION `FindUser`(`first_name` NVARCHAR(100), `last_name` NVARCHAR(100)
RETURNS INT
BEGIN
    RETURN (SELECT `ID`
            FROM `users`
            WHERE ((first_name is NULL) OR (`FirstName` LIKE CONCAT('%', first_name, '%')))
            AND ((last_name Is NULL) OR (`LastName` LIKE CONCAT('%', last_name, '%')))
            LIMIT 1);
END

This works as expected on the following examples:

SELECT `FindUser`(NULL, 'potter');
-- | ID |
-- |----|
-- |  2 |

SELECT `FindUser`('obama', NULL);
-- | ID |
-- |----|

However, this does not work on SELECT FindUser(NULL, 'or');, as the token 'or' could match 0 | Albus | Dumbledore and 1 | Lord | Voldemort.

I tried the following:

SET @cnt = 0;
SET @id = NULL;

SELECT @id = u.id, @cnt = COUNT(id)
FROM users u
WHERE ...; -- same conditions as before

RETURN IF(@cnt = 1, @id, NULL);

However, that does not work, as @id and @cnt will always be overwritten by the last line. The alternative would be to perform two queries, but that is inefficient.

How could I solve the problem most efficiently?

CodePudding user response：

Providing you're using a MySql version that supports window functions a simple modification you can make is to conditionally count the number of rows:

RETURN (
  SELECT CASE WHEN count(*) over() = 1 then ID ELSE null END
  FROM users
  WHERE (first_name is NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
    AND (last_name Is NULL OR LastName LIKE CONCAT('%', last_name, '%'))
  LIMIT 1
);

Demo Fiddle

CodePudding user response：

You could use aggregation and set the condition in the HAVING clause:

CREATE FUNCTION FindUser(first_name NVARCHAR(100), last_name NVARCHAR(100))
RETURNS INT
BEGIN
  RETURN (
    SELECT MAX(ID) 
    FROM users
    WHERE (first_name IS NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
      AND (last_name IS NULL OR LastName LIKE CONCAT('%', last_name, '%'))
    GROUP BY NULL -- you can omit this clause 
    HAVING COUNT(*) = 1
  );
END;

See the demo.

I suspect that for the edge case where there is only 1 row in the table and the parameters that you pass for the function are both null you don't want the ID of that row returned.
For this case you should add one more condition in the WHERE clause to make sure that at least one of the parameters is not null:

WHERE (first_name IS NOT NULL OR last_name IS NOT NULL)
  AND (first_name IS NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
  AND (last_name IS NULL OR LastName LIKE CONCAT('%', last_name, '%'))