In one of our posts, in the Be Careful While Using UNSIGNED Data Type in the Routine Body one, we’ve explained how to get invalid data in routine body when using UNSIGNED data type and that in this case MySQL does not throw any exceptions.
One of the possible solutions of this problem is explicit setting of the NO_UNSIGNED_SUBTRACTION mode as it is shown in an example in the MySQL documentation.
Let’s modify a routine script:
SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
DROP PROCEDURE IF EXISTS tinyintunsigned_to_tinyint;
DELIMITER $$
CREATE PROCEDURE tinyintunsigned_to_tinyint()
BEGIN
DECLARE v_TINYINT TINYINT;
-- Range for v_TINYINT : -128 .. 0 .. 127
DECLARE v_TINYINTUNSIGNED TINYINT UNSIGNED;
-- Range for …[Read more]