MYSQL常用函数



########### 日期函数 ###########

select now(); -- 当时 yyyy-mm-dd hh:mm:ss
select curdate(); -- 当天 yyyy-mm-dd
select (curdate() - interval (weekday(curdate()) + 1) day); -- 本周第一天
select (curdate() - interval (day(curdate()) - 1) day); -- 本月第一天
select (curdate() - interval (dayofyear(curdate()) - 1) day); -- 本年第一天
select (now() + interval 1 month); -- 下个月今天
select last_day(curdate()); -- 本月最后一天
select last_day(curdate()) + interval 1 day; -- 下月第一天

select date_format('2014-07-08 23:59:59','%Y-%m-%d %H:%i:%s') -- str to date

-- 查找今天的发帖
1. CREATETIME >= date_format(now(),'%Y-%m-%d') and CREATETIME <= date_format('2014-07-08 23:59:59','%Y-%m-%d %H:%i:%s')
2. date_format(CREATETIME,'%Y-%m-%d') = LEFT(now(),10)
3. date_format(CREATETIME,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')
4. CREATETIME like CONCAT(date_format(now(), '%Y-%m-%d'), '%')
5. CREATETIME >= curdate() -- and CREATETIME <= now()  -- 首选

UNIX_TIMESTAMP(SUBSTR(FROM_UNIXTIME(r.REPAYMENT_TIME)FROM 1 FOR 10)) <> UNIX_TIMESTAMP(SUBSTR(NOW()FROM 1 FOR 10))
==>
r.REPAYMENT_TIME < UNIX_TIMESTAMP(curdate())
and r.REPAYMENT_TIME >= UNIX_TIMESTAMP(curdate() + interval 1 day)

########### 其他函数 ###########
TRUNCATE(X ,D )
ROUND(X ,D )
select 12000/4578, truncate(12000/4578, 10), truncate(12000/4578, 8), round(12000/4578, 8)

IF(expr1,expr2,expr3)   -->  case when expr1 then expr2 else expr3 end
IFNULL(expr1,expr2)     -->  case when expr1 is not null then expr1 else expr2 end
CONCAT(str1 ,str2 ,...) -->  concat('%', ? '%')
select CONCAT_WS('|', '1','2','3')