【MySQL基础】MySQL内置函数全面解析：提升你的数据库操作效率

MySQL学习：

https://blog.csdn.net/2301_80220607/category_12971838.html?spm=1001.2014.3001.5482

前言：
MySQL提供了丰富的内置函数来帮助开发者高效地处理数据。这些函数覆盖了字符串操作、数值计算、日期时间处理、流程控制等多个方面。本文将全面介绍MySQL内置函数的分类、用法和实际应用场景，帮助你更好地利用这些工具优化数据库操作。

目录

一、MySQL函数概述

二、字符串函数

1. 常用字符串函数

2. 字符串搜索与替换

三、数值函数

1. 基本数学函数

2. 高级数学运算

四、日期和时间函数

1. 获取当前日期时间

2. 日期时间计算

五、流程控制函数

1. 条件判断

2. 空值处理

六、聚合函数

1. 常用聚合函数

2. 分组聚合

七、系统信息函数

1. 数据库信息

2. 连接信息

八、加密函数

1. 哈希函数

2. 加密解密

九、JSON函数（MySQL 5.7+）

1. JSON创建与查询

2. JSON修改

十、函数使用最佳实践

结语

---

一、MySQL函数概述

MySQL内置函数是数据库系统预定义的一系列功能模块，可以直接在SQL语句中调用，用于处理和转换数据。使用这些函数可以：

1. 简化复杂的数据操作

2. 减少应用程序代码量

3. 提高查询效率

4. 保证数据处理的一致性

MySQL函数主要分为以下几大类：

• 字符串函数

• 数值函数

• 日期和时间函数

• 流程控制函数

• 系统信息函数

• 聚合函数

• 加密函数

二、字符串函数

字符串函数用于处理和操作文本数据，是日常开发中最常用的函数类别。

1. 常用字符串函数

CONCAT() - 连接字符串

SELECT CONCAT('Hello', ' ', 'World'); -- 输出：Hello World

SUBSTRING()/SUBSTR() - 提取子字符串

SELECT SUBSTRING('MySQL Functions', 1, 5); -- 输出：MySQL

LENGTH()/CHAR_LENGTH() - 获取字符串长度

SELECT LENGTH('数据库'); -- 返回字节数（UTF-8中可能是9）
SELECT CHAR_LENGTH('数据库'); -- 返回字符数：3

TRIM() - 去除空格

SELECT TRIM('  MySQL  '); -- 输出：MySQL
SELECT LTRIM('  MySQL'); -- 仅去除左边空格
SELECT RTRIM('MySQL  '); -- 仅去除右边空格

UPPER()/LOWER() - 大小写转换

SELECT UPPER('mysql'); -- 输出：MYSQL
SELECT LOWER('MySQL'); -- 输出：mysql

2. 字符串搜索与替换

LOCATE() - 查找子字符串位置

SELECT LOCATE('SQL', 'MySQL SQL'); -- 返回3

REPLACE() - 替换字符串

SELECT REPLACE('MySQL is great', 'great', 'awesome');
-- 输出：MySQL is awesome

这个替换函数会将原字符串中所有符合目标的子字符串都进行替换

REGEXP - 正则表达式匹配

SELECT 'MySQL' REGEXP '^My'; -- 返回1（true）

三、数值函数

数值函数用于执行数学运算和数值处理。

1. 基本数学函数

ROUND() - 四舍五入

SELECT ROUND(3.14159, 2); -- 输出：3.14

CEIL()/FLOOR() - 向上/向下取整

SELECT CEIL(3.14); -- 输出：4
SELECT FLOOR(3.99); -- 输出：3

ABS() - 绝对值

SELECT ABS(-10); -- 输出：10

RAND() - 随机数

SELECT RAND(); -- 返回0-1之间的随机浮点数

2. 高级数学运算

POWER() - 幂运算

SELECT POWER(2, 3); -- 输出：8

SQRT() - 平方根

SELECT SQRT(16); -- 输出：4

MOD() - 取模

SELECT MOD(10, 3); -- 输出：1

四、日期和时间函数

日期时间函数用于处理日期和时间值，是业务系统中不可或缺的部分。

1. 获取当前日期时间

NOW() - 当前日期和时间

SELECT NOW(); -- 比如输出：2023-08-20 14:30:45

CURDATE() - 当前日期

SELECT CURDATE(); -- 比如输出：2023-08-20

CURTIME() - 当前时间

SELECT CURTIME(); -- 比如输出：14:30:45

2. 日期时间计算

DATE_ADD()/DATE_SUB() - 日期加减

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加1天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 减1个月

DATEDIFF() - 计算日期差

SELECT DATEDIFF('2023-08-25', '2023-08-20'); -- 输出：5

DATE_FORMAT() - 格式化日期

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');
-- 比如输出：2023年08月20日 14:30:45

五、流程控制函数

流程控制函数允许在SQL中实现条件逻辑。

1. 条件判断

IF() - 简单条件判断

SELECT IF(1 > 0, 'True', 'False'); -- 输出：True

CASE WHEN - 多条件判断

SELECT CASE WHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'ELSE 'C'END AS grade
FROM students;

2. 空值处理

IFNULL() - 处理NULL值

SELECT IFNULL(NULL, 'Default Value'); -- 输出：Default Value

COALESCE() - 返回第一个非NULL值

SELECT COALESCE(NULL, NULL, 'Third', 'Fourth'); -- 输出：Third

六、聚合函数

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

聚合函数在上一篇讲过，这里就将主要的函数再简单的讲解一遍

1. 常用聚合函数

COUNT() - 计数

SELECT COUNT(*) FROM users; -- 返回总行数

SUM() - 求和

SELECT SUM(salary) FROM employees;

AVG() - 平均值

SELECT AVG(score) FROM exams;

MAX()/MIN() - 最大/最小值

SELECT MAX(price), MIN(price) FROM products;

2. 分组聚合

GROUP_CONCAT() - 将分组值连接成字符串

SELECT department, GROUP_CONCAT(name) 
FROM employees 
GROUP BY department;

七、系统信息函数

系统信息函数提供数据库和服务器相关信息。

1. 数据库信息

DATABASE() - 当前数据库名

SELECT DATABASE();

USER()/CURRENT_USER() - 当前用户

SELECT USER();

2. 连接信息

CONNECTION_ID() - 连接ID

SELECT CONNECTION_ID();

LAST_INSERT_ID() - 最后插入的ID

SELECT LAST_INSERT_ID();

八、加密函数

加密函数用于数据安全处理。

1. 哈希函数

MD5() - MD5哈希

SELECT MD5('password');

SHA1()/SHA2() - SHA哈希

SELECT SHA1('password');
SELECT SHA2('password', 256); -- 256位SHA2

2. 加密解密

AES_ENCRYPT()/AES_DECRYPT() - AES加密解密

SELECT AES_ENCRYPT('secret', 'key');
SELECT AES_DECRYPT(encrypted_data, 'key');

九、JSON函数（MySQL 5.7+）

MySQL 5.7及以上版本支持JSON数据类型和相关函数。

1. JSON创建与查询

JSON_OBJECT() - 创建JSON对象

SELECT JSON_OBJECT('name', 'John', 'age', 30);

JSON_EXTRACT() - 提取JSON值

SELECT JSON_EXTRACT('{"name":"John"}', '$.name');

2. JSON修改

JSON_SET() - 设置JSON值

SELECT JSON_SET('{"name":"John"}', '$.age', 30);

JSON_REMOVE() - 删除JSON属性

SELECT JSON_REMOVE('{"name":"John","age":30}', '$.age');

十、函数使用最佳实践

1. 索引与函数：在WHERE条件中对列使用函数会导致索引失效

   -- 不好：无法使用name上的索引
   SELECT * FROM users WHERE UPPER(name) = 'JOHN';-- 更好：使用函数索引或存储计算值

2. 函数嵌套：避免过度嵌套函数，影响可读性和性能

   -- 不推荐
   SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%Y-%m-%d');-- 更清晰
   SELECT DATE_FORMAT(tomorrow, '%Y-%m-%d') 
   FROM (SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow) t;

3. 考虑性能：某些函数（如正则表达式）可能性能较差，大数据量时需谨慎使用

4. 兼容性：注意不同MySQL版本的函数差异

结语

MySQL内置函数是数据库操作中的强大工具，合理使用可以显著提高开发效率和查询性能。本文涵盖了大多数常用函数，但在实际应用中，还需要根据具体业务场景选择最合适的函数组合。在掌握这些基础知识后，可以进一步研究窗口函数（MySQL 8.0+）等高级特性，以应对更复杂的数据处理需求。

---

感谢各位大佬观看，创作不易，还望各位大佬点赞支持！！！