Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
Man Pages (TGZ) - 255.4Kb
Man Pages (Zip) - 360.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

12.6.1 Arithmetic Operators

Table 12.9 Arithmetic Operators

Name Description
%, MOD Modulo operator
* Multiplication operator
+ Addition operator
- Minus operator
- Change the sign of the argument
/ Division operator
DIV Integer division

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.1.7, “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
    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;
            -> 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.1, “Numeric Data 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. Discards from the division result any fractional part to the right of the decimal point.

    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, -5 DIV 2, 5 DIV -2, -5 DIV -2;
            -> 2, -2, -2, 2
  • N % M, N MOD M

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