MySQL常用函数汇总
MySQL中内置的函数不仅可以在SELECT 中使用, 还可以在INSERT , UPDATE 和 DELETE 等语句中使用.
-
数学类函数:
CEIL(X), CEILIN(X)-----------进一取整
FLOOR(X)-------------舍掉小数部分
ROUND(x, y)----------四舍五入(保留y位小数)
MOD(x,y)---------x除以y以后的余数
ABS(x)-----------取x的绝对值
POWER(x, y), POW(x, y)---------幂运算(x的y次幂)
PI()------------圆周率
RAND()或者RAND(X)------------0~1之间的随机数, x相同时返回的结果相同.
SIGN(X)-------------得到数字符号
EXP(X)-------------计算e的x次方;
PI()----------返回圆周率, 默认7位
TRUNCATE(X, Y)---------返回x,保留小数点后y位
SQRT(x)----------返回非负数x的二次方根
LOG(x)----------返回x的基数为2的对数
LOG10(x)-----------返回x的基数为10的对数
RADIANS(x)----------将角度转换为弧度
DEGREES(x)-----------将弧度转化为角度
SIN(x)---------------返回x的正弦, 其中x在弧度中被给定
ASIN(x)----------------返回x的反正弦, 若x不在-1到1之间的值, 则返回NULL
(COS, ACOS, TAN, ATAN, COT等类似)
2. 字符串函数
CHAR_LENGTH(s)-----------返回字符串s的字符数
LENGTH(s)------------------返回s的长度, 单位为字节, 一个多字节字符算作多个字节
CONCAT(s1, s2, .....)----------------返回结果为连接参数(s1, s2, ....)产生的字符串, 若有一个参数为NULL,
则返回NULL.如果所有参数均为非二进制, 则返回结果为非二进制,
若有一个参数为二进制, 则返回结果为二进制字符串.一个数字参数转化为
与之相等的二进制字符串格式, 若要避免这种情况, 可以使用显式类型CAST,
如: SELECT CONCAT(CAST(int_col AS CHAR), char_col);
CONCAT_WS(x, s1, s2, .....)------------同CONCAT()函数, 但是每个字符串要直接加上x
INSERT(s1, x, len, s2)---------------将s2替换s1的x位置开始, 长度为len的字符串
UPPER(s), UCASE(s)------------将s转换为大写
LOWER(s), LCASE(s)-------------将s转换为小写
LEFT(s, n)------------返回字符串s从左边开始的n个字符
RIGHT(s, n)----------返回字符串s从右边开始的n个字符
LPAD(s1, len, s2)------------返回字符串s1, 其左边用s2填补到len字符长度,
假如s1的长度大于len, 则返回s1被缩短至len的长度
RPAD(s1, len, s2)-------------返回字符串s1, 其右边被字符串s2填补至len字符长度,
假如s1的长度大于len, 则返回值被缩短到与len字符相等的长度
LTRIM(s)----------返回字符串s, 开头空格字符被删除
RTRIM(s)----------返回字符串s, 结尾空格字符被删除
TRIM(s)----------去掉s开头和结尾的空格
TRIM(s1 FROM s)----------去掉s中开头和结尾处的字符串s1
REPEAT(s, n)-----------将字符串s重复n次
SPACE(n)------------返回个空格
REPLACE(s, s1, s2)---------用s2代替s中的s1
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的开始位置
INSTR(s, s1)-------------查找字符串s1在s中的位置, 返回首次出现的位置的索引值
REVERSE(s)----------将字符串s反序
ELT(n, s1, s2, .....)----------返回第n个字符串
EXPORT_SET(bits, on, off[, separator [, number_of_bits ] ] )---------
返回一个字符串, 生产规则如下: 针对bits的二进制格式, 如果其位为1, 则返回一个on值,
为0返回off值, 每个字符用separator进行分隔, 默认为" , " . number_of_bits
指定可用的位数, 默认为64位.
例如: 生成数字182的二进制(10110110)替换格式, 以" @ "作为分隔符, 设置有效位为6位,
其语句如下:
SELECT EXPORT_SET(182, 'Y', 'N', '@', 6) 其返回结果为: N@Y@Y@N@Y@Y
FIFLD(s, s1, s2, ............)-----------返回第一个与字符串s匹配的字符串的位置
FIND_IN_SET(s1, s2)----------返回在字符串s2中与s1相匹配的字符串的位置
MAKE_SET(x, s1, s2, ........)-------------按x的二进制数从s1, s2, .............sn中选取字符串
3. 日期和时间函数
CURDATE(), CURRENT_DATE()----------返回当前日期
CURTIME(), CURRENT_DATE()-----------返回当前时间
NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), SYSDATE(), LOCALTIESTAMP()--------返回当前日期和时间
UNIX_TIMESTAMP()----------以UNIX时间戳的形式返回当前时间,
UNIX_TIMESTAMP(d)---------将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d)----------把UNIX时间戳转化为普通时间格式返回
UTC_DATE()-----------返回UTC(国际协调时间)日期
UTC_TIME()------------返回UTC时间
MONTH(d)------------返回日期d中的月份值, 范围为1-12
MONTHNAME(d)--------返回日期d中的月份名称, 如: January, February.....
DAYNAME(d)-----------返回日期d是星期几, 如Monday, Tuesday.....
DAYOFWEEK(d)-----------返回日期d是星期几, 1表示星期日, 2表示星期一......
WEEKDAY(d)-------------返回日期d是星期几, 0表示星期一, 1表示星期二......
WEEK(d)----------计算日期d是本年的第几个星期, 范围是0~53
WEEKOFYEAR(d)-----------计算日期 d 是本年的第几个星期，范围是 1~53
DAYOFYEAR(d)-----------计算日期 d 是本年的第几天
DAYOFMONTH(d)-----------计算日期 d 是本月的第几天
YEAR(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, 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(d, INTERVAL expr type)-------计算起始日期d加上一段时间(expr)之后的日期, type可以为: day, month.....
例如:
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
DATE_ADD(d，INTERVAL expr type)----------计算起始日期 d 加上一个时间段后的日期
例如:
SELECT ADDDATE('2011-11-11 11:11:11',1) -> 2011-11-12 11:11:11 (默认是天) SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) -> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
SUBDATE(d,n)---------日期 d 减去 n 天后的日期
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n)-------时间 t 减去 n 秒的时间
DATE_FORMAT(d,f)-----按表达式 f的要求显示日期 d
例如:
SELECT 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
例如:
SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
GET_FORMAT(type, s)---------根据字符串s获取type类型数据的显示格式
4. 条件判断函数
IF(expr, v1, v2)----------expr成立, 执行v1, 否则v2.
IFNULL(v1, v2)---------如果v1不为空, 则显示v1的值, 否则显示v2的值
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1,
如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。
例如:
SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0
5. 系统信息函数
VERSION()--------返回数据库的版本号
CONNECTION_ID()--------返回服务器的连接数
DATABASE(), SCHEMA()----------返回当前数据库名
USER(), SYSTEM_USER(), SESSION_USER()--------返回当前用户
CURRENT_USER(), CURRENT_USER---------返回当前用户
CHARSET(s)-------获取字符串s的字符集
COLLATION(s)---------获取字符串s的字符的排列方式
LAST_INSERT_ID()--------返回最近生成的 AUTO_INCREMENT 值
6. 加密函数
PASSWORD(str)------对字符串str进行加密, 经此加密后的数据是不可逆的. 其经常用于对用户注册的密码进行加密处理
MD5(str)----------对str进行加密, 经常用于对普通数据进行加密
ENCODE(str, pswd_str)---------使用字符串pwsd_str来加密字符串str, 加密的结果是一个二进制数,
必须使用BLOB类型的字段来保护它
DECODE(crypt_str, pswd_str)-------使用字符串pswd_str来为crypt_str解密, crypt_str 是通过
ENCODE(str, pswd_str)加密后的二进制数据字符串pswd_str应该与加密时的相同.
7. 其他函数
FORMAT(x, n)-------函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。
ASCII(s)------返回字符串 s 的第一个字符的 ASCII 码。
BIN(x)-------返回 x 的二进制编码
HEX(x)------返回x的十六进制编码
OTC(x)------返回x的八进制编码
CONV(x,f1,f2)----------将x从f1进制数变成f2进制数
INET_ATON(IP)----------将IP地址转化为数字表示
INET_NTOA(N)-----------将数字N转化为IP的形式
GET_LOCT(name, time)-------定义一个名为name, 持续时长为time秒的锁, 锁定成功返回1, 如果尝试超时返回0,
遇到错误返回NULL
RELEASE_LOCK(name)-------解除名为name的锁, 成功1, 超时0, 失败NULL
IS_FREE_LOCK(name)--------判断是否使用name的锁, 使用0, 否则返回1
BENCHMARK(count, expr)-------将表达式expr重复执行count次, 然后返回执行的时间, 该表达式可以用来测试
MySQL处理表达式的速度
CONVERT(s USING cs)---------函数将字符串 s 的字符集变成 cs
例如:
SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk
CAST(x AS type)------------转换数据类型
例如:
字符串日期转换为日期： SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
