Oracle-Mysql 函数转换

limit <=> ROWNUM

MySQL：

select * from table1 limit 10

Oracle：

SELECT * FROM (SELECT *, ROWNUM as rnumFROM table1 
)
WHERE 10 >= rnum

cast <=> TO_NUMBER

cast as signed

MySQL：

select cast(substr(t1.COLUMN_NAME, 6) as signed ) from dual

Oracle：

select TO_NUMBER(SUBSTR(t1.COLUMN_NAME, 6))  from dual

cast as unsigned

MySQL：

select cast(t1.COLUMN_NAME as unsigned) from dual

Oracle：

select TO_NUMBER(t1.COLUMN_NAME )  from dual

regexp ‘a_\d+’ <=> REGEXP_LIKE

MySQL：

select * from dual
where COLUMN_NAME regexp 'a_\\d+'

Oracle：

select * from dual
where  REGEXP_LIKE(COLUMN_NAME,'a_[0-9]+')

schema() <=> SELECT USER FROM DUAL

MySQL：

schema()

Oracle：

SELECT USER FROM DUAL

information_schema.COLUMNS表 <=> ALL_TAB_COLUMNS表

MySQL：

select * from information_schema.COLUMNS

Oracle：

select * from  ALL_TAB_COLUMNS

unix_timestamp

MySQL：

select unix_timestamp（t3.createTime）* 1000 where dual

Oracle：

select (t3.createTime - TO_DATE('1970-01-01', 'YYYY-MM-DD'))*86400000 from dual

from_unixtime <=> TO_TIMESTAMP / NUMTODSINTERVAL

MySQL：

select  from_unixtime(t4.OPERATE_DATE / 1000, '%Y-%m-%d %H:%m:%s') 
from dual

Oracle：

select TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(t1.start_time / 1000, 'SECOND')from dual

GROUP_CONCAT <=> LISTAGG

MySQL：

select GROUP_CONCAT(ename order by t1.ename separator ',') AS employees
from dual

Oracle：

select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
from dual

DATE_FORMAT <=> TO_CHAR

MySQL：

select date_format(update_time, '%Y-%m-%d %H:%i:%S')
from dual

Oracle：

select TO_CHAR(update_time, 'YYYY-MM-DD HH24:MI:SS') 
from dual