五、MySQL函数

发布时间：2020-07-09 11:42:52 作者：少年不在了
来源：网络阅读：724

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

5.1、数学函数

绝对值函数ABS(x)

mysql> SELECT ABS(2),ABS(-3.3),ABS(-33);
+--------+-----------+----------+
| ABS(2) | ABS(-3.3) | ABS(-33) |
+--------+-----------+----------+
|      2 |       3.3 |       33 |
+--------+-----------+----------+
1 row in set (0.00 sec)

返回圆周率函数PI()

mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

平方根函数SQRT(x)

mysql> SELECT SQRT(9),SQRT(30),SQRT(-30);
+---------+-------------------+-----------+
| SQRT(9) | SQRT(30)          | SQRT(-30) |
+---------+-------------------+-----------+
|       3 | 5.477225575051661 |      NULL |
+---------+-------------------+-----------+
1 row in set (0.00 sec)

求余函数MOD(x,y)

mysql> SELECT MOD(31,8),MOD(234,8),MOD(45.5,6);
+-----------+------------+-------------+
| MOD(31,8) | MOD(234,8) | MOD(45.5,6) |
+-----------+------------+-------------+
|         7 |          2 |         3.5 |
+-----------+------------+-------------+
1 row in set (0.00 sec)

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

mysql> SELECT CEIL(-3.35),CEILING(3.35);   #返回不小于x的最小整数
+-------------+---------------+
| CEIL(-3.35) | CEILING(3.35) |
+-------------+---------------+
|          -3 |             4 |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(-3.35),FLOOR(3.35);    #返回不大于x的最大整数
+--------------+-------------+
| FLOOR(-3.35) | FLOOR(3.35) |
+--------------+-------------+
|           -4 |           3 |
+--------------+-------------+
1 row in set (0.00 sec)

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

mysql> SELECT RAND(),RAND(),RAND();        #返回0-1之间的随机数
+--------------------+--------------------+--------------------+
| RAND()             | RAND()             | RAND()             |
+--------------------+--------------------+--------------------+
| 0.8727586752481373 | 0.6464434700519252 | 0.6139413552488585 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT RAND(10),RAND(10),RAND(11);  #x用作种子值用来产生重复序列
+--------------------+--------------------+-------------------+
| RAND(10)           | RAND(10)           | RAND(11)          |
+--------------------+--------------------+-------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
+--------------------+--------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> SELECT ROUND(-1.14),ROUND(1.67),ROUND(1.15); #返回最接近x的整数对x值进行四舍五入
+--------------+-------------+-------------+
| ROUND(-1.14) | ROUND(1.67) | ROUND(1.15) |
+--------------+-------------+-------------+
|           -1 |           2 |           1 |
+--------------+-------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(-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 |
+----------------+---------------+----------------+
1 row in set (0.00 sec)   #返回最接近x的整数值保留小数点后y位y为负数将保留x小数点左边y位

mysql> SELECT TRUNCATE(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 |
+------------------+------------------+------------------+
1 row in set (0.00 sec)  #返回被舍去至小数点后y位的数字x

符号函数SIGN(x)

mysql> SELECT SIGN(-21),SIGN(0),SIGN(21);
+-----------+---------+----------+
| SIGN(-21) | SIGN(0) | SIGN(21) |
+-----------+---------+----------+
|        -1 |       0 |        1 |
+-----------+---------+----------+
1 row in set (0.00 sec)

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

mysql> SELECT POW(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 |
+----------+------------+-----------+-------------+
1 row in set (0.04 sec)  

mysql> SELECT EXP(3),EXP(-3),EXP(0);   #返回以e为底的x次方
+--------------------+----------------------+--------+
| EXP(3)             | EXP(-3)              | EXP(0) |
+--------------------+----------------------+--------+
| 20.085536923187668 | 0.049787068367863944 |      1 |
+--------------------+----------------------+--------+
1 row in set (0.02 sec)

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

mysql> SELECT LOG(3),LOG(-3);  #返回x的自然对数x相对于基数e的对数
+--------------------+---------+
| LOG(3)             | LOG(-3) |
+--------------------+---------+
| 1.0986122886681098 |    NULL |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> SELECT LOG10(2),LOG10(100),LOG10(-100);  #返回x相对于基数10的对数
+--------------------+------------+-------------+
| LOG10(2)           | LOG10(100) | LOG10(-100) |
+--------------------+------------+-------------+
| 0.3010299956639812 |          2 |        NULL |
+--------------------+------------+-------------+
1 row in set (0.00 sec)

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

mysql> SELECT RADIANS(90),RADIANS(180);   #角度转化为弧度
+--------------------+-------------------+
| RADIANS(90)        | RADIANS(180)      |
+--------------------+-------------------+
| 1.5707963267948966 | 3.141592653589793 |
+--------------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT DEGREES(PI()),DEGREES(PI()/2);#弧度转化为角度
+---------------+-----------------+
| DEGREES(PI()) | DEGREES(PI()/2) |
+---------------+-----------------+
|           180 |              90 |
+---------------+-----------------+
1 row in set (0.00 sec)

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

mysql> SELECT SIN(1),ROUND(SIN(PI()));
+--------------------+------------------+
| SIN(1)             | ROUND(SIN(PI())) |
+--------------------+------------------+
| 0.8414709848078965 |                0 |
+--------------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT ASIN(3),ASIN(0.84);
+---------+--------------------+
| ASIN(3) | ASIN(0.84)         |
+---------+--------------------+
|    NULL | 0.9972832223717998 |
+---------+--------------------+
1 row in set (0.00 sec)

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

mysql> SELECT COS(1), COS(PI()),COS(0);
+--------------------+-----------+--------+
| COS(1)             | COS(PI()) | COS(0) |
+--------------------+-----------+--------+
| 0.5403023058681398 |        -1 |      1 |
+--------------------+-----------+--------+
1 row in set (0.00 sec)

mysql> SELECT ACOS(1),ACOS(0.54),ACOS(0);
+---------+--------------------+--------------------+
| ACOS(1) | ACOS(0.54)         | ACOS(0)            |
+---------+--------------------+--------------------+
|       0 | 1.0003592173949747 | 1.5707963267948966 |
+---------+--------------------+--------------------+
1 row in set (0.00 sec)

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

mysql> SELECT TAN(0.3),TAN(PI()/4);
+---------------------+--------------------+
| TAN(0.3)            | TAN(PI()/4)        |
+---------------------+--------------------+
| 0.30933624960962325 | 0.9999999999999999 |
+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ATAN(1),ATAN(0.393);   
+--------------------+---------------------+
| ATAN(1)            | ATAN(0.393)         |
+--------------------+---------------------+
| 0.7853981633974483 | 0.37445736689641174 |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT COT(0.3),1/TAN(0.3);
+--------------------+--------------------+
| COT(0.3)           | 1/TAN(0.3)         |
+--------------------+--------------------+
| 3.2327281437658275 | 3.2327281437658275 |
+--------------------+--------------------+
1 row in set (0.00 sec)

5.2、字符串函数

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

mysql> SELECT CHAR_LENGTH('DATE'),CHAR_LENGTH('egg');
+---------------------+--------------------+
| CHAR_LENGTH('DATE') | CHAR_LENGTH('egg') |
+---------------------+--------------------+
|                   4 |                  3 |
+---------------------+--------------------+
1 row in set (0.00 sec)

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

mysql> SELECT LENGTH('DATE'),LENGTH('egg');          
+----------------+---------------+
| LENGTH('DATE') | LENGTH('egg') |
+----------------+---------------+
|              4 |             3 |
+----------------+---------------+
1 row in set (0.00 sec)

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

mysql> SELECT CONCAT('My SQL','5.6'),CONCAT('My SQL','5.6',NULL);
+------------------------+-----------------------------+
| CONCAT('My SQL','5.6') | CONCAT('My SQL','5.6',NULL) |
+------------------------+-----------------------------+
| My SQL5.6              | NULL                        |
+------------------------+-----------------------------+
1 row in set (0.00 sec)  #连接多个字符串出现NULL时返回值为NULL

mysql> SELECT CONCAT_WS('-','My SQL','5.6'),CONCAT_WS('-','My SQL','5.6',NULL);
+-------------------------------+------------------------------------+
| CONCAT_WS('-','My SQL','5.6') | CONCAT_WS('-','My SQL','5.6',NULL) |
+-------------------------------+------------------------------------+
| My SQL-5.6                    | My SQL-5.6                         |
+-------------------------------+------------------------------------+
1 row in set (0.00 sec)  #以x为连接符连接多个字符串出现NULL时忽略NULL

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

mysql> SELECT INSERT('Quest',2,4,'What') AS col1,
    -> INSERT('Quest',-1,4,'What') AS col2, 
    -> INSERT('Quest',3,100,'What') AS col3;
+-------+-------+--------+
| col1  | col2  | col3   |
+-------+-------+--------+
| QWhat | Quest | QuWhat |
+-------+-------+--------+
1 row in set (0.00 sec)

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

mysql> SELECT LOWER('BUFF'),LCASE('Well');
+---------------+---------------+
| LOWER('BUFF') | LCASE('Well') |
+---------------+---------------+
| buff          | well          |
+---------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER('buff'),UCASE('Well');
+---------------+---------------+
| UPPER('buff') | UCASE('Well') |
+---------------+---------------+
| BUFF          | WELL          |
+---------------+---------------+
1 row in set (0.00 sec)

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

mysql> SELECT LEFT('FOOTBALL',5);
+--------------------+
| LEFT('FOOTBALL',5) |
+--------------------+
| FOOTB              |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT('FOOTBALL',5);
+---------------------+
| RIGHT('FOOTBALL',5) |
+---------------------+
| TBALL               |
+---------------------+
1 row in set (0.00 sec)

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

mysql> SELECT LPAD('HELLO',4,'??'),LPAD('HELLO',10,'??');
+----------------------+-----------------------+
| LPAD('HELLO',4,'??') | LPAD('HELLO',10,'??') |
+----------------------+-----------------------+
| HELL                 | ?????HELLO            |
+----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT RPAD('HELLO',4,'??'),RPAD('HELLO',10,'??');
+----------------------+-----------------------+
| RPAD('HELLO',4,'??') | RPAD('HELLO',10,'??') |
+----------------------+-----------------------+
| HELL                 | HELLO?????            |
+----------------------+-----------------------+
1 row in set (0.00 sec)

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

mysql> SELECT '( BOOK )',CONCAT('(',LTRIM(' BOOK '),')');  
+----------+---------------------------------+
| ( BOOK ) | CONCAT('(',LTRIM(' BOOK '),')') |
+----------+---------------------------------+
| ( BOOK ) | (BOOK )                         |
+----------+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '( BOOK )',CONCAT('(',RTRIM(' BOOK '),')'); 
+----------+---------------------------------+
| ( BOOK ) | CONCAT('(',RTRIM(' BOOK '),')') |
+----------+---------------------------------+
| ( BOOK ) | ( BOOK)                         |
+----------+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '( BOOK )',CONCAT('(',TRIM(' BOOK '),')'); 
+----------+--------------------------------+
| ( BOOK ) | CONCAT('(',TRIM(' BOOK '),')') |
+----------+--------------------------------+
| ( BOOK ) | (BOOK)                         |
+----------+--------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT TRIM('XY' FROM 'ASHDLADHXYQWEPIXY');
+-------------------------------------+
| TRIM('XY' FROM 'ASHDLADHXYQWEPIXY') |
+-------------------------------------+
| ASHDLADHXYQWEPI                     |
+-------------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT REPEAT('MYSQL',5);
+---------------------------+
| REPEAT('MYSQL',5)         |
+---------------------------+
| MYSQLMYSQLMYSQLMYSQLMYSQL |
+---------------------------+
1 row in set (0.00 sec)

空格函数SPACE(n)

mysql> SELECT CONCAT('(',SPACE(3),')');
+--------------------------+
| CONCAT('(',SPACE(3),')') |
+--------------------------+
| (   )                    |
+--------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT REPLACE('XXX.MYSQL.COM','X','W');
+----------------------------------+
| REPLACE('XXX.MYSQL.COM','X','W') |
+----------------------------------+
| WWW.MYSQL.COM                    |
+----------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT STRCMP('TXT','TXT2'),STRCMP('TXT2','TXT'),STRCMP('TXT','TXT');
+----------------------+----------------------+---------------------+
| STRCMP('TXT','TXT2') | STRCMP('TXT2','TXT') | STRCMP('TXT','TXT') |
+----------------------+----------------------+---------------------+
|                   -1 |                    1 |                   0 |
+----------------------+----------------------+---------------------+
1 row in set (0.00 sec)

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

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

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

mysql> SELECT LOCATE('BALL','FOOTBALL'),POSITION('BALL' IN 'FOOTBALL'),IN
STR('FOOTBALL','BALL');
+---------------------------+--------------------------------+--------------------------+
| LOCATE('BALL','FOOTBALL') | POSITION('BALL' IN 'FOOTBALL') | INSTR('FOOTBALL','BALL') |
+---------------------------+--------------------------------+--------------------------+
|                         5 |                              5 |                        5 |
+---------------------------+--------------------------------+--------------------------+
1 row in set (0.00 sec)

字符串逆序函数REVERSE(s)

mysql> SELECT REVERSE('ABCD');
+-----------------+
| REVERSE('ABCD') |
+-----------------+
| DCBA            |
+-----------------+
1 row in set (0.00 sec)

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

mysql> SELECT ELT(3,'1TH','2TH','3TH'),ELT(3,'NET','OS');
+--------------------------+-------------------+
| ELT(3,'1TH','2TH','3TH') | ELT(3,'NET','OS') |
+--------------------------+-------------------+
| 3TH                      | NULL              |
+--------------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> SELECT FIELD('HI','HIHI','HEY','HI','BAS') AS clo1,
    -> FIELD('HI','HEY','LO') AS clo2;
+------+------+
| clo1 | clo2 |
+------+------+
|    3 |    0 |
+------+------+
1 row in set (0.00 sec)

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

mysql> SELECT FIND_IN_SET('hi','hihi,hey,hi,bas');
+-------------------------------------+
| FIND_IN_SET('hi','hihi,hey,hi,bas') |
+-------------------------------------+
|                                   3 |
+-------------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT MAKE_SET(1,'A','B','C') as col1, 
    -> MAKE_SET(1 | 4,'hello','nice','world') as col2, 
    -> MAKE_SET(1 | 4,'hello','nice',NULL,'world') as col3,
    -> MAKE_SET(0,'a','b','c') as col4;                        
+------+-------------+-------+------+
| col1 | col2        | col3  | col4 |
+------+-------------+-------+------+
| A    | hello,world | hello |      |
+------+-------------+-------+------+
1 row in set (0.00 sec)

5.3、日期和时间函数

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

mysql> SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0;
+------------+----------------+-------------+
| CURDATE()  | CURRENT_DATE() | CURDATE()+0 |
+------------+----------------+-------------+
| 2017-08-01 | 2017-08-01     |    20170801 |
+------------+----------------+-------------+
1 row in set (0.00 sec)

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

mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0;
+-----------+----------------+---------------+
| CURTIME() | CURRENT_TIME() | CURTIME()+0   |
+-----------+----------------+---------------+
| 18:54:38  | 18:54:38       | 185438.000000 |
+-----------+----------------+---------------+
1 row in set (0.00 sec)

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

mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2017-08-01 18:55:48 | 2017-08-01 18:55:48 | 2017-08-01 18:55:48 | 2017-08-01 18:55:48 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

UNIX时间戳函数

mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();  
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1501585038 |            1501585038 | 2017-08-01 18:57:18 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)#返回UNIX格式时间即1970-01-01 00:00:00 之后的秒数

 
mysql> SELECT FROM_UNIXTIME('1466393937');  #将UNIX时间戳转换为普通格式时间
+-----------------------------+
| FROM_UNIXTIME('1466393937') |
+-----------------------------+
| 2016-06-20 11:38:57         |
+-----------------------------+
1 row in set (0.00 sec)

返回UTC日期的函数UTC_DATE()

mysql> SELECT UTC_DATE(),UTC_DATE()+0;
+------------+--------------+
| UTC_DATE() | UTC_DATE()+0 |
+------------+--------------+
| 2017-08-01 |     20170801 |
+------------+--------------+
1 row in set (0.00 sec)

返回UTC时间的函数UTC_TIME()

mysql> SELECT UTC_TIME(),UTC_TIME()+0;
+------------+---------------+
| UTC_TIME() | UTC_TIME()+0  |
+------------+---------------+
| 11:01:31   | 110131.000000 |
+------------+---------------+
1 row in set (0.00 sec)

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

mysql> SELECT MONTH('2017-07-13');
+---------------------+
| MONTH('2017-07-13') |
+---------------------+
|                   7 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME('2017-07-13');
+-------------------------+
| MONTHNAME('2017-07-13') |
+-------------------------+
| July                    |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT DAYNAME('2017-07-13');  #返回指定日期的星期
+-----------------------+
| DAYNAME('2017-07-13') |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK('2017-07-13');#返回d对应一周中的索引(1周日,2周一,...7周六)
+-------------------------+
| DAYOFWEEK('2017-07-13') |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEKDAY('2017-07-13');#返回d对应一工作日的索引(0周日,1周一,...6周六)
+-----------------------+
| WEEKDAY('2017-07-13') |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)

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

mysql> SELECT WEEK('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 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2017-08-01'),WEEKOFYEAR('2017-08-01');
+--------------------+--------------------------+
| WEEK('2017-08-01') | WEEKOFYEAR('2017-08-01') |
+--------------------+--------------------------+
|                 31 |                       31 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT DAYOFYEAR('2017-08-01');
+-------------------------+
| DAYOFYEAR('2017-08-01') |
+-------------------------+
|                     213 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH('2017-08-01');
+--------------------------+
| DAYOFMONTH('2017-08-01') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT YEAR('11-02-03'),YEAR('96-02-03');
+------------------+------------------+
| YEAR('11-02-03') | YEAR('96-02-03') |
+------------------+------------------+
|             2011 |             1996 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT QUARTER('11-02-03'),QUARTER('96-02-03');
+---------------------+---------------------+
| QUARTER('11-02-03') | QUARTER('96-02-03') |
+---------------------+---------------------+
|                   1 |                   1 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT HOUR('10:05:06'),HOUR('15:06:56');    
+------------------+------------------+
| HOUR('10:05:06') | HOUR('15:06:56') |
+------------------+------------------+
|               10 |               15 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT MINUTE('10:05:06'),MINUTE('15:06:56');
+--------------------+--------------------+
| MINUTE('10:05:06') | MINUTE('15:06:56') |
+--------------------+--------------------+
|                  5 |                  6 |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND('10:05:06'),SECOND('15:06:56');
+--------------------+--------------------+
| SECOND('10:05:06') | SECOND('15:06:56') |
+--------------------+--------------------+
|                  6 |                 56 |
+--------------------+--------------------+
1 row in set (0.00 sec)

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

mysql> SELECT EXTRACT(YEAR FROM '2017-07-03') AS col1, 
    -> EXTRACT(YEAR_MONTH FROM '2017-07-0301:02:03') AS col2, 
    ->EXTRACT(DAY_MINUTE FROM '2017-07-03 01:02:03') AS col3;    
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
| 2017 | 201707 | 30102 |
+------+--------+-------+
1 row in set (0.00 sec)

时间和秒钟转换的函数

mysql> SELECT TIME_TO_SEC('23:23:00');
+-------------------------+
| TIME_TO_SEC('23:23:00') |
+-------------------------+
|                   84180 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SEC_TO_TIME(84180);     
+--------------------+
| SEC_TO_TIME(84180) |
+--------------------+
| 23:23:00           |
+--------------------+
1 row in set (0.00 sec)

将日期格式化的函数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> SELECT DATE_FORMAT('1997-10-04 22:23:00','%W %M %Y') AS col1, 
    -> DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;  
+-----------------------+--------------------------+
| col1                  | col2                     |
+-----------------------+--------------------------+
| Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 |
+-----------------------+--------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT TIME_FORMAT('16:00:00','%H %k %h %I %l' );
+-------------------------------------------+
| TIME_FORMAT('16:00:00','%H %k %h %I %l' ) |
+-------------------------------------------+
| 16 16 04 04 4                             |
+-------------------------------------------+
1 row in set (0.00 sec)

5.4、条件判断函数

IF(expr,v1,v2)

mysql> SELECT IF(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                                    |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)

IFNULL(v1,v2)

mysql> SELECT IFNULL(1,2),IFNULL(NULL,10),IFNULL(1/0,'WRONG');
+-------------+-----------------+---------------------+
| IFNULL(1,2) | IFNULL(NULL,10) | IFNULL(1/0,'WRONG') |
+-------------+-----------------+---------------------+
|           1 |              10 | WRONG               |
+-------------+-----------------+---------------------+
1 row in set (0.00 sec)

5.5、系统信息函数

获取MySQL版本号VERSION()

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.56-log |
+------------+
1 row in set (0.00 sec)

获取连接数CONNECTION_ID()

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

显示运行的线程

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

获取用户名

mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER();
+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  |
+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

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

mysql> SELECT CHARSET('ABC'), CHARSET(CONVERT('ABC' USING latin1)), CHARSET(VERSION());    
+----------------+--------------------------------------+--------------------+
| CHARSET('ABC') | CHARSET(CONVERT('ABC' USING latin1)) | CHARSET(VERSION()) |
+----------------+--------------------------------------+--------------------+
| utf8           | latin1                               | utf8               |
+----------------+--------------------------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('ABC'),COLLATION(CONVERT('ABC' USING utf8));
+------------------+--------------------------------------+
| COLLATION('ABC') | COLLATION(CONVERT('ABC' USING utf8)) |
+------------------+--------------------------------------+
| utf8_general_ci  | utf8_general_ci                      |
+------------------+--------------------------------------+
1 row in set (0.00 sec)

5.6、加密函数

加密函数PASSWORD(str)

mysql> SELECT PASSWORD('NEWPD');
+-------------------------------------------+
| PASSWORD('NEWPD')                         |
+-------------------------------------------+
| *2AC78BA05A00714DDD77D040F46ABF58440382F2 |
+-------------------------------------------+
1 row in set (0.00 sec)

加密函数MD5(str)

mysql> SELECT MD5('NEWPD');     
+----------------------------------+
| MD5('NEWPD')                     |
+----------------------------------+
| 825ea75e25db1b886e20b14281447628 |
+----------------------------------+
1 row in set (0.00 sec)

加密函数ENCODE(str,pswd_str)

mysql> SELECT ENCODE('secret','cry'),LENGTH(ENCODE('secret','cry'));
+------------------------+--------------------------------+
| ENCODE('secret','cry') | LENGTH(ENCODE('secret','cry')) |
+------------------------+--------------------------------+
|                   |                              6 |
+------------------------+--------------------------------+
1 row in set (0.00 sec)

解密函数DECODE(crypt_str,pswd_str)

mysql> SELECT DECODE(ENCODE('secret','cry'),'cry');                 
+--------------------------------------+
| DECODE(ENCODE('secret','cry'),'cry') |
+--------------------------------------+
| secret                               |
+--------------------------------------+
1 row in set (0.00 sec)

5.7、其他函数

格式化函数FORMAT(x,n)

mysql> SELECT FORMAT(12332.12345,4),FORMAT(12332.1,4); 
+-----------------------+-------------------+
| FORMAT(12332.12345,4) | FORMAT(12332.1,4) |
+-----------------------+-------------------+
| 12,332.1235           | 12,332.1000       |
+-----------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> SELECT CONV('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              |
+----------------+---------------+---------------+----------------+
1 row in set (0.00 sec)

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

mysql> SELECT INET_ATON('209.207.224.40');
+-----------------------------+
| INET_ATON('209.207.224.40') |
+-----------------------------+
|                  3520061480 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(3520064480);      
+-----------------------+
| INET_NTOA(3520064480) |
+-----------------------+
| 209.207.235.224       |
+-----------------------+
1 row in set (0.00 sec)

重复执行指定操作的函数

mysql> SELECT BENCHMARK(5000,PASSWORD('NEWPD'));
+-----------------------------------+
| BENCHMARK(5000,PASSWORD('NEWPD')) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.01 sec)

改变字符集的函数

mysql> SELECT CHARSET('STRING'),CHARSET(CONVERT('STRING' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('STRING') | CHARSET(CONVERT('STRING' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8              | latin1                                  |
+-------------------+-----------------------------------------+
1 row in set (0.00 sec)

改变数据类型的函数

mysql> SELECT CAST(100 AS CHAR(2)),CONVERT('2010-10-01 12:12:12',TIME);
+----------------------+-------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2010-10-01 12:12:12',TIME) |
+----------------------+-------------------------------------+
| 10                   | 12:12:12                            |
+----------------------+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

五、MySQL函数

相关阅读