Skip navigation links
**Section Navigation** [Toggle]

MySQL 5.7 Reference Manual :: 12 Functions and Operators :: 12.6 Numeric Functions and Operators :: 12.6.1 Arithmetic Operators

- 12.6 Numeric Functions and Operators
- 12.6.1 Arithmetic Operators
- 12.6.2 Mathematical Functions

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>

-> 8`SELECT 3+5;`

Subtraction:

mysql>

-> -2`SELECT 3-5;`

Unary minus. This operator changes the sign of the operand.

mysql>

-> -2`SELECT - 2;`

Multiplication:

mysql>

-> 15 mysql>`SELECT 3*5;`

-> 324518553658426726783156020576256.0 mysql>`SELECT 18014398509481984*18014398509481984.0;`

-> out-of-range error`SELECT 18014398509481984*18014398509481984;`

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>

-> 0.60`SELECT 3/5;`

Division by zero produces a

`NULL`

result:mysql>

-> NULL`SELECT 102/(1-1);`

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>

-> 2`SELECT 5 DIV 2;`

Modulo operation. Returns the remainder of

divided by`N`

. For more information, see the description for the`M`

`MOD()`

function in Section 12.6.2, “Mathematical Functions”.

Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices

## User Comments