I am writing a migration test to ensure that a user was created by the migration. If the user does not exist, the test should throw an error. At first, I thought I could just use a division by zero error to get what I wanted:
SET SESSION sql_mode = 'error_for_division_by_zero';
SELECT 1/COUNT(*) FROM mysql.user WHERE user = 'foo';
SELECT IF(COUNT(*) = 1, 1, date_format(1, 2, 3))
FROM mysql.user WHERE user = 'foo';
It cannot be done without a stored procedure or function, unfortunately. I figured out how to support a function in my app, though. Borrowing the basic procedure idea from this answer, I've come up with this:
DELIMITER | CREATE FUNCTION checkit(doit INTEGER, message VARCHAR(256)) RETURNS INTEGER DETERMINISTIC BEGIN IF doit IS NULL OR doit = 0 THEN SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = message; END IF; RETURN doit; END; |
The idea is that the function can be used in triggers like a
CHECK constraint, or inline in SQL statements. Getting back to my original need to throw an error if a user does not exist, I now use the
checkit() function like this:
SELECT checkit(COUNT(*), 'User "foo" does not exist') FROM mysql.user WHERE user = 'foo';
foo exists, this query returns an integer. If the user does not exist, it throws an error with the message defined there.
Want to use the function for a check constraint, too? He's an example (mimicking this answer), with a tip of the hat to @rouland-bouman:
CREATE TRIGGER mytabletriggerexample BEFORE INSERT FOR EACH ROW BEGIN SET @dummy := checkit( NEW.important_value) >= (fancy * dancy * calculation), 'Your meaningful error message goes here' ); END;
I would rather use
DO, rather than setting a dummy variable, but a MySQL bug prevents that from working, alas.