Oracle向mysql进行数据迁移

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;