第 14 篇 MySQL 常用函数之数学函数
《MySQL 入门教程》第 14 篇 MySQL 常用函数之数学函数
MySQL 函数主要可以分为两种类型：
- 标量函数（scalar function）针对每个输入参数，返回一个输出结果。例如，ABS(x) 可以计算 x 的绝对值。
- 聚合函数（aggregate function）基于一组数据进行计算，返回一个输出结果。例如，AVG 函数可以计算一组数据的平均值。
我们已经在第 12 篇中介绍了常用的聚合函数。从本篇开始，我们将会介绍常用的标量函数，首先是数学函数。
算术运算符和数学函数通常接收数字类型的参数，执行数学计算并返回一个数值结果。以下是 MySQL 中常见的算术运算符和数学函数：
| 函数/运算符 | 描述 |
|---|---|
| %、MOD | 求余运算符 |
| ***** | 乘法运算符 |
| + | 加法运算符 |
| - | 减法运算符 |
| - | 改变正负号 |
| / | 除法运算符 |
| DIV | 整除运算符 |
| ABS(x) | 计算 x 的绝对值 |
| ACOS(x) | 计算 x 的反余弦值 |
| ASIN(x) | 计算 x 的反正弦值 |
| ATAN(x) | 计算 x 的反正切值 |
| ATAN2(x, y)、ATAN(x, y) | 返回 y/x 的反正切值 |
| CEIL(x)、CEILING(x) | 计算大于等于 x 的最小整数 |
| CONV(x, from_base, to_base) | 不同进制的转换 |
| COS(x) | 计算 x 的余弦值 |
| COT(x) | 计算 x 的余切值 |
| CRC32(expr) | 计算字符串 expr 的循环冗余校验值 |
| DEGREES(x) | 将 x 从弧度转换为角度 |
| EXP(x) | 计算 e 的 x 次幂，e 约等于 2.71828 |
| FLOOR(x) | 计算小于等于 x 的最大整数 |
| GREATEST(x1, x2, ...) | 返回参数列表中的最大值 |
| LEAST(x1, x2, ...) | 返回参数列表中的最小值 |
| LN(x)、LOG(x) | 计算 x 的自然对数，底数为 e |
| LOG(y, x) | 计算以 y 为底的 x 的对数 |
| LOG10(x) | 计算以 10 为底的 x 的对数 |
| LOG2(x) | 计算以 2 为底的 x 的对数 |
| MOD(x, y) | 计算 x 除以 y 的余数 |
| PI() | 返回常量 π 的值，约等于 3.141593 |
| POW(x, y)、POWER(x, y) | 计算 x 的 y 次幂 |
| RADIANS(x) | 将 x 从角度转换为弧度 |
| RAND(seed) | 返回一个大于等于 0 小于 1 的随机数 |
| ROUND(x, y) | 将 x 四舍五入到 y 位小数 |
| SIGN(x) | 返回 x 的正负号 |
| SIN(x) | 计算 x 的正弦值 |
| SQRT(x) | 计算 x 的平方根 |
| TAN(x) | 计算 x 的正切值 |
| TRUNCATE(x, y) | 将 x 截断到 y 位小数 |
下面我们通过一些示例来说明这些函数的作用。
14.1 算术运算符
MySQL 实现了常见的算术运算，例如：
select 3+4, 3-4, 3*4, 3/4;
3+4|3-4|3*4|3/4 |
---|---|---|------|
7| -1| 12|0.7500|
减号（-）也可以作为一元运算符，用于正负数转换：
select -1, --1;
-1|--1|
--|---|
-1| 1|
DIV 运算符表示整数除法，丢弃结果中的小数部分。例如：
select 5 div 2, -5 div 2, 5 div -2, -5 div -2;
5 div 2|-5 div 2|5 div -2|-5 div -2|
-------|--------|--------|---------|
2| -2| -2| 2|
百分号（%）、MOD 和 MOD(x, y) 函数都表示求余运算，返回两个数字相除的余数。例如：
select 5 % 2, -5 % 2, 5 mod -2, mod(-5, -2);
5 % 2|-5 % 2|5 mod -2|mod(-5, -2)|
-----|------|--------|-----------|
1| -1| 1| -1|
其中，被除数的正负号决定了结果的正负号。
14.2 绝对值函数
ABS(x) 函数返回了参数 x 的绝对值。例如：
select abs(1), abs(-1), abs(0);
abs(1)|abs(-1)|abs(0)|
------|-------|------|
1| 1| 0|
14.3 取整/四舍五入函数
CEIL(x) 和 CEILING(x) 函数用计算大于等于 x 的最小整数，FLOOR(x) 函数用于计算小于等于 x 的最大整数。例如：
select ceil(1.23), ceiling(-1.23), floor(1.23), floor(-1.23);
ceil(1.23)|ceiling(-1.23)|floor(1.23)|floor(-1.23)|
----------|--------------|-----------|------------|
2| -1| 1| -2|
ROUND(x, y) 函数用于将 x 四舍五入到 y 位小数，y 的默认值为 0；TRUNCATE(x) 函数用于将 x 截断到 y 位小数。例如：
select round(12.5), round(12.5, -1), truncate(12.5, 0), truncate(12.5, -1);
round(12.5)|round(12.5, -1)|truncate(12.5, 0)|truncate(12.5, -1)|
-----------|---------------|-----------------|------------------|
13| 10| 12| 10|
SIGN(x) 函数用于返回 x 的正负号，对于负数、零和正数返回值分别为 -1、0 和 1。例如：
select sign(12.5), sign(0), sign(-99);
sign(12.5)|sign(0)|sign(-99)|
----------|-------|---------|
1| 0| -1|
14.4 指数/对数函数
EXP(x) 函数用于计算自然常数 e 的 x 次幂，e 约等于 2.71828；LN(x) 和 LOG(x) 函数用于计算 x 的自然对数（底数为 e）。例如：
select exp(1), ln(2.718281828459045), log(2.718281828459045);
exp(1) |ln(2.718281828459045)|log(2.718281828459045)|
-----------------|---------------------|----------------------|
2.718281828459045| 1.0| 1.0|
LOG(y, x) 函数用于计算以 y 为底的 x 的对数；LOG10(x) 函数用于计算以 10 为底的 x 的对数；LOG2(x) 函数用于计算以 2 为底的 x 的对数。例如：
select log(10, 1000), log10(1000), log2(8);
log(10, 1000) |log10(1000)|log2(8)|
------------------|-----------|-------|
2.9999999999999996| 3.0| 3.0|
log(10, 1000) 等价于 log(1000) / log(10)。
14.5 平方/开方函数
POW(x, y) 和 POWER(x, y) 函数用于计算 x 的 y 次幂；SQRT(x) 函数用于计算 x 的平方根。例如：
select pow(2, 3), pow(2, -3), sqrt(100);
pow(2, 3)|pow(2, -3)|sqrt(100)|
---------|----------|---------|
8.0| 0.125| 10.0|
14.6 三角函数
SIN(x) 函数用于计算弧度 x 的正弦值；COS(x) 函数用于计算弧度 x 的余弦值；TAN(x) 函数用于计算 x 的正切值；COT(x) 函数用于计算弧度 x 的余切值；PI() 函数用于返回常量 π 的值，约等于 3.141593。例如：
select pi(), sin(pi()), cos(pi()), tan(pi()), cot(pi());
pi() |sin(pi()) |cos(pi())|tan(pi()) |cot(pi()) |
--------|----------------------|---------|-----------------------|---------------------|
3.141593|1.2246467991473532e-16| -1.0|-1.2246467991473532e-16|-8.165619676597685e15|
ASIN(x) 函数用于计算 x 的反正弦值；ACOS(x) 函数用于计算 x 的反余弦值；ATAN(x) 函数用于计算 x 的反正切值；ATAN2(x, y) 和 ATAN(x, y) 函数用于返回 y/x 的反正切值。例如：
select asin(0), acos(0), atan(1), atan2(2,2);
asin(0)|acos(0) |atan(1) |atan2(2,2) |
-------|------------------|------------------|------------------|
0.0|1.5707963267948966|0.7853981633974483|0.7853981633974483|
DEGREES(x) 函数用于将 x 从弧度转换为角度；RADIANS(x) 函数用于将 x 从角度转换为弧度。例如：
select degrees(pi()), radians(180);
degrees(pi())|radians(180) |
-------------|-----------------|
180.0|3.141592653589793|
14.7 最大值/最小值函数
GREATEST(x1, x2, …) 函数用于返回参数列表中的最大值；LEAST(x1, x2, …) 函数用于返回参数列表中的最小值。例如：
select greatest(3, 4, 5), greatest(3, null, 5), least(3, 4, 5), least('a', 'd', 'c');
greatest(3, 4, 5)|greatest(3, null, 5)|least(3, 4, 5)|least('a', 'd', 'c')|
-----------------|--------------------|--------------|--------------------|
5| | 3|a |
14.8 随机数函数
RAND(seed) 函数用于返回一个大于等于 0 小于 1 的随机数，参数 seed 用于设置一个随机数种子。例如：
select rand(), rand(1)
from employee
limit 5;
rand() |rand(1) |
--------------------|-------------------|
0.009433233187937396|0.40540353712197724|
0.7236781071160716| 0.8716141803857071|
0.5900908667501908| 0.1418603212962489|
0.7794200431363844|0.09445909605776807|
0.12682764616499437|0.04671454713373868|
select rand(), rand(1)
from employee
limit 5;
rand() |rand(1) |
-------------------|-------------------|
0.2958781321494636|0.40540353712197724|
0.09890775298598013| 0.8716141803857071|
0.6069043992151547| 0.1418603212962489|
0.7377987678514782|0.09445909605776807|
0.8682806723455718|0.04671454713373868|
设置随机数种子可以确保每次返回相同的结果。
14.9 进制转换函数
CONV(x, from_base, to_base) 函数用于数字转换为不同的进制表示，返回结果的类型为字符串。例如：
select conv('a',16,2), conv('1010', 2, 10);
conv('a',16,2)|conv('1010', 2, 10)|
--------------|-------------------|
1010 |10 |
14.10 循环冗余校验码函数
CRC32(expr) 函数用于计算字符串 expr 的循环冗余校验值并返回一个 32 位无符号值。例如：
select crc32('MySQL'), crc32('mysql');
crc32('MySQL')|crc32('mysql')|
--------------|--------------|
3259397556| 2501908538|