Logo

# MySQL教程 - 10 函数

SQL 提供了一些内置的函数，可以让我们在查询和更新数据的时候，对数据进行一些处理。例如将字符串转换为大写、去掉前后空格等操作，还包括一些日期、数值等处理。

常用的函数主要分为三类：

  • 文本处理函数
  • 日期和时间处理函数
  • 数值处理函数

# 10.1 文本处理函数

常用的文本函数如下：

函数 功能描述
CONCAT(s1, s2, ...) 拼接字符串（参数是任意多个）
LENGTH(s) 返回字符串的字节长度（按字符集不同）
UPPER(s) 转为大写
LOWER(s) 转为小写
SUBSTRING(s, pos, len) 从字符串中提取子串，pos 是其实位置，从 1 开始，len 表示长度
REPLACE(s, old, new) 替换子字符串
TRIM([remstr] FROM s) 去掉头尾字符，remstr 可以省略，TRIM(str) 默认为去掉前后空格
LOCATE(substr, s) 查找子串首次出现的位置
REVERSE(s) 反转字符串
LPAD(str, length, pad_str) 左填充，用字符串 pad_str 对 str 的左边进行填充，直到 str 的长度达到 length
RPAD(str, length, pad_str) 右填充，用字符串 pad_str 对 str 的右边进行填充，直到 str 的长度达到 length

上面列出了常用的函数，可以按照上面的例子执行一下，看看效果，使用 SELECT 执行即可：

SELECT CONCAT('My', 'SQL');    									-- 执行结果：MySQL
SELECT LENGTH('abc');    												-- 执行结果：3
SELECT UPPER('mysql');    											-- 执行结果：MYSQL
SELECT LOWER('MYSQL');    											-- 执行结果：mysql
SELECT SUBSTRING('abcdef', 2, 3);    						-- 执行结果：bcd
SELECT REPLACE('abcde', 'bc', 'xy');    				-- 执行结果：axyde
SELECT TRIM(' hello ');    											-- 去掉前后空格，执行结果：hello
SELECT TRIM('x' FROM 'xxxHello, World!xxx');		-- 执行结果：Hello, World!
SELECT LOCATE('b', 'abcde');   				  				-- 执行结果：2
SELECT REVERSE('abc');    											-- 执行结果：cba
SELECT LPAD('1', 5, '0');   				  					-- 执行结果：00001
SELECT RPAD('1', 5, '0');    										-- 执行结果：10000
1
2
3
4
5
6
7
8
9
10
11
12

所以我们可以在操作数据库数据的时候，使用上面的函数。

举个例子：

-- 员工编号为1,2,3,...,15，统一修改为00001，00002，00015 这种格式，也就是统一为5位，不足4位，前面补0
update tb_employee set emp_number = lpad(emp_number, 5, '0');
1
2

执行结果：


再举个例子：

-- 查询员工信息，查询结果格式为：姓名(工号)
SELECT CONCAT(name, '(', emp_number, ')') FROM tb_employee;
1
2

# 10.2 日期和时间处理函数

常用的日期和时间处理函数如下：

函数 功能描述
NOW() 返回当前日期和时间
CURDATE() 返回当前日期
CURTIME() 返回当前时间
DATE_FORMAT(date, fmt) 按指定格式格式化日期
DATEDIFF(d1, d2) 返回两个日期之间的天数，大的日期放前面，否则返回负数
TIMESTAMPDIFF(unit, d1, d2) 返回两个日期的时间差，按指定单位（ DAY、HOUR、MINUTE、SECOND、MONTH 等），
大的日期放后面，否则返回负数
ADDDATE(date, n) 在日期上加指定天数
SUBDATE(date, n) 在日期上减指定天数
DATE_ADD(date, INTERVAL value unit) 在指定date上添加指定的时间，
时间单位支持 DAY、HOUR、MINUTE、SECOND、MONTH 等
DATE_SUB(date, INTERVAL value unit) 在指定date上减去指定的时间，
时间单位支持 DAY、HOUR、MINUTE、SECOND、MONTH 等
YEAR(date) 返回指定date的年份
MONTH(date) 返回指定date的月份
DAY(date) 返回指定date的日期

上面的函数同样可以使用 SELECT 执行查询：

SELECT NOW();    																		-- 执行结果：2024-11-28 22:32:36
SELECT CURDATE();    																-- 执行结果：2024-11-28
SELECT CURTIME();    																-- 执行结果：22:32:45
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');    					-- 执行结果：2024-11-28 22:32:36
SELECT DATE_FORMAT(NOW(3), '%Y-%m-%d %H:%i:%s.%f'); -- 执行结果：2024-11-28 14:30:45.123456
SELECT DATEDIFF('2024-12-01', '2024-11-28');    		-- 执行结果：3
SELECT TIMESTAMPDIFF(DAY, '2024-11-01', NOW());    	-- 按指定单位DAY返回时间差，执行结果：27
SELECT ADDDATE('2024-11-28', 5);    								-- 执行结果：2024-12-03
SELECT SUBDATE('2024-11-28', 5);   				  				-- 执行结果：2024-11-23
SELECT DATE_ADD('2024-11-28', INTERVAL 2 MONTH);    -- 执行结果：2025-01-28
SELECT DATE_SUB('2024-11-28', INTERVAL 2 MONTH);    -- 执行结果：2024-09-28
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY);    					-- 当前时间+2天，执行结果：2024-09-28
SELECT YEAR('2024-11-28');    											-- 执行结果：2024
SELECT MONTH('2024-11-28');   				  						-- 执行结果：11
SELECT DAY('2024-11-28');    												-- 执行结果：28
SELECT DAY(NOW());    															-- 获取当前时间的日志，执行结果：28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  • 上面使用字符串 2024-11-28 形式的时间，都可以替换为使用 NOW() 和 CURDATE() ，来获取当前的时间进行计算；
  • 上面的执行时间按照你执行的时间为准，上面的时间只是参考。
  • NOW(3) 或 CURRENT_TIMESTAMP(3) 可以返回带有毫秒的时间。

演示一下在实际场景的使用：

-- 查询所有员工的入职天数，倒序排列，使用 DATEDIFF() 函数，比较当前时间和入职时间的时间差
SELECT name, DATEDIFF(CURDATE(), entry_time) as days FROM tb_employee ORDER BY days;

-- 或者
SELECT name, TIMESTAMPDIFF(DAY, entry_time, NOW()) as days FROM tb_employee ORDER BY days;
1
2
3
4
5

执行结果：

# 10.3 数值处理函数

常用的数值处理函数有：

函数 功能描述
ABS(x) 返回 x 的绝对值
MOD(x, y) 返回 x 除以 y 的余数
DIV 返回整数除法的商
ROUND(x, d) 将 x 四舍五入，保留 d 位小数
TRUNCATE(x, d) 截断 x ，保留 d 位小数（不四舍五入）
CEIL(x) / CEILING(x) 向上取整，变成整数
FLOOR(x) 向下取整，变成整数
RAND() 返回 0~1 之间的随机数，是小数

上面的函数同样可以使用 SELECT 执行查询：

SELECT ABS(-5);												-- 执行结果：5
SELECT MOD(10, 3);										-- 执行结果：1
SELECT 10 DIV 3;											-- 执行结果：3
SELECT ROUND(3.14159, 2);							-- 执行结果：3.14
SELECT TRUNCATE(3.14159, 2);					-- 执行结果：3.14
SELECT CEIL(4.2);											-- 执行结果：5
SELECT FLOOR(4.8);										-- 执行结果：4
SELECT RAND();												-- 执行结果：0.4522182181963867（随机）
1
2
3
4
5
6
7
8

演示一下在实际场景的使用：

-- 生成一个6位随机数，将 RAND() 生成的小数乘以1000000，并把小数截掉
SELECT TRUNCATE(RAND() * 1000000, 0)

-- 但是上面有一个问题，就是RAND()生成的小数可能是0.001234424，乘以1000000得到1234，不够6位，所以需要在前面补0，得到的是6为的字符串
SELECT LPAD(TRUNCATE(RAND() * 1000000, 0), 6, '0')
1
2
3
4
5

SQL的数值函数有很多，例如幂、对数、三角函数、二进制运算等，这里就不介绍了，需要的自己搜索一下。

# 10.4 流程函数

流程函数 是一种特殊的函数，用于在 SQL 查询中实现条件判断、逻辑控制和数据处理。通过这些函数，可以在 SQL 语句中动态地控制数据的输出和处理流程。

下面介绍一下常用的流程函数。

# 1 IF

语法：

-- 如果condition为true，返回true_value，否则返回false_value
IF(condition, true_value, false_value)
1
2

举个栗子：

-- 执行结果 Yes
SELECT IF(1 > 0, 'Yes', 'No');
1
2

举个实际使用的场景：

查询员工表，如果入职超过 365 天，就算老员工，否则算新员工，查询如下：

SELECT 
    id,
    name,
    IF(DATEDIFF(NOW(), entry_time) > 365, '老员工', '新员工') AS emp_status
FROM tb_employee;
1
2
3
4
5

执行结果：

# 2 IFNULL

语法：

-- 如果 value1 不为 NULL，则返回 value1，否则返回value2
IFNULL(value1, value1)
1
2

举个栗子：

SELECT IFNULL(NULL, 'Default');  					-- 返回 'Default'
SELECT IFNULL('Foooor', 'Default');  			-- 返回 'Foooor'
1
2

IFNULL 可以用于处理可能包含 NULL 值的数据，例如将 NULL 替换为默认值。


举个实际使用的场景：

员工表，有的身份证为空，如果希望查询员工时将身份证为 NULL 替换为默认备注 "未登记身份证"。

SELECT 
    id,
    name,
    IFNULL(id_card, '未登记身份证') AS id_card
FROM tb_employee;
1
2
3
4
5

执行结果：

# 3 CASE

CASE 可以根据多个条件进行选择并返回值，类似于编程语言中的 switch 或 if-else 语句。

语法有两种：

-- 简单 CASE：
CASE expr
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END


-- 搜索型 CASE：
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

举个栗子：

-- 简单 CASE，执行结果：'Two'
SELECT CASE 2
    WHEN 1 THEN 'One'
    WHEN 2 THEN 'Two'
    ELSE 'Other'
END;


-- 搜索型 CASE，执行结果：'Positive'
SELECT CASE
    WHEN 1 > 0 THEN 'Positive'
    WHEN 1 < 0 THEN 'Negative'
    ELSE 'Zero'
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

举个实际使用的场景：

根据员工的年龄进行显示，年龄 < 40 算青年， 年龄 > 40 并且 年龄 < 50 算中年，年龄 > 50 算老年，SQL 如下：

SELECT 
    id, 
    name, 
    age,
    CASE 
        WHEN age <= 40 THEN '青年'
        WHEN age > 40 AND age <= 50 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM tb_employee;
1
2
3
4
5
6
7
8
9
10

上面使用的是搜索型的 CASE ，判断 age 满足哪个条件。

执行结果：

← 11-约束 →