MySQL Handling Overflow in Numeric Expressions

As we know that MySQL will produce an error if overflow occurs during the assessment of numeric expressions. For example, the largest signed BIGNT is 9223372036854775807, so the following expression will produce an error −

mysql> Select 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807+1)'

MySQL can handle such kind of overflows in following ways:

BY CONVERTING VALUE TO UNSIGNED

MySQL enables such kind of operations by converting the values to unsigned as follows −

mysql> Select CAST(9223372036854775807 AS UNSIGNED) +1;
+------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) +1 |
+------------------------------------------+
|                      9223372036854775808 |
+------------------------------------------+
1 row in set (0.07 sec)

BY USING EXACT-VALUE ARITHMETIC

MySQL can use exact-value arithmetic to handle the preceding expression. It is because overflow occurs depends on the range of the operands. For example, the above calculation can be done by using DECIMAL value as follows −

mysql> Select 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
|     9223372036854775808.0 |
+---------------------------+
1 row in set (0.01 sec)