(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
`RADIANS(X)'
Returns the argument X, converted from degrees to radians.
mysql> SELECT RADIANS(90);
-> 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
`TRUNCATE()' or `FLOOR()' instead.
`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