1. MySQL函数概述MySQL作为最流行的关系型数据库之一提供了丰富的内置函数库这些函数可以显著简化SQL查询的编写提高数据处理效率。函数本质上是一组预定义的SQL指令集合能够接收参数、执行特定操作并返回结果。在实际开发中合理使用这些函数可以避免在应用层编写大量重复代码将数据处理逻辑下推到数据库层面执行。MySQL函数主要分为以下几大类字符串函数处理文本数据数值函数数学运算和数值处理日期时间函数日期和时间操作聚合函数对多行数据进行计算控制流函数条件逻辑处理系统信息函数获取数据库和系统信息2. 字符串处理函数2.1 基础字符串操作CONCAT() 是最常用的字符串连接函数SELECT CONCAT(first_name, , last_name) AS full_name FROM employees;SUBSTRING() 用于提取子字符串SELECT SUBSTRING(MySQL Functions, 7, 8); -- 返回 FunctionsLENGTH() 和 CHAR_LENGTH() 的区别SELECT LENGTH(中文), -- 返回字节数(UTF8下通常为6) CHAR_LENGTH(中文); -- 返回字符数(2)2.2 字符串格式化函数TRIM() 函数去除首尾空格SELECT TRIM( MySQL ); -- 返回 MySQLUPPER() 和 LOWER() 转换大小写SELECT UPPER(mysql), LOWER(MySQL);FORMAT() 数字格式化SELECT FORMAT(1234567.89, 2); -- 返回 1,234,567.893. 数值计算函数3.1 基本数学运算ROUND() 四舍五入SELECT ROUND(3.14159, 2); -- 返回 3.14CEIL() 和 FLOOR()SELECT CEIL(3.2), FLOOR(3.8); -- 分别返回4和3RAND() 生成随机数SELECT RAND(); -- 返回0-1之间的随机浮点数3.2 高级数值处理MOD() 取模运算SELECT MOD(10, 3); -- 返回1POW() 幂运算SELECT POW(2, 10); -- 返回1024TRUNCATE() 截断小数SELECT TRUNCATE(3.14159, 3); -- 返回3.1414. 日期时间函数4.1 日期获取与计算NOW() 获取当前日期时间SELECT NOW(); -- 返回 2023-08-20 14:30:45DATE_ADD() 和 DATE_SUB()SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);DATEDIFF() 计算日期差SELECT DATEDIFF(2023-12-31, 2023-01-01); -- 返回3644.2 日期格式化DATE_FORMAT() 自定义格式SELECT DATE_FORMAT(NOW(), %Y年%m月%d日 %H时%i分);DAYNAME() 和 MONTHNAME()SELECT DAYNAME(NOW()), MONTHNAME(NOW());5. 聚合函数5.1 基本聚合操作COUNT() 统计行数SELECT COUNT(*) FROM employees;SUM() 和 AVG()SELECT SUM(salary), AVG(salary) FROM employees;MAX() 和 MIN()SELECT MAX(salary), MIN(salary) FROM employees;5.2 分组聚合GROUP_CONCAT() 合并分组结果SELECT department_id, GROUP_CONCAT(last_name) AS employees FROM employees GROUP BY department_id;6. 条件判断函数6.1 IF函数基本IF() 函数SELECT product_name, IF(stock 0, 有货, 缺货) AS status FROM products;6.2 CASE表达式简单CASESELECT product_name, CASE price_range WHEN LOW THEN 经济型 WHEN MID THEN 标准型 WHEN HIGH THEN 豪华型 END AS type FROM products;搜索式CASESELECT product_name, CASE WHEN price 100 THEN 低价 WHEN price BETWEEN 100 AND 500 THEN 中价 ELSE 高价 END AS price_level FROM products;7. 系统信息函数7.1 数据库信息VERSION() 获取MySQL版本SELECT VERSION();DATABASE() 当前数据库SELECT DATABASE();7.2 连接信息USER() 和 CURRENT_USER()SELECT USER(), CURRENT_USER();CONNECTION_ID() 连接IDSELECT CONNECTION_ID();8. 窗口函数(MySQL 8.0)8.1 排名函数ROW_NUMBER()SELECT employee_id, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;RANK() 和 DENSE_RANK()SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees;8.2 分析函数LEAD() 和 LAG()SELECT order_date, amount, LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount FROM orders;9. JSON函数(MySQL 5.7)9.1 JSON创建与修改JSON_OBJECT()SELECT JSON_OBJECT(name, John, age, 30);JSON_ARRAY()SELECT JSON_ARRAY(1, a, TRUE, NULL);9.2 JSON查询JSON_EXTRACT()SELECT JSON_EXTRACT({name:John,age:30}, $.name);JSON_CONTAINS()SELECT JSON_CONTAINS({a:1,b:2}, 1, $.a);10. 函数使用注意事项10.1 性能考虑函数索引的使用-- 创建函数索引 CREATE INDEX idx_name_lower ON employees(LOWER(last_name));避免在WHERE条件中使用函数-- 不推荐 SELECT * FROM orders WHERE DATE_FORMAT(order_date, %Y-%m) 2023-08; -- 推荐 SELECT * FROM orders WHERE order_date BETWEEN 2023-08-01 AND 2023-08-31;10.2 兼容性问题不同MySQL版本的函数差异MySQL 5.7引入了JSON函数MySQL 8.0引入了窗口函数某些函数在不同版本的行为可能不同10.3 最佳实践为常用函数组合创建存储函数DELIMITER // CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50)) RETURNS VARCHAR(101) DETERMINISTIC BEGIN RETURN CONCAT(first, , last); END // DELIMITER ;使用函数提高SQL可读性SELECT order_id, DATE_FORMAT(order_date, %Y-%m-%d) AS formatted_date, CONCAT($, FORMAT(total_amount, 2)) AS amount FROM orders;注意NULL值处理SELECT IFNULL(NULL, 默认值); -- 返回默认值 SELECT COALESCE(NULL, NULL, 第一个非NULL值); -- 返回第一个非NULL值
1. MySQL函数概述MySQL作为最流行的关系型数据库之一提供了丰富的内置函数库这些函数可以显著简化SQL查询的编写提高数据处理效率。函数本质上是一组预定义的SQL指令集合能够接收参数、执行特定操作并返回结果。在实际开发中合理使用这些函数可以避免在应用层编写大量重复代码将数据处理逻辑下推到数据库层面执行。MySQL函数主要分为以下几大类字符串函数处理文本数据数值函数数学运算和数值处理日期时间函数日期和时间操作聚合函数对多行数据进行计算控制流函数条件逻辑处理系统信息函数获取数据库和系统信息2. 字符串处理函数2.1 基础字符串操作CONCAT() 是最常用的字符串连接函数SELECT CONCAT(first_name, , last_name) AS full_name FROM employees;SUBSTRING() 用于提取子字符串SELECT SUBSTRING(MySQL Functions, 7, 8); -- 返回 FunctionsLENGTH() 和 CHAR_LENGTH() 的区别SELECT LENGTH(中文), -- 返回字节数(UTF8下通常为6) CHAR_LENGTH(中文); -- 返回字符数(2)2.2 字符串格式化函数TRIM() 函数去除首尾空格SELECT TRIM( MySQL ); -- 返回 MySQLUPPER() 和 LOWER() 转换大小写SELECT UPPER(mysql), LOWER(MySQL);FORMAT() 数字格式化SELECT FORMAT(1234567.89, 2); -- 返回 1,234,567.893. 数值计算函数3.1 基本数学运算ROUND() 四舍五入SELECT ROUND(3.14159, 2); -- 返回 3.14CEIL() 和 FLOOR()SELECT CEIL(3.2), FLOOR(3.8); -- 分别返回4和3RAND() 生成随机数SELECT RAND(); -- 返回0-1之间的随机浮点数3.2 高级数值处理MOD() 取模运算SELECT MOD(10, 3); -- 返回1POW() 幂运算SELECT POW(2, 10); -- 返回1024TRUNCATE() 截断小数SELECT TRUNCATE(3.14159, 3); -- 返回3.1414. 日期时间函数4.1 日期获取与计算NOW() 获取当前日期时间SELECT NOW(); -- 返回 2023-08-20 14:30:45DATE_ADD() 和 DATE_SUB()SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);DATEDIFF() 计算日期差SELECT DATEDIFF(2023-12-31, 2023-01-01); -- 返回3644.2 日期格式化DATE_FORMAT() 自定义格式SELECT DATE_FORMAT(NOW(), %Y年%m月%d日 %H时%i分);DAYNAME() 和 MONTHNAME()SELECT DAYNAME(NOW()), MONTHNAME(NOW());5. 聚合函数5.1 基本聚合操作COUNT() 统计行数SELECT COUNT(*) FROM employees;SUM() 和 AVG()SELECT SUM(salary), AVG(salary) FROM employees;MAX() 和 MIN()SELECT MAX(salary), MIN(salary) FROM employees;5.2 分组聚合GROUP_CONCAT() 合并分组结果SELECT department_id, GROUP_CONCAT(last_name) AS employees FROM employees GROUP BY department_id;6. 条件判断函数6.1 IF函数基本IF() 函数SELECT product_name, IF(stock 0, 有货, 缺货) AS status FROM products;6.2 CASE表达式简单CASESELECT product_name, CASE price_range WHEN LOW THEN 经济型 WHEN MID THEN 标准型 WHEN HIGH THEN 豪华型 END AS type FROM products;搜索式CASESELECT product_name, CASE WHEN price 100 THEN 低价 WHEN price BETWEEN 100 AND 500 THEN 中价 ELSE 高价 END AS price_level FROM products;7. 系统信息函数7.1 数据库信息VERSION() 获取MySQL版本SELECT VERSION();DATABASE() 当前数据库SELECT DATABASE();7.2 连接信息USER() 和 CURRENT_USER()SELECT USER(), CURRENT_USER();CONNECTION_ID() 连接IDSELECT CONNECTION_ID();8. 窗口函数(MySQL 8.0)8.1 排名函数ROW_NUMBER()SELECT employee_id, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;RANK() 和 DENSE_RANK()SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees;8.2 分析函数LEAD() 和 LAG()SELECT order_date, amount, LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount FROM orders;9. JSON函数(MySQL 5.7)9.1 JSON创建与修改JSON_OBJECT()SELECT JSON_OBJECT(name, John, age, 30);JSON_ARRAY()SELECT JSON_ARRAY(1, a, TRUE, NULL);9.2 JSON查询JSON_EXTRACT()SELECT JSON_EXTRACT({name:John,age:30}, $.name);JSON_CONTAINS()SELECT JSON_CONTAINS({a:1,b:2}, 1, $.a);10. 函数使用注意事项10.1 性能考虑函数索引的使用-- 创建函数索引 CREATE INDEX idx_name_lower ON employees(LOWER(last_name));避免在WHERE条件中使用函数-- 不推荐 SELECT * FROM orders WHERE DATE_FORMAT(order_date, %Y-%m) 2023-08; -- 推荐 SELECT * FROM orders WHERE order_date BETWEEN 2023-08-01 AND 2023-08-31;10.2 兼容性问题不同MySQL版本的函数差异MySQL 5.7引入了JSON函数MySQL 8.0引入了窗口函数某些函数在不同版本的行为可能不同10.3 最佳实践为常用函数组合创建存储函数DELIMITER // CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50)) RETURNS VARCHAR(101) DETERMINISTIC BEGIN RETURN CONCAT(first, , last); END // DELIMITER ;使用函数提高SQL可读性SELECT order_id, DATE_FORMAT(order_date, %Y-%m-%d) AS formatted_date, CONCAT($, FORMAT(total_amount, 2)) AS amount FROM orders;注意NULL值处理SELECT IFNULL(NULL, 默认值); -- 返回默认值 SELECT COALESCE(NULL, NULL, 第一个非NULL值); -- 返回第一个非NULL值
