Compare database fields with different SQL (MySQL version)

--Using the test library ' test_project_management ' oel_project_management ' use test;--the old table drop table old_column_info; CREATE TABLE ' old_column_info ' (' ID ' INT (one) not null auto_increment, ' Table_schema ' VARCHAR (UP) ' NOT null COMMENT ' database ' , ' table_name ' varchar (+) NOT null COMMENT ' table name ', ' table_comment ' varchar (+) DEFAULT NULL COMMENT ' table Notes ', ' column_name  ' varchar ' NOT NULL COMMENT ' column name ', ' column_type ' varchar (+) NOT NULL COMMENT ' column type ', ' is_nullable ' varchar (8) NOT NULL COMMENT ' can be null ', ' column_default ' varchar (+) default NULL COMMENT ' default value ', ' column_comment ' varchar (+) default NULL CO Mment ' column notes ', PRIMARY KEY (' id ')) engine=innodb DEFAULT charset=utf8;--old table drop table new_column_info; CREATE TABLE ' new_column_info ' (' ID ' INT (one) not null auto_increment, ' Table_schema ' VARCHAR (UP) ' NOT null COMMENT ' database ' , ' table_name ' varchar (+) NOT null COMMENT ' table name ', ' table_comment ' varchar (+) DEFAULT NULL COMMENT ' table Notes ', ' column_name ' varchar ' not NULL COMMENT ' column name ', ' column_type ' varchar (+) NOT null COMMENT ' column type ', ' is_nullable ' varchar (8) Not NULL COMMENT ' nullable ', ' column_default ' varchar (+) Default NULL COMM ENT ' default value ', ' column_comment ' VARCHAR (+) default NULL comment ' column memo ', PRIMARY KEY (' id ')) engine=innodb default Charset=u tf8;--insert the old table structure insert INTO ' old_column_info ' (' table_schema ', ' table_name ', ' table_comment ', ' column_name ', ' column_ty PE ', ' is_nullable ', ' column_default ', ' column_comment ') SELECT ' project_management ', Tab.table_name, Tab.table_ Comment, col. ' column_name ', col. ' Column_type ', col. ' Is_nullable ', col. ' Column_default ', col. ' Column_comment ' from Information_schema. ' TABLES ' tab, information_schema.columns colwhere col.table_schema = tab. ' Table_schema ' and col.table_name = tab. ' table_name ' and Col.table_schema = ' test_project_management ';--insert the old table structure insert INTO ' new_column_ Info ' (' table_schema ', ' table_name ', ' table_comment ', ' column_name ', ' column_type ', ' is_nullable ', ' Column_default ' ', ' column_comment ') SELECT ' project_management ', tab.tablE_name, Tab.table_comment, col. ' column_name ', col. ' Column_type ', col. ' Is_nullable ', col. ' Column_default ', col. ' Column_comment ' from information_schema. ' TABLES ' tab, information_schema.columns colwhere col.table_schema = tab. ' Table_schema ' and Col.table_name = tab. ' table_name ' and Col.table_schema = ' oel_project_management ';-- View field name unchanged single data structure changed by select O.table_schema, O.table_name, O.table_comment, O. ' column_name ' as Old_column_name, O. ' Column_ TYPE ' as old_column_type,n. ' column_name ' as New_column_name, N. ' Column_type ' as New_column_typefrom old_column_info O, New_column_info nwhere o. ' Table_schema ' = n.table_schemaand o. ' table_name ' = N.table_nameand o. ' column_name ' = N.column_ Nameand (o.column_type! = N.column_typeor o. ' is_nullable '! = N. ' is_nullable ');--View field name unchanged single data structure changed select O.table_schema , O.table_name, O.table_comment, O. ' column_name ' as Old_column_name, O. ' Column_type ' as old_column_type,n. ' COLUMN_NAME ' As New_column_name, N. ' Column_type ' as New_column_typefrom old_column_info O, new_column_info nwhere o. ' Table_schema ' = n.table_schemaand o. ' table_name ' = N.table_nameand o. ' column_name ' = N.column_ Nameand o.column_type! = n.column_type;--Query the old repository for data that does not exist in the new library Selecto.table_schema, O.table_name, O.table_comment, O. ' Column_name ' as Old_column_name, O. ' Column_type ' as Old_column_typefrom old_column_info owhere not EXISTS (SELECT 1 from NE W_column_info nwhere o. ' Table_schema ' = n.table_schemaand o. ' table_name ' = N.table_nameand o. ' column_name ' = N.column_na ME)--query for data that exists in the new library but does not exist in the old library Selectn.table_schema, N.table_name, N.table_comment, N. ' column_name ' as New_column_name, N. ' Column_type ' as New_column_typefrom new_column_info nwhere not EXISTS (SELECT 1 from Old_column_info owhere o. ' Table_schem A ' = N.table_schemaand o. ' table_name ' = n.table_nameand o ' column_name ' = n.column_name);

Compare database fields with different SQL (MySQL version)