MySQL8实用系列（六）函数汇总：聚合函数、字符串函数、数值函数、时间函数、流程控制函数及其他常用函数

目录

一、聚合函数

汇总

COUNT(1)、COUNT(*)、COUNT(字段)的区别以及使用场景

二、数值型函数

汇总

RAND函数

ROUND函数

三、字符串函数

汇总

LENGTH函数

CONCAT函数

INSERT函数 

LEFT和RIGHT函数 

SUBSTR和SUBSTRING函数 

四、时间函数

汇总

format参数格式

CURDATE和CURRENT_DATE函数

CURTIME和CURRENT_TIME函数

NOW和SYSDATE函数以及二者的区别

WEEK函数

DATE_ADD和DATE_SUB函数

五、流程控制函数

汇总

IF函数

IFNULL函数

CASE函数

六、 其他函数

本文详细介绍了MySQL支持的常用的函数，如聚合函数、数值型函数、字符串函数、时间函数、流程控制函数、其他函数等，如何使用这些函数，以及生产中常用函数的使用方式、使用场景和区别，并且对部分函数进行详细的参数解释、注意事项和使用说明。在命名规范中，函数名建议大写。

环境：MySQL 8.0.32

下文语法中[]包括的内容表示可选项

一、聚合函数

通常与GROUP BY分组一起使用

汇总

函数名称	作用
MAX	查询指定字段的最大值
MIN	查询指定字段的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定字段的总和
AVG	求平均值，返回指定字段数据的平均值

COUNT(1)、COUNT(*)、COUNT(字段)的区别以及使用场景

生产环境中，COUNT()有几种不同的使用方式：

• COUNT(1)：统计所有记录。假设表中有一个字段固定值为1，这种方式会统计有多少个1，即通过统计行数来工作，不受字段定义的影响。
• COUNT(*)：统计所有记录，包括值为NULL的记录。
• COUNT(字段)：统计指定字段不为NULL的记录。
• COUNT(DISTINCT 字段)：统计指定字段去重且不为NULL的记录。

以上不同使用方式适用于不同的使用场景：

• 如果表中只有一列，使用COUNT(*)。
• 如果表中存在多列且存在主键或索引，使用COUNT(主键字段)或COUNT(索引字段)。
• 如果表中存在多列且不存在主键，使用COUNT(1)优于COUNT(*)。

        

二、数值型函数

汇总

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL和CEILING	向上取整，返回不小于参数的最小整数，返回值转换为BIGINT
FLOOR	向下取整，返回不大于参数的最大整数，返回值转换为BIGINT
RAND	生成一个[0,1)的随机数
ROUND	四舍五入
SIGN	返回参数的符号
POW和POWER	求次方
SIN	求正弦值
ASIN	求反正弦值，与函数SIN互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数COS互为反函数
TAN	求正切值
ATAN	求反正切值，与函数TAN互为反函数
COT	求余切值

RAND函数

RAND(x)：传入一个整数参数，是一个随机数种子，相同种子产生的随机数相同

注意：随机数种子相同，但行不同，得到的随机数不同

例：生产一个[5,10)的随机数

SELECT (RAND() * 5) + 5;

ROUND函数

ROUND(x, y)：参数x指定处理的数据，y指定保留小数位数。

注意：y默认为0；y为负数，指定小数点左边的d位整数位为0，且小数位均为0

mysql> SELECT ROUND(-6.213, 1), ROUND(3.1), ROUND(88.234, -1), ROUND(88.234, -2), ROUND(22.1234, -2);
+------------------+------------+-------------------+-------------------+--------------------+
| ROUND(-6.213, 1) | ROUND(3.1) | ROUND(88.234, -1) | ROUND(88.234, -2) | ROUND(22.1234, -2) |
+------------------+------------+-------------------+-------------------+--------------------+
|             -6.2 |          3 |                90 |               100 |                  0 |
+------------------+------------+-------------------+-------------------+--------------------+
1 row in set (0.02 sec)

三、字符串函数

汇总

函数名称	作用
LENGTH	返回字符串的字节长度
CONCAT	合并字符串，参数可为一或多个
INSERT	将一个子字符串插入另一个字符串的指定位置，返回新字符串
LOWER	字符串的字母转换为小写
UPPER	字符串的字母转换为大写
LEFT	从左侧开始截取字符串，返回字符串左边的若干字符
RIGHT	从右侧开始截取字符串，返回字符串右边的若干字符
TRIM	删除字符串左右两侧的空格
REPLACE	替换所有匹配字符串，返回替换后的新字符串
SUBSTR和SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符串
REVERSE	字符串反转（逆序）函数，返回与原字符串顺序相反的字符串

LENGTH函数

使用utf8字符集时，一个汉字是三个字节，一个数字或字母是一个字节。

CONCAT函数

CONCAT(str1, str2, ...)：返回一个或多个字符串参数拼接的字符串

注意：若有任何一个字符串参数为NULL，则返回值为NULL。若所有字符串参数均为非二进制字符串，则结果为非二进制字符串。若字符串参数含有二进制字符串，则结果为一个二进制字符串

INSERT函数 

INSERT(str, position, len, substr)：参数str指定原字符串，position指定原字符串中插入substr的指定位置，len指定原字符串中要替换的字符数，substr指定要插入的字符串

说明：

• 原字符串第一个字符位置为1
• 如果插入的指定位置超过了原字符串长度，返回原字符串
• 如果要替换的字符数不在字符串指定位置后的剩余长度内，则从指定位置替换到字符串结束
• 如果substr为空，即替换为空，即删除指定字符串

LEFT和RIGHT函数 

LEFT(str, num)：返回字符串str最左边的num个字符

RIGHT(str, num)：返回字符串str最右边的num个字符

说明：num=1表示第一个字符。num为负值时返回结果为空

SUBSTR和SUBSTRING函数 

SUBSTR是SUBSTRING函数功能相同，是同义词

SUBSTRING(str, pos)/SUBSTRING(str FROM pos)：返回字符串str从位置pos开始的子字符串

SUBSTRING(str, pos, len)/SUBSTRING(str FROM pos FOR len)：返回字符串str从位置pos开始，长度为len的子字符串

说明：

• 使用FROM的形式是标准的SQL语法
• 位置pos为正值时，从字符串str的开始从左往右截取；位置pos为负值时，从字符串str的末尾从右往左截取
• 第一个字符位置pos默认为1；长度len小于1返回结果为空

四、时间函数

汇总

函数名称	作用
CURDATE和CURRENT_DATE	返回系统当前日期
CURTIME和CURRENT_TIME	返回系统当前时间
NOW和SYSDATE	返回系统当前的日期和时间
UNIX_TIMESTAMP	返回一个以UNIX时间戳为基础的无符号整数（'1970-01-01 00:00:00'GMT之后的秒数）。参数为date/datetime类型的值
FROM_UNIXTIME(unix_timestamp[, format])	将UNIX时间戳转换为日期时间格式，与UNIX_TIMESTAMP互为反函数。参数unix_timestamp为unix时间戳；可选参数format指定要转换的格式，详见下文format参数格式
MONTH	获取指定日期中的月份。参数为date/datetime类型的值
MONTHNAME	获取指定日期中的月份的名称。参数为date/datetime类型的值
DAYNAME	获取指定日期对应的星期的名称。参数为date/datetime类型的值
DAYOFWEEK	获取指定日期对应一周的第几天，返回值范围1~7，1=周日。参数为date/datetime类型的值
WEEK	获取指定日期是一年中的第几周，返回值范围0~52或1~53。
WEEKDAY	获取指定日期在一周内对应的工作日索引（0=星期一，1=星期二, ……6= 星期日）。参数为date/datetime类型的值
DAYOFYEAR	获取指定日期是一年中的第几天，返回值范围1~366。参数为date/datetime类型的值
DAYOFMONTH	获取指定日期是一个月中的第几天，返回值范围为1~31。参数为date/datetime类型的值
YEAR	获取指定日期中的年份，返回值范围为1970~2069。参数为date/datetime类型的值
TIME_TO_SEC	时间（只针对时间部分）转换成距离当天00:00:00的秒数。参数为datetime/time类型的值
SEC_TO_TIME	秒数转换为距离当天00:00:00的time类型时间，与TIME_TO_SEC互为反函数。参数为整数或浮点数（精确到小数点后6位）
DATE_ADD和ADDDATE	向日期添加指定的时间间隔
DATE_SUB和SUBDATE	向日期减去指定的时间间隔
ADDTIME(time, expr)	时间加法运算，在原始时间上添加指定的时间。参数time为datetime/time类型的值；参数expr为time类型的值
SUBTIME(time, expr)	时间减法运算，在原始时间上减去指定的时间。参数time为datetime/time类型的值；参数expr为time类型的值
DATEDIFF	获取两个日期之间（仅针对日期部分）的间隔，两个日期参数均为date/datetime类型的值。
DATE_FORMAT(date, format)	格式化指定的日期。参数date为date/datetime类型的值；参数format指定要转换的格式，详见下文format参数格式

format参数格式

格式	说明
%M	月份名称（January~December）
%W	星期名称（Sunday~Saturday）
%w	一个星期中的天数（ 0=Sunday ～ 6=Saturday ）
%D	一个月中带英文的天数， 例如1st，2nd，13th等
%Y	4位数的年份
%y	2位数的年份
%a	缩写的星期名称（Sun~Sat）
%d	月份中的天数（00~31）
%e	月份中的天数，不带前导零（0~31）
%m	月份（0~12）
%c	月份，不带前导零（1~12）
%b	缩写的月份名称（Jan~Dec）
%j	一年中的天数（001~366）
%H	小时（24小时制）
%h或%I（大写i）	小时（12小时制）
%k	小时，不带前导零（24小时制）
%l（小写L）	小时，不带前导零（12小时制）
%p	AM 或 PM（仅与 12 小时制一起使用）
%i	分钟（00~59）
%S或%s	秒 （00 ～ 59）
%r	12小时制的时间表示（hh:mm:ss [AP]M）
%T	24小时制的时间表示（hh:mm:ss）
%U	一年中的周数（ 0～ 52）， 星期天为每周的第一天
%u	一年中的周数（0～52），星期一为每周的第一天
%%	输出%

CURDATE和CURRENT_DATE函数

作用相同，无参数，当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串中或数字语境而定，返回date类型值

CURTIME和CURRENT_TIME函数

作用相同，无参数，当前时间按照“HH:MM-SS”或“HHMMSS”格式的值返回，具体格式根据函数用在字符串中或数字语境而定，返回time类型值

NOW和SYSDATE函数以及二者的区别

作用相同，无参数，当前日期时间按照“YYYY-MM-DD HH:MM-SS”或“YYYYMMDDHHMMSS”格式的值返回，具体格式根据函数用在字符串中或数字语境而定，返回datetime类型值

区别：NOW返回开始执行语句的时间，SYSDATE返回语句执行的时间

mysql> SELECT NOW(), SLEEP(10), NOW();
+---------------------+-----------+---------------------+
| NOW()               | SLEEP(10) | NOW()               |
+---------------------+-----------+---------------------+
| 2024-05-13 16:40:44 |         0 | 2024-05-13 16:40:44 |
+---------------------+-----------+---------------------+
1 row in set (10.03 sec)

mysql> SELECT SYSDATE(), SLEEP(10), SYSDATE();
+---------------------+-----------+---------------------+
| SYSDATE()           | SLEEP(10) | SYSDATE()           |
+---------------------+-----------+---------------------+
| 2024-05-13 16:41:15 |         0 | 2024-05-13 16:41:25 |
+---------------------+-----------+---------------------+
1 row in set (10.00 sec)

WEEK函数

WEEK(date[, mode])：参数date为date/datetime类型的值；可选参数mode用于确定周数计算的逻辑，可指定一周从星期一或星期天开始，返回的周数在0~52或0~53之间，参数缺失则使用default_week_format系统变量的值，默认为0

以下表格为不同mode参数下WEEK函数的不同计算逻辑

模式	一周的第一天	范围	一年的第一周
0	星期日	0~53	从本年的第一个星期日开始是第一周，本年之前的日期为第0周
1	星期一	0~53	例如1月1日到第一个星期一的天数超过3天，则计算为本年的第一周，否则为第0周
2	星期日	1~53	从本年的第一个星期日开始是第一周，本年之前的日期为上一年的第52/53周
3	星期一	1~53	例如1月1日到第一个星期一的天数超过3天，则计算为本年的第一周，否则为上一年的第52/53周
4	星期日	0~53	例如1月1日到第一个星期日的天数超过3天，则计算为本年的第一周，否则为第0周
5	星期一	0~53	从本年的第一个星期一开始是第一周，本年之前的日期为第0周
6	星期日	1~53	例如1月1日到第一个星期日的天数超过3天，则计算为本年的第一周，则为上一年的第52/53周
7	星期一	1~53	从本年的第一个星期一开始是第一周，本年之前的日期为上一年的第52/53周

DATE_ADD和DATE_SUB函数

DATE_ADD和ADDDATE(date,INTERVAL expr type)

DATE_SUB和SUBDATE(date,INTERVAL expr type)

参数date为date/datetime类型的值；expr指定具体的时间间隔；type指定间隔时间的单位

注意：

• type复合型使用_下划线进行间隔
• type为复合型，expr可指定两个值，使用任何非数字字符作为间隔，且不能使用负值。如expr只指定一个值，则只对应type中第二个单位，可使用负值
• type不为复合型，可指定expr为负值使DATA_ADD和DATE_SUB对时间进行相同的操作

以下为参数type可用的值

type值	单位
MICROSECOND	毫秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	星期
MONTH	月
QUARTER	季度
YEAR	年
SECOND_MICROSECOND	秒、毫秒
MINUTE_MICROSECOND	分钟、毫秒
MINUTE_SECOND	分钟、秒
HOUR_MICROSECOND	小时、毫秒
HOUR_SECOND	小时、秒
HOUR_MINUTE	小时、分钟
DAY_MICROSECOND	天、毫秒
DAY_SECOND	天、秒
DAY_MINUTE	天、分钟
DAY_HOUR	天、小时
YEAR_MONTH	年、月

五、流程控制函数

汇总

函数名称	作用
IF	判断
IFNULL	判断是否为空
CASE	搜索

IF函数

IF(expr, res1, res2)：expr为true时返回res1，否则返回res2。参数expr指定条件判断语句；返回值为数字或字符串

IFNULL函数

IFNULL(expr1, expr2)：expr1不为NULL时返回expr1，否则返回expr2。返回值为数字或字符串

CASE函数

类似于if...else if...else...，两种写法

写法一：

CASE <表达式>
    WHEN <值1> THEN <操作>
    WHEN <值2> THEN <操作>
    ...
    ELSE <操作>
END CASE;

写法二（把写法一中的表达式拆解并分别放到WHEN条件中）：

CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE <命令>
END CASE;

六、 其他函数

函数名称	作用
VERSION	数据库版本号
DATABASE	当前数据库
USER	当前连接用户
PASSWORD	返回字符串的密码形式
MD5	返回字符串的md5哈希值

下一篇文章会详解MySQL8其中一个在工作中会经常使用且对数据处理更便捷快速的新特性——窗口函数。

如对本篇中某个函数想要了解更多，欢迎私信或评论，我会单独在新文章中对其进行更详细的介绍和使用更具体的方法和例子。

后续系列中会介绍MySQL更多知识。如有问题和建议，可私信或评论，非常感谢。