Oracle和MySQL函数对比

青旅半醒 2023-07-04 08:54 12阅读 0赞

> 项目改造上云，把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的语法对比][mysql_oracle]

[mysql_oracle]: https://www.cnblogs.com/wlzjdm/p/10420727.html