The usual arithmetic operators are available. The result is determined according to the following rules:
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 5.05 / 0.014
has a scale of six
decimal places (360.714286
).
These rules are applied for each operation, such that nested calculations imply the precision of each component.
Hence, (14620 / 9432456) / (24250 / 9432456)
, resolves first to (0.0014) / (0.0026)
, with the final result having 8 decimal places (0.60288653
).
Because of these rules and the way they are applied, care should be taken to ensure that components and subcomponents of a calculation use the appropriate level of precision. See Section 12.10, "Cast Functions and Operators".
For information about handling of overflow in numeric expression evaluation, see Section 11.2.6, "Out-of-Range and Overflow Handling".
Arithmetic operators apply to numbers. For other types of values, alternative operations may be available. For
example, to add date values, use DATE_ADD()
;
see Section
12.7, "Date and Time Functions".
Addition:
mysql> SELECT 3+5;
-> 8
Subtraction:
mysql> SELECT 3-5;
-> -2
Unary minus. This operator changes the sign of the operand.
mysql> SELECT - 2;
-> -2
Multiplication:
mysql>SELECT 3*5;
-> 15mysql>SELECT 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0mysql>SELECT 18014398509481984*18014398509481984;
-> out-of-range error
The last expression produces an error because the result of the integer multiplication exceeds the
64-bit range of BIGINT
calculations. (See Section
11.2, "Numeric Types".)
Division:
mysql> SELECT 3/5;
-> 0.60
Division by zero produces a NULL
result:
mysql> SELECT 102/(1-1);
-> NULL
A division is calculated with BIGINT
arithmetic only if performed in a context where its result is
converted to an integer.
Integer division. Similar to FLOOR()
, but is safe with BIGINT
values.
In MySQL 5.7, if either operand has a noninteger type, the operands are converted to DECIMAL
and divided using DECIMAL
arithmetic before converting the result to BIGINT
. If the result exceeds BIGINT
range, an error occurs.
mysql> SELECT 5 DIV 2;
-> 2
Modulo operation. Returns the remainder of N
divided by
M
. For more information, see the description for the MOD()
function in Section 12.6.2, "Mathematical Functions".