Oracle转mysql

to_date

Oracle:
to_date(str,'yyyy-mm-dd hh24:mi:ss')
Mysql: 
str_to_date(str,'%Y-%m-%d %H:%i:%S')

格式参数可替换，如yyyy/mm/dd 对应 %Y/%m/%d

to_char

Oracle:
to_char(str,'yyyy-mm-dd hh24:mi:ss')
Mysql: 
date_format(str,'%Y-%m-%d %H:%i:%S')

格式参数可替换，如yyyy/mm/dd 对应 %Y/%m/%d

sysdate

Oracle:
sysdate
Mysql: 
sysdate()

获取系统时间，mysql要加()

rownum

Oracle:
select name,rownum from user where rownum <= 100
Mysql: 
select name,@rownum:=@rownum+1 from (select @rownum:=0) r,user limit 100

此处rownum有两个用法，一个是作为查询结果，一个是作为查询条件。

||

Oracle:
select id from user where '%'||#{name}||'%' like name
Mysql: 
select id from user where concat('%',#{name},'%') like name

使用concat来替换

，concat支持多个参数

nextval

Oracle通过nextval来获取序列号，Mysql可以通过设置主键自增或者从业务代码去获取序列号

insert all

Oracle:
insert all
 into user(id,name) values('1','王一')
 into user(id,name) values('2','王二')
 select * from dual
Mysql: 
insert
 into user(id,name) values('1','王一'),('2','王二')

三个地方要注意，一是insert all改为insert，二是不用重复写表名和字段和values，三是不用写select * from dual

nulls first/null last

Oracle:
select * from user order by id desc nulls first
select * from user order by id asc nulls last
Mysql: 
select * from user order by if(isnull(id),0,1) asc,id desc 或者 select * from user order by if(isnull(id),1,0) desc,id desc
select * from user order by if(isnull(id),0,1) desc,id asc 或者 select * from user order by if(isnull(id),1,0) asc,id desc

两个地方要注意，一是mysql默认在asc的时候把null放最前面，desc的时候把null放最后面，所以这两种情况不用特意给null排序，二是if(isnull())仅区分null和非null排序，需要加一个正常的字段排序

decode

Oracle:
select decode(sex,'1','男','2','女') from user
select decode(sex,'1','男','2','女','未定') from user
Mysql: 
select case sex when '1' then '男' when '2' then '女' end sex from user
select case sex when '1' then '男' when '2' then '女' else '未定' end sex from user

用case when 替代decode，参数偶数个的时候加else

nvl

Oracle:
select nvl(name,'无名') from user
Mysql: 
select ifnull(name,'无名') from user

nvl2

Oracle:
select nvl2(name,'无名','有名') from user
Mysql: 
select case when name is null then '无名' else '有名' end name from user

to_number

Oracle:
select to_number(age) from user
Mysql: 
select cast(age as unsigned int) from user

start with connect by prior

mysql不支持递归函数，有两种方法可以起到替代作用。一是可以通过编写自定义函数来实现，二是通过@id这种自定义字段来实现,第一种方法在此不赘述。

Oracle:
select orgId from org_info start with orgId = '111' connect by prior orgId = pId
Mysql:
select t3.orgId from
(
    select 
        t1.orgId,
        if(
            findInSet(t1.pId,@ids) > 0,
            @ids := concat(@ids,',',t1.orgId),
            if(t1.orgId = '111',null, -1)
        ) as isChild 
    from 
        (select orgId from org_info order by orgId asc) t1,
        (select @ids := orgId from org_info where orgId = '111') t2

) t3
where isChild != -1 or isChild is null