官方文档地址:12.6.2 Mathematical Functions
表 12.10 数学函数
名称 | 描述 |
---|---|
ABS() | 返回绝对值 |
ACOS() | 返回反余弦 |
ASIN() | 返回反正弦 |
ATAN() | 返回反正切 |
ATAN2() ,ATAN() | 返回两个参数的反正切 |
CEIL() | 返回不小于参数的最小整数值 |
CEILING() | 返回不小于参数的最小整数值 |
CONV() | 在不同的数基之间转换数字 |
COS() | 返回余弦 |
COT() | 返回余切 |
CRC32() | 计算循环冗余校验值 |
DEGREES() | 将弧度转换为角度 |
EXP() | 返回 e(自然对数的底数)的 X 次方的值 |
FLOOR() | 返回不大于参数的最大整数值 |
LN() | 返回参数的自然对数 |
LOG() | 返回第一个参数的自然对数 |
LOG2() | 返回参数的以 2 为底的对数 |
LOG10() | 返回参数的以 10 为底的对数 |
MOD() | 返回余数,也就是取模运算 |
PI() | 返回圆周率 π 的值 |
POW() | 返回参数的指定幂 |
POWER() | 返回参数的指定幂 |
RADIANS() | 将角度转换为弧度 |
RAND() | 返回一个随机浮点值 |
ROUND() | 对参数四舍五入 |
SIGN() | 返回参数的符号 |
SIN() | 返回参数的正弦 |
SQRT() | 返回参数的平方根 |
TAN() | 返回参数的正切 |
TRUNCATE() | 截断到指定的小数位数 |
如果出现错误,所有数学函数都会返回NULL
。
- ABS(X)
返回X
的绝对值,如果X
为NULL
则返回NULL
。结果类型派生自参数的类型。这意味着ABS(-9223372036854775808)
会产生一个错误,因为结果不能存储在带符号的BIGINT
值中。
译者注:有符号的范围是
-9223372036854775808 ~ 9223372036854775807
。无符号范围是0 ~ 18446744073709551615
。ABS(-9223372036854775808)
的参数是有符号的BIGINT
,所以结果也是有符号的BIGINT
,但是超过了有符号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
被视为无符号数。CONV()
支持64
位精度。
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'
译者注:
下面示例是将数字 8 从十进制转换为 2 进制形式,得到 1000。
再将 1000 从 2 进制转换为 10 进制形式,得到 8。
mysql> SELECT CONV(8,10,2);
+--------------+
| CONV(8,10,2) |
+--------------+
| 1000 |
+--------------+
1 row in set
mysql> SELECT CONV(1000,2,10);
+-----------------+
| CONV(1000,2,10) |
+-----------------+
| 8 |
+-----------------+
1 row in set
- 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
译者注:给字符串类型的字段建立索引效率不高,但是必须要经常查这个字段怎么建索引?比如这个字符串类型字段名称是 str_name,那么可以建一个字段 str_name_crc32 来存储 CRC32 的值,并给这个字段建立索引。
字符串进行 CRC32 计算的结果是整形,在 MySQL 中,给整形字段建立索引效率比较高,CRC32 虽然不能确保唯一性,但是相同的机率也是极小的,关键是可以大大减少查询的范围,给 str_name_crc32 这个字段建立索引,查询的时候带上 str_name_crc32 字段就可以利用到索引了。
- DEGREES(X)
将参数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
位,并以字符串形式返回结果。详细信息请参见 12.8 字符串函数和操作符。
- HEX(N_or_S)
此函数可用于获得十进制数或字符串的十六进制表示;根据参数的类型不同而结果不同。请参阅 12.8 字符串函数和操作符 中对该函数的描述。
- LN(X)
返回X
的自然对数;也就是以e
为底的X
的对数。如果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)
返回X
的以2
为底的对数。如果X
小于或等于0.0E0
,该函数返回NULL
,并报告“对数参数无效”的警告。
mysql> SELECT LOG2(65536);
-> 16
mysql> SELECT LOG2(-100);
-> NULL
LOG2()
对于找出一个数字需要多少位来存储非常有用。这个函数等价于表达式LOG(X) / LOG(2)
。
- LOG10(X)
返回X
的以10
为底的对数。如果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()
返回圆周率π
的值。默认显示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
从角度转换为弧度。参数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
,它将被用作种子值:
(1)如果使用常量初始化参数,则在语句准备就绪时(在执行之前)初始化种子一次。
(2)使用一个非常量初始化参数(如列名),每次调用 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()
不是一个常数值,不能用于索引优化。有关更多信息,请参见 8.2.1.20 函数调用优化。
在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
时,如果使用此函数,则会记录一个警告。
译者注:
(1)RAND()
返回0 <= v < 1.0
范围内的随机浮点值v
。
(2)RAND(N)
返回0 <= v < 1.0
范围内的随机浮点值v
,N
为种子值,这样每次获取的随机值是一样的。比如多次调用RAND(3)
的结果是相同的,多次调用RAND(5)
的结果也是相同的,但是RAND(3)
和RAND(5)
的结果是不同的。
(3)ORDER BY RAND()
表示按照随机顺序排序。
- ROUND(X),ROUND(X,D)
参数X
四舍五入到小数点后D
位。舍入算法取决于X
的数据类型。如果没有指定,D
默认为0
。D
可以是负数,以使X
的小数点左边的D
位数变成0
。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
返回值与第一个参数具有相同的类型(假设它是integer
、double
或decimal
)。这意味着对于整数参数,结果是一个整数(没有小数点):
mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
| 150.00 | 150 |
+------------------+--------------+
ROUND()
根据第一个参数的类型使用以下规则:
(1)对于精确值,ROUND()
使用“向零四舍五入”或“向最接近的四舍五入”规则:小数部分为0.5
或更大的值,如果为正,则向上舍入到更大的一个整数;如果为负,则向下舍入到更小的一个整数。(换句话说,它是离零四舍五入。)小数部分小于0.5
的值,如果为正,则四舍五入到更小的一个整数;如果为负,则四舍五入到更大的一个整数。(换句话说,它是趋零四舍五入。)
(2)对于近似值,结果取决于 C 库。在许多系统上,这意味着ROUND()
使用“从四舍五入到最接近偶数”规则:小数部分恰好在两个整数中间的值被舍入到最接近的偶数。
下面的例子展示了精确值和近似值的舍入差异:
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
要了解更多信息,请参见 12.25 精确数学。
在 MySQL 8.0.21 及以后版本中,ROUND()
(和TRUNCATE()
)返回的数据类型是根据下面列出的规则确定的:
(1)当第一个参数为任意整数类型时,返回类型总是BIGINT
。
(2)当第一个参数为任何浮点类型或任何非数字类型时,返回类型总是DOUBLE
。
(3)当第一个参数是DECIMAL
值时,返回类型也是DECIMAL
。
(4)返回值的类型属性也从第一个参数复制,除非是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
位数变成0
。
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()
函数返回类型相同的规则;详情请参见ROUND()
的描述。