Database Reference
In-Depth Information
Raising Errors and Warnings
To produce your own errors within a stored program when you detect something awry,
use the
SIGNAL
statement. This section shows some examples, and
Recipe 9.11
demon‐
strates use of
SIGNAL
within a trigger to reject bad data.
Suppose that an application performs a division operation for which you expect that
the divisor will never be zero, and that you want to produce an error otherwise. You
might expect that you could set the SQL mode properly to produce a divide-by-zero
error (this requires
ERROR_FOR_DIVISION_BY_ZERO
plus strict mode, or just strict mode
as of MySQL 5.7.4). But that works only within the context of data-modification oper‐
ations such as
INSERT
. In other contexts, division by zero produces only a warning:
mysql>
SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES';
mysql>
SELECT 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
mysql>
SHOW WARNINGS;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
To ensure a divide-by-zero error in any context, write a function that performs the
division but checks the divisor first and uses
SIGNAL
to raise an error if the “can't hap‐
pen” condition occurs:
CREATE
FUNCTION
divide
(
numerator
FLOAT
,
divisor
FLOAT
)
RETURNS
FLOAT
DETERMINISTIC
BEGIN
IF
divisor
=
0
THEN
SIGNAL
SQLSTATE
'22012'
SET
MYSQL_ERRNO
=
1365
,
MESSAGE_TEXT
=
'unexpected 0 divisor'
;
END
IF
;
RETURN
numerator
/
divisor
;
END
;
Test the function in a nonmodification context to verify that it produces an error:
mysql>
SELECT divide(1,0);
ERROR 1365 (22012): unexpected 0 divisor
The
SIGNAL
statement specifies a SQLSTATE value plus an optional
SET
clause you can
use to assign values to error attributes.
MYSQL_ERRNO
corresponds to the MySQL-specific
error code, and
MESSAGE_TEXT
is a string of your choice.
