草稿整理后mysql两个数据库结构对比
程序员文章站
2022-01-08 17:37:13
目录1、草稿：2、整理：3、无注释总结1、草稿：-- 1.将mysql分隔符从;设置为&delimiter &-- 2.如果存在存储过程getdatabasecount则删除drop...
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`;总结
本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注的更多内容!