1、草稿：
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 EXECUTE stmt;
19 -- 8.释放资源
20 DEALLOCATE PREPARE stmt;
21 -- 9.获取动态SQL语句返回值
22 SET count_date = @count_date;
23 END
24 -- 10.定义存储过程结束
25 &
26
27 -- 2.如果存在存储过程getCount则删除
28 DROP PROCEDURE IF EXISTS `getTableCount` &
29 -- 3.定义存储过程，获取特定数据库表的数量
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 -- 4.声明变量
34 DECLARE $sqltext VARCHAR(1000);
35 -- 5.动态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 -- 6.预编释，stmt预编释变量的名称
39 PREPARE stmt FROM @sqlcounts;
40 -- 7.执行SQL语句
41 EXECUTE stmt;
42 -- 8.释放资源
43 DEALLOCATE PREPARE stmt;
44 -- 9.获取动态SQL语句返回值
45 SET count_date = @count_date;
46 END
47 -- 10.定义存储过程结束
48 &
49
50
51
52
53 -- 2.如果存在存储过程getColumnCount则删除
54 DROP PROCEDURE IF EXISTS `getColumnCount` &
55 -- 3.定义存储过程，获取特定数据库表列的数量
56 -- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传出参数count_date整数类型，为数量)
57 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)
58 BEGIN
59 -- 4.声明变量
60 DECLARE $sqltext VARCHAR(1000);
61 -- 5.动态sql，把sql返回值放到@count_date中
62 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, '\';');
63 SET @sqlcounts := $sqltext;
64 -- 6.预编释，stmt预编释变量的名称
65 PREPARE stmt FROM @sqlcounts;
66 -- 7.执行SQL语句
67 EXECUTE stmt;
68 -- 8.释放资源
69 DEALLOCATE PREPARE stmt;
70 -- 9.获取动态SQL语句返回值
71 SET count_date = @count_date;
72 END
73 -- 10.定义存储过程结束
74 &
75
76
77 -- 2.如果存在存储过程getColumnInfo则删除
78 DROP PROCEDURE IF EXISTS `getColumnInfo` &
79 -- 3.定义存储过程，获取特定数据库表列的信息
80 -- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数column_info字符串类型，列信息；传出参数result_data字符串类型，信息)
81 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))
82 BEGIN
83 -- 4.声明变量
84 DECLARE $sqltext VARCHAR(1000);
85 -- 5.动态sql，把sql返回值放到@count_date中
86 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, '\';');
87 SET @sqlcounts := $sqltext;
88 -- 6.预编释，stmt预编释变量的名称
89 PREPARE stmt FROM @sqlcounts;
90 -- 7.执行SQL语句
91 EXECUTE stmt;
92 -- 8.释放资源
93 DEALLOCATE PREPARE stmt;
94 -- 9.获取动态SQL语句返回值
95 SET result_data = @column_info;
96 END
97 -- 10.定义存储过程结束
98 &
99
100 -- 11.如果存在存储过程comparison则删除
101 DROP PROCEDURE IF EXISTS `comparison` &
102 -- 12.定义存储过程，获取指定数据库关键词的表列名
103 -- (传入参数database_n字符串类型，数据库名；传入参数collation_n字符串类型，具体编码类型；传入参数key_name字符串类型，为关键字；传出参数tableColumnNames字符串类型，表列名)
104 CREATE DEFINER=`root`@`localhost` PROCEDURE comparison(IN database_1 CHAR(20), IN database_2 CHAR(20), IN column_info CHAR(50), OUT info TEXT)
105 BEGIN
106 -- 13.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型
107 DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2 CHAR(200);
108 DECLARE this_info, database_table_no TEXT DEFAULT '';
109 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
110 -- 14.定义游标结束标识，默认为0
111 DECLARE stopflag INT DEFAULT 0;
112 -- 15.定义游标，其实就是临时存储sql返回的集合
113 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
114 -- 16.游标结束就设置为1
115 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
116 CALL getdatabaseCount(database_1, database_count_1);
117 CALL getdatabaseCount(database_2, database_count_2);
118 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
119 -- 17.打开游标
120 OPEN sql_resoult;
121 -- 18.读取游标中数据，存储到指定变量
122 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
123 -- 19.没有结束继续往下走
124 WHILE (stopflag=0) DO
125 BEGIN
126 -- 20.判断数据库是否为输入的数据库名称，和，指定具体编码类型，和，不含.
127 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
128 -- 21.调用存储过程，获取特定表列关键词的数量
129 CALL getTableCount(database_2, table_name, resoult_count);
130 -- 22.如果数量不等于0，那么记录表列名
131 IF (resoult_count <> 0) THEN
132 CALL getColumnCount(database_2, table_name, column_name, resoult_count);
133 -- 23.拼接字符串，不可直接用传出变量设值
134 IF (resoult_count <> 0) THEN
135 CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
136 CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
137 -- 23.拼接字符串，不可直接用传出变量设值
138 IF (result_data_1 <> result_data_2) THEN
139 IF (this_info IS NULL OR this_info='') THEN
140 SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
141 ELSE
142 SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
143 END IF;
144 END IF;
145 ELSE
146 IF (this_info IS NULL OR this_info='') THEN
147 SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
148 ELSE
149 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
150 END IF;
151 END IF;
152 ELSE
153 IF (this_info IS NULL OR this_info='') THEN
154 SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
155 ELSE
156 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
157 END IF;
158 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
159 END IF;
160 ELSE
161 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
162 CALL getTableCount(database_1, table_name, resoult_count);
163 IF (resoult_count <> 0) THEN
164 CALL getColumnCount(database_1, table_name, column_name, resoult_count);
165 IF (resoult_count = 0) THEN
166 IF (this_info IS NULL OR this_info='') THEN
167 SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
168 ELSE
169 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
170 END IF;
171 END IF;
172 ELSE
173 IF (this_info IS NULL OR this_info='') THEN
174 SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
175 ELSE
176 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
177 END IF;
178 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
179 END IF;
180 END IF;
181 END IF;
182 -- 24.读取游标中数据，存储到指定变量。（和18一样）
183 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
184 END;
185 END WHILE;
186 -- 25.关闭游标
187 CLOSE sql_resoult;
188 ELSE
189 IF (database_count_1 = 0 AND database_count_2 = 0) THEN
190 SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');
191 ELSE
192 IF (database_count_1 = 0) THEN
193 SET this_info = CONCAT(database_1, '数据库不存在或为空数据库');
194 ELSE
195 SET this_info = CONCAT(database_2, '数据库不存在或为空数据库');
196 END IF;
197 END IF;
198 END IF;
199 -- 26.把数据放到传出参数
200 SET info=this_info;
201 END
202 -- 27.定义存储过程结束
203 &
204 -- 28.将mysql分隔符从&设置为;
205 DELIMITER ;
206 -- 29.设置变量
207 SET @database_1='my_test';
208 SET @database_2='my_test2';
209 SET @column_info='data_type';
210 SET @count='';
211 -- 30.调用存储过程
212 CALL comparison(@database_1, @database_2, @column_info, @count);
213 -- 31.打印
214 SELECT @count;
215 -- 32.如果存在存储过程则删除
216 DROP PROCEDURE IF EXISTS `comparison`;
2、整理：
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 EXECUTE stmt;
19 -- 8.释放资源
20 DEALLOCATE PREPARE stmt;
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 EXECUTE stmt;
42 -- 17.释放资源
43 DEALLOCATE PREPARE stmt;
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 EXECUTE stmt;
66 -- 26.释放资源
67 DEALLOCATE PREPARE stmt;
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 EXECUTE stmt;
90 -- 35.释放资源
91 DEALLOCATE PREPARE stmt;
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 FROM information_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 OPEN sql_resoult;
122 -- 46.读取游标中数据，存储到指定变量
123 FETCH sql_resoult INTO database_name, table_name;
124 -- 47.没有结束继续往下走
125 WHILE (stopflag=0) DO
126 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 INTO database_name, table_name;
157 END;
158 END WHILE;
159 -- 54.关闭游标
160 CLOSE sql_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 FROM information_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 OPEN sql_resoult;
203 -- 65.读取游标中数据，存储到指定变量
204 FETCH sql_resoult INTO database_name, table_name, column_name;
205 -- 66.没有结束继续往下走
206 WHILE (stopflag=0) DO
207 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 INTO database_name, table_name, column_name;
246 END;
247 END WHILE;
248 -- 73.关闭游标
249 CLOSE sql_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 FROM information_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 OPEN sql_resoult;
283 -- 84.读取游标中数据，存储到指定变量
284 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
285 -- 85.没有结束继续往下走
286 WHILE (stopflag=0) DO
287 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 INTO database_name, table_name, column_name, collation_name;
333 END;
334 END WHILE;
335 -- 93.关闭游标
336 CLOSE sql_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`;
3、无注释
1 DELIMITER &
2
3 DROP PROCEDURE IF EXISTS `getdatabaseCount` &
4 CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
5 BEGIN
6 DECLARE $sqltext VARCHAR(1000);
7 SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
8 SET @sqlcounts := $sqltext;
9 PREPARE stmt FROM @sqlcounts;
10 EXECUTE stmt;
11 DEALLOCATE PREPARE stmt;
12 SET count_date = @count_date;
13 END
14 &
15
16 DROP PROCEDURE IF EXISTS `getTableCount` &
17 CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
18 BEGIN
19 DECLARE $sqltext VARCHAR(1000);
20 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, '\';');
21 SET @sqlcounts := $sqltext;
22 PREPARE stmt FROM @sqlcounts;
23 EXECUTE stmt;
24 DEALLOCATE PREPARE stmt;
25 SET count_date = @count_date;
26 END
27 &
28
29
30 DROP PROCEDURE IF EXISTS `getColumnCount` &
31 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)
32 BEGIN
33 DECLARE $sqltext VARCHAR(1000);
34 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, '\';');
35 SET @sqlcounts := $sqltext;
36 PREPARE stmt FROM @sqlcounts;
37 EXECUTE stmt;
38 DEALLOCATE PREPARE stmt;
39 SET count_date = @count_date;
40 END
41 &
42
43
44 DROP PROCEDURE IF EXISTS `getColumnInfo` &
45 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))
46 BEGIN
47 DECLARE $sqltext VARCHAR(1000);
48 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, '\';');
49 SET @sqlcounts := $sqltext;
50 PREPARE stmt FROM @sqlcounts;
51 EXECUTE stmt;
52 DEALLOCATE PREPARE stmt;
53 SET result_data = @column_info;
54 END
55 &
56
57 DROP PROCEDURE IF EXISTS `comparisonTableExist` &
58 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
59 BEGIN
60 DECLARE database_name, table_name CHAR(200);
61 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
62 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
63 DECLARE stopflag INT DEFAULT 0;
64 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
65 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
66 CALL getdatabaseCount(database_1, database_count_1);
67 CALL getdatabaseCount(database_2, database_count_2);
68 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
69 OPEN sql_resoult;
70 FETCH sql_resoult INTO database_name, table_name;
71 WHILE (stopflag=0) DO
72 BEGIN
73 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
74 CALL getTableCount(database_2, table_name, resoult_count);
75 IF (resoult_count = 0) THEN
76 IF (this_info IS NULL OR this_info='') THEN
77 SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
78 ELSE
79 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
80 END IF;
81 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
82 END IF;
83 ELSE
84 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
85 CALL getTableCount(database_1, table_name, resoult_count);
86 IF (resoult_count = 0) THEN
87 IF (this_info IS NULL OR this_info='') THEN
88 SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
89 ELSE
90 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
91 END IF;
92 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
93 END IF;
94 END IF;
95 END IF;
96 FETCH sql_resoult INTO database_name, table_name;
97 END;
98 END WHILE;
99 CLOSE sql_resoult;
100 ELSE
101 IF (database_count_1 = 0 AND database_count_2 = 0) THEN
102 SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');
103 ELSE
104 IF (database_count_1 = 0) THEN
105 SET this_info = CONCAT(database_1, '数据库不存在或为空数据库');
106 ELSE
107 SET this_info = CONCAT(database_2, '数据库不存在或为空数据库');
108 END IF;
109 END IF;
110 END IF;
111 SET info=this_info;
112 END
113 &
114
115
116 DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
117 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
118 BEGIN
119 DECLARE database_name, table_name, column_name CHAR(200);
120 DECLARE this_info, database_table_no TEXT DEFAULT '';
121 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
122 DECLARE stopflag INT DEFAULT 0;
123 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
124 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
125 CALL getdatabaseCount(database_1, database_count_1);
126 CALL getdatabaseCount(database_2, database_count_2);
127 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
128 OPEN sql_resoult;
129 FETCH sql_resoult INTO database_name, table_name, column_name;
130 WHILE (stopflag=0) DO
131 BEGIN
132 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
133 CALL getTableCount(database_2, table_name, resoult_count);
134 IF (resoult_count <> 0) THEN
135 CALL getColumnCount(database_2, table_name, column_name, resoult_count);
136 IF (resoult_count = 0) THEN
137 IF (this_info IS NULL OR this_info='') THEN
138 SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
139 ELSE
140 SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
141 END IF;
142 END IF;
143 ELSE
144 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
145 END IF;
146 ELSE
147 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
148 CALL getTableCount(database_1, table_name, resoult_count);
149 IF (resoult_count <> 0) THEN
150 CALL getColumnCount(database_1, table_name, column_name, resoult_count);
151 IF (resoult_count = 0) THEN
152 IF (this_info IS NULL OR this_info='') THEN
153 SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
154 ELSE
155 SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
156 END IF;
157 END IF;
158 ELSE
159 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
160 END IF;
161 END IF;
162 END IF;
163 FETCH sql_resoult INTO database_name, table_name, column_name;
164 END;
165 END WHILE;
166 CLOSE sql_resoult;
167 END IF;
168 SET info=this_info;
169 END
170 &
171
172
173 DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
174 CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
175 BEGIN
176 DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);
177 DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
178 DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
179 DECLARE stopflag INT DEFAULT 0;
180 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
181 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
182 CALL getdatabaseCount(database_1, database_count_1);
183 CALL getdatabaseCount(database_2, database_count_2);
184 IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
185 OPEN sql_resoult;
186 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
187 WHILE (stopflag=0) DO
188 BEGIN
189 IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
190 CALL getTableCount(database_2, table_name, resoult_count);
191 IF (resoult_count <> 0) THEN
192 CALL getColumnCount(database_2, table_name, column_name, resoult_count);
193 IF (resoult_count <> 0) THEN
194 SET column_info = 'DATA_TYPE';
195 CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
196 CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
197 IF (result_data_1 <> result_data_2) THEN
198 IF (this_info IS NULL OR this_info='') THEN
199 SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
200 ELSE
201 SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
202 END IF;
203 END IF;
204 SET column_info = 'CHARACTER_SET_NAME';
205 CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
206 CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
207 IF (result_data_1 <> result_data_2) THEN
208 IF (this_info IS NULL OR this_info='') THEN
209 SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
210 ELSE
211 SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
212 END IF;
213 END IF;
214 END IF;
215 ELSE
216 SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
217 END IF;
218 ELSE
219 IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
220 CALL getTableCount(database_1, table_name, resoult_count);
221 IF (resoult_count = 0) THEN
222 SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
223 END IF;
224 END IF;
225 END IF;
226 FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
227 END;
228 END WHILE;
229 CLOSE sql_resoult;
230 END IF;
231 SET info=this_info;
232 END
233 &
234 DELIMITER ;
235 SET @database_1='my_test3';
236 SET @database_2='my_test4';
237 SET @tableExistInfo='';
238 SET @columnExistInfo='';
239 SET @columnInfo='';
240 CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);
241 CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);
242 CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);
243 SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);
244 SELECT @info;
245 DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;
246 DROP PROCEDURE IF EXISTS `comparisonColumnExist`;
247 DROP PROCEDURE IF EXISTS `comparisonTableExist`;
248 DROP PROCEDURE IF EXISTS `getColumnInfo`;
249 DROP PROCEDURE IF EXISTS `getColumnCount`;
250 DROP PROCEDURE IF EXISTS `getTableCount`;
251 DROP PROCEDURE IF EXISTS `getdatabaseCount`;
