12.6.2. Mathematical Functions

Table 12.12. Mathematical Functions

Name	Description
`ABS()`	Return the absolute value
`ACOS()`	Return the arc cosine
`ASIN()`	Return the arc sine
`ATAN2()`, `ATAN()`	Return the arc tangent of the two arguments
`ATAN()`	Return the arc tangent
`CEIL()`	Return the smallest integer value not less than the argument
`CEILING()`	Return the smallest integer value not less than the argument
`CONV()`	Convert numbers between different number bases
`COS()`	Return the cosine
`COT()`	Return the cotangent
`CRC32()`	Compute a cyclic redundancy check value
`DEGREES()`	Convert radians to degrees
`EXP()`	Raise to the power of
`FLOOR()`	Return the largest integer value not greater than the argument
`LN()`	Return the natural logarithm of the argument
`LOG10()`	Return the base-10 logarithm of the argument
`LOG2()`	Return the base-2 logarithm of the argument
`LOG()`	Return the natural logarithm of the first argument
`MOD()`	Return the remainder
`PI()`	Return the value of pi
`POW()`	Return the argument raised to the specified power
`POWER()`	Return the argument raised to the specified power
`RADIANS()`	Return argument converted to radians
`RAND()`	Return a random floating-point value
`ROUND()`	Round the argument
`SIGN()`	Return the sign of the argument
`SIN()`	Return the sine of the argument
`SQRT()`	Return the square root of the argument
`TAN()`	Return the tangent of the argument
`TRUNCATE()`	Truncate to specified number of decimal places

All mathematical functions return NULL in the event of an error.

ABS(X)

Returns the absolute value of X.

mysql> SELECT ABS(2);        -> 2mysql> SELECT ABS(-32);        -> 32

This function is safe to use with BIGINT values.

ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ACOS(1);        -> 0mysql> SELECT ACOS(1.0001);        -> NULLmysql> SELECT ACOS(0);        -> 1.5707963267949

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ASIN(0.2);        -> 0.20135792079033mysql> SELECT ASIN('foo');+-------------+| ASIN('foo') |+-------------+|           0 |+-------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+-----------------------------------------+| Level   | Code | Message                                 |+---------+------+-----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |+---------+------+-----------------------------------------+

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X.

mysql> SELECT ATAN(2);        -> 1.1071487177941mysql> SELECT ATAN(-2);        -> -1.1071487177941

ATAN(Y,X), ATAN2(Y,X)

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
```
mysql> SELECT ATAN(-2,2);        -> -0.78539816339745mysql> SELECT ATAN2(PI(),0);        -> 1.5707963267949
```
CEIL(X)

CEIL() is a synonym for CEILING().
CEILING(X)

Returns the smallest integer value not less than X.
```
mysql> SELECT
                    CEILING(1.23);        -> 2mysql> SELECT
                    CEILING(-1.23);        -> -1
```
For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.
CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.
```
mysql> SELECT
                    CONV('a',16,2);        -> '1010'mysql> SELECT
                    CONV('6E',18,8);        -> '172'mysql> SELECT
                    CONV(-17,10,-18);        -> '-H'mysql> SELECT
                    CONV(10+'10'+'10'+0xa,10,10);        -> '40'
```
COS(X)

Returns the cosine of X, where X is given in radians.
```
mysql> SELECT COS(PI());        -> -1
```

COT(X)

Returns the cotangent of X.

mysql> SELECT COT(12);        -> -1.5726734063977mysql> SELECT COT(0);        -> NULL

CRC32(expr)

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.
```
mysql> SELECT
                    CRC32('MySQL');        -> 3259397556mysql> SELECT
                    CRC32('mysql');        -> 2501908538
```

DEGREES(X)

Returns the argument X, converted from radians to degrees.

mysql> SELECT
                    DEGREES(PI());        -> 180mysql> SELECT
                    DEGREES(PI() / 2);        -> 90

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X. The inverse of this function is LOG() (using a single argument only) or LN().
```
mysql> SELECT EXP(2);        -> 7.3890560989307mysql> SELECT
                    EXP(-2);        -> 0.13533528323661mysql> SELECT
                    EXP(0);        -> 1
```
FLOOR(X)

Returns the largest integer value not greater than X.
```
mysql> SELECT
                    FLOOR(1.23);        -> 1mysql> SELECT
                    FLOOR(-1.23);        -> -2
```
For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.
FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 12.5, "String Functions".
HEX(N_or_S)

This function can be used to obtain a hexadecimal representation of a decimal number or a string; the manner in which it does so varies according to the argument's type. See this function's description in Section 12.5, "String Functions", for details.
LN(X)

Returns the natural logarithm of X; that is, the base-e logarithm of X. If X is less than or equal to 0, then NULL is returned.
```
mysql> SELECT LN(2);        -> 0.69314718055995mysql> SELECT LN(-2);        -> NULL
```
This function is synonymous with LOG(X). The inverse of this function is the EXP() function.
LOG(X), LOG(B,X)

If called with one parameter, this function returns the natural logarithm of X. If X is less than or equal to 0, then NULL is returned.

The inverse of this function (when called with a single argument) is the EXP() function.
```
mysql> SELECT LOG(2);        -> 0.69314718055995mysql> SELECT LOG(-2);        -> NULL
```
If called with two parameters, this function returns the logarithm of X to the base B. If X is less than or equal to 0, or if B is less than or equal to 1, then NULL is returned.
```
mysql> SELECT
                    LOG(2,65536);        -> 16mysql> SELECT
                    LOG(10,100);        -> 2mysql> SELECT
                    LOG(1,100);        -> NULL
```
LOG(B,X) is equivalent to LOG(X) / LOG(B).
LOG2(X)

Returns the base-2 logarithm of X.
```
mysql> SELECT
                    LOG2(65536);        -> 16mysql> SELECT
                    LOG2(-100);        -> NULL
```
LOG2() is useful for finding out how many bits a number requires for storage. This function is equivalent to the expression LOG(X) / LOG(2).

LOG10(X)

Returns the base-10 logarithm of X.

mysql> SELECT LOG10(2);        -> 0.30102999566398mysql> SELECT LOG10(100);        -> 2mysql> SELECT LOG10(-100);        -> NULL

LOG10(X) is equivalent to LOG(10,X).

MOD(N,M), N % M, N MOD M

Modulo operation. Returns the remainder of N divided by M.

mysql> SELECT MOD(234,
                    10);        -> 4mysql> SELECT 253 % 7;        -> 1mysql> SELECT MOD(29,9);        -> 2mysql> SELECT 29 MOD 9;        -> 2

This function is safe to use with BIGINT values.

MOD() also works on values that have a fractional part and returns the exact remainder after division:

mysql> SELECT
                    MOD(34.5,3);        -> 1.5

MOD(N,0) returns NULL.

PI()

Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.
```
mysql> SELECT PI();        -> 3.141593mysql> SELECT PI()+0.000000000000000000;        -> 3.141592653589793116
```

POW(X,Y)

Returns the value of X raised to the power of Y.

mysql> SELECT POW(2,2);        -> 4mysql> SELECT POW(2,-2);        -> 0.25

POWER(X,Y)

This is a synonym for POW().
RADIANS(X)

Returns the argument X, converted from degrees to radians. (Note that π radians equals 180 degrees.)
```
mysql> SELECT
                    RADIANS(90);        -> 1.5707963267949
```
RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values. In the following example, note that the sequences of values produced by RAND(3) is the same both places where it occurs.
```
mysql> CREATE TABLE t (i
                    INT);Query OK, 0 rows affected (0.42 sec)mysql> INSERT
                    INTO t VALUES(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT i, RAND() FROM t;+------+------------------+| i    | RAND()           |+------+------------------+|    1 | 0.61914388706828 ||    2 | 0.93845168309142 ||    3 | 0.83482678498591 |+------+------------------+3 rows in set (0.00 sec)mysql> SELECT i, RAND(3) FROM t;+------+------------------+| i    | RAND(3)          |+------+------------------+|    1 | 0.90576975597606 ||    2 | 0.37307905813035 ||    3 | 0.14808605345719 |+------+------------------+3 rows in set (0.00 sec)mysql> SELECT i, RAND() FROM t;+------+------------------+| i    | RAND()           |+------+------------------+|    1 | 0.35877890638893 ||    2 | 0.28941420772058 ||    3 | 0.37073435016976 |+------+------------------+3 rows in set (0.00 sec)mysql> SELECT i, RAND(3) FROM t;+------+------------------+| i    | RAND(3)          |+------+------------------+|    1 | 0.90576975597606 ||    2 | 0.37307905813035 ||    3 | 0.14808605345719 |+------+------------------+3 rows in set (0.01 sec)
```
With a constant initializer, the seed is initialized once when the statement is compiled, prior to execution. If a nonconstant initializer (such as a column name) is used as the argument, the seed is initialized with the value for each invocation of RAND(). (One implication of this is that for equal argument values, RAND() will return the same value each time.)

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:
```
SELECT FLOOR(7 + (RAND() * 5));
```
RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:
```
mysql> SELECT * FROM tbl_name ORDER BY RAND();
```
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:
```
mysql> SELECT * FROM table1, table2 WHERE
                    a=b AND c<d -> ORDER BY RAND() LIMIT
                    1000;
```
RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #49222)
ROUND(X), ROUND(X,D)

Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become zero.
```
mysql> SELECT
                    ROUND(-1.23);        -> -1mysql> SELECT
                    ROUND(-1.58);        -> -2mysql> SELECT
                    ROUND(1.58);        -> 2mysql> SELECT
                    ROUND(1.298, 1);        -> 1.3mysql> SELECT
                    ROUND(1.298, 0);        -> 1mysql> SELECT
                    ROUND(23.298, -1);        -> 20
```
The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places):
```
mysql> SELECT ROUND(150.000,2),
                    ROUND(150,2);+------------------+--------------+| ROUND(150.000,2) | ROUND(150,2) |+------------------+--------------+|           150.00 |          150 |+------------------+--------------+
```
ROUND() uses the following rules depending on the type of the first argument:
- For exact-value numbers, ROUND() uses the "round half away from zero" or "round toward nearest" rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.
- For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.
The following example shows how rounding differs for exact and approximate values:
```
mysql> SELECT ROUND(2.5),
                    ROUND(25E-1);+------------+--------------+| ROUND(2.5) | ROUND(25E-1) |+------------+--------------+| 3          |            2 |+------------+--------------+
```
For more information, see Section 12.19, "Precision Math".

SIGN(X)

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.

mysql> SELECT SIGN(-32);        -> -1mysql> SELECT SIGN(0);        -> 0mysql> SELECT SIGN(234);        -> 1

SIN(X)

Returns the sine of X, where X is given in radians.

mysql> SELECT SIN(PI());        -> 1.2246063538224e-16mysql> SELECT ROUND(SIN(PI()));        -> 0

SQRT(X)

Returns the square root of a nonnegative number X.

mysql> SELECT SQRT(4);        -> 2mysql> SELECT
                    SQRT(20);        -> 4.4721359549996mysql> SELECT
                    SQRT(-16);        -> NULL

TAN(X)

Returns the tangent of X, where X is given in radians.

mysql> SELECT TAN(PI());        -> -1.2246063538224e-16mysql> SELECT TAN(PI()+1);        -> 1.5574077246549

TRUNCATE(X,D)

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.

mysql> SELECT
                    TRUNCATE(1.223,1);        -> 1.2mysql> SELECT
                    TRUNCATE(1.999,1);        -> 1.9mysql> SELECT
                    TRUNCATE(1.999,0);        -> 1mysql> SELECT
                    TRUNCATE(-1.999,1);        -> -1.9mysql> SELECT
                    TRUNCATE(122,-2);       -> 100mysql> SELECT
                    TRUNCATE(10.28*100,0);       -> 1028

All numbers are rounded toward zero.