函数(FUNCTION)

与数学中的函数概念类似,SQL语言中的函数也是指接收一个或多个参数,并按一定的关系返回预期的值。这里,根据一定的关系加工出来的预期的值,就是函数提供的功能。

MySQL数据库提供了众多功能强大、方便易用的系统函数。比如:数值函数、字符函数、日期和时间函数、条件判断函数、系统信息函数、数据类型转换函数、窗口函数、加密函数等。

通过这个链接,可以查看所有MySQL提供的系统函数:MySQL函数官方地址

数值函数

函数名称作 用示例
ABS(X)求绝对值ABS(-3.14):3.14
PI()返回π的值PI():3.141593
SQRT(X)求算数平方根SQRT(9):3
MOD(N,M)求余数MOD(9,5):4
CEIL(X) 、
CEILING(X)
两个函数功能相同,都是返回不小于参数的最小整数,即向上取整CEIL(6.2):7
CEIL(-6.7):-6
FLOOR(X)向下取整,返回值转化为一个BIGINTFLOOR(3.6):3
FLOOR(-3.6):4
RAND()生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列RAND():0.7176419149317238
RAND(X)使用X作为种子值,产生重复的随机序列RAND(2):0.6555866465490187
多次执行RAND(2),返回相同的值
ROUND(X)、
ROUND(X,D)
对所传参数进行四舍五入ROUND(3.556):3.5
ROUND(3.556,2):3.56 保留2位小数
TRUNCATE(X,Y)返回舍弃小数点后y位的数字xTRUNCATE(1.99,1):1.9
SIGN返回参数的符号,0是0,负数是-1,正数是1SIGN(0):0
SIGN(3):1
SIGN(-3):-1
POW(X,Y)、
POWER(X,Y)
返回X的Y次方。两个函数的功能相同POW(2,3):8
POWER(2,3):8
EXP(X)返回e的X方exp(4):54.598150033144236
LOG(X)返回X的自然对数(相对于e的对数)LOG(4):1.3862943611198906
LOG10(X)返回X的基数为10的对数LOG10(100):2
RADIANS(X)将X由角度转化为弧度RADIANS(180):3.141592653589793
DEGREES(X)将X由弧度转化为角度DEGREES(PI()):180
SIN(X)返回 x 的正弦值, x 为弧度值(不是角度值。30度=π/6)sin(PI()/6):0.49999999999999994
ASIN(x)返回反正弦值,与函数 SIN 互为反函数。
若 x 不在 -1 到 1 的范围之内,则返回 NULL
asin(0.49999999999999994):0.5235987755982988=π/6
COS(x)返回 x 的余弦值,x 为弧度值cos(PI()/6):0.8660254037844387
ACOS(x)返回反余弦值,与函数 COS 互为反函数。
x 值的范围必须在 -1 和 1 之间,否则返回 NULL
acos(0.8660254037844387):0.5235987755982987
TAN(x)返回 x 的正切值,x 为给定的弧度值tan(PI()/6):0.5773502691896257
ATAN(x)返回 x 的反正切值,与函数 TAN 互为反函数atan(0.5773502691896257):0.5235987755982988
COT(x)返回 x 的余切值,x 是给定的弧度值cot(PI()/6):1.7320508075688774

字符函数

函数名称作 用示例
ASCII(str)返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULLASCII(‘2’):50
CHAR(X)将ASCII 码X转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL。CHAR函数返回的数据类型为BLOB,需要使用CONVERT函数转换成字符。convert(char(50) using utf8mb4):’2′
convert(char(115,113,108,46,119,97,110,103) using utf8mb4):’sql.wang’
LENGTH(str)返回字符串str的字节长度。一个汉字占3个字节LENGTH(‘sql.wang’):8
LENGTH(‘SQL网’):6
CHAR_LENGTH(str)返回字符串str的字符个数。一个汉字算1个字符CHAR_LENGTH(‘SQL网’):4
CONCAT(str1,str2,…)合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个CONCAT(‘sql.’,‘wang’):’sql.wang’
CONCAT_WS(x,str1,str2,…)使用x作为分隔符将str1、str2…拼接起来CONCAT_WS(‘-‘,’sql’,’.’,’wang’):’sql-.-wang’
LOCATE(substr,str)返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始LOCATE(‘wang’,’sql.wang’):5
INSTR(str,substr)返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始INSTR(‘sql.wang’,’wang’):5
POSITION(substr IN str)返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始POSITION(‘wang’ IN ‘sql.wang’):5
ELT(n,str1,str2,…)返回第n个位置的字符串。如n=2,则返回str2。若n小于1或大于字符串的数目,则返回NULLELT(2,’sql’,’wang’):’wang’
FIELD(str,str1,str2,…)返回指定字符串str在列表str1、str2…中第一次出现的位置。若str不存在,则返回0FIELD(‘wang’,’sql’,’wang’):2
FIND_IN_SET(str1,str2)返回str1在str2中第一次出现的位置。str2为逗号分隔的字符串列表FIND_IN_SET(‘an’,’sq,lw,an,g’):3
MAKE_SET(x,str1,str2,…)根据x的二进制数从str1、str2…中选取字符串。x的二进制数从右往左数的位置上若为1,则选择对应位置的字符串。结果以逗号拼接MAKE_SET(3,’sql’,’.’,’wang’):’sql,.’
INSERT(str,pos,len,newStr)将str 中指定 pos 位置开始 len 长度的内容替换为 newStrINSERT(‘sql.wang’,5,4,‘com’):‘sql.com’
LOWER(str)、LCASE(str)将字符串中的字母转换为小写LOWER(‘SQL.wang’):‘sql.wang’
UPPER(str)、UCASE(str)将字符串中的字母转换为大写UPPER(‘SQL.wang’):‘SQL.WANG’
LEFT(str,len)从左侧字截取字符串,返回字符串左边的len个字符LEFT(‘sql.wang’,3):‘sql’
RIGHT(str,len)从右侧字截取字符串,返回字符串右边的len个字符RIGHT(‘sql.wang’,4):‘wang’
LTRIM(str)删除字符串左侧的空格LTRIM(’ sql wang ‘):‘sql wang ’
RTRIM(str)删除字符串右侧的空格RTRIM(’ sql wang ‘):‘ sql wang’
TRIM(str)删除字符串左右两侧的空格TRIM(’ sql wang ‘):‘sql wang’
LPAD(str1,len,str2)将str1左边以str2填充,直到长度达到lenLPAD(‘sql’,’#’,5):’##sql’
RPAD(str1,len,str2)将str1右边以str2填充,直到长度达到lenRPAD(‘sql’,’#’,5):’sql##’
REPLACE(str,s1,s2)字符串替换函数,返回s2替换str中s1后的新字符串REPLACE(‘sql.wang’,’wang’,’com’):‘sql.com’
REPEAT(str,count)对str重复count次REPEAT(‘sql’,3):’sqlsqlsql’
SPACE(n)返回n个空格组成的字符串SPACE(5):’     ‘
SUBSTRING(str,num,len)
MID(str,num,len)
截取字符串,返回从指定num位置开始的指定len长度的字符串。num为负数时,表示从右往左的位置SUBSTRING(‘sql.wang’,5,2):‘wa’
SUBSTRING(‘sql.wang’,-5,2):‘.w’
REVERSE(str)字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串REVERSE(‘sql.wang’):‘gnaw.lqs’
STRCMP(str1,str2)如果str1>str2返回1,str1=str2反回0,str1<str2返回-1STRCMP(‘sql’, ‘sql.wang’):-1

日期和时间函数

函数名称作 用示例
CURDATE() 、 CURRENT_DATE两个函数作用相同,返回当前系统的日期值CURDATE():2021-10-09
CURTIME() 、 CURRENT_TIME两个函数作用相同,返回当前系统的时间值CURTIME():16:13:10
CURRENT_TIMESTAMP()、LOCALTIME()、NOW() 、 SYSDATE()两个函数作用相同,返回当前系统的日期和时间值CURRENT_TIMESTAMP():2021-10-09 16:13:10
UNIX_TIMESTAMP()获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数UNIX_TIMESTAMP():1655303190
FROM_UNIXTIME(X)将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数FROM_UNIXTIME(1651302734):2022-04-30 15:12:14
YEAR(date)获取年份,返回值范围是 1970?2069YEAR(now()):2021
QUARTER(date)获取指定日期对应的一年中的季度,返回值范围是1~4QUARTER(now()):4
MONTH(date)获取指定日期中的月份MONTH(now()):10
MONTHNAME(date)获取指定日期中的月份英文名称MONTHNAME(now()):October
WEEK(date,mode)获取指定日期是一年中的第几周,返回值的范围是否为 0?52 或 1?53。根据mode的值来认定一周的第一天,以及一年的第一周WEEK(now(),3):40
WEEKOFYEAR(date)获取指定日期是一年中的第几周,返回值的范围是否为 1?53。等价于WEEK(date,3)WEEKOFYEAR(now()):40
WEEKDAY(date)获取指定日期在一周内的对应的工作日索引WEEKDAY(now()):6
DAYNAME(date)获取指定曰期对应的星期几的英文名称DAYNAME(now()):Sunday
DAYOFWEEK(date)获取指定日期对应的一周的索引位置值DAYOFWEEK(now()):1
DAYOFYEAR(date)获取指定曰期是一年中的第几天,返回值范围是1~366DAYOFYEAR(now()):282
DAYOFMONTH(date)获取指定日期是一个月中是第几天,返回值范围是1~31DAYOFMONTH(now()):9
HOUR(time)获取指定时间的小时值,返回值范围是0~23HOUR(CURTIME()):16
MINUTE(time)获取指定时间的分钟值,返回值范围是0~59MINUTE(CURTIME()):13
SECOND(time)获取指定时间的秒数,返回值范围是0~59SECOND(CURTIME()):10
EXTRACT(type FROM date)根据type,从指定日期中提取一部分返回EXTRACT(YEAR FROM now()):2021
TIME_TO_SEC(time)将时间参数转换为秒数TIME_TO_SEC(CURTIME()):58390
SEC_TO_TIME(seconds)将秒数转换为时间,与TIME_TO_SEC 互为反函数SEC_TO_TIME(54):00:00:54
DATE_ADD、ADDDATE两个函数功能相同,都是向日期添加指定的时间间隔DATE_ADD(now(),interval 1 day):2021-10-10 16:13:10
DATE_SUB、SUBDATE两个函数功能相同,都是向日期减去指定的时间间隔DATE_SUB(now(),interval 1 day):2021-10-08 16:13:10
ADDTIME(expr1,expr2)时间加法运算,在原始时间上添加指定的时间ADDTIME(‘2018-10-31 23:59:59′,’0:1:1’):2018-11-01 00:01:00
SUBTIME(expr1,expr2)时间减法运算,在原始时间上减去指定的时间SUBTIME(‘2018-10-31 23:59:59′,’0:1:1’):2018-10-31 23:58:58
DATEDIFF(expr1,expr2)获取两个日期之间间隔,返回参数 1 减去参数 2 的值DATEDIFF(‘2021-04-30′,’2021-04-29’):1
DATE_FORMAT(date,format)格式化指定的日期,根据参数返回指定格式的值DATE_FORMAT(‘2021-04-30′,’%Y/%m/%d’):2021/04/30

WEEK函数中mode参数的取值:

模式一周的第一天范围第一周是怎么计算的
0星期日0-53从本年的第一个星期日开始,是第一周。前面的计算为第0周
1星期一0-53假如1月1日到第一个周一的天数超过3天,则计算为本年的第一周。否则为第0周
2星期日1-53从本年的第一个星期日开始,是第一周。前面的计算为上年度的第5x周
3星期一1-53假如1月1日到第一个周日天数超过3天,则计算为本年的第一周。否则为上年度的第5x周
4星期日0-53假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为第0周
5星期一0-53从本年的第一个星期一开始,是第一周。前面的计算为第0周。
6星期日1-53假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为上年度的第5x周
7星期一1-53从本年的第一个星期一开始,是第一周。前面的计算为上年度的第5x周

EXTRACT函数中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返回年和月

条件判断函数

函数名称作 用示例
ISNULL(v1)判断v1的值是否为NULL。如果为NULL,返回1;否则,返回0ISNULL(NULL):1
ISNULL(10):0
IF(expr,v1,v2)根据表达式 expr 得到不同的结果,当 expr 为真是返回 v1 的值,否则返回 v2if(age=10,1,2):如果字段age的值为10,则返回1;否则返回2;
IFNULL(v1,v2)判断值是否为空。
如果 v1 不为 NULL,则 IFNULL 函数返回 v1; 否则返回 v2 的结果
ifnull(age,0):如果字段age的值为NULL,则返回0;否则返回字段age本身的值;
NULLIF(v1,v2)如果v1=v2,则返回null; 否则返回v1的值nullif(1,2):1
nullif(1,1):NULL
CASE根据条件表达式得到不同的结果写法一:
select
case age
when 10 then 1
when 20 then 2
else 3
end
from student;
写法二:
select
case when age <= 10 then 1
when age > 10 and age <=20 then 2
else 3
end
from student;

系统信息函数

函数名称作 用示例
BENCHMARK(count,expr)执行表达式expr,并重复count次。常用于MySQL的基准测试。benchmark(10000, md5(‘sql.wang’ ))
CHARSET(str)返回字符串参数的字符集CHARSET(‘sql.wang’):’utf8′
COLLATION(str)返回字符串参数的排序规则COLLATION(‘sql.wang’):’utf8_general_ci’
CONNECTION_ID()返回连接的连接ID(线程ID)CONNECTION_ID():23876
CURRENT_ROLE()返回当前的活动角色CURRENT_ROLE():root@%
CURRENT_USER(),CURRENT_USER经过身份验证的用户名和主机名CURRENT_USER():root@localhost
DATABASE()返回默认(当前)数据库名称DATABASE():’school’
FOUND_ROWS()返回上一条语句返回的条数FOUND_ROWS():219
LAST_INSERT_ID()最后一个INSERT的AUTOINCREMENT列的值LAST_INSERT_ID():983
ROW_COUNT()更新的行数ROW_COUNT():2
SCHEMA()DATABASE()的同义词SCHEMA():school
SESSION_USER()USER()的同义词SESSION_USER():root@localhost
SYSTEM_USER()USER()的同义词SYSTEM_USER():root@localhost
USER()客户端提供的用户名和主机名USER():root@localhost
VERSION()返回MySQL服务器版本VERSION():8.0.28
picture loss