mysql numerical function

1. Arithmetic operators

Common arithmetic operators can be used. Note that for -, + and *, if both parameters are positive numbers, the accuracy of the calculation result is BIGINT (64 bits). If one of the parameters is an unsigned integer and the other parameters are also integers, the result is an unsigned integer.

+

plus sign:

mysql> SELECT 3+5;

-> 8

-

minus sign:

mysql> SELECT 3-5;

-> -2

-

One dollar minus. Replace parameter symbols.

mysql> SELECT - 2;

-> -2

Note: If the operator is used with the same BIGINT at the same time, the return value is also a BIGINT. This means that you should try to avoid using – with integers that might result in –263.

*

Multiply sign:

mysql> SELECT 3*5;

-> 15

mysql> SELECT 18014398509481984*18014398509481984.0;

-> 324518553658426726783156020576256.0

mysql> SELECT 18014398509481984*18014398509481984;

- > 0

The result of the last expression is incorrect. The reason is that the result of integer multiplication exceeds the 64-bit range of BIGINT calculations.

/

Divisor:

mysql> SELECT 3/5;

-> 0.60

The result of division by zero is NULL:

mysql> SELECT 102/(1-1);

-& gt ; NULL

Division will only be used with the BIGINT algorithm if the execution context is such that the result is to be converted to an integer.

DIV

Integer division. Similar to FLOOR(), however using the BIGINT algorithm is also reliable.

mysql> SELECT 5 DIV 2;

-> 2

2. Math functions

If an error occurs, all math functions will return NULL.

ABS(X)

Returns the absolute value of X.

mysql> SELECT ABS(2);

-> 2

mysql> SELECT ABS(-32);

-> 32

This function supports the use of BIGINT values.

ACOS(X)

returns the inverse cosine of X, that is, the cosine is the value of X. If X is not in the range -1 to 1, NULL is returned.

mysql> SELECT ACOS(1);

-> 0

mysql> SELECT ACOS(1.0001);

-> NULL

mysql> SELECT ACOS(0);

- > 1.5707963267949

ASIN(X)

Returns the inverse sine of X, that is, the sine is the value of X. If X If X is not in the range of -1 to 1, then NULL is returned.

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)

ATAN(X)

returns the arc tangent of X , that is, the tangent is the value of X .

mysql> SELECT ATAN(2);

-> 1.1071487177941

mysql> SELECT ATAN(-2);

-> -1.1071487177941

ATAN( Y,X) , ATAN2(Y,X)

Returns the arc tangent of two variables X and Y. It is similar to the arctangent calculation of Y or X, except that the signs of both parameters 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)

Return the smallest integer value that is not less than X.

mysql> SELECT CEILING(1.23);

-> 2

mysql> SELECT CEIL(-1.23);

-> -1

These two functions have the same meaning. Note that the return value will be converted to a BIGINT.

COS(X)

Returns the cosine of X, where X is known 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)

Calculation loop Redundant code check value combination Returns a 32-bit unsigned value. If the parameter is NULL, the result is NULL. The argument should be a string, and will be treated as such (if possible) if not a string.

mysql> SELECT CRC32('MySQL');

-> 3259397556

mysql> SELECT CRC32('mysql');

-> 2501908538

DEG REES(X)

return parameter X, this parameter Converted from radians to degrees.

mysql> SELECT DEGREES(PI());

-> 180

mysql> The value after powering (the base of the natural logarithm).

mysql> SELECT EXP(2);

-> 7.3890560989307

mysql> SELECT EXP(-2);

-> 0.13533528323661

mys ql> 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 will be converted into a BIGINT.

FORMAT(X,D)

Write the number Return the result as a string.

LN(X)

Returns the natural logarithm of X , that is, the logarithm of X with respect to base e.

mysql> SELECT LN(2);

-> 0.69314718055995

mysql> SELECT LN(-2);

-> NULL

This function has the same meaning as LOG(X).

LOG(X) LOG(B,X)

If called with one parameter, this function will return the natural logarithm of X.

mysql> SELECT LOG(2);

-> 0.69314718055995

mysql> SELECT LOG(-2);

-> NULL

If called with two parameters, this function will return X for any The logarithm of 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> efficient. 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> ECT LOG10(-100);

-> NULL

LOG10(X) is equivalent to LOG(10,X).

MOD(N,M) , N % M N MOD M

Module operation. Returns the remainder after dividing N 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 supports the use of BIGINT values.

MOD() also works for values ​​with decimal parts, it returns the exact remainder after division:

mysql> SELECT MOD(34.5,3);

-> 1.5

PI()

Returns the value of ϖ (pi). The default display decimal place is 7, however MySQL internally uses full double precision values.

mysql> SELECT PI();

-> 3.141593

mysql> 93116

POW(X,Y) , POWER(X,Y)

Return The result value of X raised to the Y power.

mysql> SELECT POW(2,2);

-> 4

mysql> SELECT POW(2,-2); The parameter X, in radians (note that ϖ in radians is equal to 180 degrees).

mysql> SELECT RADIANS(90);

-> 1.5707963267949

RAND() RAND(N)

Returns a random floating point value v ranging from 0 to 1 (i.e., its range is 0 ≤ v ≤ 1.0). If an integer parameter N is specified, it is used as the seed value used to generate the repeating sequence.

mysql> SELECT RAND();

-> 0.9233482386203

mysql> SELECT RAND(20);

-> 0.15888261251047

mysq l> SELECT RAND(20);

-> 0.15888261251047

mysql> ; SELECT RAND();

-> 0.63553050033332

mysql> SELECT RAND();

-> 0.70100469486881

mysql>

-> 0.15888261251047

If i ≤ To get a random integer R in the range R ≤ j, you need to use the expression FLOOR(i + RAND() * (j – i + 1)). For example, to get a random integer in the range of 7 to 12 (inclusive), you can use the following statement:

SELECT FLOOR(7 + (RAND() * 6));

In the ORDER BY statement , you cannot use a column with a RAND() value because ORDER BY will calculate multiple times for the column. However, the data rows can be retrieved in random order as follows:

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 columns:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c

-> ORDER BY RAND() LIMIT 1000;

Note that in the WHERE statement, every time WHERE is executed, RAND() will be called again Calculate once.

RAND() is not intended as an exact randomizer, but as a fast way to generate movable ad hoc random numbers between platforms with the same MySQL version.

ROUND(X) ROUND(X,D)

返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位，可将 D 设为负值。

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

返回值的类型同 第一个自变量相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。

当第一个参数是十进制常数时，对于准确值参数，ROUND() 使用精密数学题库：

对于准确值数字, ROUND() 使用“四舍五入” 或“舍入成最接近的数” 的规则:对于一个分数部分为 .5或大于 .5的值，正数则上舍入到邻近的整数值， 负数则下舍入临近的整数值。(换言之, 其舍入的方向是数轴上远离零的方向）。对于一个分数部分小于.5 的值，正数则下舍入下一个整数值，负数则下舍入邻近的整数值，而正数则上舍入邻近的整数值。

对于近似值数字，其结果根据C 库而定。在很多系统中，这意味着 ROUND()的使用遵循“舍入成最接近的偶数”的规则： 一个带有任何小数部分的值会被舍入成最接近的偶数整数。

以下举例说明舍入法对于精确值和近似值的不同之处：

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

SIGN(X)

返回参数作为-1、 0或1的符号，该符号取决于X 的值为负、零或正。

mysql> SELECT SIGN(-32);

-> -1

mysql> SELECT SIGN(0);

-> 0

mysql> SELECT SIGN(234);

-> 1

SIN(X)

返回X 正弦，其中 X 在弧度中被给定。

mysql> SELECT SIN(PI());

-> 1.2246063538224e-16

mysql> SELECT ROUND(SIN(PI()));

-> 0

SQRT(X)

返回非负数X 的二次方根。

mysql> SELECT SQRT(4);

-> 2

mysql> SELECT SQRT(20);

-> 4.4721359549996

mysql> SELECT SQRT(-16);

-> NULL

TAN(X)

返回X 的正切，其中X 在弧度中被给定。

mysql> SELECT TAN(PI());

-> -1.2246063538224e-16

mysql> SELECT TAN(PI()+1);

-> 1.5574077246549

TRUNCATE(X,D)

返回被舍去至小数点后D位的数字X。若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.  

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

所有数字的舍入方向都接近于零。