 MySQL 5.1 Reference Manual :: 11 Functions and Operators :: 11.5 Numeric Functions :: 11.5.1 Arithmetic Operators

 MySQL 5.1 Reference Manual
 Preface, Notes, Licenses
 1 General Information
 2 Installing and Upgrading MySQL
 3 Tutorial
 4 MySQL Programs
 5 MySQL Server Administration
 6 Backup and Recovery
 7 Optimization
 8 Language Structure
 9 Internationalization and Localization
 10 Data Types
 11 Functions and Operators
 12 SQL Statement Syntax
 13 Storage Engines
 14 High Availability and Scalability
 15 MySQL Enterprise Monitor
 16 Replication
 17 MySQL Cluster NDB 6.X/7.X
 18 Partitioning
 19 Stored Programs and Views
 20 INFORMATION_SCHEMA Tables
 21 Connectors and APIs
 22 Extending MySQL
 A MySQL 5.1 Frequently Asked Questions
 B Errors, Error Codes, and Common Problems
 C MySQL Change History
 D Restrictions and Limits
 Index
 Standard Index
 C Function Index
 Command Index
 Function Index
 INFORMATION_SCHEMA Index
 Transaction Isolation Level Index
 JOIN Types Index
 Operator Index
 Option Index
 Privileges Index
 SQL Modes Index
 Status Variable Index
 Statement/Syntax Index
 System Variable Index
Table 11.10. Arithmetic Functions
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 withBIGINT
(64bit) 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 thediv_precision_increment
system variable (which is 4 by default). For example, the result of the expression5.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 subcomponents of a calculation use the appropriate level of precision. See Section 11.9, “Cast Functions and Operators”.
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 11.6, “Date and Time Functions”.
Addition:
mysql>
SELECT 3+5;
> 8 
Subtraction:
mysql>
SELECT 35;
> 2 
Unary minus. This operator changes the sign of the argument.
mysql>
SELECT  2;
> 2 
Multiplication:
mysql>
SELECT 3*5;
> 15 mysql>SELECT 18014398509481984*18014398509481984.0;
> 324518553658426726783156020576256.0 mysql>SELECT 18014398509481984*18014398509481984;
> 0The result of the last expression is incorrect because the result of the integer multiplication exceeds the 64bit range of
BIGINT
calculations. (See Section 10.2, “Numeric Types”.) 
Division:
mysql>
SELECT 3/5;
> 0.60Division by zero produces a
NULL
result:mysql>
SELECT 102/(11);
> NULLA 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 withBIGINT
values. Incorrect results may occur for noninteger operands that exceedBIGINT
range.mysql>
SELECT 5 DIV 2;
> 2 
Modulo operation. Returns the remainder of
N
divided byM
. For more information, see the description for theMOD()
function in Section 11.5.2, “Mathematical Functions”.