oracle迁移至mysql数据表字段以及索引核对

由于mysql数据库是从oracle最早的用户下拉取的数据表，后期oracle的用户又对这些数据表进行了变更，没有及时同步所以有很大的出入，所以要将mysql下的所有数据表字段类型与Oracle用户已开发好的数据表字段类型保持一致。

其实有比较简单的方式可以进行核对，目前项目还没上线，数据库表中也没有数据，完全可以重新建库，由于甲方没有给重新建库的权限，所以只能想其他的办法进行核对表和字段类型。如下：

核对索引

1.查询mysql索引及对应的字段

有两种方式，根据自己的需求任选其一

select a.table_name,a.column_name,a.index_name
from intormation.schema.statistics a
group by a.table_schema,a.table_name,a.index_name,a.column_name;

-- 将联合索引字段进行拼接
select upper(a.table_name),upper(group_concat(column_name order by column_name)) as
from information_schema.statistics a 
group by a.table_schema,a.table_name ,a.index_name
order by a.table_name ;

2.oracle查询所有表的索引及对应的字段

select t.TABLE_NAME,t.COLUMN_NAME, 
       i.index_name，i.UNIQUENESS
from user_ind_columns t,user_indexes i
where t.index_name = i.index_name
 and t.table_name = i.table_name
and table_owner='EWS' -- 用户名
group by t.table_name,i.index_name,i.UNIQUENESS
order by t.table_name ;

 -- 将联合索引字段进行拼接
select t.TABLE_NAME,listagg(t.COLUMN_NAME,',') within group(order by t.COLUMN_NAME) as COL, 
       i.UNIQUENESS,i.index_name 
from user_ind_columns t,user_indexes i
where t.index_name = i.index_name
 and t.table_name = i.table_name
and table_owner='EWS' -- 用户名
group by t.table_name,i.index_name,i.UNIQUENESS
order by t.table_name ;

-- 查询oracle数据库下所有的索引
select t.TABLE_NAME,t.COLUMN_NAME, i.index_type
from user_ind_columns t, user_indexes i
where t.index_name = i.index_name
  and t.table_name = i.table_name ;

3.查看索引同步情况

创建两张临时表用于存放oracle与mysql查询的索引字段，便于查询出oracle存在而mysql不存在的索引

将查询到的所有字段数据导出csv格式，在oracle中导入临时表中

1.创建临时表
create table MYSQL_INDEX(
  table_name VARCHAR2(255), 
  col VARCHAR2(255),
  index_type VARCHAR2(255)
  )

create table ORACLE_INDEX(
  table_name VARCHAR2(255), 
  col VARCHAR2(255),
  index_type VARCHAR2(255),
  index_name VARCHAR2(255)
  )
插入临时表MYSQL_INDEX,ORACLE_INDEX中
直接使用以下sql,点击解锁图标，选中需要插入的字段。直接复制粘贴数据，填入需要的空字段中，提交
select rowid, t.* from MYSQL_INDEX t;
select rowid, t.* from ORACLE_INDEX t;
2.查看oracle存在而mysql不存在的索引
select t.table_name,t.col,T.index_TYPE,T2.index_TYPE
FROM oracle_index T
LEFT JOIN MYSOL_index T2 ON T.table_name = T2.table_name
WHERE T2.COL IS NULL
GROUP BY t.table_name,t. col,T. index_TYPE,T2.index_TYPE
ORDER BY t. table_name;
select a.col,a.table_name
from mysql_index a

3.查询mysql与oracle不一致的索引字段
select a.col, a.table_name 
from mysql_index a
where a.col not in
(
select a.col as col
from oracle_index
left join mysql_index b on a.table_name 
where ь.table_name and a.col b.col
)

核对字段

查询两张表的字段是否相等
在查询之前需要将oracle和mysql数据库的字段全部导出
mysql字段需要在notpad++中转换成大写，将数据类型转换成oracle对应的字段类型，方便作比较

1.查询mysql所有的表,字段以及字段类型

SELECT
CONCAT_WS('',TABLE_NAME ,COLUMN_NAME )AS COLUMN_NAME,COLUMN_TYPE
FROM
information_schema.columns a
WHERE
EXISTS( SELECT 1 FROM
 information_schema.tables b
WHERE b.table_type ='BASE TABLE'
AND a.table_name = b.table_name AND table_schema ='ews_20')
ORDER BY a.TABLE_NAME, a.COLUMN_NAME

2.查询oracle所有的表,字段以及字段类型

SELECT table_name||'.'||column_name,
case when data_type = 'NUMBE'THEN data_type||'('||data_precision||','||DATA_SCLE ||')'
     when data_type = 'DATE' THEN data_type
ELSE data_type||'('||char_col_decl_length||')' END AS data_typeS
FROM ALL_TAB_COLUMNS
WHERE OWNER = UPPER('ews');
查询该用户下所有表
SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('ews');

3.查询字段同步情况

创建两张临时表用于存放oracle与mysql查询的字段类型，便于查询出oracle存在而mysql不存在的字段类型

将查询到的所有字段数据导出csv格式，在oracle中导入临时表中

1.创建临时表
create table MYSQL_COLUMNS_INFO(
  table_name VARCHAR2(255), 
  col VARCHAR2(255)
  )

create table oracle_columns_info(
  table_name VARCHAR2(255), 
  col VARCHAR2(255)
  )
插入临时表MYSQL_COLUMNS_INFO, oracle_columns_info中
直接使用以下sql,点击解锁图标，选中需要插入的字段。直接复制粘贴数据，填入需要的空字段中，提交
select rowid, t.* from mysql_columns_info t;
select rowid, t.* from oracle_columns_info t;
2.查询出mysql存在而oracle中不存在的字段
select distinct upper(substr(a.col,1,instr(a.col,'.') -1)) as table_name
from mysql_columns_info a
left join oracle_columns_info b
on upper(substr(a.col,1,instr(a.col,'.') -1)) F upper(substr(b.col,1,instr(b.col,'.') - 1))
where b.colis null;
3.查询mysql与oracle字段类型不一致的字段
SELECT A.COL,B.T_TYPE as oracle_type,A.T_TYPE as mysql_type
FROM oracle_columns_info B
LEFT JOIN MYSQL_COLUMNS_INFO A 
ON A.COL = B.COL AND B.T_TYPE<>A.T_TYPE
WHERE A.COL Is NOT NULL;