mysql常用函数

1、常用日期时间处理函数

SELECT NOW()//2020-09-17 12:12:12
SELECT CURDATE()//2020-09-17

SELECT DATE_ADD(CURDATE(),INTERVAL -1 DAY)//2020-09-16
SELECT DATE_ADD(CURDATE(),INTERVAL -1 MONTH)//2020-08-17
SELECT DATE_ADD(CURDATE(),INTERVAL -1 YEAR)//2019-09-17
SELECT DATE_ADD(CURDATE(),INTERVAL -1 HOUR)//2020-09-16 23:00:00
SELECT DATE_ADD(CURDATE(),INTERVAL 1 HOUR)//2020-09-17 01:00:00
SELECT DATE_ADD(CURDATE(),INTERVAL 1 MINUTE)//2020-09-17 00:01:00
SELECT DATE_ADD(CURDATE(),INTERVAL 1 SECOND)//2020-09-17 00:00:01
SELECT DATE_ADD('2020-09-17 12:12:12',INTERVAL '01 01:01:01' DAY_SECOND)//2020-09-18 13:13:13
SELECT DATE_SUB('2020-09-17 12:12:12',INTERVAL '01:01:01' HOUR_SECOND)//2020-09-17 11:11:11

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')//2020-09-17
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')//2020-09-17 00:00:00

SELECT UNIX_TIMESTAMP()//1600307221
SELECT UNIX_TIMESTAMP('2020-09-17')//1600272000
SELECT UNIX_TIMESTAMP('2020-09-17 12:12:12')//1600315932

SELECT FROM_UNIXTIME('1600315932')//2020-09-17 12:12:12.000
SELECT FROM_UNIXTIME('1600315932','%Y-%m-%d %h:%i:%s')//2020-09-17 12:12:12

SELECT TIMEDIFF('2020-09-17 12:12:12','2020-10-19 12:12:12')//-786:00:00
SELECT TIMEDIFF('2020-09-17 12:12:12','2020-09-19 12:12:12')//-48:00:00

SELECT DATEDIFF('2020-09-17 12:12:12','2020-09-17 13:13:12')//0
SELECT DATEDIFF('2020-09-17 12:12:12','2020-09-14 13:13:12')//3

SELECT TIMESTAMPDIFF(SECOND,'2020-09-17 12:12:12','2020-09-14 13:13:12')//-255540

2、常用字符串处理函数

SELECT LENGTH("hello你")//8
SELECT CONCAT('hello','你')//hello你
SELECT INSERT('hello****',6,3,'你')//hello你*(第6位开始后面3位替换为你)
SELECT LEFT('hello***',5)//hello
SELECT TRIM(' hello ')//hello
SELECT SUBSTRING('hello',2)//ello
SELECT REPLACE('hello','h','*')//*ello
SELECT REVERSE('hello')//olleh

3、流程控制函数

SELECT IF(2=3,2,3)//3
SELECT IFNULL(2,3)//2
SELECT IFNULL(NULL,3)//3
SELECT NULLIF(2,2)//null
SELECT NULLIF(2,3)//2
SELECT CASE WHEN 1>2 THEN '1>2' ELSE '1<2' END//1<2

4、进制转换函数

SELECT CONV(9,10,2)//1001
SELECT CONV(9,10,16)//9
SELECT CONV(9,10,8)//11