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>