Oracle迁移到mysql-表结构的坑

1.mysql中id自增字段必须是整数类型

  id               BIGINT AUTO_INCREMENT not null,
2.VARCHAR2改为VARCHAR

3.NUMBER(16)改为decimal(16,0)

4.date改为datetime

5.mysql范围分区必须int格式，不能list类型

ERROR 1697 (HY000): VALUES value for partition 'P_SHENYANG' must have type INT

6.获取注释信息：参考

迁移oracle表注释列注释到mysql_在navicat中,oracle查询所有表的字段注释,并且将这些字段注释放到mysql库中对应表-CSDN博客 

ALTER TABLE SW_SWITCH COMMENT '交换节点（局向）';ALTER TABLE SW_SWITCH MODIFY COLUMN code VARCHAR(64) COMMENT '交换节点编号';

 

7.auto_increment分区键字段值字段都得为主键

限制条件如下测试：

mysql> create table sw_decimal_res_bas-> (->   id               BIGINT AUTO_INCREMENT not null,->   zone_decimal       decimal(16,0),->   ziguan            VARCHAR(16),->   start_decimal      VARCHAR(16),->   end_decimal        VARCHAR(16),->   type              VARCHAR(16),->   switch_tab        VARCHAR(50),->   switch_id         decimal(16,0),->   ziguan_discrip    VARCHAR(64),->   open_time         VARCHAR(16),->   lose_time         VARCHAR(16),->   opreator          VARCHAR(16),->   build_time        datetime,->   area_id           VARCHAR(16),->   notes             VARCHAR(128),->   blank1            VARCHAR(255),->   blank2            VARCHAR(255),->   ne_id             decimal(16,0),->   jx                VARCHAR(64),->   countrytype       decimal(12,0),->   server_areaid     INT,->   kind              VARCHAR(16),->   occtype           VARCHAR(16),->   locationaddr      VARCHAR(128),->   quality           VARCHAR(16),->   switchcommandflag decimal(6,0) default 1,->   physicalsegmentid decimal(16,0)-> )-> partition by range (SERVER_AREAID)-> (->   partition P_SHENYANG values less than ('2102')-> ,->   partition P_DALIAN values less than ('2103')-> ,->   partition P_ANSHAN values less than ('2104')-> ,->   partition P_FUSHUN values less than ('2105')-> ,->   partition P_BENXI values less than ('2106')-> ,->   partition P_DANDONG values less than ('2107')-> ,->   partition P_JINZHOU values less than ('2108')-> ,->   partition P_YINGKOU values less than ('2109')-> ,->   partition P_FUXIN values less than ('2110')->  ,->   partition P_LIAOYANG values less than ('2111')->  ,->   partition P_PANJIN values less than ('2112')->  ,->   partition P_TIELING values less than ('2113')->  ,->   partition P_CHAOYANG values less than ('2114')->  ,->   partition P_HULUDAO values less than ('2115')->  ,->   partition P_OTHERS values less than (MAXVALUE)-> );
ERROR 1697 (HY000): VALUES value for partition 'P_SHENYANG' must have type INT
mysql> 
mysql> create table sw_decimal_res_bas-> (->   id               BIGINT AUTO_INCREMENT not null,->   zone_decimal       decimal(16,0),->   ziguan            VARCHAR(16),->   start_decimal      VARCHAR(16),->   end_decimal        VARCHAR(16),->   type              VARCHAR(16),->   switch_tab        VARCHAR(50),->   switch_id         decimal(16,0),->   ziguan_discrip    VARCHAR(64),->   open_time         VARCHAR(16),->   lose_time         VARCHAR(16),->   opreator          VARCHAR(16),->   build_time        datetime,->   area_id           VARCHAR(16),->   notes             VARCHAR(128),->   blank1            VARCHAR(255),->   blank2            VARCHAR(255),->   ne_id             decimal(16,0),->   jx                VARCHAR(64),->   countrytype       decimal(12,0),->   server_areaid     INT,->   kind              VARCHAR(16),->   occtype           VARCHAR(16),->   locationaddr      VARCHAR(128),->   quality           VARCHAR(16),->   switchcommandflag decimal(6,0) default 1,->   physicalsegmentid decimal(16,0)-> )-> partition by range (SERVER_AREAID)-> (->   partition P_SHENYANG values less than (2102)-> ,->   partition P_DALIAN values less than (2103)-> ,->   partition P_ANSHAN values less than (2104)-> ,->   partition P_FUSHUN values less than (2105)-> ,->   partition P_BENXI values less than (2106)-> ,->   partition P_DANDONG values less than (2107)-> ,->   partition P_JINZHOU values less than (2108)-> ,->   partition P_YINGKOU values less than (2109)-> ,->   partition P_FUXIN values less than (2110)->  ,->   partition P_LIAOYANG values less than (2111)->  ,->   partition P_PANJIN values less than (2112)->  ,->   partition P_TIELING values less than (2113)->  ,->   partition P_CHAOYANG values less than (2114)->  ,->   partition P_HULUDAO values less than (2115)->  ,->   partition P_OTHERS values less than (MAXVALUE)-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> 
mysql> 
mysql> create table sw_decimal_res_bas-> (->   id               BIGINT AUTO_INCREMENT not null,->   zone_decimal       decimal(16,0),->   ziguan            VARCHAR(16),->   start_decimal      VARCHAR(16),->   end_decimal        VARCHAR(16),->   type              VARCHAR(16),->   switch_tab        VARCHAR(50),->   switch_id         decimal(16,0),->   ziguan_discrip    VARCHAR(64),->   open_time         VARCHAR(16),->   lose_time         VARCHAR(16),->   opreator          VARCHAR(16),->   build_time        datetime,->   area_id           VARCHAR(16),->   notes             VARCHAR(128),->   blank1            VARCHAR(255),->   blank2            VARCHAR(255),->   ne_id             decimal(16,0),->   jx                VARCHAR(64),->   countrytype       decimal(12,0),->   server_areaid     INT,->   kind              VARCHAR(16),->   occtype           VARCHAR(16),->   locationaddr      VARCHAR(128),->   quality           VARCHAR(16),->   switchcommandflag decimal(6,0) default 1,->   physicalsegmentid decimal(16,0)-> )-> partition by range (id,SERVER_AREAID)-> (->   partition P_SHENYANG values less than (2102)-> ,->   partition P_DALIAN values less than (2103)-> ,->   partition P_ANSHAN values less than (2104)-> ,->   partition P_FUSHUN values less than (2105)-> ,->   partition P_BENXI values less than (2106)-> ,->   partition P_DANDONG values less than (2107)-> ,->   partition P_JINZHOU values less than (2108)-> ,->   partition P_YINGKOU values less than (2109)-> ,->   partition P_FUXIN values less than (2110)->  ,->   partition P_LIAOYANG values less than (2111)->  ,->   partition P_PANJIN values less than (2112)->  ,->   partition P_TIELING values less than (2113)->  ,->   partition P_CHAOYANG values less than (2114)->  ,->   partition P_HULUDAO values less than (2115)->  ,->   partition P_OTHERS values less than (MAXVALUE)-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',SERVER_AREAID)
(partition P_SHENYANG values less than (2102)
,partition P' at line 31
mysql> create table sw_decimal_res_bas-> (->   id               BIGINT AUTO_INCREMENT not null,->   zone_decimal       decimal(16,0),->   ziguan            VARCHAR(16),->   start_decimal      VARCHAR(16),->   end_decimal        VARCHAR(16),->   type              VARCHAR(16),->   switch_tab        VARCHAR(50),->   switch_id         decimal(16,0),->   ziguan_discrip    VARCHAR(64),->   open_time         VARCHAR(16),->   lose_time         VARCHAR(16),->   opreator          VARCHAR(16),->   build_time        datetime,->   area_id           VARCHAR(16),->   notes             VARCHAR(128),->   blank1            VARCHAR(255),->   blank2            VARCHAR(255),->   ne_id             decimal(16,0),->   jx                VARCHAR(64),->   countrytype       decimal(12,0),->   server_areaid     INT,->   kind              VARCHAR(16),->   occtype           VARCHAR(16),->   locationaddr      VARCHAR(128),->   quality           VARCHAR(16),->   switchcommandflag decimal(6,0) default 1,->   physicalsegmentid decimal(16,0),PRIMARY KEY (id)-> )-> partition by range (SERVER_AREAID)-> (->   partition P_SHENYANG values less than (2102)-> ,->   partition P_DALIAN values less than (2103)-> ,->   partition P_ANSHAN values less than (2104)-> ,->   partition P_FUSHUN values less than (2105)-> ,->   partition P_BENXI values less than (2106)-> ,->   partition P_DANDONG values less than (2107)-> ,->   partition P_JINZHOU values less than (2108)-> ,->   partition P_YINGKOU values less than (2109)-> ,->   partition P_FUXIN values less than (2110)->  ,->   partition P_LIAOYANG values less than (2111)->  ,->   partition P_PANJIN values less than (2112)->  ,->   partition P_TIELING values less than (2113)->  ,->   partition P_CHAOYANG values less than (2114)->  ,->   partition P_HULUDAO values less than (2115)->  ,->   partition P_OTHERS values less than (MAXVALUE)-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
mysql> create table sw_decimal_res_bas-> (->   id               BIGINT AUTO_INCREMENT not null,->   zone_decimal       decimal(16,0),->   ziguan            VARCHAR(16),->   start_decimal      VARCHAR(16),->   end_decimal        VARCHAR(16),->   type              VARCHAR(16),->   switch_tab        VARCHAR(50),->   switch_id         decimal(16,0),->   ziguan_discrip    VARCHAR(64),->   open_time         VARCHAR(16),->   lose_time         VARCHAR(16),->   opreator          VARCHAR(16),->   build_time        datetime,->   area_id           VARCHAR(16),->   notes             VARCHAR(128),->   blank1            VARCHAR(255),->   blank2            VARCHAR(255),->   ne_id             decimal(16,0),->   jx                VARCHAR(64),->   countrytype       decimal(12,0),->   server_areaid     INT,->   kind              VARCHAR(16),->   occtype           VARCHAR(16),->   locationaddr      VARCHAR(128),->   quality           VARCHAR(16),->   switchcommandflag decimal(6,0) default 1,->   physicalsegmentid decimal(16,0),PRIMARY KEY (id,SERVER_AREAID)-> )-> partition by range (SERVER_AREAID)-> (->   partition P_SHENYANG values less than (2102)-> ,->   partition P_DALIAN values less than (2103)-> ,->   partition P_ANSHAN values less than (2104)-> ,->   partition P_FUSHUN values less than (2105)-> ,->   partition P_BENXI values less than (2106)-> ,->   partition P_DANDONG values less than (2107)-> ,->   partition P_JINZHOU values less than (2108)-> ,->   partition P_YINGKOU values less than (2109)-> ,->   partition P_FUXIN values less than (2110)->  ,->   partition P_LIAOYANG values less than (2111)->  ,->   partition P_PANJIN values less than (2112)->  ,->   partition P_TIELING values less than (2113)->  ,->   partition P_CHAOYANG values less than (2114)->  ,->   partition P_HULUDAO values less than (2115)->  ,->   partition P_OTHERS values less than (MAXVALUE)-> );
Query OK, 0 rows affected (0.03 sec)mysql>