DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK

# (mysql.info.gz) Mathematical functions Info Catalog (mysql.info.gz) Arithmetic functions (mysql.info.gz) Numeric Functions
```
12.4.2 Mathematical Functions
-----------------------------

All mathematical functions return `NULL' in case of an error.

`ABS(X)'
Returns the absolute value of X.

mysql> SELECT ABS(2);
-> 2
mysql> 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);
-> 0.000000
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
-> 1.570796

`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.201358
mysql> SELECT ASIN('foo');
-> 0.000000

`ATAN(X)'
Returns 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

`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.785398
mysql> SELECT ATAN2(PI(),0);
-> 1.570796

`CEILING(X)'
`CEIL(X)'
Returns the smallest integer value not less than X.

mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEIL(-1.23);
-> -1

Note that the return value is converted to a `BIGINT'!

The `CEIL()' alias was added in MySQL 4.0.6.

`COS(X)'
Returns the cosine of X, where X is given in radians.

mysql> SELECT COS(PI());
-> -1.000000

`COT(X)'
Returns the cotangent of X.

mysql> SELECT COT(12);
-> -1.57267341
mysql> 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 be a string and will be treated as one if
it is not.

mysql> SELECT CRC32('MySQL');
-> 3259397556

`CRC32()' is available as of MySQL 4.1.0.

`DEGREES(X)'
Returns the argument X, converted from radians to degrees.

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

`EXP(X)'
Returns the value of `e' (the base of natural logarithms) raised to
the power of X.

mysql> SELECT EXP(2);
-> 7.389056
mysql> SELECT EXP(-2);
-> 0.135335

`FLOOR(X)'
Returns the largest integer value not greater than X.

mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2

Note that the return value is converted to a `BIGINT'!

`LN(X)'
Returns the natural logarithm of X.

mysql> SELECT LN(2);
-> 0.693147
mysql> SELECT LN(-2);
-> NULL

This function was added in MySQL 4.0.3.  It is synonymous with
`LOG(X)' in MySQL.

`LOG(X)'
`LOG(B,X)'
If called with one parameter, this function returns the natural
logarithm of X.

mysql> SELECT LOG(2);
-> 0.693147
mysql> SELECT LOG(-2);
-> NULL

If called with two parameters, this function returns the logarithm
of X for an arbitrary base B.
mysql> SELECT LOG(2,65536);
-> 16.000000
mysql> SELECT LOG(1,100);
-> NULL

The arbitrary base option was added in MySQL 4.0.3.  `LOG(B,X)' is
equivalent to `LOG(X)/LOG(B)'.

`LOG2(X)'
Returns the base-2 logarithm of `X'.

mysql> SELECT LOG2(65536);
-> 16.000000
mysql> SELECT LOG2(-100);
-> NULL

`LOG2()' is useful for finding out how many bits a number would
require for storage.  This function was added in MySQL 4.0.3.  In
earlier versions, you can use `LOG(X)/LOG(2)' instead.

`LOG10(X)'
Returns the base-10 logarithm of X.

mysql> SELECT LOG10(2);
-> 0.301030
mysql> SELECT LOG10(100);
-> 2.000000
mysql> SELECT LOG10(-100);
-> NULL

`MOD(N,M)'
`N % M'
`N MOD M'
Modulo operation.  Returns the remainder of N divided by M.

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

This function is safe to use with `BIGINT' values.  The `N MOD M'
syntax works only as of MySQL 4.1.

As of MySQL 4.1.7, `MOD()' works on values with a fractional part
and returns the exact remainder after division:

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

Before MySQL 4.1.7, `MOD()' rounds arguments with a fractional
value to integers and returns an integer result:

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

`PI()'
Returns the value of PI. The default number of decimals displayed
is five, but MySQL internally uses the full double-precision value
for PI.

mysql> SELECT PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116

`POW(X,Y)'
`POWER(X,Y)'
Returns the value of X raised to the power of Y.

mysql> SELECT POW(2,2);
-> 4.000000
mysql> SELECT POW(2,-2);
-> 0.250000

Returns the argument X, converted from degrees to radians.

-> 1.570796

`RAND()'
`RAND(N)'
Returns a random floating-point value in the range from `0' to
`1.0'.  If an integer argument N is specified, it is used as the
seed value (producing a repeatable sequence).

mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881

You can't use a column with `RAND()' values in an `ORDER BY'
clause, because `ORDER BY' would evaluate the column multiple
times.  As of MySQL 3.23, 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 of a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
-> ORDER BY RAND() LIMIT 1000;

Note that `RAND()' in a `WHERE' clause is re-evaluated every time
the `WHERE' is executed.

`RAND()' is not meant to be a perfect random generator, but
instead a fast way to generate ad hoc random numbers that will be
portable between platforms for the same MySQL version.

`ROUND(X)'
`ROUND(X,D)'
Returns the argument X, rounded to the nearest integer.  With two
arguments, returns X rounded to D decimals.  If D is negative, the
integer part of the number is zeroed out.

mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
mysql> SELECT ROUND(23.298, -1);
-> 20

Note that the behavior of `ROUND()' when the argument is halfway
between two integers depends on the C library implementation.
Different implementations round to the nearest even number, always
up, always down, or always toward zero.  If you need one kind of
rounding, you should use a well-defined function such as

`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);
-> -1
mysql> SELECT SIGN(0);
-> 0
mysql> SELECT SIGN(234);
-> 1

`SIN(X)'
Returns the sine of X, where X is given in radians.

mysql> SELECT SIN(PI());
-> 0.000000

`SQRT(X)'
Returns the non-negative square root of X.

mysql> SELECT SQRT(4);
-> 2.000000
mysql> SELECT SQRT(20);
-> 4.472136

`TAN(X)'
Returns the tangent of X, where X is given in radians.

mysql> SELECT TAN(PI()+1);
-> 1.557408

`TRUNCATE(X,D)'
Returns the number X, truncated to D decimals.  If D is `0', the
result will have no decimal point or fractional part.  If D is
negative, the integer part of the number is zeroed out.

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

Starting from MySQL 3.23.51, all numbers are rounded toward zero.

Note that decimal numbers are normally not stored as exact numbers
in computers, but as double-precision values, so you may be
surprised by the following result:

mysql> SELECT TRUNCATE(10.28*100,0);
-> 1027

This happens because `10.28' is actually stored as something like
`10.2799999999999999'.

``` Info Catalog (mysql.info.gz) Arithmetic functions (mysql.info.gz) Numeric Functions
automatically generated byinfo2html