Mathematical functions.

All mathematical functions returns NULL in the case of a error.

-

Sign. Changes sign of argument.

mysql> select - 2;                -> -2

ABS()

Absolute value.

mysql> select abs(2);             -> 2
mysql> select abs(-32);           -> 32

SIGN()

Sign of argument. Returns -1, 0 or 1.

mysql> select sign(-32);          -> -1
mysql> select sign(0);            -> 0
mysql> select sign(234);          -> 1

MOD()

%

Modulo (like % in C).

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

FLOOR()

Largest integer value not greater than x.

mysql> select floor(1.23);              -> 1
mysql> select floor(-1.23);             -> -2

CEILING()

Smallest integer value not less than x.

mysql> select ceiling(1.23);            -> 2
mysql> select ceiling(-1.23);           -> -1

ROUND(N)

Round argument N to an integer.

mysql> select round(-1.23);             -> -1
mysql> select round(-1.58);             -> -2
mysql> select round(1.58);              -> 2

ROUND(Number,Decimals)

Round argument Number to a number with Decimals decimals.

mysql> select ROUND(1.298, 1);          -> 1.3

EXP(N)

Returns the value of e (the base of natural logarithms) raised to the power of N.

mysql> select exp(2);                   -> 7.389056
mysql> select exp(-2);                  -> 0.135335

LOG(X)

Return the natural logarithm of X.

mysql> select log(2);             -> 0.693147
mysql> select log(-2);            -> NULL

LOG10(X)

return the base-10 logarithm of X.

mysql> select log10(2);           -> 0.301030
mysql> select log10(100);                 -> 2.000000
mysql> select log10(-100);                -> NULL

POW(X,Y)

POWER(X,Y)

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

mysql> select pow(2,2);           -> 4.000000
mysql> select pow(2,-2);                  -> 0.250000

sqrt(X)

Returns the non-negative square root of X.

mysql> select sqrt(4);            -> 2.000000
mysql> select sqrt(20);           -> 4.472136

PI()

Return the value of PI.

mysql> select PI();               -> 3.141593

COS(X)

Return the cosine of X, where X is given in radians.

mysql> select cos(PI());                  -> -1.000000

SIN(X)

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

mysql> select sin(PI());                  -> 0.000000

TAN(X)

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

mysql> select tan(PI()+1);                -> 1.557408

ACOS(X)

Return the arc cosine of X; that is the value whose cosine is X. If X is not in the range -1 to 1 NULL is returned.

mysql> select ACOS(1);                  -> 0.000000
mysql> select ACOS(1.0001);             -> NULL
mysql> select ACOS(0);          -> 1.570796

ASIN(X)

Return the arc sine of X; that is the value whose sine is X. If X is not in the range -1 to 1 NULL is returned.

mysql> select ASIN(0.2);                -> 0.201358
mysql> select ASIN('foo');              -> 0.000000

ATAN(X)

Return the arc tangent of X; that is the value whose tangent is X.

mysql> select ATAN(2);          -> 1.107149
mysql> select ATAN(-2);         -> -1.107149

ATAN2(X,Y)

Return 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.785398
mysql> select ATAN(PI(),0);             -> 1.570796

COT(N)

Return the cotangens of N.

mysql> select COT(12);          -> -1.57267341
mysql> select COT(0);           -> NULL

RAND([X])

Returns a random float, 0 <= x <= 1.0, using the integer expression X as the optional seed value.

mysql> SELECT RAND();           -> 0.5925
mysql> SELECT RAND(20);         -> 0.1811
mysql> SELECT RAND(20);         -> 0.1811
mysql> SELECT RAND();           -> 0.2079
mysql> SELECT RAND();           -> 0.7888

One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.

MIN(X,Y...)

Min value of arguments. Must have 2 or more arguments, else these are GROUP BY functions. The arguments are compared as numbers. If no records are found NULL is returned.

mysql> SELECT MIN(2,0);                         -> 0
mysql> SELECT MIN(34,3,5,767);                  -> 3
mysql> SELECT MIN(a) from table where 1=0;      -> NULL

MAX(X,Y...)

Max value of arguments. Must have 2 or more arguments, else these are GROUP BY functions. The arguments are compared as numbers. If no records are found NULL is returned.

mysql> SELECT MAX(34,3,5,767);                  -> 767
mysql> SELECT MAX(2,0,4,5,34);                  -> 34
mysql> SELECT MAX(a) from table where 1=0;      -> NULL

DEGREES(N)

Return N converted from radians to degrees.

mysql> select DEGREES(PI());                    -> 180.000000

RADIANS(N)

Return N converted from degrees to radians.

mysql> select RADIANS(90);                      -> 1.570796

TRUNCATE(Number, Decimals)

Truncate number Number to Decimals decimals.

mysql> select TRUNCATE(1.223,1);                -> 1.2
mysql> select TRUNCATE(1.999,1);                -> 1.9
mysql> select TRUNCATE(1.999,0);                -> 1

Go to the first, previous, next, last section, table of contents.