当前位置：首页 > article >正文

Mysql--基础篇--函数（字符串函数，日期函数，数值函数，聚合函数，自定义函数及与存储过程的区别等）

article 2025/1/8 17:08:09

MySQL提供了丰富的内置函数，涵盖了字符串处理、数值计算、日期和时间操作、聚合统计、控制流等多种功能。这些函数可以帮助你简化SQL查询，提升开发效率。
除了内置函数，MySQL还支持自定义函数（User-Defined Functions），允许用户根据自己的需求编写复杂的逻辑。

一、MySQL内置函数

1、字符串函数

字符串函数用于处理和操作文本数据。以下是一些常用的字符串函数：

1.1、CONCAT()

作用：连接多个字符串。
语法：CONCAT(str1, str2, …, strN)

示例：

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

1.2、CONCAT_WS()

作用：使用指定的分隔符连接多个字符串。
语法：CONCAT_WS(separator, str1, str2, …, strN)

示例：

SELECT CONCAT_WS('-', '2024', '12', '31');  -- 输出: 2024-12-31

运行结果：
在这里插入图片描述

1.3、SUBSTRING()或SUBSTR()

作用：从字符串中提取子字符串。
语法：SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)

示例：

SELECT SUBSTRING('Hello World', 7, 5);  -- 从第7个元素开始，截取5个元素。输出: World

1.4、LEFT()和RIGHT()

作用：分别从字符串的左侧或右侧提取指定长度的字符。
语法：LEFT(str, len)和RIGHT(str, len)

示例：

SELECT LEFT('Hello World', 5);  -- 从最左侧开始截取5个元素。输出: Hello
SELECT RIGHT('Hello World', 5); -- 从最右侧开始截取5个元素。输出: World

1.5、LENGTH()和CHAR_LENGTH()

作用：分别返回字符串的字节长度和字符长度。
语法：LENGTH(str)和CHAR_LENGTH(str)

示例：

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello');  -- 输出: 5, 5
SELECT LENGTH('你好'), CHAR_LENGTH('你好');     -- 输出: 6, 2 (中文字符占多个字节)

1.6、LOWER()和UPPER()

作用：将字符串转换为小写或大写。
语法：LOWER(str) 和 UPPER(str)

示例：

SELECT LOWER('HELLO'), UPPER('hello');  -- 输出: hello, HELLO

1.7、TRIM()

作用：去除字符串两端的空格或其他指定字符。
语法：TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str)

示例：

  SELECT TRIM('   Hello World   ');  -- 输出: Hello World
  SELECT TRIM(BOTH '_' FROM '__Hello__');  -- 输出: Hello

1.8、REPLACE()

作用：替换字符串中的某个子字符串。
语法：REPLACE(str, from_str, to_str)

示例：

SELECT REPLACE('Hello World', 'World', 'Universe');  -- 输出: Hello Universe

2、数值函数

数值函数用于处理和计算数值数据。

2.1、ABS()

作用：返回数值的绝对值。
语法：ABS(num)

示例：

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

2.2、CEIL()和FLOOR()

作用：分别返回大于或等于给定数值的最小整数，以及小于或等于给定数值的最大整数。
语法：CEIL(num)和FLOOR(num)

示例：

SELECT CEIL(3.14), FLOOR(3.14);  -- 输出: 4, 3

2.3、ROUND()

作用：对数值进行四舍五入。
语法：ROUND(num, decimals)

示例：

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

2.4、MOD()

作用：返回两个数相除后的余数。
语法：MOD(num1, num2)或num1 % num2

示例：

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

2.5、POW()和POWER()

作用：返回第一个参数的幂次方。
语法：POW(base, exponent) 或 POWER(base, exponent)

示例：

 SELECT POW(2, 3);  -- 输出: 8

2.6、SQRT()

作用：返回数值的平方根。
语法：SQRT(num)

示例：

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

2.7、RAND()

作用：返回一个0到1之间的随机浮点数。
语法：RAND()

示例：

  SELECT RAND();  -- 输出: 随机浮点数

运行结果：
在这里插入图片描述

3、日期和时间函数

日期和时间函数用于处理和操作日期、时间数据。

3.1、NOW()和CURRENT_TIMESTAMP()

作用：返回当前的日期和时间。
语法：NOW() 和 CURRENT_TIMESTAMP()

示例：

  SELECT NOW();  -- 输出: 当前日期和时间

运行结果：
在这里插入图片描述

3.2、CURDATE()和CURTIME()

作用：分别返回当前的日期和时间。
语法：CURDATE()和CURTIME()

示例：

SELECT CURDATE(), CURTIME();  -- 输出: 当前日期, 当前时间

在这里插入图片描述

3.3、DATE()和TIME()

作用：分别提取日期和时间部分。
语法：DATE(datetime) 和 TIME(datetime)

示例：

 SELECT DATE('2024-12-31 14:30:00'), TIME('2024-12-31 14:30:00');  -- 输出: 2024-12-31, 14:30:00

3.4、YEAR()、MONTH()、DAY()等

作用：提取日期中的年、月、日等部分。
语法：YEAR(date)、MONTH(date)、DAY(date) 等

示例：

  SELECT YEAR('2024-12-31'), MONTH('2024-12-31'), DAY('2024-12-31');  -- 输出: 2024, 12, 31

3.5、DATEDIFF()

作用：计算两个日期之间的天数差。
语法：DATEDIFF(date1, date2)

示例：

 SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- 输出: 365

3.6、TIMESTAMPDIFF()

作用：计算两个日期或时间之间的差异，支持多种单位（如年、月、日、小时等）。
语法：TIMESTAMPDIFF(unit, datetime1, datetime2)

示例：

  SELECT TIMESTAMPDIFF(YEAR, '1990-01-01', '2024-12-31');  -- 输出: 34

3.7、DATE_ADD()和DATE_SUB()

作用：在日期上添加或减去指定的时间间隔。
语法：DATE_ADD(date, INTERVAL expr unit) 和 DATE_SUB(date, INTERVAL expr unit)

示例：

  SELECT DATE_ADD('2024-12-31', INTERVAL 1 DAY);  -- 增加1个月。输出: 2025-01-01
  SELECT DATE_SUB('2024-12-31', INTERVAL 1 MONTH);  -- 减去1个月。输出: 2024-11-30

4、聚合函数

聚合函数用于对一组数据进行汇总计算。以下是一些常用的聚合函数：

4.1、COUNT()

作用：计算行数。
语法：COUNT()或COUNT(column)

示例：

 SELECT COUNT() FROM employees;  -- 计算表中所有行数
 SELECT COUNT(salary) FROM employees;  -- 计算非空 salary 的行数

4.2、SUM()

作用：计算数值列的总和。
语法：SUM(column)

示例：

 SELECT SUM(salary) FROM employees;  -- 计算所有员工的工资总和

4.3、AVG()

作用：计算数值列的平均值。
语法：AVG(column)

示例：

 SELECT AVG(salary) FROM employees;  -- 计算所有员工的平均工资

4.4、MAX()和MIN()

作用：分别返回数值列中的最大值和最小值。
语法：MAX(column)和MIN(column)

示例：

  SELECT MAX(salary), MIN(salary) FROM employees;  -- 返回最高和最低工资

4.5、GROUP_CONCAT()

作用：将多行数据合并为一个字符串。
语法：GROUP_CONCAT(column [SEPARATOR separator])

示例：

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

运行结果：
在这里插入图片描述

5、控制流函数

控制流函数用于在查询中实现条件逻辑。以下是一些常用的控制流函数：

5.1、IF()

作用：根据条件返回不同的值。
语法：IF(condition, true_value, false_value)

示例：

  SELECT name, IF(salary > 50000, 'High', 'Low') AS salary_level
  FROM employees;

运行结果：
在这里插入图片描述

5.2、CASE表达式

作用：实现多条件判断。
语法：

  CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      ...
      ELSE default_result
  END

示例：

  SELECT name,
         CASE
             WHEN salary > 80000 THEN 'Senior'
             WHEN salary > 50000 THEN 'Mid-level'
             ELSE 'Junior'
         END AS level
  FROM employees;

运行结果：
在这里插入图片描述

5.3、COALESCE()

作用：返回第一个非空的表达式。
语法：COALESCE(expr1, expr2, …, exprN)

示例：

  SELECT COALESCE(phone, email, 'No contact info') AS contact_info
  FROM employees;

运行结果：
在这里插入图片描述
说明：
COALESCE（参数1，参数2…）,方法可以包含多个参数，方法返回第一个不为空的参数值。

5.4、NULLIF()

作用：如果两个表达式相等，则返回NULL，否则返回第一个表达式。
语法：NULLIF(expr1, expr2)

示例：

  SELECT NULLIF(manager_id, 0) AS managerId
  FROM employees;

运行结果：
manger_id和null（本例的0）作比较，如果不相等返回第一个表达式的值，如果相等则返回null
在这里插入图片描述
manger_id和manger_id自身相比，肯定都是相同的，都返回了null

6、加密和哈希函数

加密和哈希函数用于生成加密字符串或哈希值。以下是一些常用的加密和哈希函数：

6.1、MD5()

作用：生成MD5哈希值。
语法：MD5(str)

示例：

 SELECT MD5('password');  -- 输出: 5f4dcc3b5aa765d61d8327deb882cf99

运行结果：
在这里插入图片描述

6.2、SHA1()和SHA2()

作用：生成SHA-1或SHA-2哈希值。
语法：SHA1(str)和SHA2(str, bit_length)

示例：

 SELECT SHA1('password');  -- 输出: 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8
 SELECT SHA2('password', 256);  -- 输出: 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8

运行结果：
在这里插入图片描述

6.3、AES_ENCRYPT()和AES_DECRYPT()

作用：使用AES算法进行加密和解密。
语法：AES_ENCRYPT(str, key)和AES_DECRYPT(encrypted_str, key)

示例：

 SELECT AES_ENCRYPT('secret', 'mykey');
 SELECT AES_DECRYPT(AES_ENCRYPT('secret', 'mykey'), 'mykey');

运行结果：
在这里插入图片描述

7、其他函数

除了上述分类，MySQL还提供了一些其他类型的函数。

7.1、UUID()

作用：生成一个唯一的UUID（通用唯一标识符）。
语法：UUID()

示例：
SELECT UUID();
运行结果：
在这里插入图片描述

7.2、INET_ATON()和INET_NTOA()

作用：将IP地址转换为整数，或将整数转换为IP地址。
语法：INET_ATON(ip_address) 和 INET_NTOA(integer)

示例：
SELECT INET_ATON(‘192.168.1.1’); – 输出: 3232235777
SELECT INET_NTOA(3232235777); – 输出: 192.168.1.1
运行结果：
在这里插入图片描述

8、内置函数总结

MySQL提供了丰富多样的内置函数，涵盖了字符串处理、数值计算、日期和时间操作、聚合统计、控制流、加密和哈希操作等多个方面。合理使用这些函数可以大大简化SQL查询，提高开发效率并增强数据处理能力。

二、MySQL自定义函数

除了内置函数，MySQL还支持自定义函数（User-Defined Functions, UDF），允许用户根据自己的需求编写复杂的逻辑。

自定义函数可以分为两类：

存储函数（Stored Functions）：使用SQL语法自定义编写函数，可以在数据库中存储并调用。
C语言编写的UDF（User-Defined Functions）：通过C或C++编写并编译为动态链接库（.so 文件），然后在MySQL中加载和使用。

1、存储函数（Stored Functions）

存储函数是使用SQL和PL/SQL编写的函数，类似于存储过程，但它们返回一个值。存储函数可以在SELECT、INSERT、UPDATE等SQL语句中直接调用。

1.1、创建存储函数

使用CREATE FUNCTION语句来创建存储函数。

创建存储函数的基本语法：

DELIMITER //
CREATE FUNCTION function_name (param1 type, param2 type, ...)
RETURNS return_type
DETERMINISTIC | NOT DETERMINISTIC
LANGUAGE SQL
BEGIN
    -- 函数体
    RETURN result;
END //
DELIMITER ;

解释：

function_name：函数的名称。
param1, param2, …：函数的参数列表，每个参数包括名称和类型。
RETURNS return_type：指定函数的返回值类型。
DETERMINISTIC | NOT DETERMINISTIC：指定函数是否是确定性的。如果函数对于相同的输入总是返回相同的结果，则为DETERMINISTIC；否则为NOT DETERMINISTIC。
LANGUAGE SQL：指定函数使用的语言，通常是SQL。
BEGIN … END：函数体，包含执行的逻辑。
RETURN result：返回结果。

1.2、示例1：简单的数学计算函数

假设我们想创建一个函数add_numbers，它接受两个整数作为参数，并返回它们的和。

示例：

DELIMITER //

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE sum INT;
    SET sum = a + b;
    RETURN sum;
END //

DELIMITER ;

运行结果：
在这里插入图片描述
调用示例：

SELECT add_numbers(5, 10);  -- 输出: 15

运行结果：
在这里插入图片描述

1.3、示例2：带有条件判断的函数

我们可以创建一个函数get_employee_level，它根据员工的工资返回其级别（如"Junior"、“Mid-level"或"Senior”）。

示例：

DELIMITER //

CREATE FUNCTION get_employee_level(salary DECIMAL(10, 2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(20);
    
    IF salary > 80000 THEN
        SET level = 'Senior';
    ELSEIF salary > 50000 THEN
        SET level = 'Mid-level';
    ELSE
        SET level = 'Junior';
    END IF;
    
    RETURN level;
END //

DELIMITER ;

运行结果：
在这里插入图片描述

调用示例：

SELECT name, salary, get_employee_level(salary) AS level
FROM employees;

运行结果：
在这里插入图片描述

1.4、示例3：带有游标的函数

假设我们有一个表orders，我们想创建一个函数get_total_orders，它接受客户ID作为参数，并返回该客户的所有订单总额。

创建表的sql：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,          -- 订单的唯一标识符
    customer_id INT NOT NULL,                   -- 客户的唯一标识符
    order_date DATE NOT NULL,                   -- 订单的日期
    amount DECIMAL(10, 2) NOT NULL             -- 订单的金额
);

示例数据：
在这里插入图片描述
创建函数示例：

DELIMITER //

CREATE FUNCTION get_total_orders(customer_id_p INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10, 2) DEFAULT 0;
    DECLARE order_amount DECIMAL(10, 2);
    DECLARE done INT DEFAULT 0;
    
    DECLARE cur CURSOR FOR
        SELECT amount FROM orders WHERE customer_id = customer_id_p;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO order_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET total = total + order_amount;
    END LOOP;
    
    CLOSE cur;
    
    RETURN total;
END //

DELIMITER ;

解释：

DELIMITER //：更改分隔符为//，以便在函数体中使用;作为语句结束符，而不会提前终止函数定义。最后再将分隔符恢复为;。
CREATE FUNCTION get_total_orders(customer_id INT)：创建一个名为get_total_orders的存储函数，接受一个INT类型的参数customer_id_p（注意入参不要和表字段相同），表示要查询的客户ID。
RETURNS DECIMAL(10, 2)：指定函数返回一个DECIMAL(10, 2)类型的值，即订单总额，最多10位数字，其中2位是小数部分。
DETERMINISTIC：表示该函数对于相同的输入总是返回相同的结果。这意味着MySQL可以对其进行优化，因为它不会依赖外部状态或产生随机结果。
DECLARE total DECIMAL(10, 2) DEFAULT 0; ：定义total，用于累加所有订单的金额，初始值为0。
DECLARE order_amount DECIMAL(10, 2); ：定义order_amount，用于存储从游标中获取的每个订单的金额。
DECLARE done INT DEFAULT 0;：定义done，用于标记游标的遍历是否完成，初始值为0。当游标遍历到末尾时，done将被设置为1。
DECLARE cur CURSOR FOR：声明一个游标cur，用于遍历orders表中属于指定customer_id_p的所有订单的amount列。
SELECT amount FROM orders WHERE customer_id = customer_id_p; :这里的customer_id_p是函数的参数，表示我们要查询的客户ID。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; ：当游标遍历到末尾（即没有更多行可以读取）时，MySQL会触发NOT FOUND异常。我们使用CONTINUE HANDLER来捕获这个异常，并将done设置为1，表示遍历结束。
OPEN cur：打开游标，准备从orders表中读取数据。
read_loop: LOOP：开始一个循环，逐行读取游标中的数据。
FETCH cur INTO order_amount：从游标中获取当前行的amount值，并将其存储在 order_amount变量中。
IF done THEN LEAVE read_loop：如果done被设置为1，表示游标已经遍历到末尾，则退出循环。
SET total = total + order_amount：将当前订单的金额累加到total中。
CLOSE cur：关闭游标，释放资源。
RETURN total：返回累加后的订单总额。

创建函数结果：
在这里插入图片描述
调用示例：

SELECT get_total_orders(2001);  -- 返回客户ID为2001的所有订单总额

运行结果：
在这里插入图片描述

2、存储函数与存储过程的区别

在这里插入图片描述

3、存储函数的最佳实践

保持函数简单：存储函数应该尽量简洁，避免过于复杂的逻辑。如果需要处理复杂的业务逻辑，考虑使用存储过程。
使用DETERMINISTIC标记：如果函数对于相同的输入总是返回相同的结果，标记为DETERMINISTIC可以提高查询优化器的效率。
避免副作用：存储函数不应该修改数据库中的数据，因为这可能会导致不可预测的行为。
合理使用变量：在函数体内使用局部变量（DECLARE）来存储中间结果，避免重复计算。
错误处理：使用SIGNAL语句抛出自定义错误，确保函数在遇到异常情况时能够正确处理。

4、自定义函数总结

MySQL的自定义函数功能非常强大，尤其是存储函数，允许你在数据库中封装复杂的逻辑并返回结果。通过合理使用存储函数，你可以简化SQL查询，提升代码的可读性和可维护性。

乘风破浪会有时，直挂云帆济沧海！！！

查看全文

http://www.kler.cn/a/471810.html

《Spring Framework实战》4：Spring Framework 文档

Flink系统知识讲解之：如何识别反压的源头

分享:osgb倾斜数据转cesium-3dtiles 小工具.

怎么管理电脑usb接口，分享四种USB端口管理方法

php反序列化 ctf例题演示框架安全（TP,Yii,Laravel） phpggc生成框架利用pop

企业国外传输大文件到国内该怎么做？

LeetCode热题100-相交链表【JavaScript讲解】

解决高并发环境消息通知涉及问题

李宏毅机器学习课程笔记02 | 机器学习任务攻略General Guide

基于Qlearning强化学习的机器人迷宫路线搜索算法matlab仿真

MTK平台-- 无线AP隔离功能

FPGA设计：入行芯片领域的理想起点

C#中的关键字out和ref的区别

低空管控技术-无人机云监视技术详解！

EntityFramework Core 数据种子

Docker：针对内网如何安装docker镜像

Golang学习笔记_20——error

计算机网络（29）网络地址转换NAT

vue2新增删除

Idea（中文版）项目结构/基本设置/设计背景

Linux(Centos 7.6)命令详解：mkdir

Wireshark基本使用

LabVIEW四边形阻抗继电器

需求分析基本操作流程

React使用Redux

Hive sql执行文件合并配置参数

一、MySQL内置函数

1、字符串函数

1.1、CONCAT()

1.2、CONCAT_WS()

1.3、SUBSTRING()或SUBSTR()

1.4、LEFT()和RIGHT()

1.5、LENGTH()和CHAR_LENGTH()

1.6、LOWER()和UPPER()

1.7、TRIM()

1.8、REPLACE()

2、数值函数

2.1、ABS()

2.2、CEIL()和FLOOR()

2.3、ROUND()

2.4、MOD()

2.5、POW()和POWER()

2.6、SQRT()

2.7、RAND()

3、日期和时间函数

3.1、NOW()和CURRENT_TIMESTAMP()

3.2、CURDATE()和CURTIME()

3.3、DATE()和TIME()

3.4、YEAR()、MONTH()、DAY()等

3.5、DATEDIFF()

3.6、TIMESTAMPDIFF()

3.7、DATE_ADD()和DATE_SUB()

4、聚合函数

4.1、COUNT()

4.2、SUM()

4.3、AVG()

4.4、MAX()和MIN()

4.5、GROUP_CONCAT()

5、控制流函数

5.1、IF()

5.2、CASE表达式

5.3、COALESCE()

5.4、NULLIF()

6、加密和哈希函数

6.1、MD5()

6.2、SHA1()和SHA2()

6.3、AES_ENCRYPT()和AES_DECRYPT()

7、其他函数

7.1、UUID()

7.2、INET_ATON()和INET_NTOA()

8、内置函数总结

二、MySQL自定义函数

1、存储函数（Stored Functions）

1.1、创建存储函数

1.2、示例1：简单的数学计算函数

1.3、示例2：带有条件判断的函数

1.4、示例3：带有游标的函数

2、存储函数与存储过程的区别

3、存储函数的最佳实践

4、自定义函数总结

相关文章：