MySQL查询中常用的函数

MySQL函数

数学函数

绝对值函数ABS(x)

ABS(x)返回x的绝对值

SELECT ABS(字段名) FROM 表名;

圆周率的函数PI()

SELECT PI();

PI()返回圆周率的值，默认显示的小数是6位。

平方根函数SQRT(x)

SQRT(x)返回非负数x的平方根

对表的某列求平方根：

SELECT SQRT(字段名) FROM 表名;

求余函数MOD(x,y)

MOD(x,y)返回x被y除后的余数，MOD()对带有小数部分的数值也起作用，它返回除法运算后的精确余数

对表的某列被y除后求余：

SELECT MOD(字段名,y) FROM 表名;

获取整数的函数CEIL(x)、CEILING(x)、FLOOR(x)

CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT

SELECT CEIL(-3.35),CEILING(3.35);
运行结果:
-3,4

FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT

SELECT FLOOR(-3.35),FLOOR(3.35);
运行结果:
-4,3

获取随机数的函数RAND()和RAND(x)

RAND(x)返回一个随机浮点值v，范围在0到1之间(0<=v<=1.0>)，若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。
不带参数的RAND()每次产生的随机数值是不同的，带有相同参数x的RAND(x)，每次产生相同的随机数。

函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

ROUND(x)返回最接近参数x的整数，即4舍五入。

SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14),ROUND(1.66);
运行结果:
-1,-2,1,2

ROUND(x,y)返回最接近x的数，其值保留到小数点后面y位,若y为负值，则将保留x值到小数点左边y位

SELECT ROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2);
运行结果:
1.4,1,230,200

TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带小数点或不带小数部分。若y为负数，则截去(归零)x小数点左起第y位开始后面所有低位的值。

SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1);
运行结果：
1.3,1,10

符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果为-1、0、1

幂运算函数POW(x,y)、POWER(x,y)、EXP(x)

POW(x,y)和POWER(x,y)函数返回x的y次方的值
EXP(x)返回e的x次方的值

对数运算函数LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，x相对于基数e的对数
LOG10(x)返回x以10为底的对数
对数定义域不能为负，否则返回结果为NULL

角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

RADIANS(x)将参数由角度转化为弧度
DEGREES(x)将参数由弧度转化为角度

正弦函数SIN(x)和反正弦函数ASIN(x)

SIN(x)返回x的正弦值，其中x为弧度值
ASIN(x)返回x的反正弦值，即正弦为x的值，若x $\in$ [-1,1]的范围内则返回NULL

余弦函数COS(x)和反余弦函数ACOS(x)

COS(x)返回x的余弦值，其中x为弧度值
ACOS(x)返回x的反余弦值，即余弦为x的值，若x $\in$ [-1,1]的范围内则返回NULL

正切函数、反正切函数和余切函数

TAN(x)返回x的正切值，其中x为给定的弧度值
ATAN(x)返回x的反正切值，即正切为x的值
COT(x)返回x的余切值

字符串函数

计算字符串字符数的函数和计算字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数
LENGTH(str)返回值为字符串的字节长度，使用utf8(UNICODE的一种变长字符编码，又称万国码)编码字符集时，一个汉字是3字节，一个数字或字母是一字节。

合并字符串函数CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)

CONCAT(s1,s2,…)返回结果为连接参数产生的字符串，或许有一个或多个参数。若任何一个参数为NULL，则返回值为NULL。若所有参数皆为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串。

CONCAT_WS(x,s1,s2,…)，CONCAT_WS代表CONCAT With Separator,是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符，分隔符放在要连接的字符串之间。分隔符可以是一个字符串，也可以是其他参数。若分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL

替换字符串的函数INSERT(s1,x,len,s2)

INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。若x超过字符串长度,则返回原始字符串。若len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。
例:

SELECT INSERT('Quest',2,4,'What'),INSERT('Quest',-1,4,'What'),INSERT('Quest',3,100,'Wh');
运行结果:
QWhat,Quest,QuWhat

字母大小写转换函数

LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换为小写
UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换为大写

获取指定长度的字符串函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s开始最左边的n个字符
RIGHT(s,n)返回字符串s最右边n个字符

例:

SELECT LEFT('football',5);
运行结果:
footb
SELECT RIGHT('football',4);
ball

填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度，若s1的长度大于len，则返回值被缩短至len字符

RPAD(s1,len,s2)返回字符串s1,其右边被字符串s2填补到len字符长度，若s1的长度大于len，则返回值被缩短到len字符长度

例:

SELECT LPAD('hello',4,'??'),LPAD('hello',10,'??');
运行结果:
hell,?????hello

SELECT RPAD('hello',4,'??'),RPAD('hello',10,'??');
运行结果:
hell,hello?????

删除空的的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s，字符串左侧空格字符被删除，LTRIM只删除字符串左侧的空格，右侧的空格将不会被删除

RTRIM(s)返回字符串s，字符串右侧空格字符被删除，TTRIM只删除字符串右侧的空格，左侧的空格将不会被删除

删除指定字符串的函数 TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定的情况下，删除空格

重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若 n<=0，则返回一个空字符串。若s或n为NULL,则返回NULL

空格函数SPACE(n)

SPACE(n)返回一个由n个空格组成的字符串

替换函数REPLACE(s,s1,s2)

REPLACE(s,s1,s2)使用s2替代字符串s中所有的字符串s1

比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2)比较两个字符串，所有的字符串均线同则返回0，将字符串s1与s2字符ASCII码进行比较，若s1>s2，则返回1，若s1<s2，则返回-1

获取字串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)带有len参数格式，从字符串s返回一个长度同len字符相同的字符串，起始于位置n。n取负值时，子字符串位置起始于字符串结尾的n字符，即倒数n个字符。

MID(s,n,len)与SUBSTRING(s,n,len)的作用相同

若对len使用的是一个小于1的值，则结果始终为空字符串

例:

SELECT SUBSTRING('breakfast',5),SUBSTRING('breakfast',5,3),SUBSTRING('lunch',-3),SUBSTRING('lunch',-5,3);

运行结果:
kfast,kfa,nch,lun

匹配字串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str,str1)3个函数的作用相同，返回子字符串str1在字符串str中的开始位置

字符串逆序的函数REVERSE(s)

REVERSE(s)函数将字符串s反转，返回的字符串的顺序和s字符串的顺序相反

返回指定位置的字符串函数

ELT(N,字符串1,字符串2,字符串2,…,字符串N)中，若N=1，则返回值为字符串1；若N小于1或大于参数的数目，则返回值为NULL

返回指定字符串位置的函数FIELD(s,s1,s2,…)

FIELD(s,s1,s2,…)返回字符串s在列表s1,s2,…中第一次出现的位置，找不到则返回0，若s为NULL则返回值为0，因为NULL不能与任何值进行同等比较

例:

SELECT FIELD('Hi','hihi','Hey','Hi','bas'),FIELD('Hi','Hey','Lo','Hilo','foo');

运行结果:
3,0

返回字串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是由一个由多个","分开的字符串组成的列表。若s1不在s2中或s2为空字符串，则返回值为0。若任意一个参数为NULL，则返回值为NULL。

例:

SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');

运行结果:
3

日期和时间函数

获取当前日期和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照’YYYY-MM-DD’或’YYYYMMDD’格式的值返回
CURTIME()和CURRENT_TIME()函数的作用相同，将当前时间按照’HH:MM:SS’或’HHMMSS’的格式返回
CURTIME()+0可将当前时间值转换为数值型

获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE() 4个函数的作用相同，均返回当前日期和时间值，格式为’YYYY-MM-DD HH:MM:SS’或’YYYYMMDDHHMMSS’

UNIX时间戳函数

UNIX_TIMESTAMP(date)若无参数调用，则返回一个UNIX时间戳('1970-01-01 00:00:00’GMT之后的秒数)作为无符号整数。其中，GMT(Greenwich Mean Time)为格林尼治标准时间。date可以时一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字

FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)护卫反函数

返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()返回当前UTC(世界标准时间)日期值，其格式为’YYYY-MM-DD’或’YYYYMMDD’，具体格式取决于函数用在字符串或数字语境

UTC_DATE()返回值为当前时区的日期值

UTC_TIME()返回当前UTC时间值

获取月份的函数MONTH(date)和MONTHNAME(date)

MONTH(date)返回date对应的月份，范围为1~12
MONTHNAME(date)返回日期date对应月份的英文全名

获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函数返回d对应的工作日的英文名称，如Sunday、Monday等
DAYOFWEEK(d)函数返回d对应的一周中的索引位置。1表示周日，2表示周一,…,7表示周六
WEEKDAY(d)函数返回d对应的工作日索引。0表示周一，1表示周二,…,6表示周日

获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值是否为0-53或1-53.若Mode参数被省略，则使用default_week_format系统自变量的值

Mode	一周的第一天	范围	Week 1为第一周...
0	周日	0-53	本年度中有一个周日
1	周一	0-53	本年度中有3天以上
2	周日	1-53	本年度中有一个周日
3	周一	1-53	本年度中有3天以上
4	周日	0-53	本年度中有3天以上
5	周一	0-53	本年度中有一个周一
6	周日	1-53	本年度中有3天以上
7	周一	1-53	本年度中有一个周一

WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是1-53。相当于WEEK(d,3)

获取天数的函数 DAYOFYEAR(d)和DAYOFMONTH(d)

DAYOFYEAR(d)返回d是一年中的第几天，范围是1-366
DAYOFMONTH(d)返回d是一月中第几天，范围是1-31

获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)返回date对应的年份，范围是1970-2069
00-69转换为2000-2069，70-99转换为1970-1999
QUARTER(date)返回date对应的一年中的季度值，范围是1-4
MINUTE(time)返回time对应的分钟数，范围是0-59
SECOND(time)返回time对应的秒数，范围是0-59

获取日期的指定值函数EXTRACT(type FROM date)

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符与DATE_ADD()或DATE_SUB()相同，但它从日期中提取一部分，而不是执行日期运算
type值为YEAR时，只返回年值；type值为YEAR_MONTH时，返回年与月份，type值为DAY_MINUTE时，返回日、小时和分钟值

时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为:小时3600+分钟60+秒
SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值，其格式为’HH:MM:SS’或HHMMSS

TIME_TO_SEC和SEC_TO_TIME互为反函数

计算日期和时间的函数

DATE_ADD(date,INTERVAL expr type)和DATE_SUB(date,INTERVAL expr type)中，
date是一个DATETIME或DATE值，用来指定起始时间，expr是一个表达式，用来指定从起始时间添加或减去的时间间隔值。对于负值的时间间隔，expr可以以一个符号"-"开头。type为关键词，它指示了表达式被解释的方式。

type值	expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	SECONDS.MICROSECONDS
MINUTE_MICROSECOND	MINUTES.MICROSECONDS
MINUTE_SECOND	MINUTES:SECONDS
HOUR_MICROSECOND	HOURS.MICROSECONDS
HOUR_SECOND	HOURS:MINUTES:SECONDS
HOUR_MINUTE	HOURS:MINUTES
DAY_MICROSECOND	DAYS.MICROSECONDS
DAY_SECOND	DAYS HOURS:MINUTES:SECONDS
DAY_MINUTE	DAYS HOURS:MINUTES
DAY_HOUR	DAYS HOURS
YEAR_MONTH	YEARS-MONTHS

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数作用相同，执行日期的加运算。

DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数的作用相同，执行日期的减函数。

ADDTIME(date,expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或日期时间表达式，而expr是一个时间表达式。

SUBTIME(date,expr)函数中date减去expr值，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或日期时间表达式。计算中只用到这些值的日期部分。

例:

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND),
ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND),
DATE_ADD('2010-12-31 23:59:59' INTERVAL '1:1' MINUTE_SECOND);

运行结果:
2011-01-01 00:00:00,2011-01-01 00:00:00,2011-01-01 00:01:00

将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format指定的格式显示date值。DATE_FORMAT时间日期格式如下表:

说明符	说明
%a	工作日的缩写名称(Sun...Sat)
%b	月份的缩写名称(Jan...Dec)
%c	月份，数字形式(0...12)
%D	带有英文后缀的该月日期(0th,1st,2nd,3rd,...)
%d	该月日期，数字形式(00...31)
%e	该月日期，数字形式(0...31)
%f	微秒(000000....999999)
%H	以2位数表示24小时(00...23)
%h,%I	以2位数表示12小时(01...12)
%i	分钟，数字形式(00...59)
%j	一年中的天数(001...366)
%k	以24(0...23)小时表示时间
%l	以12(1...12)小时表示时间
%M	月份名称(Januaty...December)
%m	月份，数字形式(00...12)
%p	上午(AM)或下午(PM)
%r	时间，12小时制(小时(hh):分钟(mm):秒数(ss)后加 AM或PM)
%S,%s	以2位数形式表示秒(00...59)
%T	时间，24小时制(小时(hh):分钟(mm):秒数(ss))
%U	周(00...53)，其中周日为每周的第一天
%u	周(01...53)，其中周一为每周的第一天
%V	周(01...53)，其中周日为每周的第一天，和%X同时使用
%v	周(01...53)，其中周一为每周的第一天，和%x同时使用
%W	工作日名称(周日...周六)
%w	一周中的每日(0=周日...6=周六)
%X	该周的年份，其中周日为每周的第一天。数字形式，4位数，和%V同时使用
%x	该周的年份，其中周一为每周的第一天。数字形式，4位数，和%v同时使用
%Y	4位数形式表示年份
%y	2位数形式表示年份
%%	%'文字字符

GET_FORMAT(val_type,format_type)返回日期时间字符串显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型。

值类型	格式化类型	显示格式字符串
DATE	EUR	%d.%m.%Y
DATE	INTERVAL	%Y%m%d
DATE	ISO	%Y-%m-%d
DATE	JIS	%Y-%m-%d
DATE	USA	%m.%d.%Y
TIME	EUR	%H.%i.%s
TIME	INTERVAL	%H%i%s
TIME	ISO	%H:%i:%s
TIME	JIS	%H:%i:%s
TIME	USA	%h:%i:%s %p
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERVAL	%Y%m%d%H%i%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	USA	%Y-%m-%d %H.%i.%s

例：

SELECT GET_FORMAT(DATE,'EUR'),GET_FORMAT(DATE,'USA');

返回结果:
%d.%m.%Y,%m.%d.%Y

条件判断函数

IF(expr,v1,v2)函数

IF(expr,v1,v2)中，若表达式expr式TRUE，则IF()的返回值为v1，否则返回v2

IFNULL(v1,v2)函数

IFNULL(v1,v2)中，若v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2

CASE函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2][ELSE rn] END
该函数表示，若expr值等于某个vn，则返回对应位置THEN后面的结果；若与所有值都不相等，则返回ELSE后面的rn

SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

返回结果:
two

系统信息函数

获取MySQL版本号、连接数和数据库名的函数

VERSION()返回指示MySQL服务器版本的字符串。

SELECT VERSION();
SELECT CONNECTION_ID();

SHOW PROCESSLIST;

返回列的含义:

列名	含义和用途
Id	用户登录MySQL时，系统分配的connection id
User	显示当前用户。如果不是root，这个命令就只显示用户权限范围内的SQL语句
Host	显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出现问题的用户
db	显示这个进程目前连接的是哪个数据库
Command	显示当前执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect)
Time	显示这个状态持续的时间，单位是秒
State	显示使用当前连接的SQL语句的状态，很重要的列，后续会有所有状态的描述，State知识语句执行中的某一状态。一个SQL语句(以查询为例)可能需要经过Copying to tmp table、Sorting result、Sending data
Info	显示这个SQL语句是判断问题语句的一个重要依据

获取用户名的函数

USER()、CURRENT_USER、SYSTEM_USER()这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。

加密函数

加密函数PASSWORD(str)

PASSWORD(str)用原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL

加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32为十六进制数字的二进制字符串形式返回，若参数为NULL，则返回NULL

加密函数ENCODE(str,pswd_str)

ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密，结果是一个和str长度相同的二进制字符串。

解密函数DECODE(crypt_str,pswd_str)

DECODE(crypt_str,pswd_str)使用pswd_str作为密码,解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串。

ENCODE与DECODE互为反函数

其他函数

FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回。若n位0则返回结果函数不含小数部分。
例:

SELECT FORMAT(12332.123456,4),FORMAT(12332.1,4),FORMAT(12332.2,0);

返回结果:
12332.1235,12332.1000,12332

不同进制的数字进行转换的函数

CONV(N,from_base,to_base)函数进行不同进制数间的转换。返回值为数值N的字符串表示，由from_base进制转化为to_base进制，若有任意一个参数为NULL，则返回值为NULL。自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为2，最大基数为36。
例:

SELECT CONV('a',16,2),CONV(15,10,2),CONV(15,10,8),CONV(15,10,16);

返回结果:
1010,1111,17,F

改变字符集的函数

CONVERT(…USING…)表示带有USING的CONVERT函数被用来在不同字符集之间转化数据
例:

SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
返回结果:
gbk,latin1

改变数据类型的函数

CAST(x, AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型，可转换的类型type有：BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED
例:

SELECT CAST(100 AS CHAR(2)),CONVERT('2018-01-01 12:12:12',TIME);

返回结果:
10,12:12:12