mysql-yourself Mathematical Functions
All mathematical functions return NULL in case of an error。
–
Unary minus。Sign change parameters。
mysql> select – 2;
note，If this operator is used with a BIGINT，The return value is a BIGINT! This means you should avoid using the integer -，That may be 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 can be used to secure value BIGINT。
SIGN(X)
Returns the signs of the parameters，-1、0Or 1，Depending on whether X is negative、Zero or a positive number。
mysql> select SIGN(-32);
-> -1
mysql> select SIGN(0);
-> 0
mysql> select SIGN(234);
-> 1
MOD(N,M)
%
mold (Similarly% operators in C)。Returns the remainder being M divided by N。
mysql> select MOD(234, 10);
-> 4
mysql> select 253 % 7;
-> 1
mysql> select MOD(29,9);
-> 2
This function can be used safely BIGINT value。
FLOOR(X)
Returns the maximum 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 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)
It returns an integer rounding parameter X。
mysql> select ROUND(-1.23);
-> -1
mysql> select ROUND(-1.58);
-> -2
mysql> select ROUND(1.58);
-> 2
Note that the return value is converted into a BIGINT!
ROUND(X,D)
Returns rounding the parameter X has a D decimals。If D is 0，The results will have no decimal point or fractional part。
mysql> select ROUND(1.298, 1);
-> 1.3
mysql> select ROUND(1.298, 0);
-> 1
Note that the return value is converted into a BIGINT!
EXP(X)
Return value e (natural logarithm of the bottom) of 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 to count on any of the bottom B of a digital X，Use the formula LOG(X)/LOG(B)。
LOG10(X)
Returns the number X of the base 10。
mysql> select LOG10(2);
-> 0.301030
mysql> select LOG10(100);
-> 2.000000
mysql> select LOG10(-100);
-> NULL
POW(X,Y)
POWER(X,Y)
Y return power value X。
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()
PI return value (pi)。
mysql> select PI();
-> 3.141593
COS(X)
Returns the cosine of X, Where X is given in radians。
mysql> select COS(PI());
-> -1.000000
WITHOUT(X)
Returns the sine of X，Here X is given in radians。
mysql> select SIN(PI());
-> 0.000000
TAN(X)
Returns the tangent of X，Here X is given in radians。
mysql> select TAN(PI()+1);
-> 1.557408
ACOS(X)
X returns the inverse cosine，Ie it is a cosine X。If X is not in the range -1 to 1,，Return NULL。
mysql> select ACOS(1);
-> 0.000000
mysql> select ACOS(1.0001);
-> NULL
mysql> select ACOS(0);
-> 1.570796
ASIN(X)
X returns arcsine，I.e. the sine value which is X。L X is not in the range of -1 to 1 if the，Return NULL。
mysql> select ASIN(0.2);
-> 0.201358
mysql> select ASIN(‘foo’);
-> 0.000000
ATA(X)
X returns the arctangent，That which is tangent X。
mysql> select ATAN(2);
-> 1.107149
mysql> select ATAN(-2);
-> -1.107149
ATAN2(X,Y)
Back two variables X and Y arctangent。It is similar to calculation of Y / X arctangent，In addition to signs of both parameters are used to determine the quadrant of the result。
mysql> select ATAN(-2,2);
-> -0.785398
mysql> select ATAN(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)
Back in the range 0 to 1.0 in the random floating point values。If N is a specified integer parameter，It is used as a 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 not use an ORDER BY clause RAND()Value using the column，Because ORDER BY would be repeated many times computed column。However, in the MySQL3.23，you can do： SELECT * FROM table_name ORDER BY RAND()，This is beneficial to get one from SELECT * FROM table1,table2 WHERE a=b AND c
LEAST(X,Y,…)
There are two or more parameters and，The minimum return(Minimum)Parameters。Parameters were compared using the following rules：
If the return value is used in a context INTEGER，Or all of the parameters are integer values，They compared as integers。
If the return value is used in a context REAL，Or all of the real-valued parameters，As they are more real numbers。
If any parameter is sensitive to the size of a string，Parameters are compared as case-sensitive string。
In other cases，Parameters are compared as a case insensitive string。
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 previous versions of MySQL 3.22.5，You can use MIN()Instead LEAST。
GREATEST(X,Y,…)
Returns the maximum(Maximum)Parameters。LEAST parameters using the same rules as to compare。
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 the MySQL 3.22.5 previous version, You can use MAX()Instead of GREATEST.
DEGREES(X)
Return parameter X，Converted from radian angle。
mysql> select DEGREES(PI());
-> 180.000000
RADIANS(X)
Return parameter X，Is converted from degrees to radians。
mysql> select RADIANS(90);
-> 1.570796
TRUNCATE(X,D)
Returns X，Truncated to D decimals。If D is 0，The results 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
