关于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