oracle到mysql数据库迁移

最近项目使用的数据库需要从oracle转到mysql，开始了程序的迁移，项目用Java开发，主要需要更改的是mybatis的xml文件中的SQL语句。这里记录下来方便以后查看。

一、创建脚本

1、oracle

以学生表TABLE_NAME 为例，列出几个两者之间的不同。以下是oracle数据库创建脚本

//判断表是否存在，存在则删除
DECLARE num NUMBER ;
BEGIN
 SELECT COUNT (1) INTO num FROM user_tables WHERE TABLE_NAME = 'STUDENT' ;
 IF num = 1 THEN
  EXECUTE IMMEDIATE 'drop table STUDENT cascade constraints' ;
 END IF ;
END ;
/
//创建表语句
CREATE TABLE STUDENT (	
	ID VARCHAR2(36), 
	NAME VARCHAR2(50), 
	AGE NUMBER, 
	SEX VARCHAR2(10)
);
//添加注释
comment on table STUDENT is '学生表';
comment on column STUDENT.ID is '主键';
comment on column STUDENT.NAME is '人员姓名';
comment on column STUDENT.AGE is '年龄';
comment on column STUDENT.SEX is '性别';
commit;

对已知的表如何查看建表语句呢，只需要执行以下语句就可以取出来

SELECT DBMS_METADATA.GET_DDL('TABLE','STUDENT') FROM DUAL;

STUDENT 是表名，dbms_metadata.get_ddl 包可以查看到某个表或者是表空间的DDL
对比两个见表脚本，手写的和自动导出的有较大的差别，导出的没有带注释

  CREATE TABLE "RSGLXHS"."STUDENT" 
 (	"ID" VARCHAR2(36), 
	"NAME" VARCHAR2(50), 
	"AGE" NUMBER, 
	"SEX" VARCHAR2(10)
 ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RSGL" 

2、mysql

mysql创建表脚本

//判断表是否存在，存在则删除
DROP TABLE IF EXISTS `STUDENT`;
//创建表
CREATE TABLE `STUDENT` (
  `STUDENT_PKID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `ID` varchar(36) NOT NULL COMMENT '主键',
  `NAME` varchar(50) DEFAULT NULL COMMENT '人员姓名',
  `AGE` int DEFAULT NULL COMMENT '年龄',
  `SEX` varchar(20) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`STUDENT_PKID`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

对已知的表如何查看建表语句呢，只需要执行以下语句就可以取出来

show create table STUDENT;

STUDENT 为表名，数据库编码方式选择 utf8mb4
原因：
utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。
utf8mb4编码的好处是：存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。
对比两个脚本语句，发现MySQL版本的脚本基本上是一致的。

CREATE TABLE `student` (
  `STUDENT_PKID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `ID` varchar(36) NOT NULL COMMENT '主键',
  `NAME` varchar(50) DEFAULT NULL COMMENT '人员姓名',
  `AGE` int(11) DEFAULT NULL COMMENT '年龄',
  `SEX` varchar(20) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`STUDENT_PKID`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COMMENT='学生表'

二、批量更新

根据查询的结果进行update更新操作。

1、Oracle

UPDATE STUDENT
SET ( NAME, AGE, SEX ) = (
	SELECT
		A0101,A0102,A0103
	FROM
		A01
	WHERE
		A01.A00 = STUDENT.A00
) WHERE
	ID= '20200515'

2、MySQL

UPDATE STUDENT
INNER JOIN (
	SELECT
		A0101,A0102,A0103
	FROM
		A01
) t ON STUDENT.A00 = t.A00
SET 
STUDENT.NAME= t.A0101,
STUDENT.AGE= t.A0102,
STUDENT.SEX = t.A0103
WHERE
	STUDENT.ID= '20200515'

参考博客：

找到一篇oracle迁移MySQL对比总结的博客，挺好的，推荐下。
【1】 https://blog.csdn.net/qq_41622282/article/details/99689319