MySQL常用函数

引言

在数据库应用中，恰当利用内置函数能极大提升数据查询与处理的灵活度与效率。MySQL 作为广泛使用的关系型数据库，提供了丰富的函数库以支持各种操作需求——从聚合统计、流程控制，到字符串操作、日期时间处理乃至加解密。本文根据功能类别逐一介绍了 MySQL 中常见的内置函数，并针对每个函数提供了简要说明，以帮助开发者和运维人员快速理解和使用这些函数，进而提升数据库开发与管理的效率。

聚合函数

• AVG([DISTINCT] expr)：（分组）对列的数据求平均值。
• SUM([DISTINCT] expr)：（分组）对列的数据求总和。
• MIN([DISTINCT] expr)：（分组）求列数据的最小值。
• MAX([DISTINCT] expr)：（分组）求列数据的最大值。
• COUNT([DISTINCT] expr)：（分组）求数据行数。

流程控制

• IF(expr1,expr2,expr3)：如果 expr1 的值为 TRUE，返回 expr2， 否则返回 expr3。
• IFNULL(expr1,expr2)：如果 expr1 不为 NULL，返回 expr1，否则返回 expr2。
• CASE WHEN ... THEN ... [ELSE ...] END：相当于 Java 语言的 if...else if...else...。
• CASE case_value WHEN when_value THEN statement_list ELSE statement_list END CASE;：相当于 Java 语言的 switch...case...。
• BENCHMARK(count,expr)：将表达式 expr 重复执行 count 次。

数据库信息

• VERSION()：返回当前 MySQL 的版本号。
• CONNECTION_ID()：返回当前会话的连接 ID（线程 ID）。
• DATABASE()，SCHEMA()：返回 MySQL 命令行当前所在的数据库。
• USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()：返回当前连接 MySQL 的用户名，返回结果格式为 “用户名@主机名”。
• CHARSET(str)：返回字符串 str 自变量的字符集。
• COLLATION(str)：返回字符串 str 的比较规则。
• CONVERT(expr USING transcoding_name)：将 expr 所使用的字符编码修改为 transcoding_name。
• ROW_COUNT()：返回上一句 SQL 增删改影响的记录数，查询返回-1。
• FOUND_ROWS()：上一句 SELECT 或 SHOW 语句的结果集的记录数。

计算/比较

• ABS(X)：返回 x 的绝对值。

• SIGN(X)：返回 x 的符号，正数为 1，负数为 -1，0 返回 0。

• PI()：返回圆周率。

• CEIL(X) / CEILING(X)：返回大于或等于某个值的最小整数。

• FLOOR(X)：返回小于或等于某个值的最大整数。

• LEAST(value1,value2,...)：返回列表中的最小值，列表含 NULL 则返回 NULL。

• GREATEST(value1,value2,...)：返回列表中的最大值，列表含 NULL 则返回 NULL。

• MOD(N,M)：返回 X 除以 Y 后的余数。

• RAND()：返回 0 ~ 1 的随机值。

• RAND(X)：返回 0 ~ 1 的随机值，其中 X 的值用作种子值，相同的 X 值会产生相同的随机数。

• ROUND(X)：返回一个对 X 的值进行四舍五入后，最接近于 X 的整数。

• ROUND(X,Y)：返回一个对 X 的值进行四舍五入后最接近 X 的值，并保留到小数点后面 Y 位。

• TRUNCATE(X,Y)：返回数字 X 截断为 Y 位小数的结果。

字符串

• ASCII(str)：返回字符串 str 中的第一个字符的 ASCII 码值。
• CHAR_LENGTH(str)：返回字符串 str 的字符数。作用与 CHARACTER_LENGTH(str) 相同。
• LENGTH(str)：返回字符串 str 的字节数，和字符集有关。
• CONCAT(str1,str2,..)：拼接 str1、str2 等字符串为一个新的字符串。
• CONCAT_WS(separator,str1,str2,...)：同 CONCAT(str1,str2,..) 函数，但是每个字符串之间拼接时要加上 separator。
• INSERT(str,pos,len,newstr)：将字符串 str 从第 pos 位置开始，len 个字符长的子串替换为字符串 newstr。
• REPLACE(str,from_str,to_str)：用字符串 to_str 替换字符串 str 中所有出现的字符串 from_str。
• UPPER(str) / UCASE(str)：将字符串 str 的所有字母转成大写字母。
• LOWER(str) / LCASE(str)：将字符串 str 的所有字母转成小写字母。
• LEFT(str,len)：返回字符串 str 最左边的 len 个字符。
• RIGHT(str,len)：返回字符串 str 最右边的 len 个字符。
• LPAD(str,len,padstr)：用字符串 padstr 对 str 最左边进行填充，直到 str 的长度为 len 个字符。
• RPAD(str,len,padstr)：用字符串 padstr 对 str 最右边进行填充，直到 str 的长度为 len 个字符。
• TRIM(str)：去掉字符串 str开始与结尾的空格。
• LTRIM(str)：去掉字符串 str 左侧的空格。
• RTRIM(str)：去掉字符串 str 右侧的空格。
• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)：去掉 str 两边/开始/结尾处的 remstr。
• REPEAT(str,count)：返回 str 重复 count 次的结果。
• SPACE(N)：返回 N 个空格。
• STRCMP(expr1,expr2)：比较字符串 expr1 和 expr2 的 ASCII 码值的大小。
• SUBSTR(str,pos,len)：返回从字符串 str 的 pos 位置其 len 个字符，作用与 SUBSTRING(str,pos,len)、MID(str,pos,len) 相同。
• LOCATE(substr,str)：返回字符串 substr 在字符串 str 中首次出现的位置，作用于 POSITION(substr IN str)、INSTR(str,substr) 相同。未找到，返回0。
• ELT(N,str1,str2,str3,...)：返回指定位置的字符串，如果 N 等于 1，则返回 str1，如果 N 等于 2，则返回 str2，以此类推，与 MAKE_SET(bits,str1,str2,...) 作用相同。
• FIELD(str,str1,str2,str3,...)：返回字符串str在字符串列表中第一次出现的位置。
• FIND_IN_SET(str,strlist)：返回字符串 str 在字符串 strlist 中出现的位置。其中，字符串 strlist 是一个以逗号分隔的字符串。
• REVERSE(str)：返回 str 反转后的字符串。
• NULLIF(expr1,expr2)：比较两个字符串，如果 expr1 与 expr2 相等，则返回 NULL，否则返回 expr1。

日期和时间

• CURDATE() / CURRENT_DATE()：返回当前日期，只包含年、 月、日。
• CURTIME() / CURRENT_TIME()：返回当前时间，只包含时、 分、秒。
• NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()：返回当前系统日期和时间。
• UTC_DATE()：返回 UTC（世界标准时间）日期。
• UTC_TIME()：返回 UTC（世界标准时间）时间。
• UNIX_TIMESTAMP()：以 UNIX 时间戳的形式返回当前时间。
• UNIX_TIMESTAMP(date)：将时间 date 以 UNIX 时间戳的形式返回。
• FROM_UNIXTIME(unix_timestamp)：将 UNIX 时间戳转换为普通格式的时间。
• FROM_UNIXTIME(unix_timestamp,format)：将 UNIX 时间戳转换为指定格式的时间。
• YEAR(date) / MONTH(date) / DAY(date)：返回具体的日期值。
• HOUR(time) / MINUTE(time) / SECOND(time)：返回具体的时间值。
• MONTHNAME(date)：返回月份：January、…。
• DAYNAME(date)：返回星期几：MONDAY、TUESDAY、…、SUNDAY。
• WEEKDAY(date)：返回周几，注意，周一是 0、周二是 1、…、周日是 6。
• QUARTER(date)：返回日期对应的季度，范围为 1～4。
• WEEK(date) / WEEKOFYEAR(date)：返回一年中的第几周。
• DAYOFYEAR(date)：返回日期是一年中的第几天。
• DAYOFMONTH(date)：返回日期位于所在月份的第几天。
• DAYOFWEEK(date)：返回周几，注意，周一是 0、周二是 1、…、周日是 6。
• EXTRACT(unit FROM date)：返回指定日期中的特定部分或组合，如 SECOND、HOUR、YEAR、…、DAY_HOUR。
• TIME_TO_SEC(time)：将 time 转化为秒并返回结果值，转化的公式为：“小时 * 3600 + 分钟 * 60 + 秒”。
• SEC_TO_TIME(seconds)：将 seconds 描述转化为包含小时、分钟和秒的时间。
• DATE_ADD(date,INTERVAL expr unit) / ADDDATE(date,INTERVAL expr unit)：返回与给定日期时间 date 之后 INTERVAL 时间段的日期时间（单位为 unit）。
• DATE_SUB(date,INTERVAL expr unit) / SUBDATE(date,INTERVAL expr unit)：返回与给定日期时间 date 之前 INTERVAL 时间段的日期时间（单位为 unit）。
• ADDTIME(expr1,expr2)：返回 expr1 加上 expr2 的时间。当 expr2 为一个数字时，代表的是秒，可以为负数。
• SUBTIME(expr1,expr2)：返回 expr1 减去 expr2 后的时间。当 expr2 为一个数字时，代表的是秒 ，可以为负数。
• DATEDIFF(expr1,expr2)：返回两个日期的间隔天数（expr1 - expr2）。
• TIMEDIFF(expr1,expr2)：返回两个时间的间隔（expr1 - expr2，格式为 “时:分:秒”）。
• FROM_DAYS(N)：返回从 0000 年 1 月 1 日起，N 天以后的日期。
• TO_DAYS(date)：返回日期 date 距离 0000 年 1 月 1 日的天数。
• LAST_DAY(date)：返回日期 date 所在月份的最后一天的日期。
• MAKEDATE(year,dayofyear)：返回给定年份 year 的第 dayofyear 天的日期。
• MAKETIME(hour,minute,second)：将给定的小时、分钟和秒组合成时间并返回。
• PERIOD_ADD(P,N)：返回 P 加上 N 后的时间，如 20221203+3=20221206、202212+3=202303。
• PERIOD_DIFF(P1,P2)：计算两个日期的差值（P1 - P2），参数格式为 %YY%m%s 或 %YY%m，结果单位为 %s 或 %m。

格式化

• FORMAT(X,D)：返回对数字 X 进行格式化后的结果数据，保留小数 D 位（四舍五入）。
• DATE_FORMAT(date,format)：按照字符串 format 格式化日期 date 值。
• TIME_FORMAT(time,format)：按照字符串 format 格式化时间 time 值。
• GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})：返回日期字符串的显示格式。
• STR_TO_DATE(str, format)：按照字符串 format 对 str 进行解析，返回一个格式为 format 的日期。

类型转换

• CAST(any AS datatype)：将任何类型的值转换为指定类型的值，目标类型可能是 BINARY、CHAR、DATE、DATETIME、TIME、DECIMAL、SIGNED、UNSIGNED，如不能进行转换则只为 NULL。
• CONVERT(str, cast_datatype)：将字符串 str 转换为指定类型 cast_datatype 的值，大多数情况下与 CAST 函数功能重叠。
• CONVERT(str USING charset)：将字符串 str 转换为指定的字符集 charset。

指数/对数

• SQRT(X)：返回 X 的平方根。结果不合法时返回 NULL。
• POW(X,Y) / POWER(X,Y)：返回 X 的 Y 次方。
• EXP(X)：返回 e 的 X 次方，其中 e 是一个常数（2.718281828459045...）。
• LN(X) / LOG(X)：返回以 e 为底的 X 的对数，当 X 小于等于 0 时，返回的结果为 NULL。
• LOG10(X)：返回以 10 为底的 X 的对数，当 X 小于等于 0 时，返回的结果为 NULL。
• LOG2(X)：返回以 2 为底的 X 的对数，结果不合法时返回 NULL。
• LOG(B,X)：以 B 为底 X 的对数，结果不合法时返回 NULL。

进制

• BIN(X)：返回 X 的二进制编码。
• HEX(X)：返回 X 的十六进制编码。
• OCT(X)：返回 X 的八进制编码。
• CONV(N,from_base,to_base)：将 N 从 from_base 进制转为 to_base 进制。

角度

• RADIANS(X)：将角度转化为弧度，其中，参数 X 为角度值。
• DEGREES(X)：将弧度转化为角度，其中，参数 X 为弧度值。

IP地址

• INET_ATON(expr)：将以点分隔的 IP 地址 expr 转化为一个数字，值为字符串类型。
• INET_NTOA(expr)：将数字形式的 IP 地址 expr 转化为以点分隔的 IP 地址，值为字符串类型。

三角函数

• SIN(X)：返回 X 的正弦值，其中，参数 X 为弧度值。
• ASIN(X)：返回 X 的反正弦值，即获取正弦为 X 的值，结果不合法时返回 NULL。
• COS(X)：返回 X 的余弦值，其中，参数 X 为弧度值。
• ACOS(X)：返回 X 的反余弦值，即获取余弦为 X 的值，结果不合法时返回 NULL。
• TAN(X)：返回 X 的正切值，其中，参数 X 为弧度值。
• ATAN(X)：返回 X 的反正切值，即返回正切值为 X 的值。
• ATAN2(Y,X)：返回两个参数的反正切值。
• COT(X)：返回 X 的余切值，其中，X为弧度值。

加解密

• PASSWORD(str)：返回字符串 str 的加密版本，41 位长的字符串。加密结果不可逆，常用于用户的密码加密。
• MD5(str)：返回字符串 str 的 MD5编码值，也是一种加密方式。若参数为 NULL，则会返回 NULL。
• SHA(str)：从原明文密码 str 计算并返回加密后的密码字符串，当参数为 NULL时，返回 NULL。SHA 加密算法比 MD5 更加安全。
• ENCODE(str,pass_str)：返回使用 pass_str 作为加密密码加密 str 后的值。
• DECODE(str,pass_str)：返回使用 pass_str 作为加密密码解密 str 后的值。

日期格式化附录

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

总结

通过对 MySQL 中各类函数的系统梳理，可以发现 MySQL 内置的各类函数在日常的数据处理和运维场景下都能发挥重要作用，熟悉并灵活运用这些函数，不仅能够简化 SQL 语句的编写，还能减少网络传输与数据处理的额外开销，为数据库层面的性能优化和业务逻辑实现带来更大的便利。正如文章所示，MySQL 丰富多样的函数提供了高度的灵活性和可扩展能力，帮助开发者和运维人员快速、高效地完成多种复杂数据操作。