mysql迁移金仓数据库主键、索引丢失问题解决方案

背景：因mysql的主键默认值为PRIMARY，且无法修改，在同一数据库中索引名称也允许相同（同一表中不允许相同），而金仓主键和索引名称均不能重复，这就导致mysql向金仓数据库迁移时，相同的主键和索引不允许插入。

一.防患于未然
为避免迁移过程的繁琐，我们尽量要做到的就是从根本上解决问题，防患于未然。

1.主键
因mysql主键无法重命名，我们只能在迁移过程中去解决问题，下面会介绍。

2.索引
我们可以从命名上解决索引重复问题，以下为索引命名规范，其中（3），（4），（5）可有效解决索引名称重复问题：

(1)单张表中索引数量不超过5个。

(2)单个索引中的字段数不超过5个。

(3)非唯一索引按照“idx_表名_字段名称”进用行命名（长度不能超30，当长度超过30时，可简写表名，单表名可写前三位，如student可简写为stu，带下划线的表名如s_au_node_function,表名可简写为sanf）。

(4)唯一索引按照“uniq_表名_字段名称”进用行命名。（长度不能超30，当长度超过30时，可简写表名，单表名可写前三位，如student可简写为stu，带下划线的表名如s_au_node_function,表名可简写为sanf）。

(5)组合索引建议包含所有表名字段名,过长的表名和字段名可以采用缩写形式。例如idx_age_name_add。（长度不能超30）。

(6)表必须有主键,推荐使用UNSIGNED自增列作为主键。

(7)禁止冗余索引。

(8)禁止重复索引

(9)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。

(10)不在低基数列上建立索引,例如“性别”。

(11)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。

(12)对字符串使用前缀索引,前缀索引长度不超过8个字符。

(13)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。

(14)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

(15)合理使用覆盖索引减少IO,避免排序。

(16)索引名称最大长度(因oracle索引名称长度只有30，为避免迁移时索引出错，各数据库索引名称均不得超过30)：

oracle：30

mysql：64

sqlserver：128

达梦：128

金仓：63

pgsql：63

Mysql迁移到kingbase的迁移过程比较简单，但是有几点需要注意：
1.1此位置选PUBLIC

1.2迁移配置如图

1.3迁移完成后，会有些报错信息（一般为主键和索引添加错误的信息，可根据信息添加主键及索引），其中主键PRIMARY已存在，是因为在迁移时，会默认主键名称为PRIMARY，只有第一个表的主键会添加成功。

以下，为我添加主键的方式，可以参考：
报错信息图片：

（1）在源头数据库（mysql）执行以下语句生成创建主键的sql，并将生成的sql在金仓数据库执行：

-- 查询并生成主键只有一个的表的添加主键的sql（金仓）

SELECT CONCAT('alter table ',UPPER(TABLE_NAME),' add constraint ',UPPER(TABLE_NAME),'_PK PRIMARY KEY (',UPPER(COLUMN_NAME),');')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA=database()  AND COLUMN_KEY='PRI'  GROUP BY UPPER(TABLE_NAME) HAVING COUNT(*) =1

union ALL

-- 生成主键不止一个的添加主键sql（金仓）

SELECT CONCAT('alter table ',UPPER(TABLE_NAME),' add constraint ',UPPER(TABLE_NAME),'_PK PRIMARY KEY (',group_concat(UPPER(COLUMN_NAME)),');')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA=database()  AND COLUMN_KEY='PRI'

AND UPPER(TABLE_NAME) in (

SELECT UPPER(TABLE_NAME) from  INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA=database()  AND COLUMN_KEY='PRI' GROUP BY UPPER(TABLE_NAME) HAVING COUNT(*) >1) GROUP BY UPPER(TABLE_NAME);

（2）拿出以上sql生成的sql在目标数据库（kingbase）执行

此时，会报一个错误，类似以下这种，是因为这张表已经存在主键了，也就是迁移时默认的主键名为PRIMARY的那个表。此时可以手动修改下主键名称（建议修改成统一的格式，便于维护），并将以上生成的sql中的设置此表的主键的sql去掉，就不会报这个错误了。

1.4.索引丢失，全部添加的脚本（根据索引名称和字段名称删除重复索引，然后添加，慎用）

-- 新建，重建索引 存储过程，在金仓数据库执行
CREATE OR REPLACE PROCEDURE add_index_mysql_to_jincang(IN p_table_name VARCHAR(64 byte),IN p_index_name VARCHAR(64 byte),IN p_index VARCHAR(64 byte))
AS
DECLARE COUNT_TABLE INT DEFAULT 0; -- 判断表名是否存在
DECLARE COUNT1 INT DEFAULT 0; -- 索引名称相同的
DECLARE COUNT2 INT DEFAULT 0; -- 索引字段相同的
DECLARE STRADD VARCHAR(250) DEFAULT ''; -- 添加索引sql
DECLARE STRDROP1 VARCHAR(250) DEFAULT ''; -- 删除索引sql1
DECLARE STRDROP2 VARCHAR(250) DEFAULT '';-- 删除索引sql2
DECLARE INDEX_NAME VARCHAR(250) DEFAULT ''; -- 存放游标中的索引
DECLARE cur1 REFCURSOR; -- 游标

BEGIN
SELECT count(1) INTO COUNT_TABLE FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA='PUBLIC' AND TABLE_NAME=p_table_name;
IF COUNT_TABLE >=1 THEN
STRADD :=concat('CREATE INDEX ',p_index_name,' ON ',p_table_name,' USING BTREE(',p_index,');');
STRDROP1 :=concat(' drop index ',p_index_name,';');
-- 查询索引名称相同的，删除索引并重新添加
SELECT count(1) INTO COUNT1 FROM sys_indexes WHERE SCHEMANAME='PUBLIC' AND INDEXNAME=p_index_name;
IF COUNT1 = 0 THEN
EXECUTE STRADD ;
ELSE
EXECUTE STRDROP1 ;
EXECUTE STRADD;
END IF;
-- 查询索引字段相同的，删除索引并重新添加
SELECT count(1) into COUNT2 FROM sys_indexes WHERE SCHEMANAME='PUBLIC' AND TABLENAME=p_table_name AND INDEXDEF LIKE CONCAT('%(',p_index,')');

IF COUNT2 > 1 THEN
OPEN cur1 FOR SELECT INDEXNAME FROM sys_indexes WHERE SCHEMANAME='PUBLIC' AND REPLACE(SUBSTRING(INDEXDEF,instr(INDEXDEF,'(')+1,length(INDEXDEF)),')','')=p_index AND TABLENAME=p_table_name AND INDEXDEF NOT LIKE 'CREATE UNIQUE INDEX%';
LOOP
FETCH cur1 INTO INDEX_NAME ;
-- 退出标识
EXIT WHEN cur1%notfound;
STRDROP2 :=concat(' drop index ',INDEX_NAME,';');
raise notice '已删除索引%',index_name;
EXECUTE STRDROP2 ;
END LOOP;
EXECUTE STRADD ;
ELSE
raise notice '已完成';
END IF;
ELSE
raise notice '表不存在';
END IF;
END;

-- 调用示例
-- CALL add_index_mysql_to_jincang('A01000','IDX_A01_A0102A_1','A0102A');



-- 查询表名，索引名，字段名。在mysql数据库执行。
/*select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='qyrlzy' AND INDEX_NAME<>'PRIMARY' AND INDEX_NAME NOT like 'FK_Reference%'
GROUP BY TABLE_NAME, INDEX_NAME;*/


-- 生成 创建索引的 调用存储过程的语句（包含了所有的索引，可根据范围自己添加条件），在mysql数据库执行。有些生成的索引名称会比较长，可以根据索引命名规范进行修改
SELECT CONCAT("CALL add_index_mysql_to_jincang(","'",upper(TABLE_NAME),"'",",'",REPLACE(concat('IDX_',upper(TABLE_NAME),'_',upper(INDEX_COLUMN),"'"),',','_'),",'",replace(upper(INDEX_COLUMN),'UID',CONCAT('"','UID','"')),"');")
from (
select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema=database() AND INDEX_NAME<>'PRIMARY' AND INDEX_NAME NOT like 'FK_Reference%' and TABLE_NAME not like '%copy%'
GROUP BY TABLE_NAME, INDEX_NAME)t;