TIDB OB迁移字段核对

##########tidb############
desc INFORMATION_SCHEMA.COLUMNS
desc INFORMATION_SCHEMA.

use test;
CREATE TABLE tb2 (
id int(11) NOT NULL,
name varchar(40) DEFAULT NULL,
dt timestamp NULL DEFAULT now(),
PRIMARY KEY (id)
);

insert into tb2 values(12,‘tidbwerewr’ ,now());
insert into tb2 values(23,‘tidb341sfsewr’ ,now());

SELECT
table_schema, table_name, column_name,
data_type,
column_type,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION
FROM
INFORMATION_SCHEMA.COLUMNS where table_schema =‘test’;

select table_schema,table_name,index_id,key_name,seq_in_index ,column_name,
clustered ,is_visible,NON_UNIQUE
from INFORMATION_SCHEMA.TIDB_INDEXES
where table_schema =‘test’
order by 1,2,3,4,5;

create table test.check_tb
( table_schema varchar(50), table_name varchar(50), column_name varchar(50), data_type varchar(50),is_nullable varchar(10),
column_type varchar(50),
CHARACTER_MAXIMUM_LENGTH varchar(50),
NUMERIC_PRECISION varchar(50),
org varchar(2)
);

drop table test.check_idx;
create table test.check_idx
(
table_schema varchar(50),
table_name varchar(50),
index_id varchar(50),
key_name varchar(50),
seq_in_index varchar(50) ,
column_name varchar(50),
clustered varchar(50),
is_visible varchar(50),
NON_UNIQUE varchar(50),
org varchar(2)
)

insert into test.check_tb ( table_schema,
table_name,
column_name,
data_type,is_nullable ,
column_type,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
org) (
SELECT
table_schema, table_name, column_name, data_type,is_nullable ,
column_type,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,‘ti’
FROM
INFORMATION_SCHEMA.COLUMNS where table_schema =‘test’ ) ;

insert into test.check_idx (
select table_schema,table_name,index_id,key_name,seq_in_index ,column_name,
clustered ,is_visible,NON_UNIQUE,‘ti’
from INFORMATION_SCHEMA.TIDB_INDEXES
where table_schema =‘test’
order by 1,2,3,4,5);

select * from test.check_idx

mysqldump -h 172.16.1.211 -P 4000 -u root --database test --tables check_tb check_idx> /tmp/check_tbidx.sql

###########OB############

select * from information_schema.tables where table_schema=‘test’;

use test;
create index idx_tb1_name on tb1(name);

create table tb2 (id int,name varchar(40),dt timestamp);
alter table tb2 add primary key pk_tb2 (id);

drop table tb2;

CREATE TABLE tb2 (
id int(11) NOT NULL,
name varchar(40) DEFAULT NULL,
dt timestamp NULL DEFAULT now(),
PRIMARY KEY (id)
);

alter table test.tb2 add column (remark varchar(50));
create index test.idx_tb2_2 on test.tb2(name,remark);

insert into tb2 values(12,‘tidbwerewr’ ,now());
insert into tb2 values(23,‘tidb341sfsewr’ ,now());

drop tables test.check_tbo;
create table test.check_tbo
( table_schema varchar(50), table_name varchar(50), column_name varchar(50), data_type varchar(50),
column_type varchar(50),
CHARACTER_MAXIMUM_LENGTH varchar(50),
NUMERIC_PRECISION varchar(50),
org varchar(2)
);

desc test.check_tbo;
insert into test.check_tbo ( table_schema,
table_name,
column_name,
data_type,
column_type,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
org) (
SELECT
table_schema, table_name, column_name, data_type,
column_type,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,‘ob’
FROM
INFORMATION_SCHEMA.COLUMNS where table_schema =‘test’ ) ;

select * from test.check_tbo;

use information_schema;
show tables;
select * from information_schema.tables where table_schema=‘test’;

select index_owner,table_owner,table_name,index_name,column_name,column_position,column_length,descend
from oceanbase.CDB_ind_columns where table_owner=‘test’;

SELECT
tb.table_name,co.column_name, co.data_type,
co.data_length,
co.data_precision,co.data_scale,tb.table_type
FROM
oceanbase.__all_column co,
oceanbase.__all_table tb
WHERE
tb.database_id=(select database_id from oceanbase.__all_database where database_name=‘test’ )
and co.table_id = tb.table_id
order by 1,2

select * from oceanbase.__all_type where typecode=18;

drop table test.check_co
select * from test.check_co where table_name not like ‘_ctas%’;

select * from oceanbase.__all_column
select database_id from oceanbase.__all_database where database_name=‘test’ ;

mysqldump -h 172.16.0.207 -P2883 -u root@sys#ob_cluster1 -prootaaAA11## --database test --tables check_tbo > /tmp/check_tbo.sql

####################
alter table test.tb2 drop column name;
alter table test.tb2 add column (name varchar(100));

select * from (
select t_tidb.org, t_tidb.table_schema ti_table_schema,
t_tidb.table_name ,
t_tidb.column_name ,
t_tidb.data_type ti_data_type,
t_tidb.is_nullable ti_isnull,
t_tidb.column_type ti_type,
t_tidb.CHARACTER_MAXIMUM_LENGTH ti_length,
t_tidb.NUMERIC_PRECISION ti_prec,
t_ob.table_schema ,
t_ob.table_name o_tb,
t_ob.column_name o_col,
t_ob.data_type,
t_ob.is_nullable,
t_ob.column_type o_type,
t_ob.CHARACTER_MAXIMUM_LENGTH o_length,
t_ob.NUMERIC_PRECISION o_prec
from test.check_tb t_tidb left join INFORMATION_SCHEMA.COLUMNS t_ob
on t_tidb.table_schema=t_ob.table_schema
and t_tidb.table_name =t_ob.table_name
and t_tidb.column_name=t_ob.column_name
) t1 where t1.o_tb is null or o_col is null or ti_type<> o_type or ti_length<>o_length or ti_prec <> o_prec or ti_isnull<> is_nullable;

select * from
(
select table_schema ti_schema,
t1.table_name ti_tb_name,
key_name ti_idx_name,
seq_in_index,
t1.column_name ti_col,
index_owner ob_idx_owner,table_owner,t2.table_name,index_name,t2.column_name o_col,column_position,column_length,descend
from test.check_idx t1 left join oceanbase.CDB_ind_columns t2
on t1.table_schema=t2.table_owner
and t1.table_name=t2.table_name
and t1.column_name=t2.column_name ) t where
t.o_col is null

– and t1.key_name=t2.index_name;

select t1.table_schema ti_schema,
t1.table_name ti_tb_name,
key_name ti_idx_name,
t1.seq_in_index ti_seqin,
t1.column_name ti_col,
t2.TABLE_SCHEMA ob_tb_schema,
t2.table_name,t2.index_name,t2.column_name o_col,t2.SEQ_IN_INDEX
from test.check_idx t1 full outer join information_schema.statistics t2
on t1.table_schema=t2.TABLE_SCHEMA
and t1.table_name=t2.table_name
and t1.key_name = t2.index_name
and t1.column_name=t2.column_name
and t1.seq_in_index =t2.SEQ_IN_INDEX
where t2.table_schema in (‘test’)
) t where ( ti_col is null or o_col is null
or seq_in_index is null or ti_seqin is null) ;

information_schema.statistics replace oceanbase.CDB_ind_columns

select * from
(
select t1.table_schema ti_schema,
t1.table_name ti_tb_name,
key_name ti_idx_name,
t1.seq_in_index,
t1.column_name ti_col,
t2.TABLE_SCHEMA ob_idx_owner,
t2.table_name,t2.index_name,t2.column_name o_col,
t2.SEQ_IN_INDEX as column_position
from information_schema.statistics t2 left join test.check_idx t1
on t1.table_schema=t2.TABLE_SCHEMA
and t1.table_name=t2.table_name
and t1.key_name = t2.index_name
and t1.column_name=t2.column_name
and t1.seq_in_index =t2.SEQ_IN_INDEX
where t2.table_schema=’ ’
) t where ti_col is null or o_col is null
or seq_in_index is null or column_position is null
;