Compare database fields with different SQL (MySQL version)

Source: Internet
Author: User
Tags 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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.