Mysql running mode and 1690 error handling

Suddenly, the last section of a good code can not run, it started giving:

MySQL Documentation said: 1690 - BIGINT UNSIGNED value is out of range in

After several hours of looking for a solution, I found out that the reason for this error is that the two-time fields are subtracted, if there is a time 0000-00-00 caused, the cause is that the result of this subtraction will exceed the range of the Mysql numeric field, triggering 1690 error.

ERROR 1690 Out-of-Range

When the number field in Mysql stores a number that exceeds the allowable range, it triggers the 1690 Out of Range error, and whether the error is triggered depends on the SQL runtime mode:

Data insertion fails when the standard Mode or Strict Mode is run when the non-limiting mode No Restrictive runs, Mysql converts the value to the maximum or minimum value allowed in the range.

Solution

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

The first implementation of the above statement, you can avoid the error in the subtraction process. In the Mysql document, it is clear that the result of the subtraction of two integers is an unsigned number, and before Mysql 5.5.5, if a negative number is generated, mysql converts the number to a maximum value.

mysql> SET sql_mode = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+

Since Mysql 5.5.5, if a negative number is generated, an error ERROR 1690 is generated.

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

About SQL_MODE

SQL_MODE default is empty, there are many options, it is recommended in the production environment set to strict MODE, so that during operation and maintenance to avoid a lot of trouble.

References

  1. Server SQL Modes
  2. Out of Range and Overflow Handling

🤓