Table 11.10. Arithmetic Functions
Name | Description |
---|---|
DIV (v4.1.0) |
Integer division |
/ |
Division operator |
- |
Minus operator |
% |
Modulo operator |
+ |
Addition operator |
* |
Times operator |
- |
Change the sign of the argument |
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 arguments are integers.
If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer.
If any of the operands of a
+
,
-
,
/
,
*
,
%
is a real or
string value, then the precision of the result is the
precision of the argument with the maximum precision.
In division performed with
/
, the scale
of the result when using two exact values is the scale of
the first argument 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)
, would
resolve 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 11.9, “Cast Functions and Operators”.
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 11.6, “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 argument.
mysql> SELECT - 2;
-> -2
Multiplication:
mysql>SELECT 3*5;
-> 15 mysql>SELECT 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0 mysql>SELECT 18014398509481984*18014398509481984;
-> 0
The result of the last expression is incorrect because the
result of the integer multiplication exceeds the 64-bit
range of BIGINT
calculations.
(See Section 10.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. Incorrect
results may occur for noninteger operands that exceed
BIGINT
range.
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 11.5.2, “Mathematical Functions”.
User Comments
Add your own comment.