oracle迁移mysql生成创建主键索引脚本

oracle迁移到mysql生成主键索引脚本
除了使用下面sql脚本也可以考虑expdp contents=metadata_only+impdp sqlfile=xxx的方式。

1.创建函数–使用DBMS_LOB.SUBSTR函数将Clob转换为Varchar的代码示例：

CREATE OR REPLACE FUNCTION clob_to_varchar(c CLOB) RETURN VARCHAR2 AS
BEGIN
  RETURN DBMS_LOB.SUBSTR(c, DBMS_LOB.GETLENGTH(c), 1);
END;
/

复制

2.准备SQL脚本：（可自行调整）

SELECT
T.TABLE_NAME , --表名
T.INDEX_NAME , --索引名
I.UNIQUENESS , --是否非空
I.INDEX_TYPE , --索引类型
C.CONSTRAINT_TYPE , --键类型
clob_to_varchar(WM_CONCAT(T.COLUMN_NAME)) COLS,
clob_to_varchar(CASE WHEN C.CONSTRAINT_TYPE = 'P' OR C.CONSTRAINT_TYPE = 'R' THEN --主键和外键创建脚本拼接
'ALTER TABLE '
|| T.TABLE_NAME
|| ' ADD CONSTRAINT '
|| T.INDEX_NAME
|| (CASE WHEN C.CONSTRAINT_TYPE = 'P' THEN ' PRIMARY KEY (' ELSE ' FOREIGN KEY (' END)
|| WM_CONCAT(T.COLUMN_NAME)
|| ');' ELSE --索引创建脚本拼接 ALTER TABLE `XXX` ADD INDEX XXX (`XXX`);--UNIQUE
'ALTER TABLE `'
|| T.TABLE_NAME
|| '` ADD '
|| (CASE WHEN I.UNIQUENESS = 'UNIQUE' THEN I.UNIQUENESS
|| ' ' ELSE 'INDEX ' END)

|| T.INDEX_NAME
|| '(`'
|| WM_CONCAT(COLUMN_NAME)
|| '`);' END) SQL_CMD --拼接创建脚本
FROM
USER_IND_COLUMNS T,
USER_INDEXES I ,
USER_CONSTRAINTS C
WHERE
T.INDEX_NAME = I.INDEX_NAME
AND T.INDEX_NAME = C.CONSTRAINT_NAME(+)
AND I.INDEX_TYPE != 'FUNCTION-BASED NORMAL' --排除基于函数的索引
AND (T.TABLE_NAME LIKE 'DES_%' or T.TABLE_NAME LIKE 'TEST_%') --特定表规则
--AND (C.CONSTRAINT_TYPE != 'P' or C.CONSTRAINT_TYPE is null) --排除主键索引
GROUP BY
T.TABLE_NAME,
T.INDEX_NAME,
I.UNIQUENESS,
I.INDEX_TYPE,
C.CONSTRAINT_TYPE;
复制

3.执行2的查询得到类似结果：

4.创建索引并记录时间参考：

[root@lnpg ~]# cat idx2.sql
select now();
create index DES_SERVICEID on DES_SERVICE (SERVICEID);
select now();
create index IDX_DES_SERVICE1 on DES_SERVICE (ACCESSNBR, AREACODE);
select now();
create index IDX_DES_SERVICE2 on DES_SERVICE (SERVICEID, RECORDSTATUS);
select now();
create index IDX_DES_SERVICE3 on DES_SERVICE (ACCESSNBR);
select now();
create index IDX_DES_SERVICE4 on DES_SERVICE (DEFAULTUSERID);
select now();
alter table DES_SERVICE add constraint PK_DES_SERVICE primary key (SERVICEID, SERVICEIDSEQ);
select now();
[root@lnpg ~]# more idx2.sh
mysql -ugistar -p1qazXSW@ -h192.168.207.143 -A -D resdb < idx2.sql
[root@lnpg ~]# nohup ./idx2.sh > ./idx2.log &
[1] 17529
[root@lnpg ~]# nohup: 忽略输入重定向错误到标准输出端

[root@lnpg ~]# 
[root@lnpg ~]# 
[root@lnpg ~]# 
[root@lnpg ~]# 
[root@lnpg ~]# tail -f idx2.log
mysql: [Warning] Using a password on the command line interface can be insecure.
^C
[root@lnpg ~]# more idx2.log
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lnpg ~]# tail -f idx2.log
mysql: [Warning] Using a password on the command line interface can be insecure.


now()
2024-02-22 09:47:30
now()
2024-02-22 10:08:53
now()
2024-02-22 10:35:41
now()
2024-02-22 10:56:08
now()
2024-02-22 11:22:25
now()
2024-02-22 11:41:59
now()
2024-02-22 14:31:31

^C
[1]+  完成                  nohup ./idx2.sh > ./idx2.log

新开窗口观察：
mysql> show index from DES_SERVICE;
mysql> select * from mysql.innodb_index_stats where database_name='resdb' and table_name='DES_SERVICE';
复制

last_update这个字段显示的是最后一次表中所有索引的更新时间，只要任意一个索引变化，全部都显示相同的时间，不是某个索引创建后的时间。
每创建一个索引就会全部变更时间：

创建完成后如图：


相关参考：
https://blog.csdn.net/jycjyc/article/details/136214062