1、此列列表已索引
2、违反表唯一性约束
3、表中已存在这样的唯一关键字或主键
4、试图在BLOB/CLOB/用户自定义数据类型列[FULL_INDEX]上建索引
5、JSON字段改为varchar(8000),迁移后还有长度不够,改为TEXT或者Clob后正常。
===================1 此列表列已索引 测试
mysql迁移到达梦遇到报错,此列列表已索引,
检查发现mysql数据库中,相同字段支持创建多个索引,达梦数据库不支持该功能,因此报错。
mysql 源表信息:
mysql> SHOW INDEX FROM contract_bill;
+---------------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| contract_bill | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_employee_begin | 1 | employee_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_employee_begin | 2 | begin_date | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_firstparty_begin | 1 | first_party_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_firstparty_begin | 2 | begin_date | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_number_comp | 1 | company_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_number_comp | 2 | number | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_employee_begin | 1 | employee_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_employee_begin | 2 | begin_date | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_number_comp | 1 | company_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_number_comp | 2 | number | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_firstparty_begin | 1 | first_party_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| contract_bill | 1 | ix_contract_bill_firstparty_begin | 2 | begin_date | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------------+------------+-----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
13 rows in set (0.01 sec)
达梦测试建表
CREATE TABLE "CONTRACT_BILL"
(
"BEGIN_DATE" DATE,
"END_DATE" DATE,
"FIRST_PARTY_ID" INT,
"EMPLOYEE_ID" INT,
NOT CLUSTER PRIMARY KEY("ID") ) STORAGE( CLUSTERBTR) ;
执行迁移时创建索引语句
CREATE INDEX "IX_CONTRACT_BILL_FIRSTPARTY_BEGIN" on "CONTRACT_BILL"("FIRST_PARTY_ID" ASC,"BEGIN_DATE" ASC) STORAGE(CLUSTERBTR) ;
在组合索引存在下,创建单列索引(成功)
create index idx1 on "CONTRACT_BILL"("EMPLOYEE_ID");
在组合索引存在下,创建字段位置不同索引(成功)
create index idx2 on "CONTRACT_BILL"("BEGIN_DATE","EMPLOYEE_ID");
在组合索引存在下,创建更多字段的组合索引(成功)
create index idx3 on "CONTRACT_BILL"("EMPLOYEE_ID","BEGIN_DATE","FIRST_PARTY_ID");
创建升序组合索引(成功)
create index idx1 on "CONTRACT_BILL"("BEGIN_DATE" ASC ,"EMPLOYEE_ID" ASC);
创建降序组合索引(成功)
create index idx2 on "CONTRACT_BILL"("BEGIN_DATE" desc ,"EMPLOYEE_ID" DESC);
drop index idx1;
drop index idx2;
drop index idx3;
===结论:该报错只会存在与索引字段完全相同的情况下,索引字段顺序和排序不同均不受影响。
=================Oracle 数据库索引字段创建测试
SQL> desc t1;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
创建组合索引1
SQL> create index idx1 on "T1"("USERNAME","USER_ID");
Index created.
创建索引2,跟1同样字段
SQL> create index idx2 on "T1"("USERNAME","USER_ID");
create index idx2 on "T1"("USERNAME","USER_ID")
*
ERROR at line 1:
ORA-01408: such column list already indexed
创建单列索引1
SQL> create index idx4 on "T1"("USER_ID");
Index created.
创建单列索引2
SQL> create index idx5 on "T1"("USER_ID");
create index idx5 on "T1"("USER_ID")
*
ERROR at line 1:
ORA-01408: such column list already indexed
结论:Oracle 同样不支持相同字段创建多个索引
===================2、违反唯一性约束
确认过源端,表中只有主键,没有唯一性约束,只有主键,检查源库没有重复数据,取消勾选唯一性约束后迁移
===================3、表中已存在这样的唯一关键字或主键
ALTER TABLE "HCM_CORE"."WF_DETAIL_PUBLISH" ADD CONSTRAINT PRIMARY KEY("ID") ;
mysql可以在主键列再次添加unique约束,但是达梦不行。
Oracle测试
===================4、试图在BLOB/CLOB/用户自定义数据类型列[FULL_INDEX]上建索引
达梦大字段上不允许创建索引,Oracle同样不允许在大字段上创建索引。
=========Oracle环境测试
创建大字段表
SQL> create table t2(name clob,pd long);
Table created.
创建索引(失败)
SQL> create index idx1 on t2(name);
create index idx1 on t2(name)
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB
创建索引2(失败)
SQL> create index idx1 on t2(pd);
create index idx1 on t2(pd)
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
结论:Oracle同样不支持在大字段上创建索引
===================5、字段长度不够
达梦varchar字段长度按照页大小分配,最长8188,超出这个长度后,就需要开启超长记录或者修改字段类型
https://eco.dameng.com