Oracle向mysql进行数据迁移
1、 需要进行的类型替换
| 序号 | Oracle(o) | Mysql(m) |
|---|---|---|
| 1 | VARCHAR2、NVARCHAR2 | VARCHAR |
| 2 | DATE | DATETIME |
| 3 | TIMESTAMP | DATETIME |
| 4 | NUMBER | DECIMAL |
| 5 | INTEGER | DECIMAL(22,0) |
| 6 | CLOB | TEXT |
| 7 | BLOB | LONGBLOB |
| 8 | LONG RAW | LONGTEXT |
| 9 | LONG | LONGTEXT |
2、常用语法与函数对照
| 序号 | Oracle(o) | Mysql(m) | 备注 |
|---|---|---|---|
| 1 | NAME IS NULL | NAME=’’ | 空字符判断 |
| 2 | Select 1 from dual | Select 1 | (m)from不是必须 |
| 3 | Like | Like | (o)大小写敏感 |
| 4 | Sysdate() | Now() | 获取系统日期 |
| 5 | TO_CHAR() | date_format() | 时间转换为字符串 |
| 6 | TO_DATE() | str_to_date() | 字符转换为时间 |
| 7 | ROWNUM < n | Limit n-1 | 取前n-1行 |
| 8 | aIIbIIc | Concat(a,b,c) | 字符串拼接 |
| 9 | SELECT id,name, SUM(a),SUM(b) FROM EMP; | SELECT id,name, SUM(a),SUM(b) FROM EMP; | (o) 不能正常执行，select后面的列必须是分组的列或者是用了聚合函数的列 (m) 随便分组都可以 |
| 10 | row_number() over (partition by xx order by xx) rank() over (partition by xx order by xx) dense_rank() over (partition by xx order by xx) count(1) over() | 不支持 | (m)8.0及以上支持 |
| 11 | Rtrim(x,y) | Rtrim(x) | (m)只有一个参数 |
| 12 | Date1 – Date2 | Datediff(d1,d2) | (o)日期类型可直接相减；(m)需使用函数 |
| 13 | Nvl(x,y) | Ifnull(x,y) | |
| 14 | To_number() | Cast(x as 类型) | 字符串转换数字 |
3、语法报错与修改
(m) ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
(m) ERROR 1221 (HY000): Incorrect usage of UNION and limit
解决：把union all连接的两张表用括号包裹
如：
select xxx limit 1 union all select xxx limit 2
改为：
(select xxx limit 1 ) union all ( select xxx limit 2)
(m) Error : Every derived table must have its own alias
解决：每一个派生出来的表都必须有一个自己的别名
如：
select name form (select * from user)
改为：
select name form (select * from user) a
(m) Error : 1093 – You can’t specify target table ‘a’ for update in FROM clause
解决：不能先select出同一表中的某些值，再update这个表(在同一语句中)
如：
update testable t set t.age = (select age from testable where id = 1)
改为：
update testable t set t.age = (select age from(select age from testable where id = 1) a)
(m) Incorrect datetime value: ‘’ for function str_to_date
解决：从表中select数据时用到str_to_date(date,format)并不报错; 从表中select数据时用到str_to_date(date,format)并将数据insert到另一张表中报错
如：
str_to_date(date,format)
改为：
str_to_date (if(date=’’, null , date), format)
(m) 无rownum
实现，如：
select a.name,a.age ,@rownum:=@rownum+1 as rownum from (select * from user order by age) a ,(select @rownum:=0) t
(m) 字段内容及列名大小写不敏感的解决方案
解决：创建表时，将字段标记为binary，二进制大小写是敏感的 或 只要在创建表的时候指定collate为utf8_bin，就可以实现大小写敏感，如果建表时未指定，则可修改字段的校对规则，也可以实现大小写敏感。
如：
create table test(id int binary);
或
create table test(id int) character set utf8 collate utf8_bin;
或
alter table test modify id collate utf8_bin;