GNU Info
(mysql.info)Mathematical functions
Mathematical Functions
......................
All mathematical functions return `NULL' in case of an error.
`-'
Unary minus. Changes the sign of the argument:
mysql> select - 2;
-> -2
Note that if this operator is used with a `BIGINT', the return
value is a `BIGINT'! This means that you should avoid using `-'
on integers that may have the value of `-2^63'!
`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.
`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
`MOD(N,M)'
`%'
Modulo (like the `%' operator in C). 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
This function is safe to use with `BIGINT' values.
`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'!
`CEILING(X)'
Returns the smallest integer value not less than `X':
mysql> select CEILING(1.23);
-> 2
mysql> select CEILING(-1.23);
-> -1
Note that the return value is converted to a `BIGINT'!
`ROUND(X)'
Returns the argument `X', rounded to the nearest integer:
mysql> select ROUND(-1.23);
-> -1
mysql> select ROUND(-1.58);
-> -2
mysql> select ROUND(1.58);
-> 2
Note that the behavior of `ROUND()' when the argument is half way
between two integers depends on the C library implementation.
Some round to the nearest even number, always up, always down, or
always towards zero. If you need one kind of rounding, you should
use a well-defined function like `TRUNCATE()' or `FLOOR()' instead.
`ROUND(X,D)'
Returns the argument `X', rounded to a number with `D' decimals.
If `D' is `0', the result will have no decimal point or fractional
part:
mysql> select ROUND(1.298, 1);
-> 1.3
mysql> select ROUND(1.298, 0);
-> 1
`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
`LOG(X)'
Returns the natural logarithm of `X':
mysql> select LOG(2);
-> 0.693147
mysql> select LOG(-2);
-> NULL
If you want the log of a number `X' to some arbitary base `B', use
the formula `LOG(X)/LOG(B)'.
`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
`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
`SQRT(X)'
Returns the non-negative square root of `X':
mysql> select SQRT(4);
-> 2.000000
mysql> select SQRT(20);
-> 4.472136
`PI()'
Returns the value of PI. The default shown number of decimals is
5, but MySQL internally uses the full double precession for PI.
mysql> select PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116
`COS(X)'
Returns the cosine of `X', where `X' is given in radians:
mysql> select COS(PI());
-> -1.000000
`SIN(X)'
Returns 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)'
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
`COT(X)'
Returns the cotangent of `X':
mysql> select COT(12);
-> -1.57267341
mysql> select COT(0);
-> NULL
`RAND()'
`RAND(N)'
Returns a random floating-point value in the range `0' to `1.0'.
If an integer argument `N' is specified, it is used as the 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
You can't use a column with `RAND()' values in an `ORDER BY'
clause, because `ORDER BY' would evaluate the column multiple
times. In MySQL Version 3.23, you can, however, do: `SELECT *
FROM table_name ORDER BY RAND()'
This is useful to get a random sample of a set `SELECT * FROM
table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000'.
Note that a `RAND()' in a `WHERE' clause will be re-evaluated
every time the `WHERE' is executed.
`LEAST(X,Y,...)'
With two or more arguments, returns the smallest (minimum-valued)
argument. The arguments are compared using the following rules:
* If the return value is used in an `INTEGER' context, or all
arguments are integer-valued, they are compared as integers.
* If the return value is used in a `REAL' context, or all
arguments are real-valued, they are compared as reals.
* If any argument is a case-sensitive string, the arguments are
compared as case-sensitive strings.
* In other cases, the arguments are compared as
case-insensitive strings:
mysql> select LEAST(2,0);
-> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> select LEAST("B","A","C");
-> "A"
In MySQL versions prior to Version 3.22.5, you can use `MIN()'
instead of `LEAST'.
`GREATEST(X,Y,...)'
Returns the largest (maximum-valued) argument. The arguments are
compared using the same rules as for `LEAST':
mysql> select GREATEST(2,0);
-> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
-> 767.0
mysql> select GREATEST("B","A","C");
-> "C"
In MySQL versions prior to Version 3.22.5, you can use `MAX()'
instead of `GREATEST'.
`DEGREES(X)'
Returns the argument `X', converted from radians to degrees:
mysql> select DEGREES(PI());
-> 180.000000
`RADIANS(X)'
Returns the argument `X', converted from degrees to radians:
mysql> select RADIANS(90);
-> 1.570796
`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:
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
Starting from MySQL 3.23.51 all numbers are rounded towards zero.
If `D' is negative, then the whole part of the number is zeroed
out:
mysql> select truncate(122,-2);
-> 100
Note that as decimal numbers are normally not stored as exact
numbers in computers, but as double values, you may be fooled by
the following result:
mysql> select TRUNCATE(10.28*100,0);
-> 1027
The above happens because 10.28 is actually stored as something
like 10.2799999999999999.
automatically generated by info2www version 1.2.2.9