MySQL的函数和运算符 - 数值函数和运算符 - 数学函数

数据库杂货铺 2021-06-11

600

数学函数

名称	介绍
ABS()	返回绝对值
ACOS()	求反余弦值
ASIN()	求反正弦值
ATAN()	求反正切值
ATAN2(),ATAN()	返回两个参数的反正切值
CEIL()	返回不小于参数的最小整数值
CEILING()	返回不小于参数的最小整数值
CONV()	在不同的基数之间转换数字
COS()	返回余弦
COT()	返回余切
CRC32()	计算循环冗余校验值
DEGREES()	将弧度转换为度
DIV	整数除法
EXP()	返回e(自然对数的底数)的X次方的值
FLOOR()	返回不大于参数的最大整数值
LN()	返回参数的自然对数
LOG()	返回第一个参数的自然对数
LOG10()	返回参数的以10为底的对数
LOG2()	返回参数的以2为底的对数
MOD()	返回余数
PI()	返回pi的值
POW()	返回参数被升到指定幂的结果
POWER()	返回参数被升到指定幂的结果
RADIANS()	返回参数转换为弧度的值
RAND()	返回一个随机浮点值
ROUND()	对参数进行舍入
SIGN()	返回参数的符号
SIN()	返回参数的正弦值
SQRT()	返回参数的平方根
TAN()	返回参数的正切值
TRUNCATE()	截断到指定的小数位数

如果出现错误，所有数学函数都返回 NULL。

● ABS(X)

返回 X 的绝对值，如果 X 为 NULL 则返回 NULL。

结果类型派生自参数类型。这意味着 ABS(-9223372036854775808) 会产生一个错误，因为结果不能存储在带符号的 BIGINT 值中。

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

这个函数与 BIGINT 值一起使用是安全的。

● ACOS(X)

返回X的反余弦值，即余弦值为 X 的值。如果 X 不在 -1 到 1 的范围内，则返回 NULL。

mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949

● ASIN(X)

返回 X 的反正弦，即正弦为 X 的值。如果 X 不在 -1 到 1 的范围内，则返回 NULL。

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)

返回 X 的反正切值，即正切为 X 的值。

mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941

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

返回两个变量 X 和 Y 的反正切值。它类似于计算 Y/X 的反正切，只是两个参数的符号用来确定结果的象限。

mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949

● CEIL(X)

CEIL() 是 CEILING() 的同义词。

● CEILING(X)

返回不小于 X 的最小整数值。

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1

对于精确值数值参数，返回值具有精确值数值类型。对于字符串或浮点值参数，返回值具有浮点类型。

● CONV(N,from_base,to_base)

在不同的基数之间转换数字。返回数字 N 的字符串表示形式，从基数 from_base 转换为基数 to_base。如果任何参数为 NULL，则返回 NULL。参数 N 被解释为整数，但可以指定为整数或字符串。最小基数为 2，最大基数为 36。如果 from_base 为负数，则 N 被视为有符号数。否则，N 被视为无符号的。

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'

● COS(X)

返回 X 的余弦，其中 X 以弧度表示。

mysql> SELECT COS(PI());
        -> -1

● COT(X)

返回 X 的余切。

mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> out-of-range error

● CRC32(expr)

计算循环冗余校验值并返回32位无符号值。如果参数为 NULL，则结果为 NULL。参数应为字符串，如果不是，则视为字符串处理（如果可能）。

mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538

● DEGREES(X)

返回参数 X 从弧度转换为度后的值。

mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI()  2);
        -> 90

● EXP(X)

返回e（自然对数的底）的 X 次幂。此函数的逆函数是 LOG() （仅使用单个参数）或 LN()。

mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);
        -> 0.13533528323661
mysql> SELECT EXP(0);
        -> 1

● FLOOR(X)

返回不大于 X 的最大整数值。

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
        -> 1, -2

● FORMAT(X,D)

将数字 X 格式化为 '#,###,###.##' 形式，四舍五入到 D 位小数，并以字符串形式返回结果。

● HEX(N_or_S)

此函数可用于获取十进制数或字符串的十六进制表示形式；具体的方式因参数的类型而异。

● LN(X)

返回 X 的自然对数；也就是 X 的以 e 为底的对数。如果 X 小于或等于0.0E0，则函数返回 NULL 并产生警告“对数参数无效”。

mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL

此函数与 LOG(X) 同义。此函数的逆函数是 EXP()。

● LOG(X), LOG(B,X)

如果调用时传递一个参数，此函数将返回 X 的自然对数。如果 X 小于或等于 0.0E0，则函数返回 NULL 并警告“对数参数无效”。

此函数的逆函数（使用单个参数调用时）是 EXP()。

mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL

如果调用时传递两个参数，此函数将返回以 B 为底 X 的对数。如果 X 小于或等于 0，或者如果 B 小于或等于 1，则返回 NULL。

mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2
mysql> SELECT LOG(1,100);
        -> NULL

LOG(B,X) 等价于 LOG(X) LOG(B)。

● LOG2(X)

返回以 2 为底 X 的对数。如果 X 小于或等于 0.0E0，则函数返回 NULL 并生成警告“对数参数无效”。

mysql> SELECT LOG2(65536);
        -> 16
mysql> SELECT LOG2(-100);
        -> NULL

LOG2() 对于找出一个数字需要多少位来存储非常有用。此函数等效于表达式 LOG(X) LOG(2)。

● LOG10(X)

返回以 10 为底 X 的对数。如果 X 小于或等于 0.0E0，则函数返回 NULL 并生成警告“对数参数无效”。

mysql> SELECT LOG10(2);
        -> 0.30102999566398
mysql> SELECT LOG10(100);
        -> 2
mysql> SELECT LOG10(-100);
        -> NULL

LOG10(X) 等价于 LOG(10,X)。

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

模运算。返回 N 除以 M 的余数。

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

此函数使用 BIGINT 值是安全的。

MOD() 也适用于具有小数部分的值，并在除法后返回精确的余数：

mysql> SELECT MOD(34.5,3);
        -> 1.5

MOD(N,0) 返回 NULL。

● PI()

返回 π (pi）的值。默认显示的小数位数是 7 位，但是 MySQL 在内部使用完整的双精度值。

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116

● POW(X,Y)

返回 X 的 Y 次幂。

mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25

● POWER(X,Y)

POW() 的同义词。

● RADIANS(X)

将参数 X 从度转换为弧度(请注意 π 弧度等于 180 度。）

mysql> SELECT RADIANS(90);
        -> 1.5707963267949

● RAND([N])

返回范围为 0 <= v < 1.0 的随机浮点值 v。要获得 i <= R < j 范围内的随机整数 R，请使用表达式 FLOOR(i + RAND() * (j − i))。例如，要获得范围 7 <= R < 12 的随机整数，请使用以下语句：

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

如果指定整数参数 N，则将其用作种子值：

■ 使用常量值初始化参数，在执行之前，在预编译语句时对种子进行一次初始化。

■ 使用非常量初始化参数（例如列名），每次调用 RAND() 时使用相应值初始化种子。

这种行为的一个隐含意思是，对于相等的参数值，RAND(N) 每次返回相同的值，从而生成一个可重复的列值序列。在下面的示例中，RAND(3) 生成的值序列在两个位置都是相同的。

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)

WHERE 子句中的 RAND() 对每一行（从一个表中选择时）或行组合（从多个表联接中选择时）求值。因此，出于优化器的目的，RAND() 不是一个常量值，不能用于索引优化。

在 ORDER BY 或 GROUP BY 子句中使用具有 RAND() 值的列可能会产生意外的结果，因为对于任一子句，RAND() 表达式可以对同一行进行多次求值，每次都返回不同的结果。如果目标是按随机顺序检索行，则可以使用如下语句：

SELECT * FROM tbl_name ORDER BY RAND();

要从一组行集合中选择随机样本，请将 ORDER BY RAND() 与 LIMIT 结合使用：

SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;

RAND() 并不是一个完美的随机生成器。这是一种按需生成随机数的快速方法，可在同一 MySQL 版本的平台之间移植。

此函数对于基于语句的复制不安全。如果 binlog_format 设置为 STATEMENT 时使用此函数，则会记录警告。

● ROUND(X), ROUND(X,D)

将参数 X 舍入到 D 个小数位。舍入算法取决于 X 的数据类型。如果未指定，则 D 默认为 0。D 可以是负数，使值 X 小数点左边的 D 位变为零。D 的最大绝对值为 30；任何超过 30（或 -30）的数字都将被截断。

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

返回值的类型与第一个参数相同（假设它是整数、双精度或小数）。这意味着对于整数参数，结果是整数（无小数点）：

mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+

ROUND() 根据第一个参数的类型使用以下规则：

■ 对于精确数值，ROUND() 使用“四舍五入”或“四舍五入到最近的”规则：小数部分为 .5 或更大的值如果为正，则向上舍入到下一个整数；如果为负，则向下舍入到下一个整数。分数部分小于 .5 的值如果为正，则向下舍入到下一个整数；如果为负，则向上舍入到下一个整数。

■ 对于近似值数值，结果取决于 C 库。在许多系统上，这意味着 ROUND() 使用“舍入到最接近的偶数”规则：小数部分正好位于两个整数中间的值被舍入到最接近的偶数整数。

以下示例显示精确值和近似值的舍入差异：

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

在 MySQL 8.0.21 及更高版本中，ROUND()（和 TRUNCATE()）返回的数据类型根据下面列出的规则确定：

■ 当第一个参数是整数类型时，返回类型总是 BIGINT。

■ 当第一个参数是浮点类型或非数字类型时，返回类型总是 DOUBLE。

■ 当第一个参数是 DECIMAL 值时，返回类型也是 DECIMAL。

■ 返回值的类型属性也从第一个参数中复制，但对于 DECIMAL，第二个参数是常量时除外。

当所需的小数位数小于参数范围时，相应地调整结果的小数位数和精度。

此外，对于 ROUND()（但不适用于 TRUNCATE() 函数），精度将扩展一个位置，以适应增加有效位数的舍入。如果第二个参数是负数，则会调整返回类型，使其小数位数为 0，并具有相应的精度。例如，ROUND(99.999, 2) 返回 100.00 — 第一个参数是 DECIMAL(5, 3)，返回类型是 DECIMAL(5, 2)。

如果第二个参数为负数，则返回类型的小数位数为 0，并具有相应的精度；ROUND(99.999, -1) 返回 100，即 DECIMAL(3, 0)。

● SIGN(X)

根据 X 是负、零还是正，将参数的符号返回为 -1、0 或 1。

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)

返回数字 X，截断为 D 个小数位。如果 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

所有数字都向零舍入。

在 MySQL 8.0.21 及更高版本中，TRUNCATE() 返回的数据类型遵循与 ROUND() 函数返回类型相同的规则。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 数值函数和运算符 - 数学函数

评论