草稿整理后mysql两个数据库结构对比
更新时间:2022年2月7日 13:10 点击:262 作者:xiaostudy
1、草稿:
-- 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`;
总结
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注猪先飞的更多内容!
原文出处:https://www.cnblogs.com/xiaostudy/p/12417151.html
上一篇: Mysql 执行一条语句的整个过程详细
下一篇: MySQL存储过程的概念与用法实例
相关文章
- 操作类就是把一些常用的一系列的数据库或相关操作写在一个类中,这样调用时我们只要调用类文件,如果要执行相关操作就直接调用类文件中的方法函数就可以实现了,下面整理了...2016-11-25
- 本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 这篇文章主要介绍了C#从数据库读取图片并保存的方法,帮助大家更好的理解和使用c#,感兴趣的朋友可以了解下...2021-01-16
- 这篇文章主要介绍了Intellij IDEA连接Navicat数据库的方法,本文通过图文并茂的形式给大家介绍的非常详细,对大家的学习或工作具有一定的参考借价值,需要的朋友可以参考下...2021-03-25
- 在开发过程中,我们经常会将日期时间的毫秒数存放到数据库,但是它对应的时间看起来就十分不方便,我们可以使用一些函数将毫秒转换成date格式。 一、 在MySQL中,有内置的函数from_unixtime()来做相应的转换,使用如下: 复制...2014-05-31
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
- C#使用System.IO中的文件操作方法在Windows系统中处理本地文件相当顺手,这里我们还总结了在Oracle中保存文件的方法,嗯,接下来就来看看整理的C#操作本地文件及保存文件到数据库的基本方法总结...2020-06-25
- 通过内网连另外一台机器的mysql服务, 确发现速度N慢! 等了大约几十秒才等到提示输入密码。 但是ping mysql所在服务器却很快! 想到很久之前有过类似的经验, telnet等一些服务在连接请求的时候,会做一些反向域名解析(如果...2015-10-21
- 某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库。使用以下方法,可以非常简单地实现。假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb。步骤如下:1. 首先创建新的数据库newd...2015-10-21
- mysqldump命令的用法1、导出所有库系统命令行mysqldump -uusername -ppassword --all-databases > all.sql 2、导入所有库mysql命令行mysql>source all.sql; 3、导出某些库系统命令行mysqldump -uusername -ppassword...2015-10-21
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 1005:创建表失败1006:创建数据库失败1007:数据库已存在,创建数据库失败1008:数据库不存在,删除数据库失败1009:不能删除数据库文件导致删除数据库失败1010:不能删除数据目录导致删除数据库失败1011:删除数据库...2013-09-23
- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 这篇文章主要介绍了c#从数据库里取得数据并异步更新ui的方法,大家参考使用吧...2020-06-25
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- yii2.0框架是PHP开发的一个比较高效率的框架,集合了作者的大量心血,下面通过用户为例给大家详解yii2使用中的一些基本的增删改查操作。 User::find()->all(); //返回所有用户数据; User::findOne($id); //返回 主键...2015-11-24
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 这篇文章主要介绍了springBoot 项目排除数据库启动方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-09-10