Movatterモバイル変換

MySQL 5.7 Reference 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 withBIGINT (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 theNO_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 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), 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. SeeSection 12.10, “Cast Functions and Operators”.

For information about handling of overflow in numeric expression evaluation, seeSection 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, useDATE_ADD(); seeSection 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 aBIGINT, the return value is also aBIGINT. This means that you should avoid using- on integers that may have the value of −2⁶³.

*

Multiplication:

mysql> SELECT 3*5;        -> 15mysql> SELECT 18014398509481984*18014398509481984.0;        -> 324518553658426726783156020576256.0mysql> 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 ofBIGINT calculations. (SeeSection 11.1, “Numeric Data Types”.)

/
Division:
```
mysql> SELECT 3/5;        -> 0.60
```
Division by zero produces aNULL result:
```
mysql> SELECT 102/(1-1);        -> NULL
```
A division is calculated withBIGINT 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 toDECIMAL and divided usingDECIMAL arithmetic before converting the result toBIGINT. If the result exceedsBIGINT 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 MODM
Modulo operation. Returns the remainder ofN divided byM. For more information, see the description for theMOD() function inSection 12.6.2, “Mathematical Functions”.

PREV HOME UP NEXT