Oracle转mysql常用函数对应说明

最新推荐文章于 2025-04-28 22:07:19 发布

java_sso_yw

最新推荐文章于 2025-04-28 22:07:19 发布

阅读量849

点赞数 5

CC 4.0 BY-SA版权

文章标签： oracle mysql adb

本文链接：https://blog.youkuaiyun.com/java_sso_yw/article/details/141222692

oracle和mysql的区别

简单函数之间的转换

函数 | oracle |mysql ------ | ------- | ------- | 《日期》转字符串 | tochar | dateformat （一定参考最下方） | | 字符串转日期 | todate | str_todate | | 判断空值 | nvl | IFNULL | 转换数字 | tonumber | cast （见下方说明） | 条件判断 | decode | case then或if else；例子：if(T.PLANSTATUS=6,1,0) | 时间串拼接 | numtodsinterval| contact | decimal的转换 | tochar | cast(column as char) （见下方说明） | 当前时间 | sysdate | now() | substring区别 | 开始位置可以为0 | 开始位置不能为0 | 时间的计算 | 可以直接相减（默认单位：天）| 使用 TIMESTAMPDIFF（需指定默认单位） | 分组排序 | row number over | 使用变量来实现 | 分组拼接| wmconcat | group_concat | instr | instr | instr | 列转行 | pivot| case when | 行数 | where/and rownum=1 或 rownum 6 | limit 1 （目前替换较多）

listagg within group mysql

Oracle

LISTAGG ( DATA_PATH, ',' ) WITHIN GROUP ( ORDER BY DATA_INFO ) datapath

Mysql

group_concat(DATA_PATH ORDER BY DATA_INFO) datapath

row_number() over

例子：

Oracle

 ( SELECT ROW_NUMBER () OVER ( PARTITION BY t1.biz_id ORDER BY t1.create_time DESC ) rn, t1.* FROM wf_order t1 )

Mysql

 1、推荐
   SELECT

    @curRow := @curRow + 1 AS rn
    FROM
        t_00_equip t
    JOIN (SELECT @curRow := 0) r
2、
SELECT count(o.biz_id) as rn,o.* from wf_order o
    LEFT OUTER JOIN wf_order r  on o.biz_id = r.biz_id  
      group by o.biz_id,o.create_time
     ORDER BY o.create_time DESC 
    ) rn

Oracle 与Mysql排序问题

mysql默认null值小

order by XXX  asc ，则null值的数据排在最前 
order by XXX desc ，则null值的数据排在最后 

同时mysql  使用 IFNULL(列名,默认值)处理null

此外 还可以   IF(ISNULL(列名),1,0) 来设置  NULL值  和  非NULL值的值,然后再进行排序

Oracle则默认null最大

order by XXX  asc ，则null值的数据排在最后
order by XXX desc ，则null值的数据排在最前

jpa兼容

需要加上{},新开发模块禁用

例如： and MAIN_ID = :mainId 转换  and MAIN_ID = ?

关键字

查询不能使用关键字，如 key，DESCRIBE

表字段更给改：

  1. T_01_JC_BASE_TASK_OPERATE   DESCRIBE-> DESCRIBE_INFO
  2.

不能使用说明：

 deleted=0 {and KEY like %:key%}"

别名问题

如下：给子查询加个别名

 select t.ft_month, count(1) defect_num from
    (select *where  a) t 
 group by t.ft_month

列转行

Oracle

Mysql

 SELECT
CASE
wq.job_ticket_type 
WHEN 1 THEN
'第一种工作票' 
WHEN 2 THEN
'第二种工作票' 
WHEN 3 THEN
'第三种工作票' ELSE '无' 
END job_ticket_type_name,
wq.job_ticket_type,
SUM( IF ( wq.status_code = 'publish', wq.numcount, 0 ) ) AS 'publishcount',
SUM( IF ( wq.status_code = 'revoke', wq.numcount, 0 ) ) AS 'revokecount' 
 FROM(
SELECT
    r.status_code,
    r.job_ticket_type,
    count( r.id ) numcount 
FROM
    T_01_JX_JOB_MAIN r 
WHERE
    r.deleted = 0 
    AND r.status_code IS NOT NULL 
GROUP BY
    r.job_ticket_type,
    r.status_code 
    ) wq 
WHERE
    wq.job_ticket_type IS NOT NULL 
ORDER BY
    wq.job_ticket_type

if else case when

case a1.status when '1' then '一级'   when '2' then '二级' when '3' then '三级' else '' end 'level_name'

mysql中cast用法

语法：CAST( value AS type ),type的类型有

类型 | 描述 ------------- | ------------- DATE | 日期，格式为 'YYYY-MM-DD'. DATETIME | 日期加具体的时间，格式为 'YYYY-MM-DD HH:MM:SS'. TIME | 时间，格式为 'HH:MM:SS'. CHAR | 字符型 SIGNED | int UNSIGNED | 无符号int BINARY | 二进制型 DECIMAL | float型

Mysql WITH AS语法

目前我们数据库版本（5.7.26），并不支持with as 语法。所以需要修改使用了with as语法的sql语句。

Oracle和Mysql中的字符串的拼接

Mysql

在Java、C#等编程语言中字符串的拼接可以通过加号“+”来实现，比如： "1"+"3"、 "a"+"b"。在MYSQL中也可以使用加号“+”来连接两个字符串，比如下面的SQL：

   SELECT '12'+'33',status+'1' FROM T_01_JC_FT_RECORD  
  如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串.

与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的：

   SELECT CONCAT('工号为',Number,'的员工姓名为',FName) FROM T_Employee WHERE FName IS NOT NULL

运行以后Oracle会报出下面的错误信息：参数个数无效

如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用，上面的SQL可以如：

  SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM T_Employee WHERE FName IS NOT NULL

查询:

一、当前时间–(sysdate)

oracle语句:

    select sysdate from dual

mysql语句:

    select now()

二、日期转字符串–(to_char)

oracle语句:

    select  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

mysql语句:

    select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

三、字符串转日期–(to_date)

oracle语句:

    select to_date('2019-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual

mysql语句:

    select STR_TO_DATE('2019-01-01 08:00:00',"%Y-%m-%d %H:%i:%s")

四、判断空值–(nvl)

oracle语句:

    NVL(a,b)

mysql语句:

    IFNULL(a,b)

五、转换数字–(to_number)

oracle语句:

    to_number(column)

mysql语句:

转换成整形：
    cast(column as unsigned int)
转换成浮点型：
    cast(column as decimal(10,2))

六、条件判断–(decode)

oracle语句:

select decode(mod(status,7),0,'A',1,'B','C') as qtype from T_01_JC_FT_RECORD

mysql语句，使用case when：

    select case mod(quantity,7) when 0 then 'A'
        when 1 then 'B'
        else 'C'
        end as qtype
    from T_01_JC_FT_RECORD

关于if else：不推荐使用if-else，因为我写不出来，建议使用case when,

七、时间串拼接–(numtodsinterval)

oracle语句:

    select a.status,
        case when length(ltrim(substr(numtodsinterval(ceil(a.status), 'second'),2,15),'0')) = 6
        then '0天' || substr(numtodsinterval(ceil(a.status), 'second'),12,8)
        when a.status is null
        then ''
        else ltrim(substr(numtodsinterval(ceil(a.status), 'second'),2,9),'0') || '天' ||
        substr(numtodsinterval(ceil(a.status), 'second'),12,8)
        end as ReportWaitTime 
    from T_01_JC_FT_RECORD a

mysql语句:

SELECT
    a.*,
    CONCAT(
    FLOOR( a.status / 86400 ),
    '天',
    LPAD( FLOOR( a.status % 86400 / 3600 ), 2, 0 ),
    ':',
    LPAD( FLOOR( a.status % 86400 % 3600 / 60 ), 2, 0 ),
    ':',
    LPAD( CEIL( a.status % 86400 % 3600 % 60 ), 2, 0 )
    ) AS ReportWaitTime 
 FROM T_01_JC_FT_RECORD a

八、decimal的转换

oracle语句:

    select to_char(status) from T_01_JC_FT_RECORD

mysql语句:

需要注意的是，在mysql中转换时不能使用as varchar：
    //SELECT cast(status as VARCHAR) from T_01_JC_FT_RECORD  ----这是错误的语法
    SELECT cast(status as CHAR) from T_01_JC_FT_RECORD ----这是正确的语法

九、substring的区别

oracle语句:

    select substr('1234567890',0,2) from dual   //返回结果12

mysql语句:

    select substr('1234567890',1,2)     //返回结果12，即相同返回结果

十、时间的计算

oracle语句:

select CREATE_TIME,UPDATE_TIME,UPDATE_TIME-CREATE_TIME t from T_01_JC_FT_RECORD
    //返回结果以天为单位

mysql语句:

    select UPDATE_TIME,CREATE_TIME,TIMESTAMPDIFF(second,UPDATE_TIME,CREATE_TIME) t from T_01_JC_FT_RECORD 
 //返回结果以秒为单位,直接相减默认结果说实话我没看懂是以什么为单位，它是每分钟为100，秒数作为十分位和百分位，逢60就加100。

补充知识说明：

1.在mysql中如何获取当前周

oracle语句:

    select to_char(sysdate,'iw') from dual

返回结果：

这表示2019-08-18是该年的第33个周 mysql语句:

    SELECT DATE_FORMAT(now(),'%u')
    //大写U与小写u不一样

返回结果：

下面附上mysql中日期标识符的作用,

    %M 月名字(January……December) 
    %W 星期名字(Sunday……Saturday) 
    %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
    %Y 年, 数字, 4 位   www.2cto.com  
    %y 年, 数字, 2 位 
    %a 缩写的星期名字(Sun……Sat) 
    %d 月份中的天数, 数字(00……31) 
    %e 月份中的天数, 数字(0……31) 
    %m 月, 数字(01……12) 
    %c 月, 数字(1……12) 
    %b 缩写的月份名字(Jan……Dec) 
    %j 一年中的天数(001……366) 
    %H 小时(00……23) 
    %k 小时(0……23) 
    %h 小时(01……12) 
    %I 小时(01……12) 
    %l 小时(1……12) 
    %i 分钟, 数字(00……59) 
    %r 时间,12 小时(hh:mm:ss [AP]M) 
    %T 时间,24 小时(hh:mm:ss) 
    %S 秒(00……59) 
    %s 秒(00……59) 
    %p AM或PM 
    %w 一个星期中的天数(0=Sunday ……6=Saturday ） 
    %U 星期(0……52), 这里星期天是星期的第一天 
    %u 星期(0……52), 这里星期一是星期的第一天 
    %% 一个文字“%”。

事务隔离级别

    @Transactional(readOnly=true) 不能写在类上！！！！

    1.针对方法添加事务，增删改 @Transactional(readOnly=false) ，这次修改，必须整改。
    2.事务处理，例如缺陷登记，缺陷信息登记成功，图像上传失败情况，明显不在同一个事务。

常用日期格式

--------date类型截取年月--------
  SELECT left(date1,7) AS date FROM  表名
  -----dateTime类型截取年月----------
  select date_format(日期字段,'%Y-%m') as '日期'  from  表
-----dateTime类型截取年月日----------
select date_format(日期字段,'%Y-%m-%d') as  '日期'  from  表
-----dateTime类型截取年月日 时分秒----------
'%Y-%m-%d %T'  === '%Y-%m-%d %H:%i:%s'
SELECT date_format(日期字段,'%Y-%m-%d %H:%i:%s') as  '日期'  from  表

十一、connect by替换

使用方法如下：设备表也有此用法，需要单独建立函数，类似替换：

 and FIND_IN_SET(id, getOrgParentList(?))