mysql存储过程数据库比对_【草稿整理】【mysql】两个数据库结构对比

1 --1.将mysql分隔符从;设置为&

2 DELIMITER &

3

4 --2.如果存在存储过程getdatabaseCount则删除

5 DROP PROCEDURE IF EXISTS `getdatabaseCount` &

6 --3.定义存储过程，获取特定数据库的数量

7 --(传入参数database_name字符串类型，为数据库名；传出参数count_date整数类型，为数量)

8 CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)9 BEGIN

10 --4.声明变量

11 DECLARE $sqltext VARCHAR(1000);12 --5.动态sql，把sql返回值放到@count_date中

13 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name,'\';');14 SET @sqlcounts :=$sqltext;15 --6.预编释，stmt预编释变量的名称

16 PREPARE stmt FROM @sqlcounts;17 --7.执行SQL语句

18 EXECUTEstmt;19 --8.释放资源

20 DEALLOCATE PREPAREstmt;21 --9.获取动态SQL语句返回值

22 SET count_date = @count_date;23 END

24 --10.定义存储过程结束

25 &

26

27 --11.如果存在存储过程getTableCount则删除

28 DROP PROCEDURE IF EXISTS `getTableCount` &

29 --12.定义存储过程，获取特定数据库表的数量

30 --(传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传出参数count_date整数类型，为数量)

31 CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)32 BEGIN

33 --13.声明变量

34 DECLARE $sqltext VARCHAR(1000);35 --14.动态sql，把sql返回值放到@count_date中

36 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,'\';');37 SET @sqlcounts :=$sqltext;38 --15.预编释，stmt预编释变量的名称

39 PREPARE stmt FROM @sqlcounts;40 --16.执行SQL语句

41 EXECUTEstmt;42 --17.释放资源

43 DEALLOCATE PREPAREstmt;44 --18.获取动态SQL语句返回值

45 SET count_date = @count_date;46 END

47 --19.定义存储过程结束

48 &

49

50

51 --20.如果存在存储过程getColumnCount则删除

52 DROP PROCEDURE IF EXISTS `getColumnCount` &

53 --21.定义存储过程，获取特定数据库表列的数量

54 --(传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传出参数count_date整数类型，为数量)

55 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)56 BEGIN

57 --22.声明变量

58 DECLARE $sqltext VARCHAR(1000);59 --23.动态sql，把sql返回值放到@count_date中

60 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,'\';');61 SET @sqlcounts :=$sqltext;62 --24.预编释，stmt预编释变量的名称

63 PREPARE stmt FROM @sqlcounts;64 --25.执行SQL语句

65 EXECUTEstmt;66 --26.释放资源

67 DEALLOCATE PREPAREstmt;68 --27.获取动态SQL语句返回值

69 SET count_date = @count_date;70 END

71 --28.定义存储过程结束

72 &

73

74

75 --29.如果存在存储过程getColumnInfo则删除

76 DROP PROCEDURE IF EXISTS `getColumnInfo` &

77 --30.定义存储过程，获取特定数据库表列的信息

78 --(传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数column_info字符串类型，列信息；传出参数result_data字符串类型，信息)

79 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))80 BEGIN

81 --31.声明变量

82 DECLARE $sqltext VARCHAR(1000);83 --32.动态sql，把sql返回值放到@count_date中

84 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,'\';');85 SET @sqlcounts :=$sqltext;86 --33.预编释，stmt预编释变量的名称

87 PREPARE stmt FROM @sqlcounts;88 --34.执行SQL语句

89 EXECUTEstmt;90 --35.释放资源

91 DEALLOCATE PREPAREstmt;92 --36.获取动态SQL语句返回值

93 SET result_data = @column_info;94 END

95 --37.定义存储过程结束

96 &

97

98 --38.如果存在存储过程comparisonTableExist则删除

99 DROP PROCEDURE IF EXISTS `comparisonTableExist` &

100 --39.定义存储过程，对比表是否存在

101 --(传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)

102 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)103 BEGIN

104 --40.声明变量。database_name查询出来的数据库，table_name查询出来的表名

105 DECLARE database_name, table_name CHAR(200);106 --this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询

107 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';108 --database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在

109 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;110 --41.定义游标结束标识，默认为0

111 DECLARE stopflag INT DEFAULT 0;112 --42.定义游标，其实就是临时存储sql返回的集合

113 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROMinformation_schema.COLUMNS t;114 --43.游标结束就设置为1

115 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;116 --44.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续

117 CALL getdatabaseCount(database_1, database_count_1);118 CALL getdatabaseCount(database_2, database_count_2);119 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN

120 --45.打开游标

121 OPENsql_resoult;122 --46.读取游标中数据，存储到指定变量

123 FETCH sql_resoult INTOdatabase_name, table_name;124 --47.没有结束继续往下走

125 WHILE (stopflag=0) DO126 BEGIN

127 --48.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在

128 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN

129 --49.调用存储过程getTableCount，查看表是否存在

130 CALL getTableCount(database_2, table_name, resoult_count);131 --50.如果数量等于0，那么表不存在

132 IF (resoult_count = 0) THEN

133 --51.把不存在的表记录下来

134 IF (this_info IS NULL OR this_info='') THEN

135 SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');136 ELSE

137 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');138 END IF;139 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');140 END IF;141 ELSE

142 --52.判断数据库是否为输入的数据库名称2，去除已经比较过数据库1的表不存在

143 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN

144 CALL getTableCount(database_1, table_name, resoult_count);145 IF (resoult_count = 0) THEN

146 IF (this_info IS NULL OR this_info='') THEN

147 SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');148 ELSE

149 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');150 END IF;151 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');152 END IF;153 END IF;154 END IF;155 --53.读取游标中数据，存储到指定变量。(和46一样)

156 FETCH sql_resoult INTOdatabase_name, table_name;157 END;158 END WHILE;159 --54.关闭游标

160 CLOSEsql_resoult;161 ELSE

162 IF (database_count_1 = 0 AND database_count_2 = 0) THEN

163 SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');164 ELSE

165 IF (database_count_1 = 0) THEN

166 SET this_info = CONCAT(database_1, '数据库不存在或为空数据库');167 ELSE

168 SET this_info = CONCAT(database_2, '数据库不存在或为空数据库');169 END IF;170 END IF;171 END IF;172 --55.把数据放到传出参数

173 SET info=this_info;174 END

175 --56.定义存储过程结束

176 &

177

178

179 --57.如果存在存储过程comparisonColumnExist则删除

180 DROP PROCEDURE IF EXISTS `comparisonColumnExist` &

181 --58.定义存储过程，对比列是否存在

182 --(传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)

183 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)184 BEGIN

185 --59.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名

186 DECLARE database_name, table_name, column_name CHAR(200);187 --this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询

188 DECLARE this_info, database_table_no TEXT DEFAULT '';189 --database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在

190 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;191 --60.定义游标结束标识，默认为0

192 DECLARE stopflag INT DEFAULT 0;193 --61.定义游标，其实就是临时存储sql返回的集合

194 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROMinformation_schema.COLUMNS t;195 --62.游标结束就设置为1

196 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;197 --63.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续(同44)

198 CALL getdatabaseCount(database_1, database_count_1);199 CALL getdatabaseCount(database_2, database_count_2);200 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN

201 --64.打开游标

202 OPENsql_resoult;203 --65.读取游标中数据，存储到指定变量

204 FETCH sql_resoult INTOdatabase_name, table_name, column_name;205 --66.没有结束继续往下走

206 WHILE (stopflag=0) DO207 BEGIN

208 --67.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在(同48)

209 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN

210 --68.调用存储过程getTableCount，查看表是否存在(同49)

211 CALL getTableCount(database_2, table_name, resoult_count);212 --69.如果数量不等于0，则继续

213 IF (resoult_count <> 0) THEN

214 --70.调用存储过程getColumnCount，查看列是否存在。为0说明不存在

215 CALL getColumnCount(database_2, table_name, column_name, resoult_count);216 IF (resoult_count = 0) THEN

217 IF (this_info IS NULL OR this_info='') THEN

218 SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');219 ELSE

220 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');221 END IF;222 END IF;223 ELSE

224 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');225 END IF;226 ELSE

227 --71.判断数据库是否为输入的数据库名称2，去除已经比较过数据库1的表不存在(同52)

228 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN

229 CALL getTableCount(database_1, table_name, resoult_count);230 IF (resoult_count <> 0) THEN

231 CALL getColumnCount(database_1, table_name, column_name, resoult_count);232 IF (resoult_count = 0) THEN

233 IF (this_info IS NULL OR this_info='') THEN

234 SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');235 ELSE

236 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');237 END IF;238 END IF;239 ELSE

240 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');241 END IF;242 END IF;243 END IF;244 --72.读取游标中数据，存储到指定变量。(和65一样)

245 FETCH sql_resoult INTOdatabase_name, table_name, column_name;246 END;247 END WHILE;248 --73.关闭游标

249 CLOSEsql_resoult;250 END IF;251 --74.把数据放到传出参数

252 SET info=this_info;253 END

254 --75.定义存储过程结束

255 &

256

257

258 --76.如果存在存储过程comparisonColumnInfo则删除

259 DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &

260 --77.定义存储过程，对比列的不同

261 --(传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)

262 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)263 BEGIN

264 --78.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型

265 --result_data_1数据库1的列信息，result_data_2数据库2的列信息，column_info对比的列(现在只比较DATA_TYPE、CHARACTER_SET_NAME)

266 DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);267 --this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询

268 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';269 --database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在

270 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;271 --79.定义游标结束标识，默认为0

272 DECLARE stopflag INT DEFAULT 0;273 --80.定义游标，其实就是临时存储sql返回的集合

274 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROMinformation_schema.COLUMNS t;275 --81.游标结束就设置为1

276 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;277 --82.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续(同63)

278 CALL getdatabaseCount(database_1, database_count_1);279 CALL getdatabaseCount(database_2, database_count_2);280 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN

281 --83.打开游标

282 OPENsql_resoult;283 --84.读取游标中数据，存储到指定变量

284 FETCH sql_resoult INTOdatabase_name, table_name, column_name, collation_name;285 --85.没有结束继续往下走

286 WHILE (stopflag=0) DO287 BEGIN

288 --86.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在(同67)

289 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN

290 --87.调用存储过程getTableCount，查看表是否存在(同68)

291 CALL getTableCount(database_2, table_name, resoult_count);292 --88.如果数量不等于0，则继续

293 IF (resoult_count <> 0) THEN

294 --89.调用存储过程getColumnCount，查看列是否存在。为0说明不存在(同70)

295 CALL getColumnCount(database_2, table_name, column_name, resoult_count);296 IF (resoult_count <> 0) THEN

297 --90.对比DATA_TYPE是否相同

298 SET column_info = 'DATA_TYPE';299 CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);300 CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);301 IF (result_data_1 <> result_data_2) THEN

302 IF (this_info IS NULL OR this_info='') THEN

303 SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');304 ELSE

305 SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');306 END IF;307 END IF;308 --91.对比CHARACTER_SET_NAME是否相同

309 SET column_info = 'CHARACTER_SET_NAME';310 CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);311 CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);312 IF (result_data_1 <> result_data_2) THEN

313 IF (this_info IS NULL OR this_info='') THEN

314 SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');315 ELSE

316 SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');317 END IF;318 END IF;319 END IF;320 ELSE

321 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');322 END IF;323 ELSE

324 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN

325 CALL getTableCount(database_1, table_name, resoult_count);326 IF (resoult_count = 0) THEN

327 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');328 END IF;329 END IF;330 END IF;331 --92.读取游标中数据，存储到指定变量。(和84一样)

332 FETCH sql_resoult INTOdatabase_name, table_name, column_name, collation_name;333 END;334 END WHILE;335 --93.关闭游标

336 CLOSEsql_resoult;337 END IF;338 --94.把数据放到传出参数

339 SET info=this_info;340 END

341 --95.定义存储过程结束

342 &

343 --96.将mysql分隔符从&设置为;

344 DELIMITER ;345 --97.设置变量

346 SET @database_1='my_test1';347 SET @database_2='my_test2';348 SET @tableExistInfo='';349 SET @columnExistInfo='';350 SET @columnInfo='';351 --98.调用存储过程

352 CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);353 CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);354 CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);355 SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);356 --99.打印

357 SELECT @info;358 --100.如果存在存储过程则删除

359 DROP PROCEDURE IF EXISTS`comparisonColumnInfo`;360 DROP PROCEDURE IF EXISTS`comparisonColumnExist`;361 DROP PROCEDURE IF EXISTS`comparisonTableExist`;362 DROP PROCEDURE IF EXISTS`getColumnInfo`;363 DROP PROCEDURE IF EXISTS`getColumnCount`;364 DROP PROCEDURE IF EXISTS`getTableCount`;365 DROP PROCEDURE IF EXISTS `getdatabaseCount`;