【MySQL 系列】MySQL 函数篇

MySQL 提供了广泛的函数，用于处理各种数据类型，包括数值、字符串、日期和时间等。这些函数可以帮助你进行复杂的数据操作，改善数据查询的效率和灵活性。

1、MySQL 函数介绍

1.1、MySQL 函数简介

函数是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在 MySQL 中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。

MySQL 提供了广泛的函数，用于处理各种数据类型，包括数值、字符串、日期和时间等。这些函数可以帮助你进行复杂的数据操作，改善数据查询的效率和灵活性。

2.1、MySQL 函数分类

MySQL 函数按功能分类主要可以分为以下几种类型：

1. 数学函数：用于执行数学计算，如加法、减法、乘法、除法、取余、四舍五入、取绝对值等。常见的数学函数包括 ABS(), CEIL(), FLOOR(), RAND(), ROUND() 等；
2. 字符串函数：用于对字符串类型的数据进行操作，如连接、替换、截取、转换大小写等。常见的字符串函数包括 CONCAT(), FORMAT(), LOWER(), UPPER(), LENGTH(), SUBSTRING(), TRIM() 等；
3. 日期和时间函数：用于处理日期和时间值的函数，如获取当前日期、时间、日期加减、提取日期部分等。常见的日期和时间函数包括 NOW(), CURDATE(), CURTIME(), DATE_ADD(), DATE_SUB(), DAY(), MONTH(), YEAR() 等；
4. 聚合函数：用于执行计算并返回单个值的函数，通常用在 SELECT 语句的 GROUP BY 子句中。常见的聚合函数包括 AVG(), COUNT(), SUM(), MIN(), MAX(), 等；
5. 逻辑函数：用于执行逻辑操作的函数，如判断条件、选择不同的值等。常见的逻辑函数包括 IF(), CASE，AND, OR, NOT 等；
6. 控制流函数：用于控制逻辑流程的函数，如根据条件选择不同的结果输出。常见的控制流函数包括 IF(), CASE, IFNULL(), NULLIF() 等；
7. 比较函数和运算符：用于比较数据的函数和运算符，包括等于、不等于、大于、小于等。这些不仅仅是函数，还包括了一些基本的运算符；
8. 加密和压缩函数：用于数据加密和压缩的函数，如对数据进行加密、解密、压缩或解压缩。常见的函数包括 MD5(), SHA1(), AES_ENCRYPT(), AES_DECRYPT() 等；
9. 信息函数：提供关于MySQL服务器信息的函数，如获取当前用户、版本信息等。常见的信息函数包括 DATABASE(), USER(), VERSION() 等；
10. JSON函数：用于处理JSON数据类型的函数，包括创建JSON对象、提取值、修改和搜索JSON数据等。常见的JSON函数包括 JSON_OBJECT(), JSON_ARRAY(), JSON_EXTRACT(), JSON_SET() 等。

每种类型的函数都有其特定的用途和应用场景，根据需要选择适合的函数进行数据处理。

2、MySQL 数学函数

MySQL 提供了多种数学函数，可以帮助用户执行各种数学运算，包括基本的算术运算、三角函数、对数运算等。下面是根据功能对 MySQL 中的数学函数进行的分类和示例：

2.1、基本算术运算

• +：加法运算；
• -：减法运算；
• *：乘法运算；
• /：除法运算；
• % 或 MOD：求余运算。

示例：

SELECT 10 + 2;  -- 结果为 12
SELECT 10 - 2;  -- 结果为 8
SELECT 10 * 2;  -- 结果为 20
SELECT 10 / 2;  -- 结果为 5
SELECT 10 % 4;  -- 结果为 2

2.2、四舍五入和截断

• ROUND(x, d)：将 x 四舍五入到小数点后 d 位；
• TRUNCATE(x, d)：将 x 截断到小数点后 d 位，不进行四舍五入。

示例：

SELECT ROUND(123.4567, 2);  		-- 结果为 123.46
SELECT TRUNCATE(123.4567, 2);  	-- 结果为 123.45

2.3、绝对值和符号函数

• ABS(x)：返回 x 的绝对值；
• SIGN(x)：返回 x 的符号，正数返回 1，负数返回 -1，0 返回 0。

示例：

SELECT ABS(-123.456);  					-- 结果为 123.456
SELECT SIGN(-123);  						-- 结果为 -1

2.4、幂和指数函数

• POW(x, y) 或 POWER(x, y)：返回 x 的 y 次幂。
• SQRT(x)：返回 x 的平方根。
• EXP(x)：返回 e 的 x 次方。

示例：

SELECT POW(2, 3);  							-- 结果为 8
SELECT SQRT(16);  							-- 结果为 4
SELECT EXP(1);  								-- 结果为 e（约等于2.71828）

2.5、对数函数

• LOG(x)：返回 x 的自然对数。
• LOG10(x)：返回 x 的以 10 为底的对数。
• LOG2(x)：返回 x 的以 2 为底的对数。

示例：

SELECT LOG(2.71828);  					-- 结果接近于 1
SELECT LOG10(100);  						-- 结果为 2
SELECT LOG2(8);  								-- 结果为 3

2.6、三角函数

• SIN(x)：返回 x（弧度）的正弦值。
• COS(x)：返回 x（弧度）的余弦值。
• TAN(x)：返回 x（弧度）的正切值。
• ASIN(x)：返回 x 的反正弦值。
• ACOS(x)：返回 x 的反余弦值。
• ATAN(x)：返回 x 的反正切值。

示例：

SELECT SIN(PI() / 2);  				-- 结果为 1
SELECT COS(0);  							-- 结果为 1
SELECT TAN(PI() / 4);  				-- 结果接近于 1

3、MySQL 字符串函数

MySQL 提供了丰富的字符串函数，这些函数允许你对数据库中存储的字符串数据进行各种操作，如比较、连接、搜索、替换、格式化等。以下是根据功能对 MySQL 中的字符串函数进行的分类和示例：

3.1、字符串连接

• CONCAT(str1, str2, ...)：连接两个或多个字符串；
• CONCAT_WS(separator, str1, str2, ...)：使用指定的分隔符来连接两个或多个字符串。

示例：

SELECT CONCAT('Hello', ' ', 'World');  			-- 结果为 'Hello World'
SELECT CONCAT_WS('-', '2024', '03', '13');  -- 结果为 '2024-03-13'

3.2、字符串长度

• LENGTH(str)：返回字符串的字节长度；
• CHAR_LENGTH(str)：返回字符串的字符长度。

示例：

SELECT LENGTH('text');  -- 字节长度
SELECT CHAR_LENGTH('text');  -- 字符长度

3.3、子串提取

• SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)：从字符串 str 中提取一个长度为 len 的子串，开始位置为 pos。

示例：

SELECT SUBSTRING('Hello World', 2, 5);  -- 结果为 'ello '

3.4、替换和移除

• REPLACE(str, from_str, to_str)：在字符串 str 中，替换所有出现的 from_str 为 to_str；
• TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)：移除字符串两端的特定字符或空格。

示例：

SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 结果为 'Hello MySQL'
SELECT TRIM('  Hello World  ');  -- 结果为 'Hello World'

3.5、字符串格式化

• LOWER(str) 或 LCASE(str)：将字符串转换为小写。
• UPPER(str) 或 UCASE(str)：将字符串转换为大写。
• LPAD(str, len, padstr)：返回字符串 str，左侧用字符串 padstr 填充到长度 len。
• RPAD(str, len, padstr)：返回字符串 str，右侧用字符串 padstr 填充到长度 len。

示例：

SELECT LOWER('MySQL');  -- 结果为 'mysql'
SELECT UPPER('mysql');  -- 结果为 'MYSQL'
SELECT LPAD('MySQL', 10, '*');  -- 结果为 '*****MySQL'
SELECT RPAD('MySQL', 10, '*');  -- 结果为 'MySQL*****'

3.6、搜索和定位

• LOCATE(substr, str[, pos])：返回子串 substr 在字符串 str 中出现的位置，从位置 pos 开始搜索。
• INSTR(str, substr)：返回子串 substr 在字符串 str 中出现的位置。

示例：

SELECT LOCATE('World', 'Hello World');  -- 结果为 7
SELECT INSTR('Hello World', 'World');  -- 结果为 7

3.7、比较函数

• STRCMP(str1, str2)：比较两个字符串，返回值为0（字符串相等），-1（str1 < str2），或1（str1 > str2）。

示例：

SELECT STRCMP('text', 'text');  -- 结果为 0

MySQL 的字符串函数为数据处理提供了强大而灵活的工具，能够满足大多数文本处理的需求。

4、MySQL 日期和时间函数

在 MySQL 中，日期和时间函数允许你操作和格式化存储在数据库中的日期和时间值。这些函数能够帮助你提取日期和时间的特定部分、计算日期和时间差、格式化日期和时间输出等。下面是根据功能对 MySQL 中的日期和时间函数进行的分类和示例：

4.1、提取日期和时间部分

• YEAR(date)：提取年份；
• MONTH(date)：提取月份；
• DAY(date) 或 DAYOFMONTH(date)：提取月份中的天数；
• WEEK(date)：提取一年中的周数；
• DAYOFWEEK(date)：提取一周中的天数（1 = 周日，2 = 周一，…，7 = 周六）；
• HOUR(time)：提取小时；
• MINUTE(time)：提取分钟；
• SECOND(time)：提取秒；

示例：

SELECT YEAR('2024-03-13'), MONTH('2024-03-13'), DAY('2024-03-13');
-- 结果为 2024, 3, 13

4.2、日期和时间的加减

• DATE_ADD(date, INTERVAL expr type) 或 ADDDATE(date, INTERVAL expr type)：日期时间加上一个时间间隔；
• DATE_SUB(date, INTERVAL expr type) 或 SUBDATE(date, INTERVAL expr type)：日期时间减去一个时间间隔。

其中 type 可以是 DAY、MONTH、YEAR、HOUR、MINUTE、SECOND 等。

示例：

SELECT DATE_ADD('2024-03-13', INTERVAL 1 DAY);
-- 结果为 '2024-03-14'
SELECT SUBDATE('2024-03-13', INTERVAL 1 MONTH);
-- 结果为 '2024-02-13'

4.3、日期和时间的比较

• DATEDIFF(date1, date2)：返回两个日期之间的天数差；
• TIMEDIFF(time1, time2)：返回两个时间之间的差。

示例：

SELECT DATEDIFF('2024-03-15', '2024-03-13');
-- 结果为 2
SELECT TIMEDIFF('18:00:00', '16:00:00');
-- 结果为 '02:00:00'

4.4、当前日期和时间

• NOW()：返回当前的日期和时间；
• CURDATE() 或 CURRENT_DATE()：返回当前的日期；
• CURTIME() 或 CURRENT_TIME()：返回当前的时间；
• UNIX_TIMESTAMP()：返回当前的 Unix 时间戳。

示例：

SELECT NOW(), CURDATE(), CURTIME();
-- 返回当前的日期和时间、当前日期、当前时间

4.5、格式化日期和时间

• DATE_FORMAT(date, format)：按照给定的格式字符串格式化日期值；
• TIME_FORMAT(time, format)：按照给定的格式字符串格式化时间值。

format 字符串中可以使用的格式化符号非常丰富，例如 %Y 表示四位年份，%m 表示两位数的月份，%d 表示两位数的日等。

示例：

SELECT DATE_FORMAT('2024-03-13', '%Y年%m月%d日');
-- 结果为 '2024年03月13日'
SELECT TIME_FORMAT('18:30:00', '%H时%i分%s秒');
-- 结果为 '18时30分00秒'

5、MySQL 聚合函数

在 MySQL 中，聚合函数是用于对一组值执行计算，并返回单个值的函数。它们通常在 SELECT 语句的 GROUP BY 子句中使用，用于对数据集合进行汇总统计。下面是 MySQL 中常用的聚合函数及其分类和示例：

5.1、基本聚合函数

• COUNT()：计算行数。COUNT(*)：计算选定的行数；COUNT(column)：计算指定列中非 NULL 值的数量；
• SUM()：计算数值列的总和；
• AVG()：计算数值列的平均值；
• MIN() 和 MAX()：分别找出列中的最小值和最大值。

示例：

SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;

5.2、高级聚合函数

GROUP_CONCAT()：将列值连接成一个字符串，可以使用分隔符。

示例：

SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM employees WHERE department_id = 1;

这些聚合函数非常强大，可以帮助你对数据进行快速的汇总分析，提取关键的统计信息。在使用聚合函数时，通常会与 GROUP BY 子句结合使用，以便对数据进行分组统计。例如，你可以使用 GROUP BY 来计算每个部门的平均工资，如下所示：

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

这条查询会返回每个部门的平均工资，展示了聚合函数在数据分析中的实际应用。

6、MySQL 逻辑与控制流函数

MySQL 的逻辑与控制流函数是编写灵活和强大的 SQL 查询的重要工具。它们允许你基于特定条件进行决策、控制数据的输出，以及处理可能的数据变异。以下是 MySQL 中逻辑与控制流函数的概览和分类，以及一些示例：

6.1、逻辑函数

逻辑函数用于进行布尔逻辑判断，它们通常返回真（TRUE）或假（FALSE）。

• AND, &&：逻辑与操作。当所有条件同时满足时返回 TRUE；
• OR, ||：逻辑或操作。当至少一个条件满足时返回 TRUE；
• NOT, !：逻辑非操作。取反操作，将 TRUE 转为 FALSE，将 FALSE 转为 TRUE。

示例：

SELECT * FROM orders WHERE shipped = TRUE AND order_date >= '2023-01-01';

6.2、控制流函数

控制流函数允许根据条件来改变查询的逻辑流程，从而实现更复杂的数据处理逻辑。

• IF(expr, then_expr, else_expr)：如果条件 expr 为真，则返回 then_expr，否则返回 else_expr。

示例：

SELECT IF(score >= 60, 'Pass', 'Fail') AS result FROM grades;

• CASE：根据条件选择不同的输出。有两种形式：简单 CASE 和搜索 CASE。

简单 CASE 示例：

SELECT CASE grade
       WHEN 'A' THEN 'Excellent'
       WHEN 'B' THEN 'Good'
       WHEN 'C' THEN 'Average'
       ELSE 'Needs Improvement'
       END AS evaluation
FROM student_grades;

搜索 CASE 示例：

SELECT CASE
       WHEN score >= 90 THEN 'Excellent'
       WHEN score >= 80 THEN 'Good'
       WHEN score >= 60 THEN 'Average'
       ELSE 'Needs Improvement'
       END AS evaluation
FROM student_grades;

• IFNULL(expr1, expr2)：如果expr1不是 NULL，返回expr1；否则，返回expr2。

示例：

SELECT IFNULL(email, 'No email provided') AS email FROM users;

• NULLIF(expr1, expr2)：如果expr1等于expr2，返回 NULL；否则，返回expr1。

示例：

SELECT NULLIF('A', 'B') AS test; -- 返回 'A'
SELECT NULLIF('A', 'A') AS test; -- 返回 NULL

• COALESCE(expr1, expr2, ..., exprN)：返回参数列表中第一个非 NULL 的值。

示例：

SELECT COALESCE(NULL, NULL, 'first non-null value', 'second non-null value') AS result;

这些逻辑与控制流函数大大增强了 SQL 的表达能力，使得数据处理和查询能够根据不同的条件和场景灵活变化。

7、比较函数和运算符

MySQL 中的比较函数和运算符用于比较两个或多个表达式的值，返回比较的结果。这些功能对于数据过滤、条件匹配和值评估至关重要。以下是一些主要的比较函数和运算符，以及它们的用例：

7.1、比较运算符

• =：等于。判断两个值是否相等。
• <> 或 !=：不等于。判断两个值是否不相等。
• >：大于。判断左侧值是否大于右侧值。
• <：小于。判断左侧值是否小于右侧值。
• >=：大于等于。判断左侧值是否大于或等于右侧值。
• <=：小于等于。判断左侧值是否小于或等于右侧值。

示例：

SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM orders WHERE order_date <= '2023-12-31';

7.2、比较函数

• COALESCE()：返回参数列表中第一个非NULL的值。虽然不是直接用于比较，但它在处理可能为NULL的表达式时非常有用。

示例：

SELECT COALESCE(salary, bonus, 0) FROM employees;

• GREATEST() 和 LEAST()：分别返回所有参数中的最大值和最小值。

示例：

SELECT GREATEST(1, 2, 3), LEAST(1, 2, 3);							-- 结果: 3 和 1

8、加密和压缩函数

MySQL 提供了一系列的加密和压缩函数，允许对数据进行安全处理和空间优化。这些函数在保护数据安全、数据传输以及优化存储空间方面非常有用。以下是MySQL中常见的加密和压缩函数：

8.1、加密函数

• MD5(str)：计算字符串str的 MD5 128 位哈希值。通常用于密码或敏感信息的一种单向加密。

示例：

SELECT MD5('your_password');

• SHA1(str) 或 SHA(str)：计算字符串str的 SHA-1 160 位哈希值。比 MD5 更安全的一种单向加密方式。

示例：

SELECT SHA1('your_password');

• SHA2(str, hash_length)：计算字符串str的 SHA-2 哈希值，hash_length 可以是 224、256、384 或 512。

示例：

SELECT SHA2('your_password', 256);

• AES_ENCRYPT(str, key_str) 和 AES_DECRYPT(crypt_str, key_str)：使用AES (Advanced Encryption Standard) 方法对数据进行加密和解密。str 是要加密的字符串，crypt_str 是要解密的数据，而 key_str 是加密和解密的密钥。

示例：

SELECT AES_ENCRYPT('your_data', 'your_key_string');
SELECT AES_DECRYPT(crypt_data, 'your_key_string');

8.2、压缩函数

• COMPRESS(string)：将字符串string进行压缩。返回的是一个二进制字符串，可以使用 UNCOMPRESS() 函数解压。

示例：

SELECT COMPRESS('your_text_here');

• UNCOMPRESS(string)：将通过 COMPRESS() 函数压缩的二进制字符串string解压缩。

示例：

SELECT UNCOMPRESS(compressed_data);

• UNCOMPRESSED_LENGTH(string)：返回COMPRESS()函数压缩之前字符串的长度。

示例：

SELECT UNCOMPRESSED_LENGTH(compressed_data);

这些加密和压缩函数在处理需要保密的数据或者需要优化存储空间的数据时非常有用。使用加密函数时，需要确保以安全的方式处理和存储密钥。同时，压缩数据可以节省存储空间，但需要考虑压缩和解压缩的性能开销。

9、信息函数

MySQL的信息函数提供了关于当前数据库环境的信息，这些函数可以帮助你获取数据库版本、当前用户、当前数据库名等有用的信息。以下是一些常用的信息函数及其用例：

9.1、数据库和服务器信息

• VERSION()：返回MySQL服务器的版本号。

示例：

SELECT VERSION();

DATABASE()：返回当前默认（或选中的）数据库的名称。

示例：

SELECT DATABASE();

• USER()：返回当前MySQL会话的用户名和主机名。

示例：

SELECT USER();

• CURRENT_USER() 或 CURRENT_USER：返回当前认证的MySQL用户的用户名和主机名。

示例：

SELECT CURRENT_USER();

• SYSTEM_USER()：返回MySQL服务器操作系统级的用户名称。

示例：

SELECT SYSTEM_USER();

9.2、连接和会话信息

• CONNECTION_ID()：返回当前连接的线程ID。

示例：

SELECT CONNECTION_ID();

• LAST_INSERT_ID()：返回最后一个INSERT操作生成的自增ID值。

示例：

SELECT LAST_INSERT_ID();

• ROW_COUNT()：返回上一条DML（数据操作语言）语句（如INSERT、UPDATE或DELETE）影响的行数。

示例：

UPDATE employees SET salary = salary + 1000 WHERE department_id = 1;
SELECT ROW_COUNT();

9.3、性能和设置

• @@global.autocommit, @@session.autocommit：显示全局或会话级别的自动提交状态。

示例：

SELECT @@autocommit;
SELECT @@global.autocommit, @@session.autocommit;

• @@version_comment：提供MySQL版本的额外信息，比如是否是社区版或企业版。

示例：

SELECT @@version_comment;

信息函数是理解和管理 MySQL 服务器及其会话的重要工具，它们能提供关键的环境细节，有助于调试、监控和优化数据库操作。

10、JSON 函数

MySQL 提供了一系列 JSON 函数，允许对 JSON 数据类型进行操作。这些函数使得在 MySQL 中处理 JSON 数据变得非常便捷，包括创建、解析、修改和搜索 JSON 数据。以下是一些常用的 JSON 函数及其用例：

10.1、创建和解析

• JSON_OBJECT(key, value[, key, value] ...)：创建一个 JSON 对象。

示例：

SELECT JSON_OBJECT('id', 1, 'name', 'John Doe');
-- 结果：{"id": 1, "name": "John Doe"}

• JSON_ARRAY([value[, value] ...])：创建一个 JSON 数组。

示例：

SELECT JSON_ARRAY(1, "a", NULL, TRUE, CURDATE());
-- 结果：[1, "a", null, true, "2023-03-13"]

• JSON_EXTRACT(json_doc, path[, path] ...) 或 -> 操作符：从 JSON 文档中提取值。

示例：

SET @json = '{"id": 1, "name": {"first": "John", "last": "Doe"}}';
SELECT JSON_EXTRACT(@json, '$.name.first');
-- 或使用 -> 操作符
SELECT @json -> '$.name.first';
-- 结果： "John"

10.2、修改和更新

• JSON_SET(json_doc, path, value[, path, value] ...)：在 JSON 文档中添加或更新值。

示例：

SET @json = '{"id": 1, "name": "John"}';
SELECT JSON_SET(@json, '$.age', 30);
-- 结果：{"id": 1, "name": "John", "age": 30}

• JSON_REPLACE(json_doc, path, value[, path, value] ...)：替换 JSON 文档中的值。

示例：

SET @json = '{"id": 1, "name": "John"}';
SELECT JSON_REPLACE(@json, '$.name', 'Jane');
-- 结果：{"id": 1, "name": "Jane"}

• JSON_REMOVE(json_doc, path[, path] ...)：从 JSON 文档中删除数据。

示例：

SET @json = '{"id": 1, "name": "John", "age": 30}';
SELECT JSON_REMOVE(@json, '$.age');
-- 结果：{"id": 1, "name": "John"}

10.3、查询和搜索

• JSON_KEYS(json_doc[, path])：返回 JSON 对象的键。

示例：

SET @json = '{"id": 1, "name": "John", "age": 30}';
SELECT JSON_KEYS(@json);
-- 结果：["id", "name", "age"]

• JSON_SEARCH(json_doc, 'one', search_str[, escape_char[, path] ...])：搜索 JSON 文档中的给定文本。

示例：

SET @json = '{"id": 1, "name": "John", "age": 30}';
SELECT JSON_SEARCH(@json, 'one', 'John');
-- 结果： "$.name"

这些 JSON 函数极大地简化了在 MySQL 中处理 JSON 数据的复杂性，使得可以直接在数据库层面上以高效、灵活的方式操作 JSON 数据。