MySQL函数（常用汇总）

来源：我的博客站 OceanicKang |《MySQL函数（常用汇总）》

一、数学函数

数学函数主要用于处理数字，包括整形、浮点数等。

函数	说明		返回值
ABS(x)	返回x的绝对值	ABS(-1)	1
CEIL(x), CEILING(x)	返回大于或等于x的最小整数	CEIL(1.5)	2
FLOOR(x)	返回小于或等于x的最大整数	FLOOR(1.5)	1
RAND()	返回0->1的随机数
RAND(x)	返回0->x的随机数，x值相同时返回的随机数相同
SIGN(x)	返回x的符号，x是负数、0、正数分别返回-1、0、1	SIGN(-10)	-1
PI()	返回圆周率(3.141593)		3.141593
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值(与ROUND最大的区别是不会进行四舍五入)	TRUNCATE(1.23456, 3)	1.234
ROUND(x)	返回离x最近的整数	ROUND(1.23456)	1
ROUND(x,y)	保留小数点后y位的值，但截断时要进行四舍五入	ROUND(1.23456, 3)	1.235
POW(x,y), POWER(x,y)	返回x的y次方	POW(2, 3)	8
SQRT(x)	返回x的平方根	SQRT(25)	5
EXP(x)	返回e的x次方	EXP(3)	20.085536923188
MOD(x,y)	返回x除以y以后的余数	MOD(5, 2)	1
LOG(x)	返回自然对数(以e为底的对数)	LOG(20.085536923188 )	3
LOG10(x)	返回以10为底的对数	LOG10(100)	2
RADIANS(x)	将角度转换为弧度	RADIANS(180)	3.1415926535898
DEGREES(x)	将弧度转换为角度	DEGREES(3.1415926535898 )	180
SIN(x)	求正弦值(参数是弧度)	SIN(RADIANS(30))	0.5
ASIN(x)	求反正弦值(参数是弧度)
COS(x)	求余弦值(参数是弧度)
ACOS(x)	求反余弦值(参数是弧度)
TAN(x)	求正切值(参数是弧度)
ATAN(x), ATAN2(x)	求反正切值(参数是弧度)
COT(x)	求余切值(参数是弧度)

二、字符串函数

字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

函数	说明		返回值
CHAR_LENGTH(s)	返回字符串s的字符数	CHAR_LENGTH(‘你好123’)	5
LENGTH(s)	返回字符串s的长度	LENGTH(‘你好123’)	9
CONCAT(s1, s2,…)	将字符串s1,s2等多个字符串合并为一个字符串	CONCAT(‘12’, ‘34’)	1234
CONCAT_WS(x, s1, s2,…)	同CONCAT(s1,s2,…)函数，但是每个字符串直接要加上x	CONCAT_WS(’@’, ‘12’, ‘34’)	12@34
INSERT(s1, x, len, s2)	将字符串s2替换s1的x位置开始长度为len的字符串	INSERT(‘12345’, 1, 3, ‘abc’)	abc45
UPPER(s), UCAASE(s)	将字符串s的所有字母变成大写字母	UPPER(‘abc’)	ABC
LOWER(s), LCASE(s)	将字符串s的所有字母变成小写字母	LOWER(‘ABC’)	abc
LEFT(s, n)	返回字符串s的前n个字符	LEFT(‘abcde’, 2)	ab
RIGHT(s, n)	返回字符串s的后n个字符	SRIGHT(‘abcde’, 2)	de
LPAD(s1, len, s2)	字符串s2来填充s1的开始处，使字符串长度达到len	LPAD(‘abc’, 5, ‘xx’)	xxabc
RPAD(s1,len,s2)	字符串s2来填充s1的结尾处，使字符串的长度达到len	RPAD(‘abc’, 5, ‘xx’)	abcxx
LTRIM(s)	去掉字符串s开始处的空格
RTRIM(s)	去掉字符串s结尾处的空格
TRIM(s)	去掉字符串s开始和结尾处的空格
TRIM(s1 FROM s)	去掉字符串s中开始处和结尾处的字符串s1	TRIM(’@’ FROM ‘@@abc@@’)	abc
REPEAT(s, n)	将字符串s重复n次	REPEAT(‘ab’, 3)	ababab
SPACE(n)	返回n个空格
REPLACE(s, s1, s2)	将字符串s2替代字符串s中的字符串s1	REPLACE(‘abc’, ‘a’, ‘x’)	xbc
STRCMP(s1,s2)	比较字符串s1和s2
SUBSTRING(s, n, len)	获取从字符串s中的第n个位置开始长度为len的字符串
MID(s, n, len)	同SUBSTRING(s,n,len)
LOCATE(s1,s), POSITION(s1 IN s)	从字符串s中获取s1的开始位置	LOCATE(‘b’, ‘abc’)	2
INSTR(s, s1)	从字符串s中获取s1的开始位置	INSTR(‘abc’, ‘b’)	2
REVERSE(s)	将字符串s的顺序反过来	REVERSE(‘abc’)	cba
ELT(n, s1, s2,…)	返回第n个字符串	ELT(2, ‘a’, ‘b’, ‘c’)	b
EXPORT_SET(x,s1,s2)	返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用	EXPORT_SET(5, ‘Y’, ‘N’, ‘,’, 4)	Y,N,Y,N
FIELD(s, s1, s2…)	返回第一个与字符串s匹配的字符串位置	FIELD(‘c’, ‘a’, ‘b’, ‘c’)	3
FIND_IN_SET(s1, s2)	返回在字符串s2中与s1匹配的字符串的位置
SUBSTRING_INDEX(str, delim, count)	返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回第count个字符左边的字符串。如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。	SUBSTRING_INDEX(‘a*b’, ‘*’, 1)	a
LOAD_FILE(file_name)	读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

三、日期时间函数

MySQL的日期和时间函数主要用于处理日期时间。

函数	说明		返回值
CURDATE(), CURRENT_DATE()	返回当前日期
CURTIME(), CURRENT_TIME()	返回当前时间
NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), SYSDATE(), LOCALTIMESTAMP()	返回当前日期和时间
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(d)	将时间d以UNIX时间戳的形式返回	UNIX_TIMESTAMP (‘2011-11-11 11:11:11’)	1320981071
FROM_UNIXTIME(d)	将UNIX时间戳的时间转换为普通格式的时间	FROM_UNIXTIME (1320981071)	2011-11-11 11:11:11
UTC_DATE()	返回UTC日期
UTC_TIME()	返回UTC时间
MONTH(d)	返回日期d中的月份值		范围是1->12
MONTHNAME(d)	返回日期当中的月份名称		如Janyary
DAYNAME(d)	返回日期d是星期几		如Monday,Tuesday
DAYOFWEEK(d)	日期d今天是星期几		1星期日，2星期一
WEEKDAY(d)	日期d今天是星期几		0表示星期一，1表示星期二
WEEK(d)， WEEKOFYEAR(d)	计算日期d是本年的第几个星期，		范围是0->53
DAYOFYEAR(d)	计算日期d是本年的第几天
DAYOFMONTH(d)	计算日期d是本月的第几天
QUARTER(d)	返回日期d是第几季节		返回1->4
HOUR(t)	返回t中的小时值
MINUTE(t)	返回t中的分钟值
SECOND(t)	返回t中的秒钟值
EXTRACT(type FROM d)	从日期d中获取指定的值，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
TIME_TO_SEC(t)	将时间t转换为秒
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式
TO_DAYS(d)	计算日期d距离0000年1月1日的天数
FROM_DAYS(n)	计算从0000年1月1日开始n天后的日期
DATEDIFF(d1, d2)	计算日期d1->d2之间相隔的天数
ADDDATE(d, n)	计算起始日期d加上n天的日期	ADDDATE (‘2011-11-11 11:11:11’, 1)	2011-11-12 11:11:11
ADDDATE(d，INTERVAL expr type)	计算起始日期d加上一个时间段后的日期	ADDDATE (‘2011-11-11 11:11:11’, INTERVAL 5 MINUTE)	2011-11-11 11:16:11
DATE_ADD(d, INTERVAL expr type)	同上
SUBDATE(d, n)	日期d减去n天后的日期
SUBDATE(d, INTERVAL expr type)	日期d减去一个时间段后的日期
ADDTIME(t, n)	时间t加上n秒的时间
SUBTIME(t, n)	时间t减去n秒的时间
DATE_FORMAT(d, f)	按表达式f的要求显示日期d	DATE_FORMAT (‘2011-11-11 11:11:11’, ‘%Y-%m-%d %r’)	2011-11-11 11:11:11 AM
TIME_FORMAT(t, f)	按表达式f的要求显示时间t	TIME_FORMAT (‘11:11:11’, ‘%r’)	11:11:11 AM
GET_FORMAT(type, s)	获得国家地区时间格式函数	GET_FORMAT (date, ‘usa’)	%m.%d.%Y

四、条件判断函数

1、IF(expr, v1, v2)函数

如果表达式expr成立，返回结果v1；否则，返回结果v2

> SELECT IF(1 > 0, 'YES', 'NO')
# return: YES

2、IFNULL(v1, v2)函数

如果v1的值不为NULL，则返回v1，否则返回v2

> SELECT IFNULL(null, 'Hello Word')
# return: Hello Word

3、CASE

> SELECT CASE
		WHEN e1
		THEN v1
		WHEN e2
		THEN v2
		...
		ELSE vn
	 END

CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1；如果e2成立，则返回v2。当有一个成立，后面的都不执行；当都不成立，则返回vn。

> SELECT CASE expr
		WHEN e1 THEN v1
		WHEN e2 THEN v2
		...
		ELSE vn
	 END

如果表达式expr的值等于e1，返回v1；如果等于e2，则返回e2.否则返回vn。

五、系统信息函数

系统信息函数用来查询MySQL数据库的系统信息

函数	说明
VERSION()	返回数据库的版本号
CONNECTION_ID()	返回服务器的连接数
DATABASE()、SCHEMA()	返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER()	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLATION(str)	返回字符串str的字符排列方式
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT值

六、加密函数

加密函数是MySQL用来对数据进行加密的函数

1、PASSWORD(str)

该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密

> SELECT PASSWORD('123')
# return: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

2、MD5

MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密

> SELECT MD5('123')
# return: 202cb962ac59075b964b07152d234b70

3、ENCODE(str, pswd_str) 与 DECODE(crypt_str, pswd_str)

ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密

> SELECT ENCODE('123', 'xxoo')
# return: ;vx
> SELECT DECODE(';vx','xxoo')
# return: 123

七、其他函数

1、格式化函数FORMAT(x, n)

FORMAT(x, n)函数可以将数字x进行格式化，将x保留到小数点后n位

> SELECT FORMAT(3.1215926, 3)
# return: 3.142

2、不同进制的数字进行转换

• ASCII(s)返回字符串s的第一个字符的ASCII码；
• BIN(x)返回x的二进制编码；
• HEX(x)返回x的十六进制编码；
• OCT(x)返回x的八进制编码；
• CONV(x, f1, f2)返回f1进制数变成f2进制数；

3、IP地址与数字相互转换的函数

• INET_ATON(IP)函数可以将IP地址转换为数字表示；IP值需要加上引号；
• INET_NTOA(n)函数可以将数字n转换成IP形式

> SELECT INET_ATON('192.168.0.1')
# return: 3232235521
> SELECT INET_NTOA(3232235521)
# return: 192.168.0.1

4、加锁函数和解锁函数

• GET_LOCK(name, time)函数定义一个名称为name、持续时间长度为time（单位秒）的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL；
• RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；
• IS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0；否则，返回1；

> SELECT GET_LOCK('MySQL',10)
# return: 1    (持续10秒)
> SELECT IS_FREE_LOCK('MySQL')
# return: 1
> SELECT RELEASE_LOCK('MySQL')
# return: 1

5、重复执行指定操作的函数

BENCHMARK(count, expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。

> SELECT BENCHMARK(10000,NOW())
# return: 0 (返回系统时间1万次)

6、改变字符集的函数

CONVERT(s USING cs)函数将字符串s的字符集编程cs

> SELECT CHARSET('ABC')
# return: utf-8
> SELECT CHARSET(CONVERT('ABC' USING gbk))
# return: gbk

7、转换数据类型

• CAST(x AS type)
• CONVERT(x, type)
  这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、DECIMAL、SIGNED、UNSIGNED有效。

> SELECT CAST('123' AS UNSIGNED INTEGER) + 1
# return: 124
> SELECT '123' + 1
# return: 124 (其实MySQL能默认转换)
> SELECT CAST(NOW() AS DATE)
# return: 2014-12-18