mysql两个数据库结构对比
xiaostudy 人气:71、草稿:
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程getdatabaseCount则删除 DROP PROCEDURE IF EXISTS `getdatabaseCount` & -- 3.定义存储过程,获取特定数据库的数量 -- (传入参数database_name字符串类型,为数据库名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql,把sql返回值放到@count_date中 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';'); SET @sqlcounts := $sqltext; -- 6.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 2.如果存在存储过程getCount则删除 DROP PROCEDURE IF EXISTS `getTableCount` & -- 3.定义存储过程,获取特定数据库表的数量 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql,把sql返回值放到@count_date中 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; -- 6.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 2.如果存在存储过程getColumnCount则删除 DROP PROCEDURE IF EXISTS `getColumnCount` & -- 3.定义存储过程,获取特定数据库表列的数量 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传入参数column_name字符串类型,为列名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql,把sql返回值放到@count_date中 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; -- 6.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 2.如果存在存储过程getColumnInfo则删除 DROP PROCEDURE IF EXISTS `getColumnInfo` & -- 3.定义存储过程,获取特定数据库表列的信息 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传入参数column_name字符串类型,为列名;传入参数column_info字符串类型,列信息;传出参数result_data字符串类型,信息) CREATE DEFINER=`root`@`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 -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql,把sql返回值放到@count_date中 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; -- 6.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET result_data = @column_info; END -- 10.定义存储过程结束 & -- 11.如果存在存储过程comparison则删除 DROP PROCEDURE IF EXISTS `comparison` & -- 12.定义存储过程,获取指定数据库关键词的表列名 -- (传入参数database_n字符串类型,数据库名;传入参数collation_n字符串类型,具体编码类型;传入参数key_name字符串类型,为关键字;传出参数tableColumnNames字符串类型,表列名) CREATE DEFINER=`root`@`localhost` PROCEDURE comparison(IN database_1 CHAR(20), IN database_2 CHAR(20), IN column_info CHAR(50), OUT info TEXT) BEGIN -- 13.声明变量。database_name查询出来的数据库,table_name查询出来的表名,column_name查询出来的列名,collation_name查询出来的具体编码类型 DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2 CHAR(200); DECLARE this_info, database_table_no TEXT DEFAULT ''; DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0; -- 14.定义游标结束标识,默认为0 DECLARE stopflag INT DEFAULT 0; -- 15.定义游标,其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t; -- 16.游标结束就设置为1 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 -- 17.打开游标 OPEN sql_resoult; -- 18.读取游标中数据,存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; -- 19.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 20.判断数据库是否为输入的数据库名称,和,指定具体编码类型,和,不含. IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN -- 21.调用存储过程,获取特定表列关键词的数量 CALL getTableCount(database_2, table_name, resoult_count); -- 22.如果数量不等于0,那么记录表列名 IF (resoult_count <> 0) THEN CALL getColumnCount(database_2, table_name, column_name, resoult_count); -- 23.拼接字符串,不可直接用传出变量设值 IF (resoult_count <> 0) THEN 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); -- 23.拼接字符串,不可直接用传出变量设值 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; ELSE 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 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 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 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; -- 24.读取游标中数据,存储到指定变量。(和18一样) FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; END; END WHILE; -- 25.关闭游标 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; -- 26.把数据放到传出参数 SET info=this_info; END -- 27.定义存储过程结束 & -- 28.将mysql分隔符从&设置为; DELIMITER ; -- 29.设置变量 SET @database_1='my_test'; SET @database_2='my_test2'; SET @column_info='data_type'; SET @count=''; -- 30.调用存储过程 CALL comparison(@database_1, @database_2, @column_info, @count); -- 31.打印 SELECT @count; -- 32.如果存在存储过程则删除 DROP PROCEDURE IF EXISTS `comparison`;
2、整理:
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程getdatabaseCount则删除 DROP PROCEDURE IF EXISTS `getdatabaseCount` & -- 3.定义存储过程,获取特定数据库的数量 -- (传入参数database_name字符串类型,为数据库名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql,把sql返回值放到@count_date中 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';'); SET @sqlcounts := $sqltext; -- 6.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET count_date = @count_date; END -- 10.定义存储过程结束 & -- 11.如果存在存储过程getTableCount则删除 DROP PROCEDURE IF EXISTS `getTableCount` & -- 12.定义存储过程,获取特定数据库表的数量 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT) BEGIN -- 13.声明变量 DECLARE $sqltext VARCHAR(1000); -- 14.动态sql,把sql返回值放到@count_date中 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; -- 15.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 16.执行SQL语句 EXECUTE stmt; -- 17.释放资源 DEALLOCATE PREPARE stmt; -- 18.获取动态SQL语句返回值 SET count_date = @count_date; END -- 19.定义存储过程结束 & -- 20.如果存在存储过程getColumnCount则删除 DROP PROCEDURE IF EXISTS `getColumnCount` & -- 21.定义存储过程,获取特定数据库表列的数量 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传入参数column_name字符串类型,为列名;传出参数count_date整数类型,为数量) CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT) BEGIN -- 22.声明变量 DECLARE $sqltext VARCHAR(1000); -- 23.动态sql,把sql返回值放到@count_date中 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; -- 24.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 25.执行SQL语句 EXECUTE stmt; -- 26.释放资源 DEALLOCATE PREPARE stmt; -- 27.获取动态SQL语句返回值 SET count_date = @count_date; END -- 28.定义存储过程结束 & -- 29.如果存在存储过程getColumnInfo则删除 DROP PROCEDURE IF EXISTS `getColumnInfo` & -- 30.定义存储过程,获取特定数据库表列的信息 -- (传入参数database_name字符串类型,为数据库名;传入参数table_name字符串类型,为表名;传入参数column_name字符串类型,为列名;传入参数column_info字符串类型,列信息;传出参数result_data字符串类型,信息) CREATE DEFINER=`root`@`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 -- 31.声明变量 DECLARE $sqltext VARCHAR(1000); -- 32.动态sql,把sql返回值放到@count_date中 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; -- 33.预编释,stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 34.执行SQL语句 EXECUTE stmt; -- 35.释放资源 DEALLOCATE PREPARE stmt; -- 36.获取动态SQL语句返回值 SET result_data = @column_info; END -- 37.定义存储过程结束 & -- 38.如果存在存储过程comparisonTableExist则删除 DROP PROCEDURE IF EXISTS `comparisonTableExist` & -- 39.定义存储过程,对比表是否存在 -- (传入参数database_1字符串类型,数据库名1;传入参数database_2字符串类型,数据库名2;传入参数info字符串类型,库表信息) CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT) BEGIN -- 40.声明变量。database_name查询出来的数据库,table_name查询出来的表名 DECLARE database_name, table_name CHAR(200); -- this_info表不存在的记录,database_table_no表不存在的记录跳过重复查询 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT ''; -- database_count_1统计数据库1存在的数量,database_count_2统计数据库2存在的数量,resoult_count统计表存在的数量。如果为0表示不存在 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0; -- 41.定义游标结束标识,默认为0 DECLARE stopflag INT DEFAULT 0; -- 42.定义游标,其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t; -- 43.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 44.调用存储过程getdatabaseCount,查看两个数据库是否存在,都存在则继续 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 -- 45.打开游标 OPEN sql_resoult; -- 46.读取游标中数据,存储到指定变量 FETCH sql_resoult INTO database_name, table_name; -- 47.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 48.判断数据库是否为输入的数据库名称1,去除已经比较过数据库2的表不存在 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN -- 49.调用存储过程getTableCount,查看表是否存在 CALL getTableCount(database_2, table_name, resoult_count); -- 50.如果数量等于0,那么表不存在 IF (resoult_count = 0) THEN -- 51.把不存在的表记录下来 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 -- 52.判断数据库是否为输入的数据库名称2,去除已经比较过数据库1的表不存在 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; -- 53.读取游标中数据,存储到指定变量。(和46一样) FETCH sql_resoult INTO database_name, table_name; END; END WHILE; -- 54.关闭游标 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; -- 55.把数据放到传出参数 SET info=this_info; END -- 56.定义存储过程结束 & -- 57.如果存在存储过程comparisonColumnExist则删除 DROP PROCEDURE IF EXISTS `comparisonColumnExist` & -- 58.定义存储过程,对比列是否存在 -- (传入参数database_1字符串类型,数据库名1;传入参数database_2字符串类型,数据库名2;传入参数info字符串类型,库表信息) CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT) BEGIN -- 59.声明变量。database_name查询出来的数据库,table_name查询出来的表名,column_name查询出来的列名 DECLARE database_name, table_name, column_name CHAR(200); -- this_info表不存在的记录,database_table_no表不存在的记录跳过重复查询 DECLARE this_info, database_table_no TEXT DEFAULT ''; -- database_count_1统计数据库1存在的数量,database_count_2统计数据库2存在的数量,resoult_count统计表存在的数量。如果为0表示不存在 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0; -- 60.定义游标结束标识,默认为0 DECLARE stopflag INT DEFAULT 0; -- 61.定义游标,其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t; -- 62.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 63.调用存储过程getdatabaseCount,查看两个数据库是否存在,都存在则继续(同44) 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 -- 64.打开游标 OPEN sql_resoult; -- 65.读取游标中数据,存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name; -- 66.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 67.判断数据库是否为输入的数据库名称1,去除已经比较过数据库2的表不存在(同48) IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN -- 68.调用存储过程getTableCount,查看表是否存在(同49) CALL getTableCount(database_2, table_name, resoult_count); -- 69.如果数量不等于0,则继续 IF (resoult_count <> 0) THEN -- 70.调用存储过程getColumnCount,查看列是否存在。为0说明不存在 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 -- 71.判断数据库是否为输入的数据库名称2,去除已经比较过数据库1的表不存在(同52) 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; -- 72.读取游标中数据,存储到指定变量。(和65一样) FETCH sql_resoult INTO database_name, table_name, column_name; END; END WHILE; -- 73.关闭游标 CLOSE sql_resoult; END IF; -- 74.把数据放到传出参数 SET info=this_info; END -- 75.定义存储过程结束 & -- 76.如果存在存储过程comparisonColumnInfo则删除 DROP PROCEDURE IF EXISTS `comparisonColumnInfo` & -- 77.定义存储过程,对比列的不同 -- (传入参数database_1字符串类型,数据库名1;传入参数database_2字符串类型,数据库名2;传入参数info字符串类型,库表信息) CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT) BEGIN -- 78.声明变量。database_name查询出来的数据库,table_name查询出来的表名,column_name查询出来的列名,collation_name查询出来的具体编码类型 -- result_data_1数据库1的列信息,result_data_2数据库2的列信息,column_info对比的列(现在只比较DATA_TYPE、CHARACTER_SET_NAME) DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200); -- this_info表不存在的记录,database_table_no表不存在的记录跳过重复查询 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT ''; -- database_count_1统计数据库1存在的数量,database_count_2统计数据库2存在的数量,resoult_count统计表存在的数量。如果为0表示不存在 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0; -- 79.定义游标结束标识,默认为0 DECLARE stopflag INT DEFAULT 0; -- 80.定义游标,其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t; -- 81.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 82.调用存储过程getdatabaseCount,查看两个数据库是否存在,都存在则继续(同63) 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 -- 83.打开游标 OPEN sql_resoult; -- 84.读取游标中数据,存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; -- 85.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN -- 86.判断数据库是否为输入的数据库名称1,去除已经比较过数据库2的表不存在(同67) IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN -- 87.调用存储过程getTableCount,查看表是否存在(同68) CALL getTableCount(database_2, table_name, resoult_count); -- 88.如果数量不等于0,则继续 IF (resoult_count <> 0) THEN -- 89.调用存储过程getColumnCount,查看列是否存在。为0说明不存在(同70) CALL getColumnCount(database_2, table_name, column_name, resoult_count); IF (resoult_count <> 0) THEN -- 90.对比DATA_TYPE是否相同 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; -- 91.对比CHARACTER_SET_NAME是否相同 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; -- 92.读取游标中数据,存储到指定变量。(和84一样) FETCH sql_resoult INTO database_name, table_name, column_name, collation_name; END; END WHILE; -- 93.关闭游标 CLOSE sql_resoult; END IF; -- 94.把数据放到传出参数 SET info=this_info; END -- 95.定义存储过程结束 & -- 96.将mysql分隔符从&设置为; DELIMITER ; -- 97.设置变量 SET @database_1='my_test1'; SET @database_2='my_test2'; SET @tableExistInfo=''; SET @columnExistInfo=''; SET @columnInfo=''; -- 98.调用存储过程 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); -- 99.打印 SELECT @info; -- 100.如果存在存储过程则删除 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`;
3、无注释
DELIMITER & DROP PROCEDURE IF EXISTS `getdatabaseCount` & CREATE DEFINER=`root`@`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=`root`@`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=`root`@`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=`root`@`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=`root`@`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=`root`@`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=`root`@`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='my_test3'; SET @database_2='my_test4'; 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`;
总结
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注的更多内容!
加载全部内容