关于oracle切换mysql8总结

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

1.使用工具为dbeaver,需要配置好mysql8的jar包,即驱动,

然后在配置里配置GMT+8,

设置为true

2.执行sql脚本时配置(工具-执行脚本):--default-character-set=utf8

1.字段类型

number(8) -> int(8)

number(16) -> bigint(16)

varchar2() -> varchar()

2.导数据

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

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

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

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

比如空格处理,有些oracle导出的csv数据含有不可见的换行符cha(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类型字段可以直接加减乘除,对,有的业务需要除;

比如date类型(无论数据是年月日还是带时分秒的)的字段+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

  • 25
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值