How does MySQL handle overflow during evaluation of 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 the 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 process the above expression. This is because overflow occurs depending on the range of the operands. For example, the above calculation can be performed by using the DECIMAL value as follows −

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

The above is the detailed content of How does MySQL handle overflow during evaluation of numeric expressions?. For more information, please follow other related articles on the PHP Chinese website!