|
|
Numeric functions deal with numbers, mostly taking numeric arguments and returning numeric results. In the case of an error, they will return NULL. You need to take care not to go beyond the numeric range of a number—most MySQL functions work with BIGINTs (263 signed, or 264 unsigned), and if you go beyond the range, MySQL will usually return NULL.
ABS(number)
Returns the absolute value (positive value) of a number. The function is safe for use with BIGINT values.
For example:
mysql> SELECT ABS(24-26); +------------+ | ABS(24-26) | +------------+ | 2 | +------------+
ACOS(number)
Returns the arc cosine of number (the inverse cosine). The number must be between –1 and 1 or the function returns NULL.
For example:
mysql> SELECT ACOS(0.9); +-----------+ | ACOS(0.9) | +-----------+ | 0.451027 | +-----------+
ASIN(number)
Returns the arc sine of number (the inverse sine). The number must be between –1 and 1 or the function returns NULL.
For example:
mysql> SELECT ASIN(-0.4); +------------+ | ASIN(-0.4) | +------------+ | -0.411517 | +------------+
ATAN(number1 [, number2])
Returns the arc tangent of number (the inverse tangent) or of two numbers (the point number1, number2).
For example:
mysql> SELECT ATAN(4); +----------+ | ATAN(4) | +----------+ | 1.325818 | +----------+ mysql> SELECT ATAN(-4,-3); +-------------+ | ATAN(-4,-3) | +-------------+ | -2.214297 | +-------------+
ATAN2(number1,number2)
A synonym for ATAN(number1,number2).
CEILING(number)
Rounds up the number to the nearest integer and returns it as a BIGINT.
For example:
mysql> SELECT CEILING(2.98); +---------------+ | CEILING(2.98) | +---------------+ | 3 | +---------------+ mysql> SELECT CEILING(-2.98); +----------------+ | CEILING(-2.98) | +----------------+ | -2 | +----------------+
Use FLOOR() to round down, and use ROUND() to round up or down.
COS(number_radians)
Returns the cosine of number_radians.
For example:
mysql> SELECT COS(51); +----------+ | COS(51) | +----------+ | 0.742154 | +----------+
COT(number_radians)
Returns the cotangent of number_radians.
For example:
mysql> SELECT COT(0.45); +------------+ | COT(0.45) | +------------+ | 2.07015736 | +------------+
DEGREES(number)
Converts the number from radians to degrees and returns the result.
For example:
mysql> SELECT DEGREES(2); +-----------------+ | DEGREES(2) | +-----------------+ | 114.59155902616 | +-----------------+ mysql> SELECT DEGREES(PI()/2); +-----------------+ | DEGREES(PI()/2) | +-----------------+ | 90 | +-----------------+
EXP(number)
Returns the number e (the base of natural logarithms) raised to the specified power.
For example:
mysql> SELECT EXP(1); +----------+ | EXP(1) | +----------+ | 2.718282 | +----------+ mysql> SELECT EXP(2.3); +----------+ | EXP(2.3) | +----------+ | 9.974182 | +----------+ mysql> SELECT EXP(0.3); +----------+ | EXP(0.3) | +----------+ | 1.349859 | +----------+
FLOOR(number)
Rounds the number down to the nearest integer and returns it as a BIGINT.
For example:
mysql> SELECT FLOOR(2.98); +-------------+ | FLOOR(2.98) | +-------------+ | 2 | +-------------+ mysql> SELECT FLOOR(-2.98); +--------------+ | FLOOR(-2.98) | +--------------+ | -3 | +--------------+
Use CEILING() to round up, and ROUND() to round up or down.
FORMAT(number,decimals)
Formats the number to a format with each three digits separated by a comma and rounds the result to the specified number of places.
For example:
mysql> SELECT FORMAT(88777634.232,2); +------------------------+ | FORMAT(88777634.232,2) | +------------------------+ | 88,777,634.23 | +------------------------+
GREATEST(argument1, argument2 [, ...])
Returns the largest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).
For example:
mysql> SELECT GREATEST(-3,-4,5); +-------------------+ | GREATEST(-3,-4,5) | +-------------------+ | 5 | +-------------------+ mysql> SELECT GREATEST('Pa','Ma','Ca'); +--------------------------+ | GREATEST('Pa','Ma','Ca') | +--------------------------+ | Pa | +--------------------------+
LEAST(argument1, argument2 [, ...])
Returns the smallest of the arguments. The arguments will be compared in different ways depending on the context of the return value or the argument types, which can be integer, real, or strings (which are case sensitive and the default).
For example:
mysql> SELECT LEAST(-3,-4,5); +----------------+ | LEAST(-3,-4,5) | +----------------+ | -4 | +----------------+ mysql> SELECT LEAST('Pa','Ma','Ca'); +-----------------------+ | LEAST('Pa','Ma','Ca') | +-----------------------+ | Ca | +-----------------------+
LN(number)
Synonym for the LOG(number) function.
LOG(number1 [, number2])
Returns the natural logarithm of number1 if there's one argument. You can also use an arbitrary base by supplying a second argument, in which case the function returns LOG(number2) / LOG(number1).
For example:
mysql> SELECT LOG(2); +----------+ | LOG(2) | +----------+ | 0.693147 | +----------+ mysql> SELECT LOG(2,3); +----------+ | LOG(2,3) | +----------+ | 1.584963 | +----------+
LOG10(number1)
Returns the base 10 logarithm of number1. This is equivalent to LOG(number1)/LOG(10).
For example:
mysql> SELECT LOG10(100); +------------+ | LOG10(100) | +------------+ | 2.000000 | +------------+
LOG2(number1)
Returns the base 2 logarithm of number1. This is equivalent to LOG(number1)/LOG(2).
For example:
mysql> SELECT LOG2(4); +----------+ | LOG2(4) | +----------+ | 2.000000 | +----------+
MOD(number1,number2)
Returns the modulus of number1 and number2 (the remainder of number1 divided by number2). This is the same as the % operator. This is safe to use with BIGINTs.
For example:
mysql> SELECT MOD(15,4); +-----------+ | MOD(15,4) | +-----------+ | 3 | +-----------+ mysql> SELECT MOD(3,-2); +-----------+ | MOD(3,-2) | +-----------+ | 1 | +-----------+
PI()
Returns the value of pi (or at least a close representation). MySQL uses the full double precision but only returns five characters by default.
For example:
mysql> SELECT PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+ mysql> SELECT PI() + 0.0000000000000000; +---------------------------+ | PI() + 0.0000000000000000 | +---------------------------+ | 3.1415926535897931 | +---------------------------+
POW(number1,number2)
This function is a synonym for POWER(number1,number2).
POWER(number1,number2)
Raises number1 to the power of number2 and returns the value.
For example:
mysql> SELECT POWER(2,3); +------------+ | POWER(2,3) | +------------+ | 8.000000 | +------------+
RADIANS(number1)
Converts the number from degrees to radians and returns the result.
For example:
mysql> SELECT RADIANS(180); +-----------------+ | RADIANS(180) | +-----------------+ | 3.1415926535898 | +-----------------+
RAND([number])
Returns a random number (a float) between 0 and 1. The argument is the random number seed. It's common to use the timestamp as a seed. The function can be used to return a result set in random order.
For example:
mysql> SELECT RAND(); +------------------+ | RAND() | +------------------+ | 0.70100469486881 | +------------------+ mysql> SELECT RAND(20021010081523); +----------------------+ | RAND(20021010081523) | +----------------------+ | 0.80558716673924 | +----------------------+ mysql> SELECT * FROM t1 ORDER BY RAND() LIMIT 1; +----+ | f1 | +----+ | 20 | +----+
ROUND(number1 [, number2])
Returns the argument number1, rounded to the nearest integer. You can supply a second argument to specify the number of decimals to round to (the default is 0, or no decimals). The rounding behavior for numbers exactly in the middle is based upon the underlying C library.
For example:
mysql> SELECT ROUND(2.49); +-------------+ | ROUND(2.49) | +-------------+ | 2 | +-------------+ mysql> SELECT ROUND(2.51); +-------------+ | ROUND(2.51) | +-------------+ | 3 | +-------------+ mysql> SELECT ROUND(-2.49,1); +----------------+ | ROUND(-2.49,1) | +----------------+ | -2.5 | +----------------+
SIGN(number)
Returns –1, 0, or 1 depending on whether the argument is negative, zero or not a number, or positive.
For example:
mysql> SELECT SIGN(-7); +----------+ | SIGN(-7) | +----------+ | -1 | +----------+ mysql> SELECT SIGN('a'); +-----------+ | SIGN('a') | +-----------+ | 0 | +-----------+
SIN(number_radians)
Returns the sine of number_radians.
For example:
mysql> SELECT SIN(45); +----------+ | SIN(45) | +----------+ | 0.850904 | +----------+
SQRT(number)
Returns the square root of the argument.
For example:
mysql> SELECT SQRT(81); +----------+ | SQRT(81) | +----------+ | 9.000000 | +----------+
TAN(number_radians)
Returns the tangent of number_radians.
For example:
mysql> SELECT TAN(66); +----------+ | TAN(66) | +----------+ | 0.026561 | +----------+
TRUNCATE(number,decimals)
Truncates (or increases) the number to the specified number of decimal places.
For example:
mysql> SELECT TRUNCATE(2.234,2); +-------------------+ | TRUNCATE(2.234,2) | +-------------------+ | 2.23 | +-------------------+ mysql> SELECT TRUNCATE(2.4,5); +-----------------+ | TRUNCATE(2.4,5) | +-----------------+ | 2.40000 | +-----------------+ mysql> SELECT TRUNCATE(2.998,0); +-------------------+ | TRUNCATE(2.998,0) | +-------------------+ | 2 | +-------------------+ mysql> SELECT TRUNCATE(-12.43,1); +--------------------+ | TRUNCATE(-12.43,1) | +--------------------+ | -12.4 | +--------------------+
|
|