(mysql.info) mathematical-functions
Info Catalog
(mysql.info) arithmetic-functions
(mysql.info) numeric-functions
12.4.2 Mathematical Functions
-----------------------------
All mathematical functions return `NULL' in the event 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
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
-> 1.5707963267949
* `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.20135792079033
mysql> SELECT ASIN('foo');
+-------------+
| ASIN('foo') |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+
* `ATAN(X)'
Returns the arc tangent of X, that is, the value whose tangent is
X.
mysql> SELECT ATAN(2);
-> 1.1071487177941
mysql> SELECT ATAN(-2);
-> -1.1071487177941
* `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.78539816339745
mysql> SELECT ATAN2(PI(),0);
-> 1.5707963267949
* `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
These two functions are synonymous. Note that the return value is
converted to a `BIGINT'.
* `COS(X)'
Returns the cosine of X, where X is given in radians.
mysql> SELECT COS(PI());
-> -1
* `COT(X)'
Returns the cotangent of X.
mysql> SELECT COT(12);
-> -1.5726734063977
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 to be a string and (if possible) is
treated as one if it is not.
mysql> SELECT CRC32('MySQL');
-> 3259397556
mysql> SELECT CRC32('mysql');
-> 2501908538
* `DEGREES(X)'
Returns the argument X, converted from radians to degrees.
mysql> SELECT DEGREES(PI());
-> 180
mysql> SELECT DEGREES(PI() / 2);
-> 90
* `EXP(X)'
Returns the value of _e_ (the base of natural logarithms) raised
to the power of X.
mysql> SELECT EXP(2);
-> 7.3890560989307
mysql> SELECT EXP(-2);
-> 0.13533528323661
mysql> SELECT EXP(0);
-> 1
* `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'.
* `FORMAT(X,D)'
Formats the number X to a format like `'#,###,###.##'', rounded to
D decimal places, and returns the result as a string. For details,
see string-functions.
* `LN(X)'
Returns the natural logarithm of X; that is, the base-_e_
logarithm of X.
mysql> SELECT LN(2);
-> 0.69314718055995
mysql> SELECT LN(-2);
-> NULL
This function is synonymous with `LOG(X)'.
* `LOG(X)', `LOG(B,X)'
If called with one parameter, this function returns the natural
logarithm of X.
mysql> SELECT LOG(2);
-> 0.69314718055995
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
mysql> SELECT LOG(10,100);
-> 2
`LOG(B,X)' is equivalent to `LOG(X) / LOG(B)'.
* `LOG2(X)'
Returns the base-2 logarithm of `X'.
mysql> SELECT LOG2(65536);
-> 16
mysql> SELECT LOG2(-100);
-> NULL
`LOG2()' is useful for finding out how many bits a number requires
for storage. This function is equivalent to the expression `LOG(X)
/ LOG(2)'.
* `LOG10(X)'
Returns the base-10 logarithm of X.
mysql> SELECT LOG10(2);
-> 0.30102999566398
mysql> SELECT LOG10(100);
-> 2
mysql> SELECT LOG10(-100);
-> NULL
`LOG10(X)' is equivalent to `LOG(10,X)'.
* `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.
`MOD()' also works on values that have a fractional part and
returns the exact remainder after division:
mysql> SELECT MOD(34.5,3);
-> 1.5
* `PI()'
Returns the value of π (pi). The default number of decimal places
displayed is seven, but MySQL uses the full double-precision value
internally.
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
mysql> SELECT POW(2,-2);
-> 0.25
* `RADIANS(X)'
Returns the argument X, converted from degrees to radians. (Note
that π radians equals 180 degrees.)
mysql> SELECT RADIANS(90);
-> 1.5707963267949
* `RAND()', `RAND(N)'
Returns a random floating-point value V between `0' and `1'
inclusive (that is, in the range `0' <= V <= `1.0'). If an integer
argument N is specified, it is used as the seed value, which
produces 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
mysql> SELECT RAND(20);
-> 0.15888261251047
To obtain a random integer R in the range I <= R <= J, use the
expression `FLOOR(I + RAND() * (J - I + 1))'. For example, to
obtain a random integer in the range of 7 to 12 inclusive, you
could use the following statement:
SELECT FLOOR(7 + (RAND() * 6));
You cannot use a column with `RAND()' values in an `ORDER BY'
clause, because `ORDER BY' would evaluate the column multiple
times. However, 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 from 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 is a fast way to generate ad hoc random numbers which is
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 decimal places. D can be
negative to cause D digits left of the decimal point of the value
X to become zero.
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
The return type is the same type as that of the first argument
(assuming that it is integer, double, or decimal). This means
that for an integer argument, the result is an integer (no decimal
places).
Before MySQL 5.0.3, 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.
As of MySQL 5.0.3, `ROUND()' uses the precision math library for
exact-value arguments when the first argument is a decimal value:
* For exact-value numbers, `ROUND()' uses the `round half up'
or `round toward nearest' rule: A value with a fractional
part of .5 or greater is rounded up to the next integer if
positive or down to the next integer if negative. (In other
words, it is rounded away from zero.) A value with a
fractional part less than .5 is rounded down to the next
integer if positive or up to the next integer if negative.
* For approximate-value numbers, the result depends on the C
library. On many systems, this means that `ROUND()' uses the
"round to nearest even" rule: A value with any fractional
part is rounded to the nearest even integer.
The following example shows how rounding differs for exact and
approximate values:
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
For more information, see precision-math.
* `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());
-> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
-> 0
* `SQRT(X)'
Returns the square root of a non-negative number X.
mysql> SELECT SQRT(4);
-> 2
mysql> SELECT SQRT(20);
-> 4.4721359549996
mysql> SELECT SQRT(-16);
-> NULL
* `TAN(X)'
Returns the tangent of X, where X is given in radians.
mysql> SELECT TAN(PI());
-> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
-> 1.5574077246549
* `TRUNCATE(X,D)'
Returns the number X, truncated to D decimal places. If D is `0',
the result has no decimal point or fractional part. D can be
negative to cause D digits left of the decimal point of the value
X to become zero.
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
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1028
All numbers are rounded toward zero.
Info Catalog
(mysql.info) arithmetic-functions
(mysql.info) numeric-functions
automatically generated byinfo2html