最近由于项目换库,特此记录
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