1. to_date 直接去掉
例如
select log.id from CM_LOGINLOG log where log.orgid =? and log.isAuto =? and log.userid = ? and log.loginDateTime between to_date(?,'yyyy-mm-dd hh24:mi:ss') and sysdate and rownum=1 "
select log.id from CM_LOGINLOG log where log.orgid =? and log.isAuto =? and log.userid = ? and log.loginDateTime between ? and ? limit 1
时间参数从使用String到直接使用Date参数.
如果必须使用函数运算转String, 可以DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
sysdate 在mysql中可以用now()
2. rownum<=1 改用limit 1
3. NVL(xxx, 0)函数:
mysql: ifnull(xxx,0)
4. 字符串截取
Oracle: SUBSTR()
mysql:
substring_index(str,delim,count)
截取log_data从逗号开始之后的字符:
SELECT substring_index(log_data,',',-1)
5. 字符串格式化
oracle:TO_CHAR
mysql: concat
concat(str1,str2,…)
6. nulls last/first可以使用ifnull替换掉或union