Oracle和MySQL函数对比

项目改造上云，把Oracle语法改成MySQL语法
MySQL日期计算函数比较耗性能，建议JAVA后台处理

时间函数

select TIMESTAMPDIFF(HOUR, '2018-03-28 09:00:00', '2018-03-28 02:00:00');
TIMESTAMPDIFF(unit, start, end)
计算结果：end-start

SELECT DATEDIFF('2020-02-22 09:00:00','2020-02-10 09:00:00')
DATEDIFF(start,end)
计算结果：start-end

• Oracle中用法

and shipment.plan_departure_time >= sysdate-7
and shipment.insert_date>=sysdate-14
and shipment.insert_date <=sysdate

• mysql中用法

and DATEDIFF(shipment.plan_departure_time,NOW()) >= -7
and DATEDIFF(shipment.insert_date,NOW())>=-14
and DATEDIFF(shipment.insert_date,NOW()) <=0

• Oracle中用法

 SH.VEHICLE_TYPE_NAME||'('||SH.CAPACITY_VOLUME||'方)'        AS "vehicleType"

• MySQL中用法

concat(SH.VEHICLE_TYPE_NAME,'(',SH.CAPACITY_VOLUME,'方)')        AS "vehicleType",

• Oracle中用法

TO_CHAR(SH.PLAN_DEPARTURE_TIME ,'yyyy-mm-dd hh24:mi') AS planDepartureTime

• MySQL中用法

DATE_FORMAT(SH.PLAN_DEPARTURE_TIME ,'%Y-%m-%d %T') AS planDepartureTime

函数替代

Oracle NVL()函数，替换方案 MySQL IFNULL()
Oracle substr()函数，替换方案 MySQL substr()函数
Oracle to_char()，替换方案 MySQL date_format()函数
Oracle to_date()，替换方案 MySQL str_to_date()函数
Oracle ceil()，替换方案 MySQL ceiling()函数、TIMESTAMPDIFF函数
Oracle rownum，替换方案MySQL limit

分组后对组内数据排序

-- Oracle
select transport_task_no, tasknote from(
  select n.transport_task_no,n.tasknote,row_number() 
  over (partition by n.transport_task_no order by n.CREATE_TIME desc) as rn 
  from sto_loadometer n
)   where rn=1

-- MySQL
SELECT transport_task_no,
   SUBSTRING_INDEX(GROUP_CONCAT(n.tasknote ORDER BY n.create_time desc ),',',1) tasknote
   FROM (SELECT transport_task_no,tasknote,create_time FROM sto_loadometer) n 
   GROUP BY transport_task_no

相关文档

• mysql与oracle的语法对比