Skip navigation links

Additional languages
11.5.2 Mathematical Functions »
Section Navigation      [Toggle]

11.5.1. Arithmetic Operators

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 sub-components of a calculation use the appropriate level of precision. See Section 11.9, “Cast Functions and Operators”.

  • +

    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

    Note

    If this operator is used with a BIGINT, the return value is also a BIGINT. This means that you should avoid using – on integers that may have the value of –263.

  • *

    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.

  • DIV

    Integer division. Similar to FLOOR(), but is safe with BIGINT values. 

    mysql> SELECT 5 DIV 2;
        -> 2

  • N % M

    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”.