Name
DAT-07: Create stored programs in strict mode to avoid invalid data assignments
Synopsis
Stored program type checking is very dependent on the
setting of the sql_mode
configuration variable. If a program is created when the sql_mode
variable includes one of the
“strict” settings (STRICT_TRANS_TABLES
or STRICT_ALL_TABLES
), then the program
will reject invalid variable assignments with an error. If neither
of the strict modes is in effect, then the stored program will
generate a warning when invalid data assignments occur, but will
continue execution.
For instance, in the following program, we accidentally
declared a variable as CHAR(1)
instead of INT
:
CREATE PROCEDURE TenPlusTen( ) BEGIN DECLARE a INTEGER DEFAULT 10; DECLARE b CHAR(1) DEFAULT 10; DECLARE c INTEGER; SET c=a+b; SELECT c ; END;
If created in “non-strict” mode, this program generates a warning, but continues execution and returns the wrong result (10+10=11?):
mysql> CALL TenPlusTen( ); +------+ | C | +------+ | 11 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'B' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)
If created in strict mode, the program generates an error during execution, which is clearly better than returning the ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.