May 15, 2021 MySQL
All mathematical functions return NULL in the event of
NULL
-
Yuan minus.
Change the symbol of the argument
mysql> SELECT - 2; -> -2Note that if this operator is used for a
BIGINT
the return value is also a
BIGINT
This means that you should avoid using the
-
on an integer that may have a value of
-2^63
ABS(X)
the
X
absolute value of X:
mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32This function can be safely used in
BIGINT
SIGN(X)
the symbol of
the
argument in
-1
0
1
depending
X
parameter X is
negative, 0, or positive.
mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1
MOD(N,M)
%
molded (as in the %
%
in
C).
Returns
N
remaining
number after N is 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; -> 2This function can be safely used in
BIGINT
The last example works in MySQL 4.1.
FLOOR(X)
maximum integer
value
that is not greater than
X
mysql> SELECT FLOOR(1.23); -> 1 mysql> SELECT FLOOR(-1.23); -> -2Note that the return value is converted to
BIGINT
CEILING(X)
a minimum integer
not less than
X
mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1Note that the return value is converted to
BIGINT
ROUND(X)
ROUND(X,D)
parameter
X
to
the nearest integer and returns. T
he two parameters take the form of rounding a number to
D
and returning it.
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); -> 1Note that when the argument is between two integers, the behavior
ROUND()
on the implementation of the C library. S
ome rounding to the nearest even number is always down, always up, or always close to zero.
If you need some sort of rounding type, you should use a well-defined
TRUNCATE()
or
FLOOR()
DIV
dictity. S
imilar
FLOOR()
but it can be safely used for
BIGINT
values.
mysql> SELECT 5 DIV 2 -> 2
DIV
was added in MySQL 4.1.0.
EXP(X)
the
e
of the value e (the bottom of the natural
X
pair):
mysql> SELECT EXP(2); -> 7.389056 mysql> SELECT EXP(-2); -> 0.135335
LN(X)
the
X
number
of X:
mysql> SELECT LN(2); -> 0.693147 mysql> SELECT LN(-2); -> NULLThis function was added to MySQL 4.0.3. In MySQL, it is
LOG(X)
LOG(X)
LOG(B,X)
called with one argument, it returns the natural
log of
X
mysql> SELECT LOG(2); -> 0.693147 mysql> SELECT LOG(-2); -> NULLIf called with two arguments, this function returns a pair of
X
for any bottom
B
mysql> SELECT LOG(2,65536); -> 16.000000 mysql> SELECT LOG(1,100); -> NULLAny bottom option is added in MySQL 4.0.3.
LOG(B,X)
is equivalent
LOG(X)/LOG(B)
LOG2(X)
X
log of X with a base of 2:
mysql> SELECT LOG2(65536); -> 16.000000 mysql> SELECT LOG2(-100); -> NULL
LOG2()
usually be used to count how many bits a number requires to store it. T
his function was added in MySQL 4.0.3.
In earlier versions, you could
LOG(X)/LOG(2)
instead of it.
LOG10(X)
X
logs with a base of 10:
mysql> SELECT LOG10(2); -> 0.301030 mysql> SELECT LOG10(100); -> 2.000000 mysql> SELECT LOG10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
the
Y
power
of
X
mysql> SELECT POW(2,2); -> 4.000000 mysql> SELECT POW(2,-2); -> 0.250000
SQRT(X)
the
non-no square root of
X
mysql> SELECT SQRT(4); -> 2.000000 mysql> SELECT SQRT(20); -> 4.472136
PI()
the PI value (circular rate). 5
-bit scales are missing, but inside MySQL, all doubles are used for PI.
mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116
COS(X)
X
cosine of X, where
X
is given
in radian:
mysql> SELECT COS(PI()); -> -1.000000
SIN(X)
X
sine of X, where
X
is given in
radian:
mysql> SELECT SIN(PI()); -> 0.000000
TAN(X)
the positive cut of
X
where
X
given in
radian:
mysql> SELECT TAN(PI()+1); -> 1.557408
ACOS(X)
X
backstring of X, or rather, the value of
X
If
X
not
in the range of
-1
1
return
NULL
mysql> SELECT ACOS(1); -> 0.000000 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.570796
ASIN(X)
the
antisine of X, or rather the value of
X
X
If
X
not
in the range of
-1
1
return
NULL
mysql> SELECT ASIN(0.2); -> 0.201358 mysql> SELECT ASIN('foo'); -> 0.000000
ATAN(X)
the reverse cut of X, or rather the value of the positive cut
X
value of
X
mysql> SELECT ATAN(2); -> 1.107149 mysql> SELECT ATAN(-2); -> -1.107149
ATAN(Y,X)
ATAN2(Y,X)
the reverse cut of
the
two
variables
X
and
Y
It is similar to calculating
Y / X
except that the
symbols of two parameters 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)
the
remaining cut of
X
mysql> SELECT COT(12); -> -1.57267341 mysql> SELECT COT(0); -> NULL
RAND()
RAND(N)
a random floating-point
value
between
0
and
1.0
If an integer parameter
N
is specified, it is used as a seed value (used to produce a repeatable value):
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.70100469486881In an
ORDER BY
you can't
RAND()
value because
ORDER BY
the calculated column multiple times. S
tarting with MySQL 3.23, you can use:
SELECT * FROM table_name ORDER BY RAND()
facilitates the obtaining of a random sample from a collection of SELECT s FROM
SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000
N
ote that
RAND()
in a
WHERE
clause is recalculated each time
WHERE
executes.
RAND()
not the expected perfect random number generator, but can be used instead as a quick way to produce special random numbers, making it easy to move between the same MySQL versions under two different platforms.
LEAST(X,Y,...)
INTEGER
or if all parameters are integer values, they are compared as integers.
REAL
context, or if all parameters are real, they are compared as real numbers.
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 versions earlier than MySQL 3.22.5, you can use
MIN()
LEAST
GREATEST(X,Y,...)
red
Parameters are compared
using rules that are consistent with
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 versions earlier than MySQL 3.22.5,
MAX()
of
GREATEST
DEGREES(X)
parameter
X
radian to angle, and then returns:
mysql> SELECT DEGREES(PI()); -> 180.000000
RADIANS(X)
parameter
X
an angle to radian and returns:
mysql> SELECT RADIANS(90); -> 1.570796
TRUNCATE(X,D)
the numeric
X
D
and
returns.
If
D
0
the result will not contain the tap and the
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.9Starting with MySQL 3.23.51, all numbers are rounded to zero. If
D
is negative, the entire part of the number is aligned to the zero output:
mysql> SELECT TRUNCATE(122,-2); -> 100Note that the hete ten-point scale is usually not stored in exact numbers on your computer, but as a double value, and you may be fooled by the following results:
mysql> SELECT TRUNCATE(10.28*100,0); -> 1027The above result occurs because 10.28 is actually stored in some form like 10.2799999999999999.