MySql--函数

小灰灰 2022-06-18 09:45 140阅读 0赞

MySql提供了很多常用的函数，可以简化很多的操作，现将这些函数总结一下。

/*****************************函数**************************************/
    1、绝对值函数abs
    	mysql> select abs(-21), abs(21), abs('-1'), abs(-91/4);
    	+----------+---------+-----------+------------+
    	| abs(-21) | abs(21) | abs('-1') | abs(-91/4) |
    	+----------+---------+-----------+------------+
    	|       21 |      21 |         1 |    22.7500 |
    	+----------+---------+-----------+------------+
    	1 row in set (0.02 sec)
    	
    2、取余函数mod
    	mysql> select mod(10, 3), mod('10', 3), mod(10,5/3), mod(-10, 2), mod(1, 0);
    	+------------+--------------+-------------+-------------+-----------+
    	| mod(10, 3) | mod('10', 3) | mod(10,5/3) | mod(-10, 2) | mod(1, 0) |
    	+------------+--------------+-------------+-------------+-----------+
    	|          1 |            1 |      0.0000 |           0 |      NULL |
    	+------------+--------------+-------------+-------------+-----------+
    	1 row in set (0.00 sec)
    	
    3、求平方根函数sqrt
    	mysql> select sqrt(-100), sqrt(100), sqrt(100.89), sqrt('100.89');
    	+------------+-----------+------------------+------------------+
    	| sqrt(-100) | sqrt(100) | sqrt(100.89)     | sqrt('100.89')   |
    	+------------+-----------+------------------+------------------+
    	|       NULL |        10 | 10.0444014256699 | 10.0444014256699 |
    	+------------+-----------+------------------+------------------+
    	1 row in set (0.00 sec)
    	
    4、获取随机数函数rand				可返回一个随机浮点值  0~1.0   带参数作为种子值
    	mysql> select rand(), rand(2), rand(5.1);
    	+-------------------+-------------------+-------------------+
    	| rand()            | rand(2)           | rand(5.1)         |
    	+-------------------+-------------------+-------------------+
    	| 0.967204678773139 | 0.655586646549019 | 0.406135974830143 |
    	+-------------------+-------------------+-------------------+
    	1 row in set (0.00 sec)
    
    5、四舍五入函数round
    	mysql> select round(6.54321, 2), round(6.54321, 2.88), round(6.54321, -1);
    	+-------------------+----------------------+--------------------+
    	| round(6.54321, 2) | round(6.54321, 2.88) | round(6.54321, -1) |
    	+-------------------+----------------------+--------------------+
    	|              6.54 |                6.543 |                 10 |
    	+-------------------+----------------------+--------------------+
    	1 row in set (0.00 sec)
    
    6、符号函数sign					正数返1,0返0，负数返-1
    	mysql> select sign(21), sign('321'), sign(-1), sign(0.00),sign(-1*'1');
    	+----------+-------------+----------+------------+--------------+
    	| sign(21) | sign('321') | sign(-1) | sign(0.00) | sign(-1*'1') |
    	+----------+-------------+----------+------------+--------------+
    	|        1 |           1 |       -1 |          0 |           -1 |
    	+----------+-------------+----------+------------+--------------+
    	1 row in set (0.00 sec)
    
    7、幂运算函数power
    	mysql> select power(8,2), power('8', 3), power(2.1, 2.5), power(-2, 2), power(-1,2.2);
    	+------------+---------------+------------------+--------------+---------------+
    	| power(8,2) | power('8', 3) | power(2.1, 2.5)  | power(-2, 2) | power(-1,2.2) |
    	+------------+---------------+------------------+--------------+---------------+
    	|         64 |           512 | 6.39069714506954 |            4 |          NULL |
    	+------------+---------------+------------------+--------------+---------------+
    	1 row in set (0.00 sec)
    
    8、对数运算函数log						无参代表ln()   
    	mysql> select log(5, 25), log(3, '9'), log(2), log(-2);
    	+------------+-------------+-------------------+---------+
    	| log(5, 25) | log(3, '9') | log(2)            | log(-2) |
    	+------------+-------------+-------------------+---------+
    	|          2 |           2 | 0.693147180559945 |    NULL |
    	+------------+-------------+-------------------+---------+
    	1 row in set (0.00 sec)
    
    9、pi函数
    	mysql> select pi();
    	+----------+
    	| pi()     |
    	+----------+
    	| 3.141593 |
    	+----------+
    	1 row in set (0.00 sec)
    
    10、三角函数
    	mysql> select sin(pi()/2), cos(pi()), tan(pi()/4);
    	+-------------+-----------+-------------+
    	| sin(pi()/2) | cos(pi()) | tan(pi()/4) |
    	+-------------+-----------+-------------+
    	|           1 |        -1 |           1 |
    	+-------------+-----------+-------------+
    	1 row in set (0.00 sec)
    
    11、获取最小整数ceil、ceiling
    	mysql> select ceiling(-234.32), ceil(-234.32), ceiling(234.32), ceil(234.32);
    	+------------------+---------------+-----------------+--------------+
    	| ceiling(-234.32) | ceil(-234.32) | ceiling(234.32) | ceil(234.32) |
    	+------------------+---------------+-----------------+--------------+
    	|             -234 |          -234 |             235 |          235 |
    	+------------------+---------------+-----------------+--------------+
    	1 row in set (0.00 sec)
    
    12、合并字符串函数concat
    	mysql> select concat('this','a','test'),concat('this', 'a', 'test',null),concat(123, 456);
    	+---------------------------+----------------------------------+------------------+
    	| concat('this','a','test') | concat('this', 'a', 'test',null) | concat(123, 456) |
    	+---------------------------+----------------------------------+------------------+
    	| thisatest                 | NULL                             | 123456           |
    	+---------------------------+----------------------------------+------------------+
    	1 row in set (0.00 sec)
    
    13、计算字符串长度函数length				char_length()以字符为单位，多字节字符当成一个字符，length()以字节为单位
    	mysql> select char_length('1234'),char_length('测试'), length('测试');
    	+---------------------+-----------------------+------------------+
    	| char_length('1234') | char_length('测试')   | length('测试')   |
    	+---------------------+-----------------------+------------------+
    	|                   4 |                     2 |                6 |
    	+---------------------+-----------------------+------------------+
    	1 row in set (0.00 sec)
    
    14、字母小写转大写upper
    	mysql> select upper('abcd测试'), ucase('abcd测试');
    	+---------------------+---------------------+
    	| upper('abcd测试')   | ucase('abcd测试')   |
    	+---------------------+---------------------+
    	| ABCD测试            | ABCD测试            |
    	+---------------------+---------------------+
    	1 row in set (0.00 sec)
    
    15、字母大写转小写lower
    	mysql> select lower('ABCD测试'), lcase('ABCD测试');
    	+---------------------+---------------------+
    	| lower('ABCD测试')   | lcase('ABCD测试')   |
    	+---------------------+---------------------+
    	| abcd测试            | abcd测试            |
    	+---------------------+---------------------+
    	1 row in set (0.00 sec)
    	
    16、获取指定长度的字符串的函数left和right
    	mysql> select left('left测试函数', 5), right('right测试函数', 2);
    	+-----------------------------+-------------------------------+
    	| left('left测试函数', 5)     | right('right测试函数', 2)     |
    	+-----------------------------+-------------------------------+
    	| left测                      | 函数                          |
    	+-----------------------------+-------------------------------+
    	1 row in set (0.00 sec)
    
    17、填充字符串函数lpad和rpad
    	mysql> select lpad('测试函数', 8, 'lpad'),rpad('测试函数',8,'rpad'),rpad('测试函数',2,'MYSQL');
    	+---------------------------------+-------------------------------+--------------------------------+
    	| lpad('测试函数', 8, 'lpad')     | rpad('测试函数',8,'rpad')     | rpad('测试函数',2,'MYSQL')     |
    	+---------------------------------+-------------------------------+--------------------------------+
    	| lpad测试函数                    | 测试函数rpad                  | 测试                           |
    	+---------------------------------+-------------------------------+--------------------------------+
    	1 row in set (0.00 sec)
    
    18、删除指定字符函数trim				both两端删除   leading前端删除   trailing后端删除  无参删两端空格
    	mysql> select trim(trailing 'm' from 'msqltrim'), trim(both 'm' from 'mysqltrim'), trim(' mysqltrim ');
    	+------------------------------------+---------------------------------+---------------------+
    	| trim(trailing 'm' from 'msqltrim') | trim(both 'm' from 'mysqltrim') | trim(' mysqltrim ') |
    	+------------------------------------+---------------------------------+---------------------+
    	| msqltri                            | ysqltri                         | mysqltrim           |
    	+------------------------------------+---------------------------------+---------------------+
    
    19、删除两端空格函数ltrim，rtrim
    	mysql> select length('  mysql'), length('mysql  '), length(ltrim('  mysql')), length(rtrim('mysql  '));
    	+-------------------+-------------------+--------------------------+--------------------------+
    	| length('  mysql') | length('mysql  ') | length(ltrim('  mysql')) | length(rtrim('mysql  ')) |
    	+-------------------+-------------------+--------------------------+--------------------------+
    	|                 7 |                 7 |                        5 |                        5 |
    	+-------------------+-------------------+--------------------------+--------------------------+
    	1 row in set (0.00 sec)
    
    20、重复生成字符串的函数repeat
    	mysql> select repeat('测试',3),repeat('测试',-1), repeat('测试',null);
    	+--------------------+---------------------+-----------------------+
    	| repeat('测试',3)   | repeat('测试',-1)   | repeat('测试',null)   |
    	+--------------------+---------------------+-----------------------+
    	| 测试测试测试       |                     | NULL                  |
    	+--------------------+---------------------+-----------------------+
    	1 row in set (0.00 sec)
    
    21、空格函数space
    	mysql> select length(space(10));
    	+-------------------+
    	| length(space(10)) |
    	+-------------------+
    	|                10 |
    	+-------------------+
    	1 row in set (0.00 sec)
    
    22、替换函数replace
    	mysql> select replace('this is test', 't', 'T');
    	+-----------------------------------+
    	| replace('this is test', 't', 'T') |
    	+-----------------------------------+
    	| This is TesT                      |
    	+-----------------------------------+
    	1 row in set (0.00 sec)
    
    23、替换字符串的函数insert
    	mysql> select insert('testinsert',2,2,'uu'),insert('testinsert',2,5,'uu'),insert('testinsert',2,1,'uuu');
    	+-------------------------------+-------------------------------+--------------------------------+
    	| insert('testinsert',2,2,'uu') | insert('testinsert',2,5,'uu') | insert('testinsert',2,1,'uuu') |
    	+-------------------------------+-------------------------------+--------------------------------+
    	| tuutinsert                    | tuusert                       | tuuustinsert                   |
    	+-------------------------------+-------------------------------+--------------------------------+
    	1 row in set (0.00 sec)
    
    24、比较字符串大小的函数strcmp
    	mysql> select strcmp('strcmptest','strcmptest'),strcmp('stacmp','strcmp'),strcmp('strcmp','stacmp');
    	+-----------------------------------+---------------------------+---------------------------+
    	| strcmp('strcmptest','strcmptest') | strcmp('stacmp','strcmp') | strcmp('strcmp','stacmp') |
    	+-----------------------------------+---------------------------+---------------------------+
    	|                                 0 |                        -1 |                         1 |
    	+-----------------------------------+---------------------------+---------------------------+
    	1 row in set (0.00 sec)
    
    25、获取子串函数substring
    	mysql> select substring('substringtest',4,3),substring('substringtest',4);
    	+--------------------------------+------------------------------+
    	| substring('substringtest',4,3) | substring('substringtest',4) |
    	+--------------------------------+------------------------------+
    	| str                            | stringtest                   |
    	+--------------------------------+------------------------------+
    	1 row in set (0.00 sec)
    
    26、字符串逆序函数reverse
    	mysql> select reverse('abcdefg'),reverse('学习MYSQL');
    	+--------------------+------------------------+
    	| reverse('abcdefg') | reverse('学习MYSQL')   |
    	+--------------------+------------------------+
    	| gfedcba            | LQSYM习学              |
    	+--------------------+------------------------+
    	1 row in set (0.00 sec)
    
    27、返回指定字符串位置的函数field
    	mysql> select field('ab', 'abc','cd','of','ab','ab'),field(null, 'abc','cd','of','ab',null);
    	+----------------------------------------+----------------------------------------+
    	| field('ab', 'abc','cd','of','ab','ab') | field(null, 'abc','cd','of','ab',null) |
    	+----------------------------------------+----------------------------------------+
    	|                                      4 |                                      0 |
    	+----------------------------------------+----------------------------------------+
    	1 row in set (0.00 sec)
    
    28、返回指定日期对应的工作日索引dayofweek和weekday				dayofweek从1周日开始 7为周六，weekday从0周一开始，6周日结束
    	mysql> select dayofweek('2019-1-30'),weekday('2019-1-30');
    	+------------------------+----------------------+
    	| dayofweek('2019-1-30') | weekday('2019-1-30') |
    	+------------------------+----------------------+
    	|                      4 |                    2 |
    	+------------------------+----------------------+
    	1 row in set (0.03 sec)
    
    29、返回指定日期所在月中的日期索引dayofmonth
    	mysql> select dayofmonth('2017-4-4 0:18:20'),dayofmonth('2017-4-2'),dayofmonth('2017-2-30');
    	+--------------------------------+------------------------+-------------------------+
    	| dayofmonth('2017-4-4 0:18:20') | dayofmonth('2017-4-2') | dayofmonth('2017-2-30') |
    	+--------------------------------+------------------------+-------------------------+
    	|                              4 |                      2 |                    NULL |
    	+--------------------------------+------------------------+-------------------------+
    	1 row in set, 1 warning (0.00 sec)
    
    30、返回指定日期所在年中的日期索引dayofyear
    	mysql> select dayofyear('2017-4-4 0:18:20'),dayofyear('2017-4-1'),dayofyear('2017-2-30');
    	+-------------------------------+-----------------------+------------------------+
    	| dayofyear('2017-4-4 0:18:20') | dayofyear('2017-4-1') | dayofyear('2017-2-30') |
    	+-------------------------------+-----------------------+------------------------+
    	|                            94 |                    91 |                   NULL |
    	+-------------------------------+-----------------------+------------------------+
    	1 row in set, 1 warning (0.00 sec)
    	
    31、返回指定日期对应的月份month
    	mysql> select month('2017-4-4 0:22:22'),month('2007-4-2'),month('2017-2-30');
    	+---------------------------+-------------------+--------------------+
    	| month('2017-4-4 0:22:22') | month('2007-4-2') | month('2017-2-30') |
    	+---------------------------+-------------------+--------------------+
    	|                         4 |                 4 |               NULL |
    	+---------------------------+-------------------+--------------------+
    	1 row in set, 1 warning (0.00 sec)
    
    32、返回指定日期对应的月名称monthname
    	mysql> select monthname('2017-4-4 0:22:22'),monthname('2007-4-2'),monthname('2017-2-30');
    	+-------------------------------+-----------------------+------------------------+
    	| monthname('2017-4-4 0:22:22') | monthname('2007-4-2') | monthname('2017-2-30') |
    	+-------------------------------+-----------------------+------------------------+
    	| April                         | April                 | NULL                   |
    	+-------------------------------+-----------------------+------------------------+
    	1 row in set, 1 warning (0.00 sec)
    	
    33、返回指定日期对应的工作日名称dayname
    	mysql> select dayname('2017-4-4 0:22:22'),dayname('2007-4-2'),dayname('2017-2-30');
    	+-----------------------------+---------------------+----------------------+
    	| dayname('2017-4-4 0:22:22') | dayname('2007-4-2') | dayname('2017-2-30') |
    	+-----------------------------+---------------------+----------------------+
    	| Tuesday                     | Monday              | NULL                 |
    	+-----------------------------+---------------------+----------------------+
    	1 row in set, 1 warning (0.00 sec)
    	
    34、返回指定日期对应的季度quarter
    	mysql> select quarter('2017-4-4 0:22:22'),quarter('2007-4-2'),quarter('2017-2-30');
    	+-----------------------------+---------------------+----------------------+
    	| quarter('2017-4-4 0:22:22') | quarter('2007-4-2') | quarter('2017-2-30') |
    	+-----------------------------+---------------------+----------------------+
    	|                           2 |                   2 |                 NULL |
    	+-----------------------------+---------------------+----------------------+
    	1 row in set, 1 warning (0.00 sec)
    		
    35、返回指定日期对应的年份year
    	mysql> select year('2017-4-4 0:22:22'),year('2007-4-2'),year('2017-2-30');
    	+--------------------------+------------------+-------------------+
    	| year('2017-4-4 0:22:22') | year('2007-4-2') | year('2017-2-30') |
    	+--------------------------+------------------+-------------------+
    	|                     2017 |             2007 |              NULL |
    	+--------------------------+------------------+-------------------+
    	1 row in set, 1 warning (0.00 sec)
    
    36、返回指定时间中的小时hour、分钟minute、秒数second	
    	mysql> select hour('2017-4-4 0:28:12'),minute('2017-4-4 0:28:12'),second('2017-4-4 0:28:12');
    	+--------------------------+----------------------------+----------------------------+
    	| hour('2017-4-4 0:28:12') | minute('2017-4-4 0:28:12') | second('2017-4-4 0:28:12') |
    	+--------------------------+----------------------------+----------------------------+
    	|                        0 |                         28 |                         12 |
    	+--------------------------+----------------------------+----------------------------+
    	1 row in set (0.00 sec)
    
    37、增加月份函数period_add
    	mysql> select period_add(1201,5),period_add(201101,12);
    	+--------------------+-----------------------+
    	| period_add(1201,5) | period_add(201101,12) |
    	+--------------------+-----------------------+
    	|             201206 |                201201 |
    	+--------------------+-----------------------+
    	1 row in set (0.00 sec)
    
    38、取月份差函数period_diff
    	mysql> select period_diff(1201,1208),period_diff(201704,201104);
    	+------------------------+----------------------------+
    	| period_diff(1201,1208) | period_diff(201704,201104) |
    	+------------------------+----------------------------+
    	|                     -7 |                         72 |
    	+------------------------+----------------------------+
    	1 row in set (0.00 sec)
    
    39、返回当前日期函数curdate和current_date
    	mysql> select curdate(), current_date;
    	+------------+--------------+
    	| curdate()  | current_date |
    	+------------+--------------+
    	| 2017-04-04 | 2017-04-04   |
    	+------------+--------------+
    	1 row in set (0.00 sec)
    
    40、返回当前时间函数curtime和current_time
    	mysql> select curtime(), current_time;
    	+-----------+--------------+
    	| curtime() | current_time |
    	+-----------+--------------+
    	| 00:35:39  | 00:35:39     |
    	+-----------+--------------+
    	1 row in set (0.00 sec)
    
    41、获取当前时间日期函数now和sysdate
    	mysql> select now(),sysdate(),sysdate()+0;
    	+---------------------+---------------------+----------------+
    	| now()               | sysdate()           | sysdate()+0    |
    	+---------------------+---------------------+----------------+
    	| 2017-04-04 00:36:37 | 2017-04-04 00:36:37 | 20170404003637 |
    	+---------------------+---------------------+----------------+
    	1 row in set (0.00 sec)
    
    42、秒转换时间函数sec_to_time
    	mysql> select sec_to_time(60),sec_to_time(180),sec_to_time(8180);
    	+-----------------+------------------+-------------------+
    	| sec_to_time(60) | sec_to_time(180) | sec_to_time(8180) |
    	+-----------------+------------------+-------------------+
    	| 00:01:00        | 00:03:00         | 02:16:20          |
    	+-----------------+------------------+-------------------+
    	1 row in set (0.00 sec)
    
    43、时间转换秒函数time_to_sec
    	mysql> select time_to_sec('00:01:23'),time_to_sec('1:20:21');
    	+-------------------------+------------------------+
    	| time_to_sec('00:01:23') | time_to_sec('1:20:21') |
    	+-------------------------+------------------------+
    	|                      83 |                   4821 |
    	+-------------------------+------------------------+
    	1 row in set (0.00 sec)
    
    44、返回当前用户名函数session_user
    	mysql> select session_user();
    	+----------------+
    	| session_user() |
    	+----------------+
    	| root@localhost |
    	+----------------+
    	1 row in set (0.00 sec)
    		
    45、返回当前数据库名称函数database
    	mysql> select database();
    	+------------+
    	| database() |
    	+------------+
    	| test       |
    	+------------+
    	1 row in set (0.00 sec)
    		
    46、返回字符串MD5校验和函数md5
    	mysql> select md5('md5test');
    	+----------------------------------+
    	| md5('md5test')                   |
    	+----------------------------------+
    	| 82da61aa724b5d149a9c5dc8682c2a45 |
    	+----------------------------------+
    	1 row in set (0.00 sec)
    		
    /*****************************************************************************/