MySQL Arithmetic Arithmetic Operators
Example
MySQL provides the following arithmetic operators
| Operator | Name | Example |
|---|---|---|
+ | Addition | SELECT 3+5; -> 8SELECT 3.5+2.5; -> 6.0SELECT 3.5+2; -> 5.5 |
- | Subtraction | SELECT 3-5; -> -2 |
* | Multiplication | SELECT 3 * 5; -> 15 |
/ | Division | SELECT 20 / 4; -> 5 SELECT 355 / 113; -> 3.1416SELECT 10.0 / 0; -> NULL |
DIV | Integer Division | SELECT 5 DIV 2; -> 2 |
% or MOD | Modulo | SELECT 7 % 3; -> 1 SELECT 15 MOD 4 -> 3 SELECT 15 MOD -4 -> 3SELECT -15 MOD 4 -> -3SELECT -15 MOD -4 -> -3 SELECT 3 MOD 2.5 -> 0.5 |
BIGINT
If the numbers in your arithmetic are all integers, MySQL uses the BIGINT (signed 64-bit) integer data type to do its work. For example:
select (1024 * 1024 * 1024 * 1024 *1024 * 1024) + 1 -> 1,152,921,504,606,846,977
and
select (1024 * 1024 * 1024 * 1024 *1024 * 1024 * 1024 -> BIGINT out of range error
DOUBLE
If any numbers in your arithmetic are fractional, MySQL uses 64-bit IEEE 754 floating point arithmetic. You must be careful when using floating point arithmetic, because many floating point numbers are, inherently, approximations rather than exact values.
