How does MySQL handle overflow during numeric expression assessment?

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)

Govinda Sai

e

Updated on: 20-Jun-2020

167 Views

Kickstart Your Career

Get certified by completing the course

Get Started