函数表示对输入参数值返回一个具有特定关系的值MySQL提供大量丰富的函数在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理数据库功能可以变得更加强大更加灵活地满足不同需求。各类函数从功能主要分为以下几类数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数。

5.1、数学函数

绝对值函数ABS(x)

mysql>SELECTABS(2),ABS(-3.3),ABS(-33);+--------+-----------+----------+|ABS(2)|ABS(-3.3)|ABS(-33)|+--------+-----------+----------+|2|3.3|33|+--------+-----------+----------+1rowinset(0.00sec)

返回圆周率函数PI()

mysql>SELECTPI();+----------+|PI()|+----------+|3.141593|+----------+1rowinset(0.00sec)

平方根函数SQRT(x)

mysql>SELECTSQRT(9),SQRT(30),SQRT(-30);+---------+-------------------+-----------+|SQRT(9)|SQRT(30)|SQRT(-30)|+---------+-------------------+-----------+|3|5.477225575051661|NULL|+---------+-------------------+-----------+1rowinset(0.00sec)

求余函数MOD(x,y)

mysql>SELECTMOD(31,8),MOD(234,8),MOD(45.5,6);+-----------+------------+-------------+|MOD(31,8)|MOD(234,8)|MOD(45.5,6)|+-----------+------------+-------------+|7|2|3.5|+-----------+------------+-------------+1rowinset(0.00sec)

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

mysql>SELECTCEIL(-3.35),CEILING(3.35);#返回不小于x的最小整数+-------------+---------------+|CEIL(-3.35)|CEILING(3.35)|+-------------+---------------+|-3|4|+-------------+---------------+1rowinset(0.00sec)mysql>SELECTFLOOR(-3.35),FLOOR(3.35);#返回不大于x的最大整数+--------------+-------------+|FLOOR(-3.35)|FLOOR(3.35)|+--------------+-------------+|-4|3|+--------------+-------------+1rowinset(0.00sec)

获取随机数函数RAND()、RAND(x)

mysql>SELECTRAND(),RAND(),RAND();#返回0-1之间的随机数+--------------------+--------------------+--------------------+|RAND()|RAND()|RAND()|+--------------------+--------------------+--------------------+|0.8727586752481373|0.6464434700519252|0.6139413552488585|+--------------------+--------------------+--------------------+1rowinset(0.00sec)mysql>SELECTRAND(10),RAND(10),RAND(11);#x用作种子值用来产生重复序列+--------------------+--------------------+-------------------+|RAND(10)|RAND(10)|RAND(11)|+--------------------+--------------------+-------------------+|0.6570515219653505|0.6570515219653505|0.907234631392392|+--------------------+--------------------+-------------------+1rowinset(0.00sec)

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

mysql>SELECTROUND(-1.14),ROUND(1.67),ROUND(1.15);#返回最接近x的整数对x值进行四舍五入+--------------+-------------+-------------+|ROUND(-1.14)|ROUND(1.67)|ROUND(1.15)|+--------------+-------------+-------------+|-1|2|1|+--------------+-------------+-------------+1rowinset(0.00sec)mysql>SELECTROUND(-1.14,1),ROUND(1.67,0),ROUND(1.15,-1);+----------------+---------------+----------------+|ROUND(-1.14,1)|ROUND(1.67,0)|ROUND(1.15,-1)|+----------------+---------------+----------------+|-1.1|2|0|+----------------+---------------+----------------+1rowinset(0.00sec)#返回最接近x的整数值保留小数点后y位y为负数将保留x小数点左边y位mysql>SELECTTRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0);+------------------+------------------+------------------+|TRUNCATE(1.31,1)|TRUNCATE(1.99,1)|TRUNCATE(1.99,0)|+------------------+------------------+------------------+|1.3|1.9|1|+------------------+------------------+------------------+1rowinset(0.00sec)#返回被舍去至小数点后y位的数字x

符号函数SIGN(x)

mysql>SELECTSIGN(-21),SIGN(0),SIGN(21);+-----------+---------+----------+|SIGN(-21)|SIGN(0)|SIGN(21)|+-----------+---------+----------+|-1|0|1|+-----------+---------+----------+1rowinset(0.00sec)

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

mysql>SELECTPOW(2,2),POWER(2,2),POW(2,-2),POWER(2,-2);#返回x的y次方+----------+------------+-----------+-------------+|POW(2,2)|POWER(2,2)|POW(2,-2)|POWER(2,-2)|+----------+------------+-----------+-------------+|4|4|0.25|0.25|+----------+------------+-----------+-------------+1rowinset(0.04sec)mysql>SELECTEXP(3),EXP(-3),EXP(0);#返回以e为底的x次方+--------------------+----------------------+--------+|EXP(3)|EXP(-3)|EXP(0)|+--------------------+----------------------+--------+|20.085536923187668|0.049787068367863944|1|+--------------------+----------------------+--------+1rowinset(0.02sec)

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

mysql>SELECTLOG(3),LOG(-3);#返回x的自然对数x相对于基数e的对数+--------------------+---------+|LOG(3)|LOG(-3)|+--------------------+---------+|1.0986122886681098|NULL|+--------------------+---------+1rowinset(0.00sec)mysql>SELECTLOG10(2),LOG10(100),LOG10(-100);#返回x相对于基数10的对数+--------------------+------------+-------------+|LOG10(2)|LOG10(100)|LOG10(-100)|+--------------------+------------+-------------+|0.3010299956639812|2|NULL|+--------------------+------------+-------------+1rowinset(0.00sec)

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

mysql>SELECTRADIANS(90),RADIANS(180);#角度转化为弧度+--------------------+-------------------+|RADIANS(90)|RADIANS(180)|+--------------------+-------------------+|1.5707963267948966|3.141592653589793|+--------------------+-------------------+1rowinset(0.00sec)mysql>SELECTDEGREES(PI()),DEGREES(PI()/2);#弧度转化为角度+---------------+-----------------+|DEGREES(PI())|DEGREES(PI()/2)|+---------------+-----------------+|180|90|+---------------+-----------------+1rowinset(0.00sec)

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

mysql>SELECTSIN(1),ROUND(SIN(PI()));+--------------------+------------------+|SIN(1)|ROUND(SIN(PI()))|+--------------------+------------------+|0.8414709848078965|0|+--------------------+------------------+1rowinset(0.00sec)mysql>SELECTASIN(3),ASIN(0.84);+---------+--------------------+|ASIN(3)|ASIN(0.84)|+---------+--------------------+|NULL|0.9972832223717998|+---------+--------------------+1rowinset(0.00sec)

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

mysql>SELECTCOS(1),COS(PI()),COS(0);+--------------------+-----------+--------+|COS(1)|COS(PI())|COS(0)|+--------------------+-----------+--------+|0.5403023058681398|-1|1|+--------------------+-----------+--------+1rowinset(0.00sec)mysql>SELECTACOS(1),ACOS(0.54),ACOS(0);+---------+--------------------+--------------------+|ACOS(1)|ACOS(0.54)|ACOS(0)|+---------+--------------------+--------------------+|0|1.0003592173949747|1.5707963267948966|+---------+--------------------+--------------------+1rowinset(0.00sec)

正切函数TAN(X)、反正切函数ATAN(x)和余切函数COT(x)

mysql>SELECTTAN(0.3),TAN(PI()/4);+---------------------+--------------------+|TAN(0.3)|TAN(PI()/4)|+---------------------+--------------------+|0.30933624960962325|0.9999999999999999|+---------------------+--------------------+1rowinset(0.00sec)mysql>SELECTATAN(1),ATAN(0.393);+--------------------+---------------------+|ATAN(1)|ATAN(0.393)|+--------------------+---------------------+|0.7853981633974483|0.37445736689641174|+--------------------+---------------------+1rowinset(0.00sec)mysql>SELECTCOT(0.3),1/TAN(0.3);+--------------------+--------------------+|COT(0.3)|1/TAN(0.3)|+--------------------+--------------------+|3.2327281437658275|3.2327281437658275|+--------------------+--------------------+1rowinset(0.00sec)

5.2、字符串函数

计算字符串字符数CHAR_LENGTH(str)

mysql>SELECTCHAR_LENGTH('DATE'),CHAR_LENGTH('egg');+---------------------+--------------------+|CHAR_LENGTH('DATE')|CHAR_LENGTH('egg')|+---------------------+--------------------+|4|3|+---------------------+--------------------+1rowinset(0.00sec)

计算字符串字节长度LENGTH(str)

mysql>SELECTLENGTH('DATE'),LENGTH('egg');+----------------+---------------+|LENGTH('DATE')|LENGTH('egg')|+----------------+---------------+|4|3|+----------------+---------------+1rowinset(0.00sec)

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

mysql>SELECTCONCAT('MySQL','5.6'),CONCAT('MySQL','5.6',NULL);+------------------------+-----------------------------+|CONCAT('MySQL','5.6')|CONCAT('MySQL','5.6',NULL)|+------------------------+-----------------------------+|MySQL5.6|NULL|+------------------------+-----------------------------+1rowinset(0.00sec)#连接多个字符串出现NULL时返回值为NULLmysql>SELECTCONCAT_WS('-','MySQL','5.6'),CONCAT_WS('-','MySQL','5.6',NULL);+-------------------------------+------------------------------------+|CONCAT_WS('-','MySQL','5.6')|CONCAT_WS('-','MySQL','5.6',NULL)|+-------------------------------+------------------------------------+|MySQL-5.6|MySQL-5.6|+-------------------------------+------------------------------------+1rowinset(0.00sec)#以x为连接符连接多个字符串出现NULL时忽略NULL

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

mysql>SELECTINSERT('Quest',2,4,'What')AScol1,->INSERT('Quest',-1,4,'What')AScol2,->INSERT('Quest',3,100,'What')AScol3;+-------+-------+--------+|col1|col2|col3|+-------+-------+--------+|QWhat|Quest|QuWhat|+-------+-------+--------+1rowinset(0.00sec)

字母大小写转换函数LOWER(str)、UPPER(str)

mysql>SELECTLOWER('BUFF'),LCASE('Well');+---------------+---------------+|LOWER('BUFF')|LCASE('Well')|+---------------+---------------+|buff|well|+---------------+---------------+1rowinset(0.00sec)mysql>SELECTUPPER('buff'),UCASE('Well');+---------------+---------------+|UPPER('buff')|UCASE('Well')|+---------------+---------------+|BUFF|WELL|+---------------+---------------+1rowinset(0.00sec)

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

mysql>SELECTLEFT('FOOTBALL',5);+--------------------+|LEFT('FOOTBALL',5)|+--------------------+|FOOTB|+--------------------+1rowinset(0.00sec)mysql>SELECTRIGHT('FOOTBALL',5);+---------------------+|RIGHT('FOOTBALL',5)|+---------------------+|TBALL|+---------------------+1rowinset(0.00sec)

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

mysql>SELECTLPAD('HELLO',4,'??'),LPAD('HELLO',10,'??');+----------------------+-----------------------+|LPAD('HELLO',4,'??')|LPAD('HELLO',10,'??')|+----------------------+-----------------------+|HELL|?????HELLO|+----------------------+-----------------------+1rowinset(0.00sec)mysql>SELECTRPAD('HELLO',4,'??'),RPAD('HELLO',10,'??');+----------------------+-----------------------+|RPAD('HELLO',4,'??')|RPAD('HELLO',10,'??')|+----------------------+-----------------------+|HELL|HELLO?????|+----------------------+-----------------------+1rowinset(0.00sec)

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

mysql>SELECT'(BOOK)',CONCAT('(',LTRIM('BOOK'),')');+----------+---------------------------------+|(BOOK)|CONCAT('(',LTRIM('BOOK'),')')|+----------+---------------------------------+|(BOOK)|(BOOK)|+----------+---------------------------------+1rowinset(0.00sec)mysql>SELECT'(BOOK)',CONCAT('(',RTRIM('BOOK'),')');+----------+---------------------------------+|(BOOK)|CONCAT('(',RTRIM('BOOK'),')')|+----------+---------------------------------+|(BOOK)|(BOOK)|+----------+---------------------------------+1rowinset(0.00sec)mysql>SELECT'(BOOK)',CONCAT('(',TRIM('BOOK'),')');+----------+--------------------------------+|(BOOK)|CONCAT('(',TRIM('BOOK'),')')|+----------+--------------------------------+|(BOOK)|(BOOK)|+----------+--------------------------------+1rowinset(0.00sec)

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

mysql>SELECTTRIM('XY'FROM'ASHDLADHXYQWEPIXY');+-------------------------------------+|TRIM('XY'FROM'ASHDLADHXYQWEPIXY')|+-------------------------------------+|ASHDLADHXYQWEPI|+-------------------------------------+1rowinset(0.00sec)

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

mysql>SELECTREPEAT('MYSQL',5);+---------------------------+|REPEAT('MYSQL',5)|+---------------------------+|MYSQLMYSQLMYSQLMYSQLMYSQL|+---------------------------+1rowinset(0.00sec)

空格函数SPACE(n)

mysql>SELECTCONCAT('(',SPACE(3),')');+--------------------------+|CONCAT('(',SPACE(3),')')|+--------------------------+|()|+--------------------------+1rowinset(0.00sec)

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

mysql>SELECTREPLACE('XXX.MYSQL.COM','X','W');+----------------------------------+|REPLACE('XXX.MYSQL.COM','X','W')|+----------------------------------+|WWW.MYSQL.COM|+----------------------------------+1rowinset(0.00sec)

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

mysql>SELECTSTRCMP('TXT','TXT2'),STRCMP('TXT2','TXT'),STRCMP('TXT','TXT');+----------------------+----------------------+---------------------+|STRCMP('TXT','TXT2')|STRCMP('TXT2','TXT')|STRCMP('TXT','TXT')|+----------------------+----------------------+---------------------+|-1|1|0|+----------------------+----------------------+---------------------+1rowinset(0.00sec)

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

mysql>SELECTSUBSTRING('BREAKFAST',5)AScol1,从第5个位置到结尾的字符串->SUBSTRING('BREAKFAST',5,3)AScol2,从第5个位置开始长度3的子字符串->SUBSTRING('lunch',-3)AScol3,从结尾开始第3个位置到字符串结尾->SUBSTRING('lunch',-5,3)AScol4;从结尾开始第5个位置长度为3的自字符串+-------+------+------+------+|col1|col2|col3|col4|+-------+------+------+------+|KFAST|KFA|nch|lun|+-------+------+------+------+1rowinset(0.00sec)

匹配子串开始位置的函数LOCATE(s1,s)、POSITION(s1 IN s)、INSTR(s,s1)

mysql>SELECTLOCATE('BALL','FOOTBALL'),POSITION('BALL'IN'FOOTBALL'),INSTR('FOOTBALL','BALL');+---------------------------+--------------------------------+--------------------------+|LOCATE('BALL','FOOTBALL')|POSITION('BALL'IN'FOOTBALL')|INSTR('FOOTBALL','BALL')|+---------------------------+--------------------------------+--------------------------+|5|5|5|+---------------------------+--------------------------------+--------------------------+1rowinset(0.00sec)

字符串逆序函数REVERSE(s)

mysql>SELECTREVERSE('ABCD');+-----------------+|REVERSE('ABCD')|+-----------------+|DCBA|+-----------------+1rowinset(0.00sec)

返回指定位置的字符串函数ELT(n,s1,s2,...)

mysql>SELECTELT(3,'1TH','2TH','3TH'),ELT(3,'NET','OS');+--------------------------+-------------------+|ELT(3,'1TH','2TH','3TH')|ELT(3,'NET','OS')|+--------------------------+-------------------+|3TH|NULL|+--------------------------+-------------------+1rowinset(0.00sec)

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

mysql>SELECTFIELD('HI','HIHI','HEY','HI','BAS')ASclo1,->FIELD('HI','HEY','LO')ASclo2;+------+------+|clo1|clo2|+------+------+|3|0|+------+------+1rowinset(0.00sec)

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

mysql>SELECTFIND_IN_SET('hi','hihi,hey,hi,bas');+-------------------------------------+|FIND_IN_SET('hi','hihi,hey,hi,bas')|+-------------------------------------+|3|+-------------------------------------+1rowinset(0.00sec)

选取字符串的函数MAKE_SET(x,s1,s2,...)

mysql>SELECTMAKE_SET(1,'A','B','C')ascol1,->MAKE_SET(1|4,'hello','nice','world')ascol2,->MAKE_SET(1|4,'hello','nice',NULL,'world')ascol3,->MAKE_SET(0,'a','b','c')ascol4;+------+-------------+-------+------+|col1|col2|col3|col4|+------+-------------+-------+------+|A|hello,world|hello||+------+-------------+-------+------+1rowinset(0.00sec)

5.3、日期和时间函数

获取当前日期的函数CURDATE()、CURRENT_DATE()

mysql>SELECTCURDATE(),CURRENT_DATE(),CURDATE()+0;+------------+----------------+-------------+|CURDATE()|CURRENT_DATE()|CURDATE()+0|+------------+----------------+-------------+|2017-08-01|2017-08-01|20170801|+------------+----------------+-------------+1rowinset(0.00sec)

获取当前时间的函数CURTIME()、CURRENT_TIME()

mysql>SELECTCURTIME(),CURRENT_TIME(),CURTIME()+0;+-----------+----------------+---------------+|CURTIME()|CURRENT_TIME()|CURTIME()+0|+-----------+----------------+---------------+|18:54:38|18:54:38|185438.000000|+-----------+----------------+---------------+1rowinset(0.00sec)

获取当前日期和时间的函数CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

mysql>SELECTCURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();+---------------------+---------------------+---------------------+---------------------+|CURRENT_TIMESTAMP()|LOCALTIME()|NOW()|SYSDATE()|+---------------------+---------------------+---------------------+---------------------+|2017-08-0118:55:48|2017-08-0118:55:48|2017-08-0118:55:48|2017-08-0118:55:48|+---------------------+---------------------+---------------------+---------------------+1rowinset(0.00sec)

UNIX时间戳函数

mysql>SELECTUNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();+------------------+-----------------------+---------------------+|UNIX_TIMESTAMP()|UNIX_TIMESTAMP(NOW())|NOW()|+------------------+-----------------------+---------------------+|1501585038|1501585038|2017-08-0118:57:18|+------------------+-----------------------+---------------------+1rowinset(0.00sec)#返回UNIX格式时间即1970-01-0100:00:00之后的秒数mysql>SELECTFROM_UNIXTIME('1466393937');#将UNIX时间戳转换为普通格式时间+-----------------------------+|FROM_UNIXTIME('1466393937')|+-----------------------------+|2016-06-2011:38:57|+-----------------------------+1rowinset(0.00sec)

返回UTC日期的函数UTC_DATE()

mysql>SELECTUTC_DATE(),UTC_DATE()+0;+------------+--------------+|UTC_DATE()|UTC_DATE()+0|+------------+--------------+|2017-08-01|20170801|+------------+--------------+1rowinset(0.00sec)

返回UTC时间的函数UTC_TIME()

mysql>SELECTUTC_TIME(),UTC_TIME()+0;+------------+---------------+|UTC_TIME()|UTC_TIME()+0|+------------+---------------+|11:01:31|110131.000000|+------------+---------------+1rowinset(0.00sec)

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

mysql>SELECTMONTH('2017-07-13');+---------------------+|MONTH('2017-07-13')|+---------------------+|7|+---------------------+1rowinset(0.00sec)mysql>SELECTMONTHNAME('2017-07-13');+-------------------------+|MONTHNAME('2017-07-13')|+-------------------------+|July|+-------------------------+1rowinset(0.00sec)

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

mysql>SELECTDAYNAME('2017-07-13');#返回指定日期的星期+-----------------------+|DAYNAME('2017-07-13')|+-----------------------+|Thursday|+-----------------------+1rowinset(0.00sec)mysql>SELECTDAYOFWEEK('2017-07-13');#返回d对应一周中的索引(1周日,2周一,...7周六)+-------------------------+|DAYOFWEEK('2017-07-13')|+-------------------------+|5|+-------------------------+1rowinset(0.00sec)mysql>SELECTWEEKDAY('2017-07-13');#返回d对应一工作日的索引(0周日,1周一,...6周六)+-----------------------+|WEEKDAY('2017-07-13')|+-----------------------+|3|+-----------------------+1rowinset(0.00sec)

获取星期数的函数WKKE(d)、WEEKOFYEAR(d)

mysql>SELECTWEEK('2017-08-01'),WEEK('2017-08-01',0),WEEK('2017-08-01',1);+--------------------+----------------------+----------------------+|WEEK('2017-08-01')|WEEK('2017-08-01',0)|WEEK('2017-08-01',1)|+--------------------+----------------------+----------------------+|31|31|31|+--------------------+----------------------+----------------------+1rowinset(0.00sec)mysql>SELECTWEEK('2017-08-01'),WEEKOFYEAR('2017-08-01');+--------------------+--------------------------+|WEEK('2017-08-01')|WEEKOFYEAR('2017-08-01')|+--------------------+--------------------------+|31|31|+--------------------+--------------------------+1rowinset(0.00sec)

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

mysql>SELECTDAYOFYEAR('2017-08-01');+-------------------------+|DAYOFYEAR('2017-08-01')|+-------------------------+|213|+-------------------------+1rowinset(0.00sec)mysql>SELECTDAYOFMONTH('2017-08-01');+--------------------------+|DAYOFMONTH('2017-08-01')|+--------------------------+|1|+--------------------------+1rowinset(0.00sec)

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

mysql>SELECTYEAR('11-02-03'),YEAR('96-02-03');+------------------+------------------+|YEAR('11-02-03')|YEAR('96-02-03')|+------------------+------------------+|2011|1996|+------------------+------------------+1rowinset(0.00sec)mysql>SELECTQUARTER('11-02-03'),QUARTER('96-02-03');+---------------------+---------------------+|QUARTER('11-02-03')|QUARTER('96-02-03')|+---------------------+---------------------+|1|1|+---------------------+---------------------+1rowinset(0.00sec)mysql>SELECTHOUR('10:05:06'),HOUR('15:06:56');+------------------+------------------+|HOUR('10:05:06')|HOUR('15:06:56')|+------------------+------------------+|10|15|+------------------+------------------+1rowinset(0.00sec)mysql>SELECTMINUTE('10:05:06'),MINUTE('15:06:56');+--------------------+--------------------+|MINUTE('10:05:06')|MINUTE('15:06:56')|+--------------------+--------------------+|5|6|+--------------------+--------------------+1rowinset(0.00sec)mysql>SELECTSECOND('10:05:06'),SECOND('15:06:56');+--------------------+--------------------+|SECOND('10:05:06')|SECOND('15:06:56')|+--------------------+--------------------+|6|56|+--------------------+--------------------+1rowinset(0.00sec)

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

mysql>SELECTEXTRACT(YEARFROM'2017-07-03')AScol1,->EXTRACT(YEAR_MONTHFROM'2017-07-0301:02:03')AScol2,->EXTRACT(DAY_MINUTEFROM'2017-07-0301:02:03')AScol3;+------+--------+-------+|col1|col2|col3|+------+--------+-------+|2017|201707|30102|+------+--------+-------+1rowinset(0.00sec)

时间和秒钟转换的函数

mysql>SELECTTIME_TO_SEC('23:23:00');+-------------------------+|TIME_TO_SEC('23:23:00')|+-------------------------+|84180|+-------------------------+1rowinset(0.00sec)mysql>SELECTSEC_TO_TIME(84180);+--------------------+|SEC_TO_TIME(84180)|+--------------------+|23:23:00|+--------------------+1rowinset(0.00sec)

将日期格式化的函数DATE_FORMAT(date,formate)

说明符说明%a工作日的缩写名称(Sun..Sat)%b
月份的所写名称(Jan..Dec)%c
月份数字形式(1,..12)%D
带有英文后缀的该月份日期(1th,2nd,3rd,..)%d
该月日期数字形式(01..31)%e
该月日期数字形式(1,..31)%f
微秒(000000..999999)%H
以2位数表示24小时(00,..23)%h,%I
以2位数表示12小时(01,..12)%i
分钟数字形式(00..59)%j
一年中的天数(001..366)%k
以24小时表示时间%l
以12小时表示时间%M
月份名称(January..December)%m
月份数字形式(01..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
周(00..53)周一为每周的第一天%V
周(01..53)周日为每周的第一天和%X同时使用%v
周(01..53)周一为每周的第一天和%x同时使用%W
工作日名称(周日..周六)%w
一周中的每日(0=周日..6=周六)%X
该周的年份周日为每周第一天数字形式4位数和%V同时使用%x
该周的年份周一为每周第一天数字形式4位数和%v同时使用%Y
4位数形式表示年份%y
2位数形式表示年份%%
'%'文字字符

mysql>SELECTDATE_FORMAT('1997-10-0422:23:00','%W%M%Y')AScol1,->DATE_FORMAT('1997-10-0422:23:00','%D%y%a%d%m%b%j')AScol2;+-----------------------+--------------------------+|col1|col2|+-----------------------+--------------------------+|SaturdayOctober1997|4th97Sat0410Oct277|+-----------------------+--------------------------+1rowinset(0.00sec)

将时间格式化的函数TIME_FORMAT(time,formate)

mysql>SELECTTIME_FORMAT('16:00:00','%H%k%h%I%l');+-------------------------------------------+|TIME_FORMAT('16:00:00','%H%k%h%I%l')|+-------------------------------------------+|161604044|+-------------------------------------------+1rowinset(0.00sec)

5.4、条件判断函数

IF(expr,v1,v2)

mysql>SELECTIF(1>2,2,3),->IF(1<2,'YES','NO'),->IF(STRCMP('TEST','TEST1'),'NO','YES');+-------------+--------------------+---------------------------------------+|IF(1>2,2,3)|IF(1<2,'YES','NO')|IF(STRCMP('TEST','TEST1'),'NO','YES')|+-------------+--------------------+---------------------------------------+|3|YES|NO|+-------------+--------------------+---------------------------------------+1rowinset(0.00sec)

IFNULL(v1,v2)

mysql>SELECTIFNULL(1,2),IFNULL(NULL,10),IFNULL(1/0,'WRONG');+-------------+-----------------+---------------------+|IFNULL(1,2)|IFNULL(NULL,10)|IFNULL(1/0,'WRONG')|+-------------+-----------------+---------------------+|1|10|WRONG|+-------------+-----------------+---------------------+1rowinset(0.00sec)

5.5、系统信息函数

获取MySQL版本号VERSION()

mysql>SELECTVERSION();+------------+|VERSION()|+------------+|5.5.56-log|+------------+1rowinset(0.00sec)

获取连接数CONNECTION_ID()

mysql>SELECTCONNECTION_ID();+-----------------+|CONNECTION_ID()|+-----------------+|6|+-----------------+1rowinset(0.00sec)

显示运行的线程

mysql>SHOWPROCESSLIST;#显示运行的线程+----+------+-----------+------+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+------+---------+------+-------+------------------+|6|root|localhost|NULL|Query|0|NULL|SHOWPROCESSLIST|+----+------+-----------+------+---------+------+-------+------------------+1rowinset(0.00sec)

获取用户名

mysql>SELECTUSER(),CURRENT_USER(),SYSTEM_USER();+----------------+----------------+----------------+|USER()|CURRENT_USER()|SYSTEM_USER()|+----------------+----------------+----------------+|root@localhost|root@localhost|root@localhost|+----------------+----------------+----------------+1rowinset(0.00sec)

获取字符串的字符集和排序方式

mysql>SELECTCHARSET('ABC'),CHARSET(CONVERT('ABC'USINGlatin1)),CHARSET(VERSION());+----------------+--------------------------------------+--------------------+|CHARSET('ABC')|CHARSET(CONVERT('ABC'USINGlatin1))|CHARSET(VERSION())|+----------------+--------------------------------------+--------------------+|utf8|latin1|utf8|+----------------+--------------------------------------+--------------------+1rowinset(0.00sec)mysql>SELECTCOLLATION('ABC'),COLLATION(CONVERT('ABC'USINGutf8));+------------------+--------------------------------------+|COLLATION('ABC')|COLLATION(CONVERT('ABC'USINGutf8))|+------------------+--------------------------------------+|utf8_general_ci|utf8_general_ci|+------------------+--------------------------------------+1rowinset(0.00sec)

5.6、加密函数

加密函数PASSWORD(str)

mysql>SELECTPASSWORD('NEWPD');+-------------------------------------------+|PASSWORD('NEWPD')|+-------------------------------------------+|*2AC78BA05A00714DDD77D040F46ABF58440382F2|+-------------------------------------------+1rowinset(0.00sec)

加密函数MD5(str)

mysql>SELECTMD5('NEWPD');+----------------------------------+|MD5('NEWPD')|+----------------------------------+|825ea75e25db1b886e20b14281447628|+----------------------------------+1rowinset(0.00sec)

加密函数ENCODE(str,pswd_str)

mysql>SELECTENCODE('secret','cry'),LENGTH(ENCODE('secret','cry'));+------------------------+--------------------------------+|ENCODE('secret','cry')|LENGTH(ENCODE('secret','cry'))|+------------------------+--------------------------------+||6|+------------------------+--------------------------------+1rowinset(0.00sec)

解密函数DECODE(crypt_str,pswd_str)

mysql>SELECTDECODE(ENCODE('secret','cry'),'cry');+--------------------------------------+|DECODE(ENCODE('secret','cry'),'cry')|+--------------------------------------+|secret|+--------------------------------------+1rowinset(0.00sec)

5.7、其他函数

格式化函数FORMAT(x,n)

mysql>SELECTFORMAT(12332.12345,4),FORMAT(12332.1,4);+-----------------------+-------------------+|FORMAT(12332.12345,4)|FORMAT(12332.1,4)|+-----------------------+-------------------+|12,332.1235|12,332.1000|+-----------------------+-------------------+1rowinset(0.00sec)

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

mysql>SELECTCONV('a',16,2),->CONV(15,10,2),->CONV(15,10,8),->CONV(15,10,16);+----------------+---------------+---------------+----------------+|CONV('a',16,2)|CONV(15,10,2)|CONV(15,10,8)|CONV(15,10,16)|+----------------+---------------+---------------+----------------+|1010|1111|17|F|+----------------+---------------+---------------+----------------+1rowinset(0.00sec)

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

mysql>SELECTINET_ATON('209.207.224.40');+-----------------------------+|INET_ATON('209.207.224.40')|+-----------------------------+|3520061480|+-----------------------------+1rowinset(0.00sec)mysql>SELECTINET_NTOA(3520064480);+-----------------------+|INET_NTOA(3520064480)|+-----------------------+|209.207.235.224|+-----------------------+1rowinset(0.00sec)

重复执行指定操作的函数

mysql>SELECTBENCHMARK(5000,PASSWORD('NEWPD'));+-----------------------------------+|BENCHMARK(5000,PASSWORD('NEWPD'))|+-----------------------------------+|0|+-----------------------------------+1rowinset(0.01sec)

改变字符集的函数

mysql>SELECTCHARSET('STRING'),CHARSET(CONVERT('STRING'USINGlatin1));+-------------------+-----------------------------------------+|CHARSET('STRING')|CHARSET(CONVERT('STRING'USINGlatin1))|+-------------------+-----------------------------------------+|utf8|latin1|+-------------------+-----------------------------------------+1rowinset(0.00sec)

改变数据类型的函数

mysql>SELECTCAST(100ASCHAR(2)),CONVERT('2010-10-0112:12:12',TIME);+----------------------+-------------------------------------+|CAST(100ASCHAR(2))|CONVERT('2010-10-0112:12:12',TIME)|+----------------------+-------------------------------------+|10|12:12:12|+----------------------+-------------------------------------+1rowinset,1warning(0.00sec)