草稿整理后mysql两个数据库结构对比_Mysql_

-- 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`;

-- 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字符串类型，为表名；传入参数col
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：Mysql到Elasticsearch高效实时同步Debezium实现_Mysql_
                                                                
下一篇：详解Mysql中tinyint与int的区别_Mysql_