Table 14.10 Mathematical Functions
Name | Description |
---|---|
ABS() |
Return the absolute value |
ACOS() |
Return the arc cosine |
ASIN() |
Return the arc sine |
ATAN() |
Return the arc tangent |
ATAN2() , ATAN() |
Return the arc tangent of the two arguments |
CEIL() |
Return the smallest integer value not less than the argument |
CEILING() |
Return the smallest integer value not less than the argument |
CONV() |
Convert numbers between different number bases |
COS() |
Return the cosine |
COT() |
Return the cotangent |
CRC32() |
Compute a cyclic redundancy check value |
DEGREES() |
Convert radians to degrees |
EXP() |
Raise to the power of |
FLOOR() |
Return the largest integer value not greater than the argument |
LN() |
Return the natural logarithm of the argument |
LOG() |
Return the natural logarithm of the first argument |
LOG10() |
Return the base-10 logarithm of the argument |
LOG2() |
Return the base-2 logarithm of the argument |
MOD() |
Return the remainder |
PI() |
Return the value of pi |
POW() |
Return the argument raised to the specified power |
POWER() |
Return the argument raised to the specified power |
RADIANS() |
Return argument converted to radians |
RAND() |
Return a random floating-point value |
ROUND() |
Round the argument |
SIGN() |
Return the sign of the argument |
SIN() |
Return the sine of the argument |
SQRT() |
Return the square root of the argument |
TAN() |
Return the tangent of the argument |
TRUNCATE() |
Truncate to specified number of decimal places |
All mathematical functions return NULL
in the
event of an error.
Returns the absolute value of
X
, orNULL
ifX
isNULL
.The result type is derived from the argument type. An implication of this is that
ABS(-9223372036854775808)
produces an error because the result cannot be stored in a signedBIGINT
value.mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32
This function is safe to use with
BIGINT
values.Returns the arc cosine of
X
, that is, the value whose cosine isX
. ReturnsNULL
ifX
is not in the range-1
to1
, or ifX
isNULL
.mysql> SELECT ACOS(1); -> 0 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.5707963267949
Returns the arc sine of
X
, that is, the value whose sine isX
. ReturnsNULL
ifX
is not in the range-1
to1
, or ifX
isNULL
.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' | +---------+------+-----------------------------------------+
Returns the arc tangent of
X
, that is, the value whose tangent isX
. ReturnsNULL
ifX
isNULL
mysql> SELECT ATAN(2); -> 1.1071487177941 mysql> SELECT ATAN(-2); -> -1.1071487177941
Returns the arc tangent of the two variables
X
andY
. It is similar to calculating the arc tangent of
, except that the signs of both arguments are used to determine the quadrant of the result. ReturnsY
/X
NULL
ifX
orY
isNULL
.mysql> SELECT ATAN(-2,2); -> -0.78539816339745 mysql> SELECT ATAN2(PI(),0); -> 1.5707963267949
Returns the smallest integer value not less than
X
. ReturnsNULL
ifX
isNULL
.mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1
For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.
Converts numbers between different number bases. Returns a string representation of the number
N
, converted from basefrom_base
to baseto_base
. ReturnsNULL
if any argument isNULL
. The argumentN
is interpreted as an integer, but may be specified as an integer or a string. The minimum base is2
and the maximum base is36
. Iffrom_base
is a negative number,N
is regarded as a signed number. Otherwise,N
is treated as unsigned.CONV()
works with 64-bit precision.CONV()
returnsNULL
if any of its arguments areNULL
.mysql> SELECT CONV('a',16,2); -> '1010' mysql> SELECT CONV('6E',18,8); -> '172' mysql> SELECT CONV(-17,10,-18); -> '-H' mysql> SELECT CONV(10+'10'+'10'+X'0a',10,10); -> '40'
Returns the cosine of
X
, whereX
is given in radians. ReturnsNULL
ifX
isNULL
.mysql> SELECT COS(PI()); -> -1
Returns the cotangent of
X
. ReturnsNULL
ifX
isNULL
.mysql> SELECT COT(12); -> -1.5726734063977 mysql> SELECT COT(0); -> out-of-range error
Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is
NULL
if the argument isNULL
. 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
Returns the argument
X
, converted from radians to degrees. ReturnsNULL
ifX
isNULL
.mysql> SELECT DEGREES(PI()); -> 180 mysql> SELECT DEGREES(PI() / 2); -> 90
Returns the value of e (the base of natural logarithms) raised to the power of
X
. The inverse of this function isLOG()
(using a single argument only) orLN()
.If
X
isNULL
, this function returnsNULL
.mysql> SELECT EXP(2); -> 7.3890560989307 mysql> SELECT EXP(-2); -> 0.13533528323661 mysql> SELECT EXP(0); -> 1
Returns the largest integer value not greater than
X
. ReturnsNULL
ifX
isNULL
.mysql> SELECT FLOOR(1.23), FLOOR(-1.23); -> 1, -2
For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.
Formats the number
X
to a format like'#,###,###.##'
, rounded toD
decimal places, and returns the result as a string. For details, see Section 14.8, “String Functions and Operators”.This function can be used to obtain a hexadecimal representation of a decimal number or a string; the manner in which it does so varies according to the argument's type. See this function's description in Section 14.8, “String Functions and Operators”, for details.
Returns the natural logarithm of
X
; that is, the base-e logarithm ofX
. IfX
is less than or equal to 0.0E0, the function returnsNULL
and a warning “Invalid argument for logarithm” is reported. ReturnsNULL
ifX
isNULL
.mysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL
This function is synonymous with
LOG(
. The inverse of this function is theX
)EXP()
function.If called with one parameter, this function returns the natural logarithm of
X
. IfX
is less than or equal to 0.0E0, the function returnsNULL
and a warning “Invalid argument for logarithm” is reported. ReturnsNULL
ifX
orB
isNULL
.The inverse of this function (when called with a single argument) is the
EXP()
function.mysql> SELECT LOG(2); -> 0.69314718055995 mysql> SELECT LOG(-2); -> NULL
If called with two parameters, this function returns the logarithm of
X
to the baseB
. IfX
is less than or equal to 0, or ifB
is less than or equal to 1, thenNULL
is returned.mysql> SELECT LOG(2,65536); -> 16 mysql> SELECT LOG(10,100); -> 2 mysql> SELECT LOG(1,100); -> NULL
LOG(
is equivalent toB
,X
)LOG(
.X
) / LOG(B
)Returns the base-2 logarithm of
. IfX
X
is less than or equal to 0.0E0, the function returnsNULL
and a warning “Invalid argument for logarithm” is reported. ReturnsNULL
ifX
isNULL
.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 expressionLOG(
.X
) / LOG(2)Returns the base-10 logarithm of
X
. IfX
is less than or equal to 0.0E0, the function returnsNULL
and a warning “Invalid argument for logarithm” is reported. ReturnsNULL
ifX
isNULL
.mysql> SELECT LOG10(2); -> 0.30102999566398 mysql> SELECT LOG10(100); -> 2 mysql> SELECT LOG10(-100); -> NULL
Modulo operation. Returns the remainder of
N
divided byM
. ReturnsNULL
ifM
orN
isNULL
.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
MOD(
returnsN
,0)NULL
.Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.
Because the return value of this function is a double-precision value, its exact representation may vary between platforms or implementations. This also applies to any expressions making use of
PI()
. See Section 13.1.4, “Floating-Point Types (Approximate Value) - FLOAT, DOUBLE”.mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793000
Returns the value of
X
raised to the power ofY
. ReturnsNULL
ifX
orY
isNULL
.mysql> SELECT POW(2,2); -> 4 mysql> SELECT POW(2,-2); -> 0.25
This is a synonym for
POW()
.Returns the argument
X
, converted from degrees to radians. (Note that π radians equals 180 degrees.) ReturnsNULL
ifX
isNULL
.mysql> SELECT RADIANS(90); -> 1.5707963267949
Returns a random floating-point value
v
in the range0
<=v
<1.0
. To obtain a random integerR
in the rangei
<=R
<j
, use the expressionFLOOR(
−i
+ RAND() * (j
. For example, to obtain a random integer in the range the rangei
))7
<=R
<12
, use the following statement:SELECT FLOOR(7 + (RAND() * 5));
If an integer argument
N
is specified, it is used as the seed value:With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.
With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of
RAND()
.
One implication of this behavior is that for equal argument values,
RAND(
returns the same value each time, and thus produces a repeatable sequence of column values. In the following example, the sequence of values produced byN
)RAND(3)
is the same both places it occurs.mysql> CREATE TABLE t (i INT); Query OK, 0 rows affected (0.42 sec) mysql> INSERT INTO t VALUES(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT i, RAND() FROM t; +------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.61914388706828 | | 2 | 0.93845168309142 | | 3 | 0.83482678498591 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND(3) FROM t; +------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND() FROM t; +------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.35877890638893 | | 2 | 0.28941420772058 | | 3 | 0.37073435016976 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND(3) FROM t; +------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.01 sec)
RAND()
in aWHERE
clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). Thus, for optimizer purposes,RAND()
is not a constant value and cannot be used for index optimizations. For more information, see Section 10.2.1.20, “Function Call Optimization”.Use of a column with
RAND()
values in anORDER BY
orGROUP BY
clause may yield unexpected results because for either clause aRAND()
expression can be evaluated multiple times for the same row, each time returning a different result. If the goal is to retrieve rows in random order, you can use a statement like this:SELECT * FROM tbl_name ORDER BY RAND();
To select a random sample from a set of rows, combine
ORDER BY RAND()
withLIMIT
:SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;
RAND()
is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Rounds the argument
X
toD
decimal places. The rounding algorithm depends on the data type ofX
.D
defaults to 0 if not specified.D
can be negative to causeD
digits left of the decimal point of the valueX
to become zero. The maximum absolute value forD
is 30; any digits in excess of 30 (or -30) are truncated. IfX
orD
isNULL
, the function returnsNULL
.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 mysql> SELECT ROUND(.12345678901234567890123456789012345, 35); -> 0.123456789012345678901234567890
The return value has the same type as 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):
mysql> SELECT ROUND(150.000,2), ROUND(150,2); +------------------+--------------+ | ROUND(150.000,2) | ROUND(150,2) | +------------------+--------------+ | 150.00 | 150 | +------------------+--------------+
ROUND()
uses the following rules depending on the type of the first argument:For exact-value numbers,
ROUND()
uses the “round half away from zero” 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 a fractional part exactly halfway between two integers 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 Section 14.24, “Precision Math”.
The data type returned by
ROUND()
(andTRUNCATE()
) is determined according to the rules listed here:When the first argument is of any integer type, the return type is always
BIGINT
.When the first argument is of any floating-point type or of any non-numeric type, the return type is always
DOUBLE
.When the first argument is a
DECIMAL
value, the return type is alsoDECIMAL
.The type attributes for the return value are also copied from the first argument, except in the case of
DECIMAL
, when the second argument is a constant value.When the desired number of decimal places is less than the scale of the argument, the scale and the precision of the result are adjusted accordingly.
In addition, for
ROUND()
(but not for theTRUNCATE()
function), the precision is extended by one place to accommodate rounding that increases the number of significant digits. If the second argument is negative, the return type is adjusted such that its scale is 0, with a corresponding precision. For example,ROUND(99.999, 2)
returns100.00
—the first argument isDECIMAL(5, 3)
, and the return type isDECIMAL(5, 2)
.If the second argument is negative, the return type has scale 0 and a corresponding precision;
ROUND(99.999, -1)
returns100
, which isDECIMAL(3, 0)
.
Returns the sign of the argument as
-1
,0
, or1
, depending on whetherX
is negative, zero, or positive. ReturnsNULL
ifX
isNULL
.mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1
Returns the sine of
X
, whereX
is given in radians. ReturnsNULL
ifX
isNULL
.mysql> SELECT SIN(PI()); -> 1.2246063538224e-16 mysql> SELECT ROUND(SIN(PI())); -> 0
Returns the square root of a nonnegative number
X
. IfX
isNULL
, the function returnsNULL
.mysql> SELECT SQRT(4); -> 2 mysql> SELECT SQRT(20); -> 4.4721359549996 mysql> SELECT SQRT(-16); -> NULL
Returns the tangent of
X
, whereX
is given in radians. ReturnsNULL
ifX
isNULL
.mysql> SELECT TAN(PI()); -> -1.2246063538224e-16 mysql> SELECT TAN(PI()+1); -> 1.5574077246549
Returns the number
X
, truncated toD
decimal places. IfD
is0
, the result has no decimal point or fractional part.D
can be negative to causeD
digits left of the decimal point of the valueX
to become zero. IfX
orD
isNULL
, the function returnsNULL
.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.
The data type returned by
TRUNCATE()
follows the same rules that determine the return type of theROUND()
function; for details, see the description forROUND()
.