关于oracle切换mysql8总结

最近由于项目换库，特此记录

1.字段类型

number(8) -> int(8)

number(16) -> bigint(16)

varchar2() -> varchar()

2.导数据

从oracle迁移数据到mysql，除了用专门的数据泵，经常需要用csv导入到mysql；

导出的csv数据如果有中文，需要先用记事本编辑成UTF-8再导入。

关于如何导出正确的csv如下：

1.对于有特殊字符的字段导入后提示不合法或者无法导入时

比如空格处理，有些oracle导出的csv数据含有不可见的换行符char(10)以及回车符chr(13)

比如有 / \ 的，一律使用如下查询消除，将\改为|，剔除回车、空格以及换行

replace(trim(replace(replace(id,chr(13),''),chr(10),'')),'\','|')

如果是clob字段，则先to_char ，然后如上一样的处理

 3.decode

oracle: update table set 字段=decode() where ...

mysql: update table set 字段=case when ... end where ...

4.rownum字段

如果是where条件后面，比如rownum=1改为 limit 1

如果是select后面，比如

select rownum from dual

改为

 SELECT
    ( SELECT @rownum := @rownum + 1 FROM ( SELECT @rownum := 0 ) rownum ) 
FROM
DUAL

5.mysql设置默认时间的字段

select CURRENT_DATE #2024-04-01
select CURRENT_TIME #15:02:54
select CURRENT_TIMESTAMP #2024-04-01 15:03:13 

 6.左右连接

对于a.id=b.id(+) 用left join，a.id(+)=b.id用right join

7.nvl函数

nvl() -> ifnull()

8.merge函数

这个比较有意思，mysql8没有能够实现的函数，代码里多写几个接口吧

9.关于时间的sql改造

记得有一次从oracle导入时间的数据，到了mysql发现时间少了8小时，注意下东八区（GMT+8）问题；

整型： to_number(id)  改为  cast(id as unsigned int)

浮点型：to_number(id)  改为  cast(id as decimal(10,2))

数字转字符串：to_char(id)  改为  convert(id,char)

时间之类的：

to_char(sysdate,'yyyy-mm-dd').  改为 date_format(字段,'%Y-%m-%d')

 (yyyy-mm-dd hh24:mi:ss) -> (%Y-%m-%d %H:%i:%s)

或者(%Y-%m-%d %T)

对于oracle的timestamp类型的数据查询导出时候需要转成年月日时分秒，

因为数据长这样：21-4月 -21 02.15.29.556170 下午

这样导入mysql会报错

to_char(wo_date,'yyyy-mm-dd hh24:mi:ss')

然后mysql里字段相应设置为datetime/timestamp即可，注意时区问题，

如果是年月日，mysql对应为date类型

10.关于时间sql运算改造

有趣的是oracle的timestamp类型字段可以直接加减乘除，对，有的业务需要除；

比如字段+1则直接按天加：

字段名+1

mysql里得这样：

date_add(sysdate(),interval 1 day)，减写成-1

这个是按天，按月如下

date_add(date_format('2024-01-02','%Y-%m-%d'),interval -8 month)

两个时间相差天数

datediff(time1,time2)

两个日期相差月数

timestampdiff(month,字段,now()) 

11.trunc函数

trunc(1.2233)对应mysql truncate(1.2233,0).  0默认保留整数位

trunc(日期)对应date_format(日期,'%Y-%m-%d')

12.拼接函数

mysql通过concat(字段,字段) 实现拼接 ||

13.start with ... connect by

这个函数着实用了不少时间改造，因为mysql5根本无法实现，必须是mysql8以上的。

这个用于带有组织表数据的分组函数，prior跟随父字段查该条件以上节点，否则查子节点

比如：

select orgId from org z 

start with z.orgid='32401'

connect by prior z.orgid = z.parentorgId;

 改造成mysql需要 使用 with recursive函数

with recursive z1(orgId) as (

     select z0.orgId from org z0 where z0.orgid='32401'

union alll

    select z2.orgid from org z2, z1

where z2.parentorgId = z1.orgId

)

select * from z1;

如果查询父节点， 则绿色部分改为 z2.orgId=z1.parentorgId

14.nulls last/first

order by id nulls last. -> order by if(isnull(id),1,0),id

order by id nulls first -> order by if(isnull(id),0,1),id

15.connect by+level

 oracle比如：

select sysdate+1-rownum sday,level from dual connect by level<=8;

这个忘记是什么业务了，不好直接改，可以先建个表，名叫temp_num，字段只有一个rn(int)，然后数据是1-100000，差不多够用就行，这个辅助表会方便很多

后期看到直接套以下语句即可

select rn,date_format(date_add(now(),interval rn-8 day),'%Y-%m-%d %T') sday, 

           8+1-rn

from temp_num tn

where rn<=8;

再来一组：

select trunc(to_date(#{lastDate},'yyyy-mm-dd')) - level +1 DDATE from dual

connect by level<=#{dateSize}

复杂了点，改为

select date_format(date_add(#{lastDate},

interval rn-#{dateSize} day), '%Y-%m-%d') DDATE

from temp_num tn

where rn<=#{dateSize}

 再来一组复杂的

select (to_date('2024-01-01','yyyy-mm-dd') + level -1 ) as every_day

from dual

connect by level<=(trunc(to_date('2024-02-01','yyyy-mm-dd')) -

trunc(to_date('2024-01-01','yyyy-mm-dd')) + 1)

这里的日期是实际上是作为参数传递的，这样写可以直接测试用

改为

SELECT
    rn,date_add( date_format( '2024-01-01', '%Y-%m-%d' ),
    INTERVAL datediff( date_format( '2024-02-01', '%Y-%m-%d' ),
    date_format( '2024-01-01', '%Y-%m-%d' ))+1-rn DAY ) AS every_day 
FROM
    temp_num tn 
WHERE rn <= datediff(date_format( '2024-02-01', '%Y-%m-%d' ),date_format( '2024-01-01', '%Y-%m-%d' )) +1

再比如

SELECT
    add_months ( to_date ( '2024-01-01', 'yyyy-mm-dd' ),- LEVEL + 1 ) dates 
FROM
    DUAL connect BY LEVEL <= 12

改为

SELECT date_add(date_format('2024-01-01','%Y-%m-%d'),interval rn-12 month) as dates
    from temp_num tn
    where rn<=12

上述代码如果复制运行报错，则是空格导致的，删除空格重新写，内容没有问题

16.listagg函数

listagg within group 改为 group_concat

listagg(字段, ';') 改为  group_concat(字段 separator ';')

再比如复杂一点的

listagg(to_char(stat_date,'MM/dd') || '!' || flag, ',') within group(order by stat_date) as data

改为

group_concat(concat(date_format(stat_date,'%m/%d'),'!',flag) order by stat_date SEPARATOR ',') as data

 17.释放表空间

delete from table ，对应myisam会立刻释放表空间，InnoDB不会；

所以在delete之后执行optimize table tableName会释放

如果optimize不支持，

先alter table tableName engine=InnoDB,

再 analyze table tableName