Oracle转MySQL

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