MySQL函数大全超详细完整指南：语法说明和使用示例

前言

MySQL 提供了海量的内置函数，熟练运用这些函数可以让我们在数据处理、字符串操作、日期计算、统计分析等场景下事半功倍。本文将系统梳理 MySQL 8.0 中所有常用内置函数，涵盖聚合函数、字符串函数、日期时间函数、窗口函数、数学函数、JSON函数、加密函数、信息函数、类型转换函数等，并为每个函数提供语法说明和使用示例。

环境说明：本文示例基于 MySQL 8.0 版本，MySQL 8.0 引入了窗口函数、JSON_TABLE 等大量新特性，建议使用 8.0 及以上版本以获得完整函数支持。

1. 聚合函数

聚合函数对一组值执行计算，返回单个汇总值，常与 GROUP BY 子句配合使用。

1.1 基础聚合函数

示例 1 — 基础统计：

-- 假设有一张 products 表-- 总产品数SELECT COUNT(*) AS total_products FROM products;​-- 有效库存记录数（stock 列非 NULL）SELECT COUNT(stock) AS stock_count FROM products;​-- 不同类别的数量SELECT COUNT(DISTINCT category) AS category_count FROM products;​-- 总库存SELECT SUM(stock) AS total_stock FROM products;​-- 平均价格SELECT AVG(price) AS avg_price FROM products;​-- 最高价格SELECT MAX(price) AS max_price FROM products;​-- 最低价格SELECT MIN(price) AS min_price FROM products;

1.2 COUNT 详解：COUNT(1)、COUNT(*)、COUNT(字段) 的区别

生产环境中经常混淆 COUNT 的不同用法，它们的区别如下：

• COUNT(1)：统计所有行数，不受字段定义影响。相当于增加一个常量列，统计有多少个 1。

• COUNT(*)：统计所有行数，包括 NULL 行。

• COUNT(字段)：统计指定字段不为 NULL 的行数。

• COUNT(DISTINCT 字段)：统计指定字段去重且不为 NULL 的行数。

-- 示例对比SELECT     COUNT(1)         AS all_rows,       -- 所有行    COUNT(*)         AS all_rows_too,   -- 所有行（同上）    COUNT(email)     AS has_email,      -- 邮件不为空的行    COUNT(DISTINCT department) AS dept_count  -- 部门数量FROM employees;

使用建议：表中存在主键或索引时使用 COUNT(主键字段) 或 COUNT(索引字段)；表中无主键时使用 COUNT(1) 优于 COUNT(*)。

1.3 高级聚合函数

示例 2 — GROUP_CONCAT 行转列：

-- 将每个部门的人员姓名合并SELECT     department,    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS membersFROM employeesGROUP BY department;​-- 结果类似：IT | Alice, Bob, Charlie

示例 3 — JSON 聚合（MySQL 8.0 新增）：

-- 将分组数据聚合为 JSON 数组SELECT department, JSON_ARRAYAGG(name) AS names FROM employees GROUP BY department;​-- 将分组数据聚合为 JSON 对象SELECT department, JSON_OBJECTAGG(emp_id, name) AS emp_map FROM employees GROUP BY department;

2. 字符串函数

2.1 字符串截取与拼接

示例 4：

-- 拼接姓名SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;-- 结果：Alice Smith​-- 用分隔符拼接SELECT CONCAT_WS('-', '2024', '01', '15') AS date_str;-- 结果：2024-01-15​-- 截取子字符串（pos 从 1 开始）SELECT SUBSTRING('Hello World', 1, 5) AS result;-- 结果：Hello​-- 手机号脱敏SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone FROM users;-- 结果：138****1234

2.2 大小写转换与修剪

示例 5：

SELECT UPPER('mysql') AS result;         -- MYSQLSELECT LOWER('MYSQL') AS result;         -- mysqlSELECT TRIM('  hello  ') AS result;      -- helloSELECT TRIM(LEADING '0' FROM '00123') AS result;  -- 123SELECT TRIM(TRAILING 'x' FROM 'abcxxx') AS result; -- abc

2.3 填充与重复

示例 6：

-- 左侧补零（常用于生成工号）SELECT LPAD('123', 8, '0') AS emp_id;-- 结果：00000123​-- 右侧填充SELECT RPAD('MySQL', 10, '*') AS result;-- 结果：MySQL*****​-- 重复SELECT REPEAT('Go', 3) AS result;-- 结果：GoGoGo

2.4 查找与替换

注意：LOCATE() 不区分大小写，与 INSTR() 功能相同但参数顺序相反。

示例 7：

-- 查找位置（不区分大小写）SELECT LOCATE('world', 'Hello World') AS position;  -- 7SELECT INSTR('Hello World', 'world') AS position;    -- 7​-- SQL 标准写法SELECT POSITION('world' IN 'Hello World') AS position; -- 7​-- 替换SELECT REPLACE('Hello World', 'World', 'MySQL') AS result;-- Hello MySQL​-- 指定位置替换SELECT INSERT('Hello World', 7, 5, 'CSDN') AS result;-- Hello CSDN

2.5 长度与字符集

重要区别：LENGTH() 返回字节长度，中文 UTF-8 编码下每个字符占 3 字节；CHAR_LENGTH() 返回字符个数。

示例 8：

SELECT     CHAR_LENGTH('Hello')    AS char_len,   -- 5（字符数）    LENGTH('Hello')         AS byte_len,   -- 5（字节数）    CHAR_LENGTH('你好')      AS cn_char_len,-- 2（字符数）    LENGTH('你好')           AS cn_byte_len; -- 6（UTF-8 每中文字符 3 字节）

2.6 其他字符串函数

3. 日期时间函数

3.1 获取当前时间

示例 9：

SELECT NOW();          -- 2024-01-15 10:30:45SELECT CURDATE();      -- 2024-01-15SELECT CURTIME();      -- 10:30:45SELECT UTC_TIMESTAMP();-- 2024-01-15 02:30:45（UTC 时间）

3.2 日期提取

3.3 日期计算

INTERVAL 常用单位：MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。

示例 10：

-- 加 3 天SELECT DATE_ADD('2024-01-15', INTERVAL 3 DAY) AS result;-- 2024-01-18​-- 减 1 个月SELECT DATE_SUB('2024-01-15', INTERVAL 1 MONTH) AS result;-- 2023-12-15​-- 日期差SELECT DATEDIFF('2024-03-01', '2024-02-15') AS days_diff;-- 15​-- 时间差SELECT TIMESTAMPDIFF(HOUR, '2024-01-15 08:00:00', '2024-01-15 17:30:00') AS hours;-- 9​-- 计算年龄SELECT TIMESTAMPDIFF(YEAR, '1990-05-20', CURDATE()) AS age;

3.4 日期格式化

常用格式化符号：

示例 11：

-- 格式化SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS formatted;-- 2024年01月15日 10:30:45​-- 字符串转日期SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d') AS date_val;-- 2024-01-15​-- 时间格式化SELECT TIME_FORMAT('10:30:45', '%H时%i分%s秒') AS time_str;-- 10时30分45秒

3.5 其他日期函数

4. 窗口函数

窗口函数是 MySQL 8.0 引入的重要特性，作用类似于分组聚合，但不会将分组结果聚合成一条记录，而是将结果保留在每一条数据中。

4.1 序号函数

示例 12：

-- 假设有 scores 表：student, subject, scoreSELECT     student, subject, score,    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,    RANK()       OVER (ORDER BY score DESC) AS rank_val,    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_valFROM scores;​-- 结果对比（分数相同时）：-- student | score | row_num | rank_val | dense_rank_val-- Alice   | 95    | 1       | 1        | 1-- Bob     | 95    | 2       | 1        | 1-- Charlie | 90    | 3       | 3        | 2

从 MySQL 8.0.2 开始，窗口函数不再需要显式的 FROM DUAL，可直接在普通查询中使用。

4.2 偏移函数

示例 13：

-- LAG/LEAD 计算环比SELECT     order_date,    daily_amount,    LAG(daily_amount, 1) OVER (ORDER BY order_date) AS prev_day_amount,    daily_amount - LAG(daily_amount, 1) OVER (ORDER BY order_date) AS diffFROM daily_sales;

4.3 分布函数

示例 14：

SELECT     student, score,    NTILE(4) OVER (ORDER BY score DESC) AS quartile,    PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rankFROM scores;

4.4 窗口函数语法说明

function_name([expr]) OVER (    [PARTITION BY expr, ...]    -- 分区    [ORDER BY expr [ASC|DESC]]  -- 排序    [frame_clause]              -- 窗口帧范围)

常见 frame_clause：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- 默认ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING            -- 前后各一行ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING    -- 当前行到末尾RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW -- 时间范围

5. 数学函数

5.1 基本运算

5.2 取整与舍入

ROUND 的 d 为负数时：

SELECT ROUND(88.234, -1);   -- 90SELECT ROUND(88.234, -2);   -- 100

5.3 随机数与三角函数

生成指定范围随机数：

-- [a, b) 之间的随机数：RAND() * (b - a) + a-- 生成 [5, 10) 的随机数SELECT RAND() * 5 + 5;​-- 生成 [1, 100] 的随机整数SELECT FLOOR(RAND() * 100) + 1;

5.4 对数与指数

6. 流程控制函数

示例 15：

-- IF 简单判断SELECT name, IF(score >= 60, '通过', '未通过') AS result FROM exams;​-- IFNULL 处理 NULLSELECT IFNULL(avatar_url, '/images/default.png') AS avatar FROM users;​-- NULLIF 避免除零错误SELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;​-- COALESCE 优先取值SELECT COALESCE(mobile, phone, email, '无联系方式') AS contact FROM users;​-- CASE 多条件判断SELECT name, score,    CASE         WHEN score >= 90 THEN '优秀'        WHEN score >= 80 THEN '良好'        WHEN score >= 60 THEN '及格'        ELSE '不及格'    END AS gradeFROM students;

7. JSON 函数

MySQL 8.0 提供了强大的 JSON 处理能力。

7.1 创建 JSON

7.2 查询 JSON

示例 16：

-- 假设 data 列存储：{"name":"Alice","address":{"city":"Beijing"}}​-- 提取字段SELECT data->'$.name' AS name FROM users;         -- "Alice"（带引号）SELECT data->>'$.name' AS name FROM users;        -- Alice（文本）​-- 嵌套取值SELECT data->>'$.address.city' AS city FROM users; -- Beijing​-- 提取数组元素SELECT JSON_EXTRACT('[1, 2, 3]', '$[0]');         -- 1

7.3 修改 JSON

7.4 JSON 工具函数

示例 17 — JSON_TABLE：

-- 将 JSON 数组转为表格数据SELECT * FROM JSON_TABLE(    '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]',    '$[*]' COLUMNS (        id INT PATH '$.id',        name VARCHAR(50) PATH '$.name'    )) AS jt;​-- 结果：-- +------+-------+-- | id   | name  |-- +------+-------+-- | 1    | Alice |-- | 2    | Bob   |-- +------+-------+

8. 加密与压缩函数

8.1 哈希函数

8.2 加密解密

注意：MySQL 8.0 已弃用 PASSWORD()、ENCRYPT()、DES_ENCRYPT()、DES_DECRYPT() 等函数。

9. 信息函数

信息函数用于获取 MySQL 系统和会话相关信息。

10. 类型转换函数

支持的转换类型：BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED, DOUBLE, FLOAT, JSON。

示例 18：

-- 字符串转整数SELECT CAST('123' AS SIGNED) + 1;          -- 124​-- 整数转字符串SELECT CONCAT('编号：', CAST(123 AS CHAR)); -- 编号：123​-- 字符串转日期SELECT CAST('2024-01-15' AS DATE);          -- 2024-01-15​-- 转为 JSONSELECT CAST('{"id":1}' AS JSON);​-- 大小写敏感比较SELECT 'ABC' = 'abc';                        -- 1（不区分大小写）SELECT BINARY 'ABC' = 'abc';                 -- 0（区分大小写）

11. 其他函数

11.1 比较与条件函数

11.2 进制转换

11.3 锁函数

12. 常见问题解答

Q1：COUNT(1) 和 COUNT(*) 到底有什么区别？

在 MySQL 8.0 的 InnoDB 引擎中，COUNT(*) 和 COUNT(1) 在性能上几乎没有区别，优化器会将它们做同样的优化处理。不同点在于：

• COUNT(字段) 会忽略 NULL 值

• COUNT(*) 和 COUNT(1) 统计所有行，包括 NULL 行

Q2：LENGTH() 和 CHAR_LENGTH() 的区别？

• LENGTH(str)：返回字节长度，UTF-8 中文字符占 3 字节

• CHAR_LENGTH(str)：返回字符个数，每个字符算 1 个

Q3：NOW() 和 SYSDATE() 的区别？

NOW() 在语句执行开始时取值，同一事务中多次调用返回相同值；SYSDATE() 在每次调用时实时取值，多次调用可能返回不同时间。

Q4：RANK() 和 DENSE_RANK() 的区别？

当存在相同值时，RANK() 会跳过排名（1, 1, 3），DENSE_RANK() 不会跳过（1, 1, 2）。

Q5：MySQL 8.0 中哪些函数已被弃用？

MySQL 8.0 中已弃用或移除的函数包括：PASSWORD()、ENCRYPT()、DES_ENCRYPT()、DES_DECRYPT()、OLD_PASSWORD() 等，请使用 MD5()、SHA2()、AES_ENCRYPT() 等替代。

Q6：如何避免除零错误？

使用 NULLIF 函数：

-- 如果 quantity 为 0，NULLIF 返回 NULL，除 NULL 结果为 NULLSELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;

总结

本文系统梳理了 MySQL 8.0 的常用内置函数，涵盖以下十大类别：

建议将本文收藏，在实际开发中查阅参考。完整函数列表可查阅 MySQL 8.0 官方参考手册。

温馨提示：熟练掌握常用函数可以让你在写 SQL 时得心应手，但也要注意函数对性能的影响。在 WHERE 子句中对列使用函数（如 WHERE YEAR(created_at) = 2024）可能导致索引失效，建议在条件中直接使用范围比较（如 WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'）。