一、函数的基本概念
函数是一段预定义的 SQL 代码块，接受输入参数，执行特定操作后返回单个值。它的核心作用是简化 SQL 语句、提高查询效率和增强代码复用性。
函数与存储过程的区别
|
特性 |
函数（Function） |
存储过程（Stored Procedure） |
|
返回值 |
必须返回一个值（通过RETURN语句） |
可返回多个值、单个值或不返回值 |
|
嵌入使用 |
可嵌入到 SQL 语句中（如SELECT、WHERE子句） |
不能嵌入到 SQL 语句，需通过CALL单独调用 |
|
功能限制 |
不能使用事务、不能修改表结构等 |
功能更灵活，支持事务、表结构修改等操作 |
|
适用场景 |
简单数据计算、值转换（如字符串处理、数值计算） |
复杂业务逻辑（如批量数据处理、多表操作） |
二、内置函数分类及示例
MySQL 提供了丰富的内置函数，按功能可分为字符串函数、数值函数、日期时间函数、聚合函数和条件函数，覆盖日常开发中 90% 以上的数据处理场景。
1. 字符串函数
用于处理字符串类型数据（如拼接、截取、转换大小写等），常用函数及示例如下：
|
函数语法 |
功能描述 |
示例代码 |
执行结果 |
|
CONCAT(s1, s2, ..., sn) |
连接多个字符串，若有 NULL 则返回 NULL |
CONCAT('MySQL', ' ', 'Function') |
MySQL Function |
|
LENGTH(str) |
返回字符串字节数（UTF8 编码：汉字占 3 字节） |
LENGTH('数据库') |
9 |
|
CHAR_LENGTH(str) |
返回字符串字符数（忽略编码差异） |
CHAR_LENGTH('数据库') |
3 |
|
UPPER(str) / LOWER(str) |
将字符串转换为大写 / 小写 |
UPPER('hello') |
HELLO |
|
SUBSTRING(str, pos, len) |
截取子串（pos：起始位置，len：长度） |
SUBSTRING('MySQL', 2, 3) |
ySQ |
|
TRIM([BOTH/LEADING/TRAILING] str) |
去除字符串首尾 / 开头 / 结尾的空格（默认首尾） |
TRIM(' SQL '), TRIM(LEADING 'x' FROM 'xxSQLxx') |
SQL, SQLxx |
|
REPLACE(str, from_str, to_str) |
将 str 中的 from_str 替换为 to_str |
REPLACE('2024-05-01', '-', '/') |
2024/05/01 |
综合代码示例：字符串函数实战
-- 场景：格式化用户信息（拼接姓名+ID，处理空邮箱）
SELECT
id,
CONCAT('用户_', id, '_', name) AS user_code, -- 拼接用户编码
LOWER(email) AS lower_email, -- 邮箱统一小写
TRIM(IFNULL(phone, '未填写')) AS clean_phone -- 处理NULL手机号
FROM users
WHERE CHAR_LENGTH(name) >= 2; -- 筛选姓名长度≥2的用户
2. 数值函数
用于数值计算（如取整、四舍五入、求余等），适合处理价格、分数、数量等数值型数据：
|
函数语法 |
功能描述 |
示例代码 |
执行结果 |
|
ABS(x) |
返回 x 的绝对值 |
ABS(-15.6) |
15.6 |
|
CEIL(x) / FLOOR(x) |
向上取整 / 向下取整（忽略小数部分） |
CEIL(3.2), FLOOR(3.9) |
4, 3 |
|
ROUND(x, d) |
四舍五入（d：保留小数位数，默认 0） |
ROUND(3.14159, 2) |
3.14 |
|
MOD(x, y) |
求 x 除以 y 的余数（同x % y） |
MOD(10, 3) |
1 |
|
POWER(x, y) |
计算 x 的 y 次方（如平方、立方） |
POWER(2, 3) |
8 |
|
RAND() |
生成 0~1 的随机浮点数（每次执行结果不同） |
RAND() |
示例：0.6283 |
|
FORMAT(x, d) |
格式化数值（千分位分隔，保留 d 位小数） |
FORMAT(12345.678, 2) |
12,345.68 |
综合代码示例：数值函数实战
-- 场景：计算商品折扣价并处理异常值
SELECT
product_id,
product_name,
price AS original_price,
discount,
ROUND(price * discount, 2) AS discount_price, -- 折扣价保留2位小数
CEIL(price * 0.8) AS promotion_price, -- 促销价向上取整
MOD(stock, 10) AS remainder_stock -- 库存对10取余（用于分批发货）
FROM products
WHERE price > 0; -- 避免价格为0或负数的异常数据
3. 日期和时间函数
用于处理日期时间类型数据（如获取当前时间、计算日期差、格式化日期等），是业务开发中最常用的函数类别之一：
|
函数语法 |
功能描述 |
示例代码 |
执行结果（示例） |
|
NOW() |
返回当前日期 + 时间（格式：YYYY-MM-DD HH:MM:SS） |
NOW() |
2024-05-20 14:30:45 |
|
CURDATE() / CURTIME() |
返回当前日期 / 当前时间 |
CURDATE(), CURTIME() |
2024-05-20, 14:30:45 |
|
DATE_ADD(date, INTERVAL expr unit) |
日期加法（unit：DAY/MONTH/YEAR/HOUR 等） |
DATE_ADD('2024-05-20', INTERVAL 7 DAY) |
2024-05-27 |
|
DATE_SUB(date, INTERVAL expr unit) |
日期减法 |
DATE_SUB('2024-05-20', INTERVAL 1 MONTH) |
2024-04-20 |
|
DATEDIFF(date1, date2) |
计算 date1 与 date2 的天数差（date1 - date2） |
DATEDIFF('2024-05-20', '2024-05-15') |
5 |
|
TIMESTAMPDIFF(unit, start, end) |
计算两个时间的差值（支持更细粒度：HOUR/MINUTE/SECOND） |
TIMESTAMPDIFF(HOUR, '2024-05-20 10:00', '2024-05-20 15:30') |
5 |
|
DATE_FORMAT(date, format) |
自定义日期格式（format：% Y = 年，% m = 月，% d = 日，% H = 时，% i = 分） |
DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i') |
2024年05月20日 14:30 |
综合代码示例：日期函数实战
-- 场景：统计近30天的订单数据（按天分组，计算订单量和金额）
SELECT
DATE_FORMAT(order_time, '%Y-%m-%d') AS order_day, -- 日期格式化
COUNT(*) AS order_count, -- 订单数量
SUM(order_amount) AS total_amount, -- 订单总金额
-- 计算订单平均处理时长（小时）
AVG(TIMESTAMPDIFF(HOUR, order_time, pay_time)) AS avg_process_hour
FROM orders
-- 筛选近30天的订单（避免包含未来日期）
WHERE order_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
AND order_status = '已支付' -- 只统计已支付订单
GROUP BY order_day
ORDER BY order_day DESC; -- 按日期倒序排列
4. 聚合函数
用于对一组数据进行 “汇总计算” 并返回单一结果，通常与GROUP BY一起使用（实现分组统计），常用函数如下：
|
函数语法 |
功能描述 |
适用场景 |
示例代码 |
|
COUNT(expr) |
统计非 NULL 值的数量（COUNT(*)统计所有行） |
统计记录数（如用户数、订单数） |
COUNT(*), COUNT(user_id) |
|
SUM(expr) |
计算数值列的总和（忽略 NULL 值） |
统计总金额、总销量等 |
SUM(sales_amount) |
|
AVG(expr) |
计算数值列的平均值（忽略 NULL 值） |
统计平均分数、平均工资等 |
AVG(student_score) |
|
MAX(expr) / MIN(expr) |
计算列的最大值 / 最小值（支持日期、字符串） |
查找最高价格、最早入职日期等 |
MAX(product_price), MIN(hire_date) |
|
GROUP_CONCAT(expr [SEPARATOR str]) |
将分组内的 expr 值拼接为字符串（默认分隔符为逗号） |
合并分组内的多个值（如同一用户的所有订单 ID） |
`GROUP_CONCAT(order_id SEPARATOR ' |
综合代码示例：聚合函数实战
-- 场景：按部门统计员工信息（分组统计+筛选）
SELECT
department AS dept_name, -- 分组字段：部门
COUNT(*) AS total_emp, -- 部门总人数
COUNT(DISTINCT post) AS post_count, -- 部门内岗位种类数（去重）
SUM(salary) AS total_salary, -- 部门工资总和
AVG(salary) AS avg_salary, -- 部门平均工资
MAX(hire_date) AS latest_hire, -- 部门最新入职日期
-- 拼接部门内所有员工姓名（分隔符为逗号）
GROUP_CONCAT(emp_name SEPARATOR ', ') AS emp_list
FROM employees
-- 筛选条件：入职时间在2020年之后
WHERE hire_date >= '2020-01-01'
-- 按部门分组
GROUP BY department
-- 筛选分组结果：部门人数≥5且平均工资≥8000
HAVING total_emp >= 5 AND avg_salary >= 8000
-- 按平均工资降序排列
ORDER BY avg_salary DESC;
5. 条件函数
用于实现 SQL 中的 “逻辑判断”，类似编程语言中的if-else，常用函数包括IF、IFNULL和CASE：
（1）IF(expr, v1, v2)
- 功能：如果条件expr为TRUE（非 0、非 NULL），返回v1，否则返回v2
- 示例：判断用户是否成年
SELECT
name,
age,
IF(age >= 18, '成年', '未成年') AS age_status,
-- 嵌套IF：判断工资等级
IF(salary >= 15000, '高薪', IF(salary >= 8000, '中薪', '底薪')) AS salary_level
FROM users;
（2）IFNULL(v1, v2)
- 功能：如果v1不为NULL，返回v1，否则返回v2（专门处理 NULL 值）
- 示例：处理空字段（如用户未填写的邮箱、手机号）
SELECT
name,
IFNULL(email, '未绑定邮箱') AS user_email,
IFNULL(phone, '未填写手机号') AS user_phone
FROM users;
（3）CASE表达式（多条件判断）
- 语法 1（简单 CASE）：匹配表达式与固定值
CASE expr
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
- 语法 2（搜索 CASE）：匹配自定义条件（更灵活）
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
- 示例：给考试成绩分级
SELECT
student_name,
score,
-- 搜索CASE：多条件判断成绩等级
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS score_grade,
-- 简单CASE：匹配科目ID与科目名称
CASE subject_id
WHEN 1 THEN '数学'
WHEN 2 THEN '语文'
WHEN 3 THEN '英语'
ELSE '其他科目'
END AS subject_name
FROM exam_results;
三、自定义函数（UDF）
当内置函数无法满足业务需求时，可创建自定义函数（User-Defined Function, UDF） ，封装特定逻辑供重复调用。
1. 自定义函数的创建语法
-- 1. 修改分隔符（避免函数内的;与SQL默认分隔符冲突）
DELIMITER //
-- 2. 创建函数
CREATE FUNCTION 函数名(参数1 类型1, 参数2 类型2, ...)
RETURNS 返回值类型 -- 如INT、VARCHAR(50)、DECIMAL(10,2)
[DETERMINISTIC | NOT DETERMINISTIC] -- 输入相同是否返回相同结果（默认NOT DETERMINISTIC）
[NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA] -- 函数对数据的操作权限
BEGIN
-- 函数体（声明变量、编写逻辑）
DECLARE 变量名 类型 [DEFAULT 默认值]; -- 声明局部变量
-- 业务逻辑代码
SET 变量名 = 值; -- 赋值
IF 条件 THEN ... END IF; -- 条件判断
WHILE 条件 DO ... END WHILE; -- 循环（如需）
RETURN 返回值; -- 必须返回与RETURNS声明一致类型的值
END //
-- 3. 恢复默认分隔符
DELIMITER ;
2. 自定义函数示例 1：计算百分比
-- 场景：计算A占B的百分比（保留2位小数，处理除数为0的情况）
DELIMITER //
CREATE FUNCTION calc_percent(a DECIMAL(10,2), b DECIMAL(10,2))
RETURNS DECIMAL(5,2) -- 返回值：5位数字，2位小数（如99.99）
DETERMINISTIC -- 输入相同，输出相同
NO SQL -- 不操作数据库数据
BEGIN
-- 处理除数为0的异常（避免报错）
IF b = 0 THEN
RETURN 0.00;
ELSE
-- 计算百分比并四舍五入
RETURN ROUND((a / b) * 100, 2);
END IF;
END //
DELIMITER ;
-- 使用自定义函数：统计各商品销量占比
SELECT
product_name,
sales AS product_sales,
total_sales,
calc_percent(sales, total_sales) AS sales_percent -- 调用自定义函数
FROM product_sales;
3. 自定义函数示例 2：生成随机验证码
-- 场景：生成指定长度的随机验证码（包含数字和大小写字母）
DELIMITER //
CREATE FUNCTION generate_code(code_len INT)
RETURNS VARCHAR(20) -- 验证码最长20位
NOT DETERMINISTIC -- 每次调用结果不同（因RAND()）
NO SQL
BEGIN
-- 声明变量：字符库、结果存储、循环计数器
DECLARE chars VARCHAR(62) DEFAULT '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHI
扫一扫
3311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
