Numeric Functions

Numeric functions deal with numbers, mostly taking numeric arguments and returning numeric results. In the case of an error, they will return NULL. You need to take care not to go beyond the numeric range of a number—most MySQL functions work with BIGINTs (2⁶³ signed, or 2⁶⁴ unsigned), and if you go beyond the range, MySQL will usually return NULL.

ABS

ABS(number)

Returns the absolute value (positive value) of a number. The function is safe for use with BIGINT values.

For example:

mysql> SELECT ABS(24-26); +------------+ | ABS(24-26) | +------------+ |          2 | +------------+

ACOS

ACOS(number)

Returns the arc cosine of number (the inverse cosine). The number must be between –1 and 1 or the function returns NULL.

For example:

mysql> SELECT ACOS(0.9); +-----------+ | ACOS(0.9) | +-----------+ |  0.451027 | +-----------+

ASIN

ASIN(number)

Returns the arc sine of number (the inverse sine). The number must be between –1 and 1 or the function returns NULL.

For example:

mysql> SELECT ASIN(-0.4); +------------+ | ASIN(-0.4) | +------------+ |  -0.411517 | +------------+ 

ATAN

ATAN(number1 [, number2])

Returns the arc tangent of number (the inverse tangent) or of two numbers (the point number1, number2).

For example:

mysql> SELECT ATAN(4); +----------+ | ATAN(4)  | +----------+ | 1.325818 | +----------+ mysql> SELECT ATAN(-4,-3); +-------------+ | ATAN(-4,-3) | +-------------+ |   -2.214297 | +-------------+

ATAN2

ATAN2(number1,number2)

A synonym for ATAN(number1,number2).

CEILING

CEILING(number)

Rounds up the number to the nearest integer and returns it as a BIGINT.

For example:

mysql> SELECT CEILING(2.98); +---------------+ | CEILING(2.98) | +---------------+ |             3 | +---------------+ mysql> SELECT CEILING(-2.98); +----------------+ | CEILING(-2.98) | +----------------+ |             -2 | +----------------+

Use FLOOR() to round down, and use ROUND() to round up or down.

COS

COS(number_radians) 

Returns the cosine of number_radians.

For example:

mysql> SELECT COS(51); +----------+ | COS(51)  | +----------+ | 0.742154 | +----------+

COT

COT(number_radians)

Returns the cotangent of number_radians.

For example:

mysql> SELECT COT(0.45); +------------+ | COT(0.45)  | +------------+ | 2.07015736 | +------------+

DEGREES

DEGREES(number)

Converts the number from radians to degrees and returns the result.

For example:

mysql> SELECT DEGREES(2); +-----------------+ | DEGREES(2)      | +-----------------+ | 114.59155902616 | +-----------------+ mysql> SELECT DEGREES(PI()/2); +-----------------+ | DEGREES(PI()/2) | +-----------------+ |              90 | +-----------------+

EXP

EXP(number)

Returns the number e (the base of natural logarithms) raised to the specified power.

For example:

mysql> SELECT EXP(1); +----------+ | EXP(1)   | +----------+ | 2.718282 | +----------+ mysql> SELECT EXP(2.3); +----------+ | EXP(2.3) | +----------+ | 9.974182 | +----------+ mysql> SELECT EXP(0.3); +----------+ | EXP(0.3) | +----------+ | 1.349859 | +----------+

FLOOR

FLOOR(number)

Rounds the number down to the nearest integer and returns it as a BIGINT.

For example:

mysql> SELECT FLOOR(2.98); +-------------+ | FLOOR(2.98) | +-------------+ |           2 | +-------------+ mysql> SELECT FLOOR(-2.98); +--------------+ | FLOOR(-2.98) | +--------------+ |           -3 | +--------------+

Use CEILING() to round up, and ROUND() to round up or down.

FORMAT

FORMAT(number,decimals)

Formats the number to a format with each three digits separated by a comma and rounds the result to the specified number of places.

For example:

mysql> SELECT FORMAT(88777634.232,2); +------------------------+ | FORMAT(88777634.232,2) | +------------------------+ | 88,777,634.23          | +------------------------+

GREATEST

GREATEST(argument1, argument2 [, ...])

Returns the largest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).

For example:

mysql> SELECT GREATEST(-3,-4,5); +-------------------+ | GREATEST(-3,-4,5) | +-------------------+ |                 5 | +-------------------+ mysql> SELECT GREATEST('Pa','Ma','Ca'); +--------------------------+ | GREATEST('Pa','Ma','Ca') | +--------------------------+ | Pa                       | +--------------------------+

LEAST

LEAST(argument1, argument2 [, ...])

Returns the smallest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).

For example:

mysql> SELECT LEAST(-3,-4,5); +----------------+ | LEAST(-3,-4,5) | +----------------+ |             -4 | +----------------+ mysql> SELECT LEAST('Pa','Ma','Ca'); +-----------------------+ | LEAST('Pa','Ma','Ca') | +-----------------------+ | Ca                    | +-----------------------+ 

LN

LN(number)

Synonym for the LOG(number) function.

LOG

LOG(number1 [, number2])

Returns the natural logarithm of number1 if there's one argument. You can also use an arbitrary base by supplying a second argument, in which case the function returns LOG(number2) / LOG(number1).

For example:

mysql> SELECT LOG(2); +----------+ | LOG(2)   | +----------+ | 0.693147 | +----------+ mysql> SELECT LOG(2,3); +----------+ | LOG(2,3) | +----------+ | 1.584963 | +----------+

LOG10

LOG10(number1)

Returns the base 10 logarithm of number1. This is equivalent to LOG(number1)/LOG(10).

For example:

mysql> SELECT LOG10(100); +------------+ | LOG10(100) | +------------+ |   2.000000 | +------------+ 

LOG2

LOG2(number1)

Returns the base 2 logarithm of number1. This is equivalent to LOG(number1)/LOG(2).

For example:

mysql> SELECT LOG2(4); +----------+ | LOG2(4)  | +----------+ | 2.000000 | +----------+

MOD

MOD(number1,number2)

Returns the modulus of number1 and number2 (the remainder of number1 divided by number2). This is the same as the % operator. This is safe to use with BIGINTs.

For example:

mysql> SELECT MOD(15,4); +-----------+ | MOD(15,4) | +-----------+ |         3 | +-----------+ mysql> SELECT MOD(3,-2); +-----------+ | MOD(3,-2) | +-----------+ |         1 | +-----------+

PI

PI()

Returns the value of pi (or at least a close representation). MySQL uses the full double precision but only returns five characters by default.

For example:

mysql> SELECT PI(); +----------+ | PI()     | +----------+ | 3.141593 | +----------+ mysql> SELECT PI() + 0.0000000000000000; +---------------------------+ | PI() + 0.0000000000000000 | +---------------------------+ |        3.1415926535897931 | +---------------------------+ 

POW

POW(number1,number2)

This function is a synonym for POWER(number1,number2).

POWER

POWER(number1,number2)

Raises number1 to the power of number2 and returns the value.

For example:

mysql> SELECT POWER(2,3); +------------+ | POWER(2,3) | +------------+ |   8.000000 | +------------+

RADIANS

RADIANS(number1)

Converts the number from degrees to radians and returns the result.

For example:

mysql> SELECT RADIANS(180); +-----------------+ | RADIANS(180)    | +-----------------+ | 3.1415926535898 | +-----------------+

RAND

RAND([number])

Returns a random number (a float) between 0 and 1. The argument is the random number seed. It's common to use the timestamp as a seed. The function can be used to return a result set in random order.

For example:

mysql> SELECT RAND(); +------------------+ | RAND()           | +------------------+ | 0.70100469486881 | +------------------+ mysql> SELECT RAND(20021010081523); +----------------------+ | RAND(20021010081523) | +----------------------+ |     0.80558716673924 | +----------------------+ mysql> SELECT * FROM t1 ORDER BY RAND() LIMIT 1; +----+ | f1 | +----+ | 20 | +----+

ROUND

ROUND(number1 [, number2])

Returns the argument number1, rounded to the nearest integer. You can supply a second argument to specify the number of decimals to round to (the default is 0, or no decimals). The rounding behavior for numbers exactly in the middle is based upon the underlying C library.

For example:

mysql> SELECT ROUND(2.49); +-------------+ | ROUND(2.49) | +-------------+ |           2 | +-------------+ mysql> SELECT ROUND(2.51); +-------------+ | ROUND(2.51) | +-------------+ |           3 | +-------------+ mysql> SELECT ROUND(-2.49,1); +----------------+ | ROUND(-2.49,1) | +----------------+ |           -2.5 | +----------------+ 

SIGN

SIGN(number)

Returns –1, 0, or 1 depending on whether the argument is negative, zero or not a number, or positive.

For example:

mysql> SELECT SIGN(-7); +----------+ | SIGN(-7) | +----------+ |       -1 | +----------+ mysql> SELECT SIGN('a'); +-----------+ | SIGN('a') | +-----------+ |         0 | +-----------+

SIN

SIN(number_radians)

Returns the sine of number_radians.

For example:

mysql> SELECT SIN(45); +----------+ | SIN(45)  | +----------+ | 0.850904 | +----------+

SQRT

SQRT(number)

Returns the square root of the argument.

For example:

mysql> SELECT SQRT(81); +----------+ | SQRT(81) | +----------+ | 9.000000 | +----------+

TAN

TAN(number_radians) 

Returns the tangent of number_radians.

For example:

mysql> SELECT TAN(66); +----------+ | TAN(66)  | +----------+ | 0.026561 | +----------+

TRUNCATE

TRUNCATE(number,decimals)

Truncates (or increases) the number to the specified number of decimal places.

For example:

mysql> SELECT TRUNCATE(2.234,2); +-------------------+ | TRUNCATE(2.234,2) | +-------------------+ |              2.23 | +-------------------+ mysql> SELECT TRUNCATE(2.4,5); +-----------------+ | TRUNCATE(2.4,5) | +-----------------+ |         2.40000 | +-----------------+ mysql> SELECT TRUNCATE(2.998,0); +-------------------+ | TRUNCATE(2.998,0) | +-------------------+ |                 2 | +-------------------+ mysql> SELECT TRUNCATE(-12.43,1); +--------------------+ | TRUNCATE(-12.43,1) | +--------------------+ |              -12.4 | +--------------------+