Additional languages
« 12.5 Numeric Functions
12.5.2 Mathematical Functions »
Section Navigation      [Toggle]

12.5.1. Arithmetic Operators

Name Description
/ Division operator
CEILING(), CEIL() Return the smallest integer value not less than the argument
DIV Integer division
- Minus operator
% Modulo operator
+ Addition operator
* Times operator
- Change the sign of the argument

The usual arithmetic operators are available. The precision of the result is determined according to the following rules:

  • Note that 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.

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.57692308).

Because of these rules and the method 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 12.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 11.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

    DIV was implemented in MySQL 4.1.0.

  • N % M

    Modulo operation. Returns the remainder of N divided by M. For more information, see the description for the MOD() function in Section 12.5.2, “Mathematical Functions”.


User Comments

Posted by Chris Lacy-Hulbert on October 1 2003 5:59am[Delete] [Edit]

Beware that if one of the values in your arithmetic statment is NULL you will get a NULL result. Perhaps the simplest illustration of this is as below. Note that 4 + NULL does not return 4:

mysql> select 4 + NULL;

+----------+
| 4 + NULL |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

hth
christo

Posted by Ebow Anamuah-Mensah on October 29 2003 10:00pm[Delete] [Edit]

To get around the problem of adding numeric and NULL values

example:
SELECT 4 + NULL;
-> NULL

You can use the IFNULL() function:
SELECT 4 + IFNULL(NULL,0);
-> 4

This is particularly useful when using fields that can be NULL

Ebow

Posted by elrad on November 21 2003 12:21am[Delete] [Edit]

The previous trick:
SELECT 4 + IFNULL(NULL,0);
is perfect to create a progressive sum of a field values, with this query structure:

select field1, @field2 := (ifnull(@field2, 0) + field2) as field2 from tablename

using a mysql variable.

Hi

Posted by Chr. Ludwig on April 30 2004 9:03am[Delete] [Edit]

Hi,

I have two fields:
goals_1 tinyint(3) unsigned default '0'
goals_2 tinyint(3) unsigned default '0'

When I want to substract one result from the other by:

SELECT (goals_1 - goals_2) AS difference FROM mytable;
and goals_2 id bigger than goals_1 so that the result would be negative, I get a large positive number as 'difference' instead.

When I change the field types to not 'unsinged'

goals_1 tinyint(3) default '0'
goals_2 tinyint(3) default '0'

Everything works fine. This problem did not apear in MySQL 3.23.56 which I used before.

Version I am using now is: MySQL 4.0.18-standard

Hope this helps
Chr.

Posted by Sencer on August 11 2004 11:58am[Delete] [Edit]

In reponse to Chr. Ludwig:

You don't have to change field-types, you can instead CAST the variables to a signed type.

http://dev.mysql.com/doc/mysql/en/Cast_Functions.html

Instead of
SELECT (goals_1 - goals_2) AS difference FROM mytable;

you'd have:
SELECT CAST(goals_1 - goals_2 AS SIGNED ) AS difference FROM mytable;


Add your own comment.