做oracle->mysql数据迁移的一点笔记(更新)
1.nvl函数
oracle:nvl(a,b) 有点类似if else
<=>mysql:ifnull(a,b)
2.to_number略有区别
oracle:to_number(a)
≈>mysql:cast(a as decimal)
3.mysql中分支长语句要用()
4.获取当前系统时间(精确到秒,格式有差别)
oracle:sysdate
≈>mysql:now()
5.左右连接(有加号的一边为补充匹配表,不一定全部显示)
1)右连接
oracle:select * from a, b where a.id(+) = b.id
<=>mysql:select * from a right join b on a.id = b.id
2)左连接
oracle:select * from a, b where a.id = b.id(+)
<=>mysql:select * from a left join b on a.id = b.id
6.指定查询结果条数
oracle:select * from a where a.id =:id and rownum <=1
<=>mysql:select * from a where a.id =:id limit 0,1
PS:
1)mysql的limit后第一个参数表示起始位置,第二个表示数量(查询所有列用-1);limit n <=>limit 0,n
2)oracle行数从1开始,mysql初始记录行的偏移量是0
3)oracle查询2-8行:select * from area where rownum <= 8 minus select * from area where rownum < 2
7.时间格式转换函数
1)字符串转换为Date类型
oracle:
select to_date('2018-01-01 01:01:01','yyyy-mm-dd hh24:mi:ss') from dual;
运行结果:
mysql:第二个参数注意大小写格式有区别
select str_to_date('2018-01-01 01:01:01','%Y-%m-%d %H:%i:%s') from dual;
运行结果:
2)Date类型转换为字符串
oracle:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
运行结果:
mysql:
select date_format(now(),'%Y/%m/%d %H:%i:%s') from dual;
运行结果: