MySQL------单行函数汇总

基本函数

函数	用法	举例
ABS(x)	返回x的绝对值	ABS(-2)=2；ABS(13)=13
SIGN(X)	返回X的符号。正数返回 1 ，负数返回-1， 0 返回 0	SIGN(-265)=-1；SIGN(123)=1
PI()	返回圆周率的值	PI()=3.141592653589793
CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数	CEIL(56.234)=57；CEIL(-16.254)=-16
FLOOR(x)	返回小于或等于某个值的最大整数	FLOOR(26.152)=26；FLOOR(-22.36)=-23
LEAST(e1,e2,e3…)	返回列表中的最小值	LEAST(3,1,5)=1
GREATEST(e1,e2,e3…)	返回列表中的最大值	GREATEST(3,1,5)=5
MOD(x,y)	返回X除以Y后的余数（取模）	MOD(12,5)=2
RAND()	返回大于等于0且小于1的随机值 [0,1)	RAND()=0.1523654869871548，且每次执行得到的值不同
RAND(x)	返回大于等于0且小于1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数	RAND(5)=0.40613597483014313，且每次执行得到的值不变
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数	ROUND(5.36)=5
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位	ROUND(5.36，1)=5.4
TRUNCATE(x,y)	返回数字x截断为y位小数的结果	TRUNCATE(5.3678,2)=5.36
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL	SQRT(9)=3；SQRT(-9)=NULL

字符串函数

函数	用法	举例
ASCII(S)	返回字符串S中的第一个字符的ASCII码值	ASCII('hello')=104=ASCII('hi')
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同	CHAR_LENGTH('你好')=2
LENGTH(s)	返回字符串s的字节数，和字符集有关	LENGTH('你好')=6
CONCAT(s1,s2,……,sn)	连接s1,s2,……,sn为一个字符串	CONCAT('你好','，','世界')=‘你好，世界’
CONCAT_WS(x,s1,s2,……,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x	CONCAT_WS(',','你好','我是','吴彦祖')=‘你好,我是,吴彦祖’
INSERT(str,idx,len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr	INSERT('你好,我是吴彦祖',6,3,'胡歌')=‘你好,我是胡歌’
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a	REPLACE('a>b>c', '>', '<=')=‘a<=b<=c’
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母	UPPER('a>b>c')=‘A>B>C’
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母	LOWER(‘A>B>C’) ='a>b>c'
LEFT(str,n)	返回字符串str最左边的n个字符	LEFT('a>b>c',3)=‘a>b’
RIGHT(str,n)	返回字符串str最右边的n个字符	RIGHT('a>b>c',3)='b>c'
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符	LPAD('abc', 5, '*')=‘**abc’
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符	RPAD('abc', 5, '*')=‘abc**’
LTRIM(s)	去掉字符串s左侧的空格	LTRIM(' abc ')=‘abc ’
RTRIM(s)	去掉字符串s右侧的空格	RTRIM(' abc ')=‘ abc’
TRIM(s)	去掉字符串s开始与结尾的空格	TRIM(' abc ')=‘abc’
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s	TRIM('c' FROM 'ccabc')=‘ab’
TRIM(LEADING s FROM s)	去掉字符串s开始处的s	TRIM(LEADING 'c' FROM 'ccabc')=‘abc’
TRIM(TRAILING s FROM s)	去掉字符串s结尾处的s	TRIM(TRAILING 'c' FROM 'ccabc')=‘ccab’
REPEAT(str, n)	返回str重复n次的结果	REPEAT('abc', 3)=‘abcabcabc’
SPACE(n)	返回n个空格	SPACE(3)=‘ ’
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小	STRCMP('a','a')=0；STRCMP('b','a')=1；STRCMP('a','b')=-1；
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同	SUBSTR('abcdefg',2,3)=‘bcd’
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回 0	LOCATE('de','abcdefg')=4
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn	ELT(3,'aaa','bbb','ccc','ddd','eee')=‘ccc’
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置	FIELD('ddd','aaa','bbb','ccc','ddd','eee','ddd')=4
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串	FIND_IN_SET('ddd','aaa,bbb,ccc,ddd,eee,ddd')=4
REVERSE(s)	返回s反转后的字符串	REVERSE('abcd')=‘dcba’
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1	NULLIF('abcd','abcd')=NULL；NULLIF('abc','abcd')=‘abc’

角度与弧度互换函数

函数	用法	举例
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值	RADIANS(180)=3.141592653589793=PI()
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值	DEGREES(2*PI())=360

三角函数

函数	用法
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在- 1 到 1 之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在- 1 到 1 之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值

指数与对数

函数	用法
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的对数，当X <= 0 时，返回NULL

进制间的转换

函数	用法	举例
BIN(x)	返回x的二进制编码	BIN(10)=1010
HEX(x)	返回x的十六进制编码	HEX(26)=1A
OCT(x)	返回x的八进制编码	OCT(13)=15
CONV(x,f1,f2)	返回f1进制数变成f2进制数	CONV(1101,2,8)=15

获取日期、时间

函数	用法	举例
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日	CURDATE()=2028-11-02
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒	CURTIME()=17:51:04
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期和时间	NOW()=2028-11-02 17:52:01
UTC_DATE()	返回UTC（世界标准时间）日期	UTC_DATE()=2028-11-02
UTC_TIME()	返回UTC（世界标准时间）时间	UTC_TIME()=09:52:50

日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time)/MINUTE(time)/SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，…
DAYNAME(date)	返回星期几：MONDAY，TUESDAY…..SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，…周日是6
QUARTER(date)	返回日期对应的季度，范围为 1 ～ 4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是 1 ，周一是 2 ，…周六是7

日期的操作函数

函数	用法
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

type取值	含义	举例
MICROSECOND	返回毫秒数	EXTRACT( MICROSECOND FROM '2023-01-01 12:12:55')=0
SECOND	返回秒数	EXTRACT( SECOND FROM '2023-01-01 12:12:55')=55
MINUTE	返回分钟数	EXTRACT( MINUTE FROM '2023-01-01 12:12:55')=12
HOUR	返回小时数	EXTRACT( HOUR FROM '2023-01-01 12:12:55')=12
DAY	返回天数	EXTRACT( DAY FROM '2023-01-01 12:12:55')=1
WEEK	返回日期在一年中的第几个星期	EXTRACT( WEEK FROM '2023-01-01 12:12:55')=1
MONTH	返回日期在一年中的第几个月	EXTRACT( MONTH FROM '2023-01-01 12:12:55')=1
QUARTER	返回日期在一年中的第几个季度	EXTRACT( QUARTER FROM '2023-01-01 12:12:55')=1
YEAR	返回日期的年份	EXTRACT( YEAR FROM '2023-01-01 12:12:55')=2023
SECOND_MICROSECOND	返回秒数和毫秒值	EXTRACT(SECOND_MICROSECOND FROM '2023-01-01 12:12:55')=55000000
MINUTE_MICROSECOND	返回分钟和毫秒值	EXTRACT(MINUTE_MICROSECOND FROM '2023-01-01 12:12:55')=1255000000
HOUR_MICROSECOND	返回小时和毫秒值	EXTRACT(HOUR_MICROSECOND FROM '2023-01-01 12:12:55')=121255000000
HOUR_SECOND	返回小时和秒数	EXTRACT( HOUR_SECOND FROM '2023-01-01 12:12:55')=121255
HOUR_MINUTE	返回小时和分钟	EXTRACT( HOUR_MINUTE FROM '2023-01-01 12:12:55')=1212
DAY_MICROSECOND	返回天数的毫秒值	EXTRACT( DAY_MICROSECOND FROM '2023-01-01 12:12:55')=1121255000000
DAY_SECOND	返回天数和秒数	EXTRACT( DAY_SECOND FROM '2023-01-01 12:12:55')=1121255
DAY_MINUTE	返回天数和分钟数	EXTRACT( DAY_MINUTE FROM '2023-01-01 12:12:55')=11212
DAY_HOUR	返回天数和小时	EXTRACT( DAY_HOUR FROM '2023-01-01 12:12:55')=112
YEAR_MONTH	返回年和月	EXTRACT( YEAR_MONTH FROM '2023-01-01 12:12:55')=202301

时间和秒钟转换的函数

函数	用法
TIME_TO_SEC(time)	将time转化为秒并返回结果值。转化的公式为：小时 * 3600+分钟 * 60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间

计算日期和时间的函数

函数	用法	举例
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间	DATE_ADD('2025-12-31 11:11:50', INTERVAL 1 DAY)=2026-01-01 11:11:50；DATE_ADD('2025-12-31 11:11:50', INTERVAL '10_10' YEAR_MONTH)=2036-10-31 11:11:50
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期	DATE_SUB('2025-12-31 11:11:50', INTERVAL 1 DAY)=2025-12-30 11:11:50；DATE_SUB('2025-12-31 11:11:50', INTERVAL '10_10' YEAR_MONTH)=2015-02-28 11:11:50

上述函数中type的取值：

间隔类型	含义
SECOND	秒数
MINUTE	分钟数
HOUR	小时数
DAY	天数
MONTH	月数
YEAR	年数
MINUTE_SECOND	分和秒
HOUR_SECOND	小时和秒
HOUR_MINUTE	小时和分
DAY_SECOND	天和秒
DAY_MINUTE	天和分钟
DAY_HOUR	天和小时
YEAR_MONTH	年和月

函数	用法	举例
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数	ADDTIME('2035-11-12 11:25:12','1:10:00')=2035-11-12 12:35:12；ADDTIME('2035-11-12 11:25:12',20)=2035-11-12 11:25:32
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数	SUBTIME('2035-11-12 11:25:12','1:10:00')=2035-11-12 10:15:12；SUBTIME('2035-11-12 11:25:12',20)=2035-11-12 11:24:52
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数	DATEDIFF('2035-11-12 11:25:12','2036-11-12 11:25:12')=-366
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔	TIMEDIFF('2035-11-12 11:25:12','2036-11-12 11:25:12')=-838:59:59
FROM_DAYS(N)	返回从 0000 年 1 月 1 日起，N天以后的日期	FROM_DAYS(738521)=2022-01-01
TO_DAYS(date)	返回日期date距离 0000 年 1 月 1 日的天数	TO_DAYS('2022-01-01')=738521
LAST_DAY(date)	返回date所在月份的最后一天的日期	LAST_DAY('2022-02-01')=2022-02-28
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期	MAKEDATE('2022',32)=2022-02-01
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回	MAKETIME(12,30,26)=12:30:26
PERIOD_ADD(time,n)	在格式 YYMM 或 YYYYMM 的time中添加给定期间内的给定月份数n	PERIOD_ADD('202202',1)=202203

日期的格式化与解析

函数	用法	举例
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值	DATE_FORMAT(NOW(),GET_FORMAT(DATETIME, 'INTERNAL'))=20250103193602
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

上述非GET_FORMAT函数中fmt参数常用的格式符：

格式符	说明	格式符	说明
%Y	4 位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,….）	%m	两位数字表示月份（01,02,03…）
%b	缩写的月名（Jan.，Feb.，….）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…..）
%H	两位数字表示小数， 24 小时制（01,02..）	%h和%I	两位数字表示小时， 12 小时制（01,02..）
%k	数字形式的小时， 24 小时制(1,2,3)	%l	数字形式表示小时， 12 小时制（1,2,3,4….）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday….)
%j	以 3 位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3…）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3…）其中Monday为周中第一天
%T	24 小时制	%r	12 小时制
%p	AM或PM	%%	表示%

GET_FORMAT函数中date_type和format_type参数取值如下：

日期类型	格式化类型	返回的格式化字符串
DATE	USA	%m.%d.%Y
DATE	JIS	%Y-%m-%d
DATE	ISO	%Y-%m-%d
DATE	EUR	%d.%m.%Y
DATE	INTERNAL	%Y%m%d
TIME	USA	%h.%i.%s.%p
TIME	JIS	%H:%i:%s
TIME	ISO	%H:%i:%s
TIME	EUR	%H.%i.%s
TIME	INTERNAL	%H%i%s
DATETIME	USA	%Y-%m-%d %H.%i.%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERNAL	%Y%m%d%H%i%s

流程控制函数

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果 2 …. [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 …. [ELSE 值n] END	相当于Java的switch…case…

加密与解密函数

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

MySQL信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

其他函数

函数	用法
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code