当前位置：Gxlcms > 数据库问题 > 五、MySQL函数

五、MySQL函数

时间：2021-07-01 10:21:17 帮助过：3人阅读

绝对值函数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)

本文出自 “随风而飘” 博客，请务必保留此出处http://yinsuifeng.blog.51cto.com/10173491/1952779

五、MySQL函数

标签：mysql函数

五、MySQL函数

人气教程排行