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

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

查询数据库中有多少个表：

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema ='soc_common' GROUP BY table_schema;

以下代码亲测：（比较两个数据库中不同）

无注释：

DELIMITER &DROP PROCEDURE IF EXISTS `getdatabaseCount` &CREATE DEFINER=`oms`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT) BEGIN DECLARE $sqltext VARCHAR(1000); SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';'); SET @sqlcounts := $sqltext; PREPARE stmt FROM @sqlcounts; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET count_date = @count_date; END&DROP PROCEDURE IF EXISTS `getTableCount` &CREATE DEFINER=`oms`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT) BEGIN DECLARE $sqltext VARCHAR(1000); SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';'); SET @sqlcounts := $sqltext; PREPARE stmt FROM @sqlcounts; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET count_date = @count_date; END&DROP PROCEDURE IF EXISTS `getColumnCount` &CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT) BEGIN DECLARE $sqltext VARCHAR(1000); SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`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_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20)) BEGIN DECLARE $sqltext VARCHAR(1000); SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`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_2 CHAR(20), OUT info MEDIUMTEXT) BEGIN DECLARE 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 t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS 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) THEN OPEN sql_resoult; FETCH sql_resoult INTO database_name, table_name; WHILE (stopflag=0) DO BEGIN IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN CALL getTableCount(database_2, table_name, resoult_count); IF (resoult_count = 0) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n'); ELSE SET 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; ELSE IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN CALL getTableCount(database_1, table_name, resoult_count); IF (resoult_count = 0) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n'); ELSE SET 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; ELSE IF (database_count_1 = 0 AND database_count_2 = 0) THEN SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库'); ELSE IF (database_count_1 = 0) THEN SET this_info = CONCAT(database_1, '数据库不存在或为空数据库'); ELSE SET 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_2 CHAR(20), OUT info MEDIUMTEXT) BEGIN DECLARE 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 t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS 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) THEN OPEN sql_resoult; FETCH sql_resoult INTO database_name, table_name, column_name; WHILE (stopflag=0) DO BEGIN IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN CALL 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) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n'); ELSE SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n'); END IF; END IF; ELSE SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';'); END IF; ELSE IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN CALL getTableCount(database_1, table_name, resoult_count); IF (resoult_count <> 0) THEN CALL getColumnCount(database_1, table_name, column_name, resoult_count); IF (resoult_count = 0) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n'); ELSE SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n'); END IF; END IF; ELSE 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, column_name; END; END WHILE; CLOSE sql_resoult; END IF; SET info=this_info; END&DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT) BEGIN DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info 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 t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS 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) THEN OPEN sql_resoult; FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; WHILE (stopflag=0) DO BEGIN IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN CALL 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) THEN SET column_info = 'DATA_TYPE'; CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1); CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2); IF (result_data_1 <> result_data_2) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n'); ELSE SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n'); END IF; END IF; SET column_info = 'CHARACTER_SET_NAME'; CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1); CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2); IF (result_data_1 <> result_data_2) THEN IF (this_info IS NULL OR this_info='') THEN SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n'); ELSE SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n'); END IF; END IF; END IF; ELSE SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';'); END IF; ELSE IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN CALL getTableCount(database_1, table_name, resoult_count); IF (resoult_count = 0) THEN 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, column_name, collation_name; END; END WHILE; CLOSE sql_resoult; END IF; SET info=this_info; END&DELIMITER ;SET @database_1='test_common';SET @database_2='soc_common';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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。