单行函数

本文笔记根据【b站 尚硅谷-宋红康 MySQL 课程】整理。笔记和代码存储在我的 GitHub 库中 github.com/isKage/sql-notes。

不同的数据库管理系统 DBMS 的函数使用差异极大，SQL 语言函数的可移植性很差。下面我们介绍的是 SQL 语言的内置函数。

MySQL 提供的内置函数包括单行函数和聚合函数，这章讲解单行函数。

单行函数：对行数据进行操作；输入参数，返回结果；对每行进行操作。

1 数值函数

1.1 基本数值函数

  • ABS(x) ：返回 x 的绝对值
  • SIGN(x) ：返回 x 的符号，正数返回 1 ，负数返回 -1 ， 0 返回 0
  • PI() ：返回圆周率的值
  • CEIL(x), CEILING(x) ：返回大于或等于 x 的最小整数
  • FLOOR(x) ：返回小于或等于 x 的最大整数
  • LEAST(x1, x2, x3, ...) ：返回列表中的最小值
  • GREATEST(x1, x2, x3, ...) ：返回列表中的最大值
  • MOD(x, y) ：返回 x 除以 y 后的余数
  • RAND() ：返回 0-1 的随机值
  • RAND(x) ：返回 0-1 的随机值，其中 x 的值用作种子值，相同的X值会产生相同的随机数
  • ROUND(x) ：返回一个对 x 的值进行四舍五入后，最接近于 x 的整数
  • ROUND(x, y) ：返回一个对 x 的值进行四舍五入后最接近 x 的值，并保留到小数点后面 y 位
  • TRUNCATE(x, y) ：返回数字 x 截断为 y 位小数的结果
  • SQRT(x) ：返回 x 的平方根，当 x 的值为负数时，返回NULL
  • RADIANS(x) ：将角度转化为弧度，其中参数 x 为角度值
  • DEGREES(x) ：将弧度转化为角度，其中参数 x 为弧度值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT ABS(-10),            -- 10
       SIGN(-20),           -- -1
       SIGN(30),            -- 1
       PI(),                -- 3.141593
       CEIL(40.2),          -- 41
       CEILING(-40.3),      -- 40
       FLOOR(50.4),         -- 50
       FLOOR(-60.5),        -- -61
       MOD(12, 5),          -- 2
       RAND(),-- 0.5452342969786695
       RAND(10),            -- 0.6570515219653505
       RAND(10),            -- 0.6570515219653505
       ROUND(12.33),        -- 12
       ROUND(12.348, 2),    -- 12.35
       ROUND(12.324, -1),   -- 10
       TRUNCATE(12.66, 1),  -- 12.6
       TRUNCATE(12.66, -1), -- 10
       RADIANS(180),        -- 3.141592653589793
       DEGREES(2 * PI()),   -- 360
       DEGREES(RADIANS(90)); -- 90

1.2 三角函数

以下 x 均为弧度值

  • SIN(x) ：正弦值
  • ASIN(x) ：反正弦值，即获取正弦为 x 的弧度，如果 x 的值不在 [-1, 1] ，则返回 NULL
  • COS(x) ：余弦值
  • ACOS(x) ：返回 x 的反余弦值，即获取余弦为 x 的弧度，如果 x 的值不在 [-1, 1] ，则返回 NULL
  • TAN(x) ：正切值
  • ATAN(x) ：返回 x。的反正切值，即获取正切值为 x 的弧度
  • ATAN2(m,n) ：返回点 (m, n) 到 (0, 0) 的连线构成的角度的弧度值
  • COT(x) ：余切值

反三角函数返回的就是角度的弧度值，可以方便的得到角度大小

ATAN2(m, n) 函数返回 (m, n) 到 (0, 0) 的连线构成的角度的弧度值。如果使用 ATAN(x) 则会出现报错，例如当 x=∞x=\infty 时，即求点 (1, 0) 到 (0, 0) 的连线构成的角度的弧度值

1
2
3
4
5
SELECT SIN(RADIANS(30)), -- sin(π/6) = 0.5
       DEGREES(ASIN(1)), -- arcsin(90º) = 1
       TAN(RADIANS(45)), -- tan(45º) = tan(π/4) = 1
       DEGREES(ATAN(1)), -- arctan(1) = 45º
       DEGREES(ATAN2(1, 1)); -- arctan(1) = 45º (1, 1) 与 (0, 0) 的连线角度

1.3 指数与对数

  • POW(x, y), POWER(x, y) ：返回 x 的 y 次方
  • EXP(X) ：返回 e 的 x 次方，e 为自然对数
  • LN(x), LOG(x) ：返回以 e 为底的 x 的对数，当 x 小于等于 0 时，返回 NULL
  • LOG10(x) ：返回以 10 为底的 x 的对数，当 x 小于等于 0 时，返回 NULL
  • LOG2(x) ：返回以 2 为底的 x 的对数，当 x 小于等于 0 时，返回 NULL
1
2
3
4
5
6
SELECT POW(2, 5),  -- 2^5 = 32
       POWER(2, 4),-- 2^4 = 16
       EXP(2),     -- e^2 = 7.3891
       LN(10),     -- ln(10) = 2.303
       LOG10(10),  -- log_10(10) = 1
       LOG2(4); -- log_2(2) = 2

1.4 进制转换

  • BIN(x) ：返回 x 的二进制编码
  • HEX(x) ：返回 x 的十六进制编码
  • OCT(x) ：返回 x 的八进制编码
  • CONV(x, f1, f2) ：返回 x 从 f1 进制数变成 f2 进制数
1
2
3
4
SELECT BIN(1),  -- 1
       HEX(15), -- F
       OCT(7),  -- 7
       CONV(1, 10, 2); -- 1

2 字符串函数

MySQL 中，字符串的位置是从 1 开始计数

  • ASCII(s) ：返回字符串 s 中的第一个字符的 ASCII 码

  • CHAR_LENGTH(s)：返回字符串 s 的字符数，作用与 CHARACTER_LENGTH(s) 相同

  • LENGTH(s)：返回字符串 s 的字节数（与字符集有关）

  • CONCAT(s1, s2, ..., sn)：将 s1, s2, ..., sn 连接为一个字符串

  • CONCAT_WS(x, s1, s2, ..., sn)：用分隔符 x 连接 s1, s2, ..., sn（每个字符串间插入 x）

  • INSERT(str, idx, len, replacestr)：从字符串 str 的 idx 位置开始，替换 len 个字符为 replacestr

  • REPLACE(str, a, b)：将字符串 str 中所有子串 a 替换为 b

  • UPPER(s) 或 UCASE(s)：将字符串 s 全部转为大写

  • LOWER(s) 或 LCASE(s)：将字符串 s 全部转为小写

  • LEFT(str, n)：返回字符串 str 最左侧的 n 个字符

  • RIGHT(str, n)：返回字符串 str 最右侧的 n 个字符

  • LPAD(str, len, pad)：用 pad 从左侧填充 str，直到总长度为 len

  • RPAD(str, len, pad)：用 pad 从右侧填充 str，直到总长度为 len

  • LTRIM(s)：去掉字符串 s 左侧的空格

  • RTRIM(s)：去掉字符串 s 右侧的空格

  • TRIM(s)：去掉字符串 s 开头和结尾的空格

  • TRIM(s1 FROM s)：去掉字符串 s 开头和结尾的 s1

  • TRIM(LEADING s1 FROM s)：去掉字符串 s 开头的 s1

  • TRIM(TRAILING s1 FROM s)：去掉字符串 s 结尾的 s1

  • REPEAT(str, n)：返回 str 重复 n 次的结果

  • SPACE(n)：返回由 n 个空格组成的字符串

  • STRCMP(s1, s2)：比较字符串 s1 和 s2 的 ASCII 码值大小

  • SUBSTR(s, index, len)：从 s 的 index 位置截取 len 个字符，作用同 SUBSTRING(s, n, len) 和 MID(s, n, len)

  • LOCATE(substr, str)：返回 substr 在 str 中首次出现的位置，作用同 POSITION(substr IN str) 和 INSTR(str, substr)（未找到返回 0）

  • ELT(m, s1, s2, ..., sn)：返回第 m 个字符串（如 m=1 返回 s1）

  • FIELD(s, s1, s2, ..., sn)：返回 s 在列表 s1, s2, ..., sn 中第一次出现的位置

  • FIND_IN_SET(s1, s2)：返回 s1 在逗号分隔的字符串 s2 中的位置

  • REVERSE(s)：返回 s 反转后的字符串

  • NULLIF(value1, value2)：若 value1 与 value2 相等则返回 NULL，否则返回 value1

例：FIND_IN_SET(s1, s2) 函数

1
2
SELECT FIELD('mm', 'hello', 'msm', 'amma'), -- 0 : 因为 mm 没有出现在字符串列表中
       FIND_IN_SET('mm', 'hello,mm,amma'); -- 2 : 出现在第 2 个，从 1 开始计数

FIND_IN_SET(s1, s2)：返回 s1 在逗号分隔的字符串 s2 中的位置。s2 是里的元素只能以逗号为间隔

1
2
SELECT FIND_IN_SET('mm', 'hello,mm,amma'), -- 2 : 出现在第 2 个，从 1 开始计数
       FIND_IN_SET('mm', 'hello, mm, amma'); -- 0 : 因为出现了空格，此时第 2 个是 'mm '

例：NULLIF() 函数

1
2
SELECT NULLIF('mysql', 'mysql'),  -- 相同返回 NULL
       NULLIF('mysql', 'sql');  -- 不同返回第一个值 mysql

3 日期和时间函数

3.1 获取日期和时间

  • CURDATE(), CURRENT_DATE() ：返回当前日期，只包含年、月、日

  • CURTIME(), CURRENT_TIME() ：返回当前时间，只包含时、分、秒

  • NOW() SYSDATE() CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP() ：返回当前系统日期和时间

  • UTC_DATE() ：返回 UTC（世界标准时间）日期

  • UTC_TIME() ：返回UTC（世界标准时间）时间

1
2
3
4
5
6
SELECT CURDATE(),        -- 2025-02-10
       CURTIME(),        -- 18:15:16
       NOW(),            -- 2025-02-10 18:15:16
       LOCALTIMESTAMP(), -- 2025-02-10 18:15:16
       UTC_DATE(),       -- 2025-02-10
       UTC_TIME(); -- 10:15:16

3.2 日期与时间戳的转换

  • UNIX_TIMESTAMP() ：以 UNIX 时间戳的形式返回当前时间
  • UNIX_TIMESTAMP(date) ：将时间 date 以 UNIX 时间戳的形式返回
  • FROM_UNIXTIME(timestamp) ：将 UNIX 时间戳的时间转换为普通格式的时间

UNIX 时间戳（UNIX Timestamp）是指从 1970 年 1 月 1 日 00:00:00 UTC（协调世界时）开始所经过的秒数

1
2
3
4
5
SELECT UNIX_TIMESTAMP(now()),                 -- 1739182869 精确到时分秒
       UNIX_TIMESTAMP(CURTIME()),             -- 1739182869 与第一个相同
       UNIX_TIMESTAMP(CURDATE()),             -- 1739116800 日期转时间戳
       UNIX_TIMESTAMP('2011-11-11 11:11:11'), -- 特定日期时间
       FROM_UNIXTIME(1739182869); -- 2025-02-10 18:21:09

3.3 获取月份、星期、星期数、天数等

3.4 日期的操作函数

  • EXTRACT(type FROM date) ：返回指定日期中特定的部分，type 指定返回的值

具体的 type 类型可以自行查询

3.5 日期的格式化与解析

1
2
3
# 格式化输出
SELECT DATE_FORMAT(NOW(), '%Y-%M-%D-%W %H:%i:%s');
-- 2025-February-10th-Monday 18:30:58
1
2
3
4
# 解析为标准日期形式
SELECT STR_TO_DATE('09/01/2009', '%m/%d/%Y'),
       STR_TO_DATE('20140422154706', '%Y%m%d%H%i%s'),
       STR_TO_DATE('2014-04-22 15:47:06', '%Y-%m-%d %H:%i:%s');

4 流程控制函数【重要】

根据不同的条件，执行不同的处理流程，类似于其他语言中的 if for while

4.1 IF() 函数

  • IF(value, value1, value2) ：如果 value 的值为 TRUE ，返回 value1，否则返回 value2
1
SELECT IF(TRUE, 'value1', 'value2');  -- 'value1'

4.2 IFNULL(value1, value2) 函数

  • IFNULL(value1, value2) ：如果 value1 不为 NULL，返回 value1，否则返回 value2
1
2
SELECT IFNULL(NULL, 'value2'); -- NULL
SELECT IFNULL('value1', 'value2'); -- 'value1'

4.3 按条件返回结果

  • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 [ELSE 结果3] END 列名 ：如果 条件1 成立，则返回 结果1 ；如果 条件2 成立，则返回 结果2 ；如果 条件3 成立，则返回 结果3

格式：

1
2
3
4
5
6
7
8
9
SELECT 列名1,
       列名2,
       CASE
           WHEN 条件1 THEN 结果1
           WHEN 条件2 THEN 结果2
           WHEN 条件3 THEN 结果3
           ELSE 结果4 
           END 列名
FROM 表名;

例如：按照 salary 给员工增加一个字段 "level of salary"

1
2
3
4
5
6
7
8
9
SELECT employee_id,
       salary,
       CASE
           WHEN salary >= 15000 THEN 'high'
           WHEN salary >= 10000 THEN 'average'
           WHEN salary >= 8000 THEN 'low'
           ELSE 'too low' 
           END "level of salary"
FROM employees;

4.4 按数值返回结果

  • CASE 列名1 WHEN num1 THEN 结果1 WHEN num2 THEN 结果2 ELSE 结果3 END 列名2 ：如果 列名1 这行的值等于 num1 则返回 结果1 ；若这行的值等于 num2 则返回 结果2 ；否则返回 结果3 ，最终存入 列名2

格式

1
2
3
4
5
6
7
8
9
10
SELECT 列名1,
       列名2,
       CASE 列名1
           WHEN 1 THEN 结果1
           WHEN 2 THEN 结果2
           WHEN 3 THEN 结果3
           WHEN 4 THEN 结果4
           ELSE 结果5 
           END 新列名
FROM 表名;

例如：按照 region_id 填充 region_name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 法一：多表连接
SELECT c.country_name,
       c.region_id,
       r.region_name
FROM countries AS c
         LEFT JOIN regions as r
                   ON c.region_id = r.region_id;

-- 法二：按数值返回结果 CASE WHEN THEN
SELECT c.country_name,
       c.region_id,
       CASE c.region_id
           WHEN 1 THEN 'Europe'
           WHEN 2 THEN 'Americas'
           WHEN 3 THEN 'Asia'
           WHEN 4 THEN 'Middle East and Africa'
           ELSE 'Not find' 
           END "region name"
FROM countries AS c;

4.5 一些练习

  • 计算年工资，考虑到奖金
1
2
3
SELECT employee_id,
       12 * salary * (1 + IFNULL(commission_pct, 0)) AS "annual salary"
FROM employees;
  • 按照职位调整薪水，同时补充职位全程
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT e.last_name,
       e.job_id,
       e.salary,
       j.job_title,
       CASE e.job_id
           WHEN 'IT_PROG' THEN 1.10 * salary
           WHEN 'ST_CLERK' THEN 1.15 * salary
           WHEN 'SA_REP' THEN 1.20 * salary
           ELSE salary
           END "REVISED_SALARY" -- 按照职位调整薪水
FROM employees AS e
         LEFT JOIN jobs AS j
                   ON e.job_id = j.job_id; -- 补充职位全程，多表连接

5 加密与解密函数【数据库安全】

因为安全性问题，在 MySQL 8.0 版本后 PASSWORD(str) ENCODE() DECODE() 函数均被移除

5.1 MD5 和 SHA 加密

1
2
SELECT MD5('mysql') AS "md5 key"; -- 81c3b080dad537de7e10e0987a4bf52e
SELECT SHA('mysql') AS "sha key"; -- f460c882a18c1304d88854e902e11b85d71e7e1b

5.2 加密和解密

使用 AES_ENCRYPT() 和 AES_DECRYPT() 函数实现 ENCODE 和 DECODE 功能

1
2
3
4
5
6
7
mysql> SELECT AES_ENCRYPT('mysql', 'seed') AS encrypted_data;
+------------------------------------+
| encrypted_data                     |
+------------------------------------+
| 0x0F46E6F2FE63CD819E2AD70B1919B9C5 |
+------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
mysql> SELECT AES_DECRYPT(AES_ENCRYPT('mysql', 'seed'), 'seed') AS decrypted_data;
+--------------------------------+
| decrypted_data                 |
+--------------------------------+
| 0x6D7973716C                   |  -- 'mysql' 的十六进制
+--------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
mysql> SELECT CAST(AES_DECRYPT(AES_ENCRYPT('mysql', 'seed'), 'seed') AS CHAR) AS decrypted_data;
+----------------+
| decrypted_data |
+----------------+
| mysql          |  -- CAST() 函数转格式
+----------------+
1 row in set (0.00 sec)

容易得知，AES_ENCRYPT AES_DECRYPT 互为反函数

6 获取 MySQL 相关信息

  • MySQL 版本号
1
2
3
4
5
6
7
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)
  • 当前数据库
1
2
3
4
5
6
7
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dbtest1    |
+------------+
1 row in set (0.00 sec)
  • 当前连接 MySQL 的用户名，返回结果格式为 "主机名@用户名"
1
2
3
4
5
6
7
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
  • 字符集和比较规则
1
2
3
4
5
6
7
mysql> SELECT CHARSET('ABC'), COLLATION('ABC');
+----------------+--------------------+
| CHARSET('ABC') | COLLATION('ABC')   |
+----------------+--------------------+
| utf8mb4        | utf8mb4_0900_ai_ci |
+----------------+--------------------+
1 row in set (0.00 sec)

7 其他函数

  • 格式化输出

FORMAT(value, n) ：返回对数字 value 进行格式化后的结果数据，n表示四舍五入保留到小数点后 n 位

1
2
3
4
5
6
7
8
-- n <= 0 均表示保留整数
mysql> SELECT FORMAT(1.124, 2), FORMAT(10.523, 0), FORMAT(100.123, -2);
+------------------+-------------------+---------------------+
| FORMAT(1.124, 2) | FORMAT(10.523, 0) | FORMAT(100.123, -2) |
+------------------+-------------------+---------------------+
| 1.12             | 11                | 100                 |
+------------------+-------------------+---------------------+
1 row in set (0.00 sec)
  • IP 转换

INET_ATON(ipvalue) ：将以点分隔的IP地址转化为一个数字

INET_NTOA(value) ：将数字形式的IP地址转化为以点分隔的IP地址

1
2
3
4
5
6
7
8
9
mysql> SELECT INET_ATON('192.168.1.100') "IP - Num", INET_NTOA(3232235876) "Num - IP";
+------------+---------------+
| IP - Num   | Num - IP      |
+------------+---------------+
| 3232235876 | 192.168.1.100 |
+------------+---------------+
1 row in set (0.00 sec)

# IP 按照 256 进制计算: 192 * 256^3 + 168 * 256^2 + 1 * 256^1 + 100
  • 重复执行

BENCHMARK(n,expr) ：将表达式 expr 重复执行n次。用于测试 MySQL 处理 expr 表达式所耗费的时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT BENCHMARK(1000000, MD5('mysql')) "Once";
+------+
| Once |
+------+
|    0 |
+------+
1 row in set (0.25 sec) -- 0.25 sec

mysql> SELECT BENCHMARK(10000000, MD5('mysql')) "Many times";
+------------+
| Many times |
+------------+
|          0 |
+------------+
1 row in set (2.32 sec) -- 2.32 sec
  • 修改编码方式

CONVERT(value USING char_code) ：将 value 所使用的字符编码修改为 char_code

1
2
3
4
5
6
7
mysql> SELECT CHARSET('mysql') "Origin", CHARSET(CONVERT('mysql' USING 'utf8')) "Convert to utf8";
+---------+-----------------+
| Origin  | Convert to utf8 |
+---------+-----------------+
| utf8mb4 | utf8mb3         |
+---------+-----------------+
1 row in set, 1 warning (0.00 sec)