mysql比较差异列_mysql对比两个数据库中不同的表和列字段

2024年3月5日发(作者：)

DECLARE $sqltext VARCHAR(1000);SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_S t wheret.`TABLE_SCHEMA` = '', database_name, '' and t.`TABLE_NAME` = '', table_name, '' and t.`COLUMN_NAME` = '',column_name, '';');SET @sqlcounts := $sqltext;PREPARE stmt FROM @sqlcounts;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET count_date = @count_date;END&DROP PROCEDURE IF EXISTS `getColumnInfo` &CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_nameCHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))BEGINDECLARE $sqltext VARCHAR(1000);SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_S t wheret.`TABLE_SCHEMA` = '', database_name, '' and t.`TABLE_NAME` = '', table_name, '' and t.`COLUMN_NAME` = '',column_name, '';');SET @sqlcounts := $sqltext;PREPARE stmt FROM @sqlcounts;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET result_data = @column_info;END&DROP PROCEDURE IF EXISTS `comparisonTableExist` &CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2CHAR(20), OUT info MEDIUMTEXT)BEGINDECLARE database_name, table_name CHAR(200);DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;DECLARE stopflag INT DEFAULT 0;DECLARE sql_resoult CURSOR FOR SELECT _SCHEMA, _NAME FROM information_S t;DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;

CALL getdatabaseCount(database_1, database_count_1);CALL getdatabaseCount(database_2, database_count_2);IF (database_count_1 <> 0 AND database_count_2 <> 0) THENOPEN sql_resoult;FETCH sql_resoult INTO database_name, table_name;WHILE (stopflag=0) DOBEGINIF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THENCALL getTableCount(database_2, table_name, resoult_count);IF (resoult_count = 0) THENIF (this_info IS NULL OR this_info='') THENSET this_info=CONCAT(database_2, '的', table_name, '表不存在;n');ELSESET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;n');END IF;SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');END IF;ELSEIF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THENCALL getTableCount(database_1, table_name, resoult_count);IF (resoult_count = 0) THENIF (this_info IS NULL OR this_info='') THENSET this_info=CONCAT(database_1, '的', table_name, '表不存在;n');ELSESET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;n');END IF;SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');END IF;END IF;END IF;FETCH sql_resoult INTO database_name, table_name;END;END WHILE;CLOSE sql_resoult;

ELSEIF (database_count_1 = 0 AND database_count_2 = 0) THENSET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');ELSEIF (database_count_1 = 0) THENSET this_info = CONCAT(database_1, '数据库不存在或为空数据库');ELSESET this_info = CONCAT(database_2, '数据库不存在或为空数据库');END IF;END IF;END IF;SET info=this_info;END&DROP PROCEDURE IF EXISTS `comparisonColumnExist` &CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2CHAR(20), OUT info MEDIUMTEXT)BEGINDECLARE database_name, table_name, column_name CHAR(200);DECLARE this_info, database_table_no TEXT DEFAULT '';DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;DECLARE stopflag INT DEFAULT 0;DECLARE sql_resoult CURSOR FOR SELECT _SCHEMA, _NAME, _NAME FROMinformation_S t;DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;CALL getdatabaseCount(database_1, database_count_1);CALL getdatabaseCount(database_2, database_count_2);IF (database_count_1 <> 0 AND database_count_2 <> 0) THENOPEN sql_resoult;FETCH sql_resoult INTO database_name, table_name, column_name;WHILE (stopflag=0) DOBEGINIF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THENCALL getTableCount(database_2, table_name, resoult_count);IF (resoult_count <> 0) THEN

CALL getColumnCount(database_2, table_name, column_name, resoult_count);IF (resoult_count = 0) THENIF (this_info IS NULL OR this_info='') THENSET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;n');ELSESET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;n');END IF;END IF;ELSESET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');END IF;ELSEIF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THENCALL getTableCount(database_1, table_name, resoult_count);IF (resoult_count <> 0) THENCALL getColumnCount(database_1, table_name, column_name, resoult_count);IF (resoult_count = 0) THENIF (this_info IS NULL OR this_info='') THENSET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;n');ELSESET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;n');END IF;END IF;ELSESET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');END IF;END IF;END IF;FETCH sql_resoult INTO database_name, table_name, column_name;END;END WHILE;CLOSE sql_resoult;END IF;SET info=this_info;

SET @tableExistInfo='';SET @columnExistInfo='';SET @columnInfo='';CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);SET @info=CONCAT(@tableExistInfo, 'n', @columnExistInfo, 'n', @columnInfo);SELECT @info;DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;DROP PROCEDURE IF EXISTS `comparisonColumnExist`;DROP PROCEDURE IF EXISTS `comparisonTableExist`;DROP PROCEDURE IF EXISTS `getColumnInfo`;DROP PROCEDURE IF EXISTS `getColumnCount`;DROP PROCEDURE IF EXISTS `getTableCount`;DROP PROCEDURE IF EXISTS `getdatabaseCount`;获取数据库中表结构：show columns from t_user或者SHOW FULL COLUMNS FROM t_user