Table 12.11. Arithmetic Operators
The usual arithmetic operators are available. The result is determined according to the following rules:
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.
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 (
These rules are applied for each operation, such that nested calculations imply the precision of each component.
(14620 / 9432456) / (24250 / 9432456), resolves first to
(0.0014) / (0.0026), with the final result having 8 decimal places (
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
12.7, "Date and Time Functions".
SELECT 3+5;-> 8
SELECT 3-5;-> -2
Unary minus. This operator changes the sign of the operand.
SELECT - 2;-> -2
SELECT 3*5;-> 15mysql>
SELECT 18014398509481984*18014398509481984.0;-> 324518553658426726783156020576256.0mysql>
SELECT 18014398509481984*18014398509481984;-> out-of-range error
SELECT 3/5;-> 0.60
Division by zero produces a
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.
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
range, an error occurs.
SELECT 5 DIV 2;-> 2