As in any other programming, the usual group of arithmetic operations is available in MySQL as well.
The following table shows all possible operations:
|% or MOD||Modulo operator|
|-||Change the sign of the argument|
The following rules guide the arithmetic operations in MySQL:
- In the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both operands are integers.
- If both operands are integers and any of them are unsigned, the result is an unsigned integer. For subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is signed even if any operand is unsigned.
- If any of the operands of a +, -, /, *, % is a real or string value, the precision of the result is the precision of the operand with the maximum precision.
- In division performed with /, the scale of the result when using two exact-value operands is the scale of the first operand plus the value of the div_precision_increment system variable (which is 4 by default). For example, the result of the expression 1.23 / 0.321 has a scale of six decimal places (3.831775).
A basic example using arithmetic operations would be something like this:
SELECT 2*4; // produces '8'
SELECT MOD (29, 9); // produces '2'
Irregular operations, such as dividing by zero, will produce a NULL value as result.