MySQL函数

MySQL 函数大全：从基础操作到高级应用

MySQL 提供了丰富的内置函数，涵盖字符处理、数学计算、日期操作、聚合统计等场景，掌握这些函数能大幅提升查询效率和灵活性。本文按功能分类详解常用函数，包含语法、示例及使用注意事项。

字符函数：处理字符串的核心工具

字符函数用于字符串的拼接、截取、转换等操作，是日常查询中最常用的函数类别。

1. `LENGTH(str)`：获取字符串字节数

作用：返回字符串的字节长度（注意：不同字符集字节数不同，如 UTF8 中一个汉字占 3 字节）。

示例：

SELECT LENGTH('john');  -- 结果：4（纯英文，每个字符1字节）
SELECT LENGTH('张三');  -- 结果：6（UTF8 编码，每个汉字3字节）

2. `CONCAT(str1, str2, ...)` 与 `CONCAT_WS(sep, str1, ...)`：拼接字符串

CONCAT：直接拼接多个字符串，若任一参数为 NULL，结果为 NULL。
CONCAT_WS：以第一个参数为分隔符拼接字符串（WS 即 “With Separator”），忽略 NULL。

示例：

SELECT CONCAT('I', ' ', 'like', ' ', 'MySQL');  -- 结果：'I like MySQL'
SELECT CONCAT_WS(' ', 'I', 'like', 'MySQL', NULL);  -- 结果：'I like MySQL'（忽略NULL）

3. `GROUP_CONCAT(expr)`：聚合拼接（分组后生成分隔列表）

作用：将分组内的字段值按指定分隔符拼接（默认逗号），需配合 GROUP BY 使用。
语法：GROUP_CONCAT([DISTINCT] 字段 [ORDER BY 排序] [SEPARATOR 分隔符])

示例：

-- 按 app_id 分组，拼接 item_id 为逗号分隔的字符串
SELECT app_id, 
       GROUP_CONCAT(item_id SEPARATOR ',') AS item_ids  -- 自定义分隔符为逗号
FROM app_video 
GROUP BY app_id;

4. `UPPER(str)` 与 `LOWER(str)`：大小写转换

作用：UPPER 转为大写，LOWER 转为小写。

示例：

SELECT UPPER('mysql');  -- 结果：'MYSQL'
SELECT LOWER('MySQL');  -- 结果：'mysql'

5. `SUBSTR(str, pos, len)` / `SUBSTRING(...)`：字符串截取

作用：从指定位置截取字符串（pos 从 1 开始，len 可选，默认截取到末尾）。

示例：

SELECT SUBSTR('I like MySQL', 8);  -- 从第8位开始截取：'MySQL'
SELECT SUBSTRING('I like MySQL', 8, 2);  -- 从第8位开始，截取2位：'My'

6. `SUBSTRING_INDEX(str, delim, count)`：按分隔符拆分

作用：按 delim 分隔字符串，返回第 count 个分隔符左侧（count>0）或右侧（count<0）的部分。

示例：

SELECT SUBSTRING_INDEX('aaa:bbb:2', ':', 1);  -- 左侧第1个分隔符：'aaa'
SELECT SUBSTRING_INDEX('aaa:bbb:2', ':', -1); -- 右侧第1个分隔符：'2'

7. `INSTR(str, substr)`：查找子串位置

作用：返回子串 substr 在 str 中首次出现的位置（从 1 开始，未找到返回 0）。

示例：

SELECT INSTR('I like MySQL', 'MySQL');  -- 结果：8（'MySQL'从第8位开始）

8. `TRIM(str)` / `LTRIM(str)` / `RTRIM(str)`：去除空格

作用：TRIM 去除首尾空格，LTRIM 仅去左空格，RTRIM 仅去右空格。

示例：

SELECT TRIM('  My SQL  ');  -- 结果：'My SQL'

9. `LPAD(str, len, padstr)` / `RPAD(str, len, padstr)`：填充字符串

作用：用 padstr 向左（LPAD）或向右（RPAD）填充 str，直到总长度为 len。

示例：

SELECT LPAD('MySQL', 8, '*');  -- 左填充：'***MySQL'（总长度8）
SELECT RPAD('MySQL', 8, '*');  -- 右填充：'MySQL***'

10. `REPLACE(str, from_str, to_str)`：替换子串

作用：将 str 中所有 from_str 替换为 to_str。

示例：

SELECT REPLACE('aabb', 'bb', 'cc');  -- 结果：'aacc'

11. `LEFT(str, len)` / `RIGHT(str, len)`：取左侧 / 右侧字符

作用：返回 str 左侧 / 右侧的 len 个字符。

示例：

SELECT LEFT('qwer', 2);  -- 左侧2位：'qw'
SELECT RIGHT('qwer', 2); -- 右侧2位：'er'

12. `REVERSE(str)`：反转字符串

作用：颠倒字符串中字符的顺序。

示例：

SELECT REVERSE('qwer');  -- 结果：'rewq'

13. `COALESCE(expr1, expr2, ...)`：返回第一个非 NULL 值

作用：依次检查参数，返回第一个非 NULL 的值（所有为 NULL 则返回 NULL）。

示例：

-- 当 imagesJson 为 NULL 时，返回 '默认图片'
SELECT COALESCE(imagesJson, '默认图片') FROM doc;

数学函数：数值计算的实用工具

数学函数用于基本运算、取整、统计等数值处理，支持常见的数学操作。

1. 基础聚合函数（统计类）

SUM(expr)：求和（忽略 NULL）。
AVG(expr)：求平均值（忽略 NULL）。
MAX(expr)：求最大值。
MIN(expr)：求最小值。

示例：

SELECT SUM(sal) AS 总薪资, AVG(sal) AS 平均薪资 FROM emp;
SELECT MAX(sal) AS 最高薪资, MIN(sal) AS 最低薪资 FROM emp;

2. `ABS(n)`：绝对值

示例：
1
SELECT ABS(-10); -- 结果：10

3. `ROUND(n, d)`：四舍五入

作用：将 n 四舍五入保留 d 位小数（d 默认为 0，即整数）。

示例：

SELECT ROUND(1.4);      -- 结果：1
SELECT ROUND(1.567, 2); -- 保留2位小数：1.57

4. `CEIL(n)` / `FLOOR(n)`：取整

CEIL：向上取整（返回大于等于 n 的最小整数）。
FLOOR：向下取整（返回小于等于 n 的最大整数）。

示例：

SELECT CEIL(1.2);   -- 结果：2
SELECT FLOOR(1.8);  -- 结果：1

5. `TRUNCATE(n, d)`：截断（直接截取，不四舍五入）

示例：

SELECT TRUNCATE(1.618, 2); -- 结果：1.61（保留 2 位，直接截断）

6. `MOD(n, m)`：取模（求余数）

示例：

SELECT MOD(10, 3);  -- 结果：1（等价于 `10 % 3`）

7. `FORMAT(n, d)`：数字格式化

作用：将数字 n 格式化为带千分位的字符串，保留 d 位小数。

示例：

SELECT FORMAT(12345.678, 1); -- 结果：'12,345.7'

日期函数：时间处理的核心工具

日期函数用于获取、转换、计算时间，是日志分析、报表统计的必备工具。

1. 获取当前时间

NOW()：返回当前日期 + 时间（YYYY-MM-DD HH:MM:SS）。
CURDATE()：返回当前日期（YYYY-MM-DD）。
CURTIME()：返回当前时间（HH:MM:SS）。

示例：

SELECT NOW();      -- 2023-10-01 15:30:45
  SELECT CURDATE();  -- 2023-10-01
SELECT CURTIME();  -- 15:30:45

2. 提取日期部分

YEAR(date) / MONTH(date) / DAY(date)：提取年 / 月 / 日。
HOUR(time) / MINUTE(time) / SECOND(time)：提取时 / 分 / 秒。

示例：

SELECT YEAR('2020-10-10');  -- 结果：2020
  SELECT MONTH('2020-10-10'); -- 结果：10

3. `DATE_ADD(date, INTERVAL expr unit)`：增减时间

作用：为 date 增加或减少指定时间（expr 为负数时减少）。
常用单位：DAY（天）、MONTH（月）、YEAR（年）、HOUR（时）等。

示例：

SELECT DATE_ADD('2023-10-01', INTERVAL 5 DAY);  -- 加5天：2023-10-06
  SELECT DATE_ADD('2023-10-01', INTERVAL -5 YEAR); -- 减5年：2018-10-01

4. 时间戳转换

UNIX_TIMESTAMP(date)：将日期转为 Unix 时间戳（秒级）。
FROM_UNIXTIME(ts)：将 Unix 时间戳转为日期。

示例：

SELECT UNIX_TIMESTAMP(NOW())*1000;  -- 毫秒级时间戳
  SELECT FROM_UNIXTIME(1696133445);  -- 从秒级时间戳转日期：2023-10-01 15:30:45

5. 日期格式转换

STR_TO_DATE(str, format)：将字符串按 format 转为日期。
DATE_FORMAT(date, format)：将日期按 format 转为字符串。
常用格式符：%Y（4 位年）、%m（2 位月）、%d（2 位日）、%H（24 小时制）等。

示例：

-- 字符串转日期
  SELECT STR_TO_DATE('2020年10月12', '%Y年%m月%d');  -- 2020-10-12

  -- 日期转字符串
SELECT DATE_FORMAT('2020/10/12', '%Y-%m-%d');  -- '2020-10-12'

6. 特殊日期处理

获取当天 0 点：

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d 00:00:00');  -- 2023-10-01 00:00:00

聚合函数：数据统计的核心工具

聚合函数用于对一组数据进行统计计算，通常与 GROUP BY 配合使用（无 GROUP BY 时默认对全表聚合）。

1. `COUNT(expr)`：计数

作用：统计非NULL值的数量，常用三种形式：
- COUNT(*)：统计所有行数（包含 NULL）。
COUNT(字段)：统计该字段非 NULL 的行数。
- COUNT(1)：等价于 COUNT(*)（用 1 代替字段，性能相近）。

示例：

SELECT COUNT(*) AS 总记录数 FROM users;  -- 包含NULL的所有行
  SELECT COUNT(email) AS 有效邮箱数 FROM users;  -- 排除email为NULL的行

2. 其他聚合函数

同 “数学函数” 中的SUM、AVG、MAX、MIN，需注意：
- 聚合函数自动忽略 NULL 值。
与 GROUP BY 联用时，对每个分组单独计算。

加密函数：数据安全相关

用于简单加密（注意：生产环境建议使用更安全的加密方式，如应用层加密）。

1. `MD5(str)`：MD5 加密

作用：返回字符串的 32 位 MD5 哈希值（不可逆）。

示例：

SELECT MD5('123');  -- 结果：'202cb962ac59075b964b07152d234b70'

2. `PASSWORD(str)`：密码加密（已废弃）

注意：MySQL 8.0 已移除该函数，不推荐使用，建议用 MD5 或应用层加密。

流程控制：条件判断与循环

流程控制函数用于查询中的条件判断，或存储过程中的逻辑控制。

1. 分支结构（条件判断）

（1）`IF(expr, v1, v2)`：简单条件判断

作用：expr 为真（非 0 非 NULL）返回 v1，否则返回 v2。

示例：

SELECT IF(6<3, '小于', '大于');  -- 结果：'大于'

（2）`CASE` 函数：多条件判断

形式 1（类似 switch-case）：

CASE 表达式
    WHEN 值1 THEN 结果1
  WHEN 值2 THEN 结果2
    ...
  ELSE 默认结果
  END

示例：

-- 根据 job 分类员工
SELECT name,
       CASE job
         WHEN 'SALES' THEN '销售岗'
         WHEN 'MANAGER' THEN '管理岗'
         ELSE '其他岗'
       END AS 岗位类型
FROM emp;

形式 2（类似多重 if）：

CASE
    WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
    ...
  ELSE 默认结果
  END

示例：

-- 根据薪资分级
SELECT name, sal,
       CASE
         WHEN sal > 10000 THEN '高薪'
         WHEN sal > 5000 THEN '中薪'
         ELSE '底薪'
       END AS 薪资等级
FROM emp;

2. 循环结构（存储过程中使用）

循环用于重复执行一段逻辑，需在存储过程 / 函数中使用，常用三种循环：

（1）`WHILE` 循环

-- 语法
[标签:] WHILE 条件 DO
  循环体;
END WHILE [标签];

-- 示例：计算1到10的和
CREATE PROCEDURE sum_1_to_10()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE total INT DEFAULT 0;
  WHILE i <= 10 DO
    SET total = total + i;
    SET i = i + 1;
  END WHILE;
  SELECT total;
END;

（2）`REPEAT` 循环（先执行后判断）

-- 语法
[标签:] REPEAT
  循环体;
UNTIL 退出条件
END REPEAT [标签];

（3）`LOOP` 循环（无限循环，需手动跳出）

-- 语法
[标签:] LOOP
  循环体;
  IF 退出条件 THEN
    LEAVE 标签;  -- 跳出循环（类似 break）
  END IF;
END LOOP [标签];

3. 循环控制

LEAVE 标签：跳出循环（类似 break）。
ITERATE 标签：跳过当前循环剩余部分，进入下一次循环（类似 continue）

MySQL 函数大全：从基础操作到高级应用

字符函数：处理字符串的核心工具

1. LENGTH(str)：获取字符串字节数

2. CONCAT(str1, str2, ...) 与 CONCAT_WS(sep, str1, ...)：拼接字符串

3. GROUP_CONCAT(expr)：聚合拼接（分组后生成分隔列表）

4. UPPER(str) 与 LOWER(str)：大小写转换

5. SUBSTR(str, pos, len) / SUBSTRING(...)：字符串截取

6. SUBSTRING_INDEX(str, delim, count)：按分隔符拆分

7. INSTR(str, substr)：查找子串位置

8. TRIM(str) / LTRIM(str) / RTRIM(str)：去除空格

9. LPAD(str, len, padstr) / RPAD(str, len, padstr)：填充字符串

10. REPLACE(str, from_str, to_str)：替换子串

11. LEFT(str, len) / RIGHT(str, len)：取左侧 / 右侧字符

12. REVERSE(str)：反转字符串

13. COALESCE(expr1, expr2, ...)：返回第一个非 NULL 值