mysql函数整理(字符串函数,日期函数,数字函数)

字符串函数

函数	描述	使用
CHAR_LENGTH(s)	返回字符串s的长度	CHAR_LENGTH(123)=>3
CHARACTER_LENGTH(s)	同上
CONCAT(s1,s2…sn)	拼接字符串,如果有null返回null	CONCAT(‘a’,‘b’,‘c’)=>‘abc’ CONCAT(‘a’,‘b’,null)=>null
CONCAT_WS(sep, s1,s2…sn)	指定分隔符(sep)拼接字符串,忽略字符串为null	CONCAT_WS(’/’,‘a’,‘b’,‘c’) =>‘a/b/c’ CONCAT_WS(’/’,null,‘b’,‘c’) =>‘b/c’
GROUP_CONCAT(s1,s2…sn ORDER BY s SEPARATOR sep)	group by id 时,id相同的拼接字符串	select GROUP_CONCAT(name order by name desc SEPARATOR ‘’) group by id =>如果id=1的有三行,name分别为’a’,‘b’,‘c’ 则返回’cba’(order by 排序了) SEPARATOR不指定默认为’,’ order by 跟SEPARATOR都可以不指定
FIELD(s,s1,s2,s3…)	返回s在s1-sn的位置	FIELD(‘a’,‘abc’,‘a’,‘a’) =>返回2,扫描到第一个符合的就返回
FIND_IN_SET(s1,s2)	返回s1在s2中的位置 s2是一个字符串并且元素以’,'分隔	FIND_IN_SET(‘a’,‘c,a,b’)=>2 select * from test where FIND_IN_SET(name,‘c,a,b’) 函数返回0(不包含)为假返回其他数字(包含)为真
INSERT(s1,i,len,s2)	将字符串S1从i个元素开始用s2的字符串替换len个字符	INSERT(‘abcdefg’,3,4,‘12345’) =>‘ab12345g’
LOCATE(s1,s2)	s1在s2中的位置	LOCATE(‘e’,‘abcd’)=>0 LOCATE(‘d’,‘abcd’)=>4
LCASE(s)/LOWER(s)	将字符串小写	略
UPPER(s)/ UCASE(s)	将字符串大写	略
LEFT(S,i)	从左边开始的第i个位置截取S字符串	LEFT(‘abc’,2)=>‘ab’
RIGHT(S,i)	从右边开始的第i个位置截取S字符串	RIGHT(‘abc’,2)=>‘bc’
LTRIM(s)	去除字符串开始处的空格	LTRIM(’ abc’)=>‘abc’
RTRIM(s)	去除字符串结尾处的空格	RTRIM('abc ')=>‘abc’
TRIM(s)	去除字符串前后的空格或者指定字符	TRIM(’ abc ')=>‘abc’ ;TRIM(‘a’ FROM ‘abcada’)=>‘bcad’
SUBSTRING(s,i,n)	从字符串s的i位置,截取长度为n的字符串同mid函数	SUBSTRING(‘abcdefg’,3,2)=>‘cd’
POSITION(s IN s1)	s在s1中的开始位置	POSITION(‘e’ IN ‘abcdefg’)=>5
format(i,n)	将数字格式化后返回字符串(会有千分位的逗号)	select FORMAT(12341234.5678,2)=>‘12,341,234.57’
SUBSTRING_INDEX(s, sep, i)	s字符串中以sep分隔符划分,返回第i个分隔符左边的字符串,如果i为负数就取右边	SUBSTRING_INDEX(‘a,b,c,d’, ‘,’, 2)=>a,b SUBSTRING_INDEX(‘a,b,c,d’, ‘,’, -2)=>c,d
LPAD(str,len,padstr)	当str长度不足len时,以padstr左填充,查过则截取lenth	LPAD(‘abc’,5,‘1’)=>‘11abc’ LPAD(‘123456’,5,‘1’)=>‘12345’
RPAD(str,len,padstr)	当str长度不足len时,以padstr右填充,查过则截取len	RPAD(‘abc’,5,‘1’)=>‘abc11’ RPAD(‘123456’,5,‘1’)=>‘12345’

数字函数

函数	描述	使用
ABS(i)	返回i的绝对值	ABS(-1)=>1
CEIL(i)	返回大于等于i的最小整数	CEIl(1.2)=>2
i1 DIV I2	i1整除i2	5 DIV 2 =>2
FLOOR(i)	小于或者等于i的最大整数	FLOOR(1.2)=>1
LOG10(i)/LOG2(i)	返回以10/2为底的对数	LOG10(100)=>2 LOG2(8)=>3
MOD(i1,i2)	返回i1除以i2的余数	MOD(10,6)=>4
POW(i,n)/POWER(i,n)	返回i的n次方	POW(10,2)=>100
SQRT(i)	返回i的平方根	SQRT(100)=>10
RAND()	返回0-1之间的随机数	RAND()=>0.6542808848007404
ROUND(i,n)	返回i保留n位小数,会四舍五入	ROUND(1.23456,4) =>1.2346
TRUNCATE(x,y)	返回保留n为小数,不会四舍五入	TRUNCATE(1.23456,4)=>1.2345

日期函数

函数	描述	使用
CURDATE()/CURRENT_DATE()	返回当前日期	CURDATE() => 2020-09-10
CURRENT_TIME()/CURTIME()	返回当前时间	CURTIME()=> 14:15:39
CURRENT_TIMESTAMP()	返回当前日期加时间	CURRENT_TIMESTAMP() =>2020-09-10 14:15:39
NOW() /SYSDATE()	返回当前日期加时间	NOW() =>2020-09-10 14:15:39
DATE(s/d)	根据当前字符串或者日期获取日期	DATE(‘2020-08-08 09:09:09’)=> 2020-08-08
ADDDATE(d,n)	d的日期加上n天	ADDDATE(‘2020-08-08 01:01:01’,INTERVAL 1 month)=>2020-09-08 01:01:01 如果不加INTERVAL XX XX 则加上n天
DATE_ADD(d，INTERVAL n type)	d时间加上n年/月/日/时/分钟/秒	DATE_ADD(‘2020-08-08 01:01:01’,INTERVAL 1 MONTH)=>2020-09-08 01:01:01 不加INTERVAL表达式会报错
ADDTIME(d,n)	d时间加上n秒等同于DATE_ADD(d,INTERVAL 1 SECOND)
DATEDIFF(d1,d2)	d1-d2时间相差天数	DATEDIFF(‘2020-08-08 01:00:00’,‘2020-08-09 00:00:00’)=>-1
DATE_SUB(d,INTERVAL expr type)	时间d减去INTERVAL间隔	DATE_SUB(‘2020-08-08 01:01:01’,INTERVAL 1 DAY)
YEAR(d)	返回时间的年	YEAR(‘2020-08-09’)=>2020
MONTH(d)	返回时间的月	MONTH(‘2020-08-09’)=>8
DAY(d)	返回时间的日	DAY(‘2020-08-09’)=>9
HOUR(d)	返回时间的时	HOUR(‘2020-08-09 10:11:12’)=>10
MINUTE(d)	返回时间的分钟	MINUTE(‘2020-08-09 10:11:12’)=>11
SECOND(d)	返回时间的秒	SECOND(‘2020-08-09 10:11:12’) =>12
DAYNAME(d)	返回星期几	DAYNAME(‘2020-08-09 10:11:12’)=>Sunday
DAYOFMONTH(d)	返回月份的第几天	DAYOFMONTH(‘2020-08-09 10:11:12’)=>9
DAYOFWEEK(d)	一个星期中的第几天(周日是第一天)	DAYOFWEEK(‘2020-08-09 10:11:12’)=>1
DAYOFYEAR(d)	一年中的第几天	DAYOFYEAR(‘2020-08-09 10:11:12’)=>222
WEEKOFYEAR(d)	一年中的第几个星期	WEEKOFYEAR(‘2020-08-09 10:11:12’)=>32
EXTRACT(type FROM d)	返回时间中的type	EXTRACT(year FROM ‘2020-08-09 10:11:12’)=>2020
LAST_DAY(d)	返回日期所属月的最后一天	LAST_DAY(‘2020-08-09 10:11:12’)=>2020-08-09
MAKEDATE(year, n)	给点年,计算第n天日期	MAKEDATE(2020, 1)=>2020-01-01
MONTHNAME(d)	返回月份名称	MONTHNAME(‘2020-08-09 10:11:12’)=>August
QUARTER(d)	返回是第几季度	QUARTER(‘2020-08-09 10:11:12’)=>3
SEC_TO_TIME(s)	s为秒数返回对应秒数的时间	SEC_TO_TIME(1)=>00:00:01
TIME_TO_SEC(t)	返回时间的秒数	TIME_TO_SEC(‘2020-08-09 10:11:12’) =>36672
FROM_UNIXTIME(i) /	FROM_UNIXTIME(i,format) 给定时间戳返回时间	FROM_UNIXTIME(1523434234)=>2018-04-11 16:10:34
UNIX_TIMESTAMP(d)	获取时间戳	UNIX_TIMESTAMP(‘2020-01-01’) =>1577808000
DATE_FORMAT(d,formate)	将日期按照format格式转化成字符串	DATE_FORMAT(now(),’%Y-%m-%d %H:%i:%s’)=>‘2020-09-10 15:11:03’
STR_TO_DATE(s, format)	将字符串转化成日期	STR_TO_DATE(‘2020-09-10 15:11:03’,’%Y-%m-%d’)=>2020-09-10

format

以下使用中都是 select DATE_FORMAT(‘2020-01-02 03:04:05’,符号) 的结果

符号	描述	使用
a%	英文星期名缩写	Thu
%b	英文月名缩写	Jan
%c	月份值	1
%D	英文天值	2nd
%d	天值(00-31)	02
%e	天值(0-31)	2
%f	微秒值	000000
%H	小时值(24小时制)	03
%h	小时值(12小时制)	03
%I	小时值(12小时制)	同上
%i	分钟数	04
%j	年的天(001,366)	002
%k	同%H
%l	同%h
%M	月英文名	January
%m	月值(01-12)	01
%p	AM或者PM	AM
%r	略	03:04:05 AM
%S	秒	05
%s	同上
%T	获取时间	03:04:05
%U	一年的第几周(第一周为00)	00 如果是’2020-01-07’=>01
%u	一年的第几周(第一周为01)	01
%W	英文星期名	Thursday
%w	一周的第几天(周日为0)	4
%V	一年的第几周(第一周为00) 与%X一起使用	00
%v	年的第几周(第一周为01) 与%X一起使用
%X	年四位与%X一起使用	201952 2019年的第52周
%x	年两位位与%x一起使用	202001 2020年第1周
%Y	年(四位)	2020
%y	年(两位)	20

其他函数

函数	描述	使用
CAST(xx as type)	将某个类型转化为type类型	cast(id as char) 将id转化为字符串类型
COALESCE(expr1, expr2, …, expr_n)	返回第一个非空	COALESCE(null,null,1)=>1
DATABASE()	返回库名
USER()	返回登陆用户
VERSION()	返回mysql版本号
IF(expr,v1,v2)	expr true v1 false v2	IF(1=1,1,2)=>1 IF(1!=1,1,2)=>2
IFNULL(v1,v2)	v1是null 返回 v2 v1不是null返回v1	IFNULL(null,1)=>1 IFNULL(2,1)=>2
ISNULL(expr)	是否为null 是反1 不是反0	ISNULL(1)=>0
NULLIF(expr1, expr2)	expr1与expr2相等返回null 不等返回expr1	NULLIF(‘a’, ‘a’)=>null NULLIF(‘a’, ‘b’)=>‘a’
LAST_INSERT_ID()	最近生成的自增值